Page Comparison

...

• Smartsite Logins to Date
  • Query:

    No Format
    SELECT COUNT(*) FROM SAKAI_SESSION where 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:

    No Format
    SELECT COUNT(*) FROM SAKAI_SESSION where SESSION_START!=SESSION_END AND SESSION_START > (SYSDATE - 1)

  • Notes: Same as the above with the caveat that the session must have started in the past 24 hours, and it does not matter when the session ended.
• 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_GR g , SAKAI_REALM_ROLE r WHERE USER_ID NOT IN (SELECT USER_ID FROM SAKAI_USER)

...

...

...

• • exists (SELECT 1 FROM SAKAI_USER where user_id = g.user_id)

...

* There are a no performance increases with second version; it is just better form. The second sql can be easily extended, for instance, for multicolumns.

• • 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

    select count(UNIQUE mp.kerberos_name) from FROM SAKAI_REALM_RL_GR gr, SAKAI_REALM_ROLE rl, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp where gr.ROLE_KEY = rl.ROLE_KEY AND rl.ROLE_NAME IN ('Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain'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 ('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:

    No Format

    selectSELECT count(UNIQUE mp.kerberos_name) fromFROM SAKAI_REALM_RL_GR gr, SAKAI_REALM_ROLE rl, SAKAI_USER_ID_MAP um, MOTHRA_PERSON@SAKAI_EXT mp WHERE where gr.ROLE_KEY = rl.ROLE_KEY AND rl.ROLE_NAME IN ('Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain'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 when bl.level_code = 'UG' then 'Undergrad' else 'Graduate' 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, SAKAI_REALM_ROLE rl WHERE NOT exists (SELECT 1 FROM SAKAI_USER where user_id = gr.user_id) 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 gr.ROLE_KEY = rl.ROLE_KEY AND rl.ROLE_NAME IN ('Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain')

  • Notes: This query requires post processing. It generates a list of kerberos names and a code(Graduate/Undergraduate) 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 student/faculty overlap) .
• Undergraduate Student Users
  • Notes: See Graduate Student Users above
• Total Unique Users
  • Notes: See Unique Users query in General Statistics section

...