...
- 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:
No Format 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.
- 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(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) AND g.ROLE_KEY = r.ROLE_KEY AND r.ROLE_NAME IN ('Collaborator', 'Guest', 'Instructor', 'Student', 'Teaching Assistant', 'access', 'maintain')
- 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:
...
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.