...
5. For this documentation, please add the description for the Mothra Codes so we're sure what they mean. E.g., "mp.User_Type in ('U', 'R', 'P', 'C') . . ." Note that Instructor and staff include the code 'M' right? Then too staff and students both include 'R'. Without the descriptions spelled out here, it's hard to know at a glance what this means.
- As of May 2009, the codes that might appear in the mothra_person.user_type field are:
C
Fac, Staff, Stu
D
Davis Community Net
E
Visiting Staff
F
Faculty
G
Visiting Student
H
Visiting Faculty
L
Summer Advising
M
Faculty, Staff
O
Applicant
P
Faculty, Student
R
Staff, Student
S
Staff
U
Student
W
Gone, forwarding
X
External
6. I have attached one file here with two pages. The first page shows hourly usage statistics with a date heading. It also includes only the hours of the day for which the real world hours (wall clock) have occurred. E.g., if the hourly display is showing statistics for today's usage, it would include only those hours for which we have completed in the real world (wall clock). If the hourly statistic is showing statistics for days in the past, it would show 24 hours.
...
- 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.
- Query:
- 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.
- Query:
- 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.
- Query:
- Project Sites
- Query:
No Format SELECT COUNT(*) from SAKAI_SITE WHERE TYPE='project' AND PUBLISHED=1
- Notes: none
- Query:
- 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
- Query:
- Unique Users
- 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: 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.
- Query:
...
- 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:
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.
- Query:
- 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 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 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. 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.
- Query:
- Undergraduate Student Users
- Notes: See Graduate Student Users above
- Total Unique Users
- Notes: See Unique Users query in General Statistics section
...