Versions Compared

Old Version 21

changes.mady.by.user Former user

Saved on

compared with

New Version 22

changes.mady.by.user Mike Wenk

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • 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.
  • Smartsite Logins in the Past 24 Hours
    • Query:
      No Formatcode
      SELECT
	COUNT(*1) 
FROM
	SAKAI_SESSION 
WHERE
where	( SESSION_START!=SESSION_END AND 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, and it does not matter when the session ended.
  • 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.
  • Project Sites
    • Query:
      No Format
      SELECT COUNT(*) from SAKAI_SITE WHERE TYPE='project' AND PUBLISHED=1


    • Notes: none
  • 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
  • Unique Users
    • Query:
      No Format
      SELECT COUNT(DISTINCT g.USER_ID) FROM SAKAI_REALM_RL_GR g
WHERE NOT exists (SELECT 1 FROM SAKAI_USER where user_id = g.user_id)


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

...

  • Each individual time will use the below query. Please see Requirements for daily hourly usage statistic for info on the user interface.
    • Query:
      No Formatcode
      SELECT
count(*   COUNT(1)
FROM
    SAKAI_SESSION
WHERE
    (
        SESSION_START!= > (SYSDATE - 1) OR
        SESSION_END > (SYSDATE - 1)
    ) AND

    (
-- Sessions starting or ending in this hour
    TO_CHAR( NEW_TIME(SESSION_START > (SYSDATE - 1) AND ,'GMT', 'PDT'), 'HH24') = ? OR
    TO_CHAR( NEW_TIME(SESSION_END,'GMT', 'PDT'), 'HH24') = ?  OR
        (
        -- Sessions starting before and ending after
            TO_CHAR( NEW_TIME(SESSION_START,'GMT', 'PDT'), 'HH24') < ? AND
            TO_CHAR( NEW_TIME(SESSION_END,'GMT', 'PDT'), 'HH24') > ?
        ) OR
        (
            TO_CHAR( NEW_TIME(SESSION_START,'GMT', 'PDT'), 'HH24') <= ? AND
            SESSION_START = SESSION_END
        )

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

...