...
- Instructor Users
- Query:
No Format 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 ('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.
- Query:
- Staff Users
- Query:
No Format 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.
- Query:
- Graduate Student Users
- Query:
No Format SELECT UNIQUE mp.kerberosKERBEROS_nameNAME, case when MAX(bl.level_code = 'UG' then 'Undergrad' else 'Graduate' end AS STUDENT_TYPE FROM ) 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 GROUP BY mp.KERBEROS_NAME
- Notes: This query requires post processing. It generates a list of kerberos names and a code(Graduate/UndergraduateUG 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 student/faculty 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. This needs to be corrected.
- Query:
- Undergraduate Student Users
- Notes: See Graduate Student Users above
- Total Unique Users
- Notes: See Unique Users query in General Statistics section
...