Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 13 Next »

UCD Stats

This page will describe the stats and how the stats are derived from the data.

The stats tool uses data from both the internal database and the external database. The external database is referenced via an oracle database link. The link used is called sakai_ext.

Stat Queries

General Statistics

  • Smartsite Logins to Date
    • Query: 
       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.
  • Smartsite Logins in the Past 24 Hours
    • Query: 
       SELECT COUNT(*) FROM SAKAI_SESSION where SESSION_START!=SESSION_END AND SESSION_START > (SYSDATE - 1)
    • Notes: Same as the above with the caveat that the session must have started in the past 24 hours, and it does not matter when the session ended.
  • Course Sites
    • Query: 
       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.
  • Project Sites
    • Query: 
       SELECT COUNT(*) from SAKAI_SITE WHERE TYPE='project' AND PUBLISHED=1
    • Notes: none
  • Training Sites
    • Query: 
      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
  • Unique Users
    • Query: 
      SELECT COUNT(DISTINCT g.USER_ID) FROM SAKAI_REALM_RL_GR g , SAKAI_REALM_ROLE r 
WHERE USER_ID NOT IN (SELECT USER_ID FROM SAKAI_USER)
    • 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.

Breakdown of Users

  • Instructor Users
    • Query: 
      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 ('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: 
      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: 
      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 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 = rl.ROLE_KEY AND 
rl.ROLE_NAME IN ('Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain')
    • 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)
    • Query: 
      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.
Saving Data

The quartz job UCD Stats Job is what is used to save off the stats at any point to the database. Because the query "Logins in the Past 24 Hours" runs for exactly one day the quartz job should run once per day at the same time every day. The data from each query above is saved into a table: SMARTSITE_STATS. This quartz job needs to be setup manually.

To do

The following is what I have to do:

  • Place UI around SMARTSITE_STATS DB and refactor tool to use that UI rather than immediate querying which is what it is doing now.
  • Allow the queries to be date bound.
Questions and Assumptions

We have the following questions:

1) For project and course sites, should published and/or joinable flag be taken into account?
2) Assumption: Joinable should not be taken into account for course sites.
3) Assumption: Test accounts can show up in unique user counts.

  • No labels