Versions Compared

Old Version 23

changes.mady.by.user Mike Wenk

Saved on

compared with

New Version 24

changes.mady.by.user Mike Wenk

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Smartsite Logins to Date
    • Query:
      No Format
      SELECT
	COUNT(*) 
FROM
	SAKAI_SESSION 
whereWHERE
	SESSION_START!=SESSION_END


    • Notes: This tracks sessions/users that are complete. Ie, a logout or timeout must have occurred for it to show up here.
  • Smartsite Logins in the Past 24 Hours
    • Query:
      Code Block
      SELECT
	COUNT(1) 
FROM
	SAKAI_SESSION 
WHERE
	( SESSION_START > (SYSDATE - 1) OR
	SESSION_END > (SYSDATE - 1) ) AND
	SESSION_START != SESSION_END
    • Notes: Same as the above with the caveat that the session must have started or have ended in the past 24 hours.
  • Course Sites
    • Query:
      No Format
      SELECT
	COUNT(DISTINCT TITLE) 
FROM
	SAKAI_SITE 
WHERE
	TYPE='course' AND
	SITE_ID NOT IN (SELECT
						SITE_ID 
					FROM
						SAKAI_SITE_PROPERTY 
					WHERE
						NAME IN ('UCDADMIN:Site', 'UCDADMIN:Template')
	)
    • Notes: This query takes into account and removes the sites that are training sites.
  • Project Sites
    • Query:
      No Format
      SELECT COUNT(*) from SAKAI_SITE WHERE TYPE='project' AND PUBLISHED=1
    • Notes: none
  • Training Sites
    • Query:
      No Format
      SELECT
	COUNT(*) 
FROM
	SAKAI_SITE 
WHERE
	(TYPE='training-course' OR
	TYPE='training-project') OR
	SITE_ID IN (SELECT
					SITE_ID 
				FROM
					SAKAI_SITE_PROPERTY 
				WHERE
					NAME='UCDADMIN:Site'
	)


    • Notes: None
  • Unique Users
    • Query:
      No Format
      SELECT
	COUNT(DISTINCT g.USER_ID)*) 
FROM
	SAKAI_REALM_RL_GRSITE g
WHERE NOT exists
	(TYPE='training-course' OR
	TYPE='training-project') OR
	SITE_ID IN (SELECT
1 FROM SAKAI_USER where user_id = g.user_id					SITE_ID 
				FROM
					SAKAI_SITE_PROPERTY 
				WHERE
					NAME='UCDADMIN:Site'
	)


    • Notes: This query returns provided users that have a meaningful(ie, not automatic) role in a site. In some cases this is only the user's My Workspace site.

...

  • Instructor Users
    • Query:
      No Format
      selectSELECT
count	COUNT(UNIQUE mp.kerberos_name) 
FROM
	SAKAI_REALM_RL_GR gr, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp 
WHERE
	NOT existsEXISTS (SELECT
					1 
				FROM
					SAKAI_USER 
where 				WHERE
					user_id = gr.user_id
	)
and	AND
	gr.USER_ID = um.USER_ID andAND
	um.EID = mp.MOTHRAID andAND
	mp.USER_TYPE IN ('F','M','P','C')
    • Notes: This query pulls users out of the realm table and joins them to specific USER_TYPE(s) in the mothra person table. The unique clause in the count is required as a single user may be in multiple site realms.
  • Staff Users
    • Query:
      Code Block
      SELECT
	COUNT(UNIQUE mp.kerberos_name) 
FROM
	SAKAI_REALM_RL_GR gr, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp 
WHERE
	NOT EXISTS (SELECT
					1 
				FROM
					SAKAI_USER 
				WHERE
					user_id = gr.user_id 
	)
	AND
	gr.USER_ID = um.USER_ID AND
	um.EID = mp.MOTHRAID AND
	mp.USER_TYPE IN ('S', 'M', 'R', 'C')


    • Notes: This query pulls users out of the realm table and joins them to specific USER_TYPE(s) relating to staff(Staff, Staff/Instructor, Staff/Student, and Staff/Student/Instructor) in the mothra person table. The unique clause in the count is required as a single user may be in multiple site realms.
  • Graduate Student Users
    • Query:
      No Format
      SELECT
	UNIQUE     mp.kerberos_name, 
	CASE 
 case when		WHEN bl.level_code = 'UG' 
then		THEN 'Undergrad' 
else		ELSE 'Graduate' end
	END AS STUDENT_TYPE 
FROM
    	mothra_person@sakai_ext mp, banner_level@sakai_ext bl,
    SAKAI_REALM_RL_GR gr, 
	SAKAI_USER_ID_MAP um 
WHERE
    	NOT existsEXISTS (SELECT
					1 
				FROM
					SAKAI_USER 
where 				WHERE
					user_id = gr.user_id
	)
and
    	AND
	mp.banner_pidm = bl.pidm AND
    	mp.mothraid = um.EID AND
    	mp.USER_TYPE IN ('U','R','P','C') AND
    	um.USER_ID = gr.USER_ID and
    AND
	bl.term_code=(select max	SELECT
						MAX(term_code) from 
					FROM
						BANNER_LEVEL@SAKAI_EXT where  
					WHERE
						pidm=mp.banner_pidm
	)


    • Notes: This query requires post processing. It generates a list of kerberos names and a code(UG stands for undergraduates, all other codes are grad students) The post processing totals each type and assigns it to its particular bucket. It also joins in the USER_TYPE for students(and as you can see there is potential for overlap.)
    • Issue: Previous to this query, we had a scenario where a student was in the banner level code as both a undergrad and a grad. The above query has been corrected for this issue. One last thing remains, is to verify that the assumption that the double counting is truly an issue.
  • Undergraduate Student Users
    • Notes: See Graduate Student Users above
  • Total Unique Users
    • Notes: See Unique Users query in General Statistics section

...