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

  • 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

User Usage

• (Each time) individual time will use the below query. Please see Requirements for daily hourly usage statistic for info on the user interface.
  • Query:

    No Format
    SELECT count(*) FROM SAKAI_SESSION WHERE SESSION_START!= SESSION_END AND SESSION_START > (SYSDATE - 1) AND TO_CHAR( NEW_TIME(SESSION_START,'GMT', 'PDT'), 'HH24') = ?

  • Notes: This is just a variation of Logins in the Past 24 Hours from General Statistics. All it does in additional is extract the hour in 24 hour clock mode(after converting due to the time being in GMT and not PDT.) The code loops through this for each hour of the day and presents the table as shown.

...