Versions Compared

Old Version 24

changes.mady.by.user Mike Wenk

Saved on

compared with

New Version 25

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:
      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.
  • 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(*) 
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.

...

  • Each individual time will use the below query. Please see Requirements for daily hourly usage statistic for info on the user interface.
    • Query:
      Code Block
      SELECT
   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,'GMT', 'PDT'), 'HH24') = ? OR
    (TO_CHAR( NEW_TIME(SESSION_END,'GMT', 'PDT'), 'HH24') = ?  OR 14  AND
    SESSION_END > SESSION_START) 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. At the time of this writing, there is data in which the session end is occurring before the session start. The above query works around it, counting it only in the session start hour.
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.

...