Page Comparison

...

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.

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.

Graduate Student Users

Query:

No Format

SELECT UNIQUE 
    mp.KERBEROSkerberos_NAME, MAX(name, 
    case when bl.level_code) FROM 
 = '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
   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 and 
    bl.term_code=(select max(term_code) from BANNER_LEVEL@SAKAI_EXT 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. This needs to be corrected. 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

...

Versions Compared

Old Version 18

New Version 19

Key