Versions Compared

Old Version 3

changes.mady.by.user Mike Wenk

Saved on

compared with

New Version 4

changes.mady.by.user Mike Wenk

Saved on

Key

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

...

  • Instructor Users
    • Query:
      No Format
       select count(UNIQUE mp.kerberos_name) 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 (3,5,6,8,9,10,30) and'Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain') 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
      select count(UNIQUE mp.kerberos_name) 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') 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 WHERE 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 IN (3,5,6,8,9,10,30)
    • 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

...