Sakai 2.8.x – Stats SQL queries

Posted On March 10, 2013

/*Number of unique logins in the last 365 days with ANU alias*/ 
SELECT&nbsp;sakai_user_id_map.eid FROM sakai_user_id_map INNER JOIN sst_userstats ON sakai_user_id_map.user_id=sst_userstats.user_id
AND 
sakai_user_id_map.eid LIKE 'u%' 
AND 
sst_userstats.login_date BETWEEN '2016-01-24' 
AND 
'2017-01-023' 

/* Number of users with more than 15 login in the past 365 days matched with uid*/
SELECT Count(session_id) AS c,
       SESSION_USER, 
       sakai_user_id_map.`eid` &nbsp;FROM sakai_session INNER JOIN sakai_user_id_map ON sakai_session.SESSION_USER = sakai_user_id_map.user_id WHERE (
  session_start > date_sub(curdate(), interval 365 day) 
) 
AND 
sakai_user_id_map.eid LIKE 'u%' GROUP BY SESSION_USER HAVING c > 15 ORDER BY c; 

/*Active project sites in the past 7 days*/
SELECT DISTINCT s.site_id, 
                title 
FROM            sst_sitevisits s 
JOIN            sakai_site ss 
ON              ( 
                                ss.site_id = s.site_id) 
WHERE           ss.type = 'project' 
AND             s.visits_date > date_sub(Curdate(), interval 1 week) 

/*Number of project site visits in the past 7 days*/
SELECT Sum(s.total_visits) 
FROM   sst_sitevisits s 
JOIN   sakai_site ss 
ON     ( 
              ss.site_id = s.site_id) 
WHERE  ss.type = 'project' 
AND    s.visits_date > date_sub(Curdate(), interval 1 week) #total number OF resources IN the past 28 weeks.SELECT&nbsp;s.resource_count, ss.title FROM sst_resources s JOIN sakai_site ss ON (
  ss.site_id = s.site_id 
) 
WHERE s.`resource_action` = 'new' 
AND 
s.resource_date > date_sub(curdate(), interval 28 week) 

/*Number of unique logins in the last 7 days*/
SELECT Count(1)&nbsp;FROM ( 
                SELECT DISTINCT SESSION_USER 
                FROM            sakai_session 
                WHERE           session_start > date_sub(curdate(), interval 7 day) 
                AND             session_server NOT LIKE '%websrv%') 
/*Number of logins in the last month*/
SELECT Count(1) 
FROM   ( 
              SELECT SESSION_USER 
              FROM   sakai_session 
              WHERE  session_start > date_sub(Curdate(), interval 31 day) 
              AND    session_server NOT LIKE '%websrv%') 

/*Number of users that have logged on at least once*/
SELECT Count(DISTINCT s.SESSION_USER)&nbsp;FROM sakai_session s WHERE session_server NOT LIKE '%websrv%'

/*Active courses in the last week*/
SELECT Count(DISTINCT(s.site_id)) 
FROM   sst_sitevisits s 
JOIN   sakai_site ss 
ON     ( 
              ss.site_id = s.site_id) 
WHERE  ss.type = 'course' 
AND    s.visits_date > date_sub(Curdate(), interval 1 week) 

/*Number of course visits in the past 7 days*/
SELECT Sum(s.total_visits) 
FROM   sst_sitevisits s 
JOIN   sakai_site ss 
ON     ( 
              ss.site_id = s.site_id) 
WHERE  ss.type = 'course' 
AND    s.visits_date > date_sub(Curdate(), interval 1 week) #active project sites IN the past 7 daysselect count(DISTINCT(s.site_id))
FROM   sst_sitevisits s 
JOIN   sakai_site ss 
ON     ( 
              ss.site_id = s.site_id) 
WHERE  ss.type = 'project' 
AND    s.visits_date > date_sub(curdate(), interval 1 week) 

/*Number of portfolio visits in the last week*/
SELECT Sum(s.total_visits) 
FROM   sst_sitevisits s 
JOIN   sakai_site ss 
ON     ( 
              ss.site_id = s.site_id) 
WHERE  ss.type = 'portfolio' 
AND    s.visits_date > date_sub(Curdate(), interval 1 week) 

/*Total Number of Project Sites*/
SELECT Count(0) 
FROM   sakai_site ss 
WHERE  ss.type = 'project'

/*General Stats: Total number of users, guests, sits, projects and workspaces*/
SELECT "Total Users"  AS property,
       Count(user_id) AS amount 
FROM   sakai_user 
UNION 
SELECT "System Users" AS property, 
       Count(user_id) AS amount 
FROM   sakai_user 
WHERE  type='system' 
UNION 
SELECT "Registered users with e-mails" AS property, 
       Count(user_id)                  AS amount 
FROM   sakai_user 
WHERE  email IS NOT NULL 
AND    type='registered' 
UNION 
SELECT "Registered users without e-mails" AS property, 
       Count(user_id)                     AS amount 
FROM   sakai_user 
WHERE  email IS NULL 
AND    type='registered' 
UNION 
SELECT "Guests with e-mails" AS property, 
       Count(user_id)        AS amount 
FROM   sakai_user 
WHERE  email IS NOT NULL 
AND    type='guest' 
UNION 
SELECT "Guests without e-mails" AS property, 
       Count(user_id)           AS amount 
FROM   sakai_user 
WHERE  email IS NULL 
AND    type='guest' 
UNION 
SELECT "--------------------------------" AS property, 
       0                                  AS amount&nbsp;UNION 
SELECT "Total sites"  AS property, 
       count(site_id) AS amount 
FROM   sakai_site 
UNION 
SELECT "Total Projects" AS property, 
       count(site_id)   AS amount 
FROM   sakai_site 
WHERE  type="project" 
UNION 
SELECT "Total My Workspaces" AS property, 
       count(site_id)        AS amount 
FROM   sakai_site 
WHERE  title="My Workspace";

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

/*Number of unique logins in the last 365 days with ANU alias*/

SELECT sakai_user_id_map.eid FROM sakai_user_id_map INNER JOIN sst_userstats ON sakai_user_id_map.user_id=sst_userstats.user_id

AND

sakai_user_id_map.eid LIKE 'u%'

AND

sst_userstats.login_date BETWEEN '2016-01-24'

AND

'2017-01-023'

/* Number of users with more than 15 login in the past 365 days matched with uid*/

SELECT Count(session_id) AS c,

SESSION_USER,

sakai_user_id_map.`eid`  FROM sakai_session INNER JOIN sakai_user_id_map ON sakai_session.SESSION_USER = sakai_user_id_map.user_id WHERE (

session_start > date_sub(curdate(), interval 365 day)

)

AND

sakai_user_id_map.eid LIKE 'u%' GROUP BY SESSION_USER HAVING c > 15 ORDER BY c;

/*Active project sites in the past 7 days*/

SELECT DISTINCT s.site_id,

title

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'project'

AND s.visits_date > date_sub(Curdate(), interval 1 week)

/*Number of project site visits in the past 7 days*/

SELECT Sum(s.total_visits)

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'project'

AND s.visits_date > date_sub(Curdate(), interval 1 week) #total number OF resources IN the past 28 weeks.SELECT s.resource_count, ss.title FROM sst_resources s JOIN sakai_site ss ON (

ss.site_id = s.site_id

)

WHERE s.`resource_action` = 'new'

AND

s.resource_date > date_sub(curdate(), interval 28 week)

/*Number of unique logins in the last 7 days*/

SELECT Count(1) FROM (

SELECT DISTINCT SESSION_USER

FROM sakai_session

WHERE session_start > date_sub(curdate(), interval 7 day)

AND session_server NOT LIKE '%websrv%')

/*Number of logins in the last month*/

SELECT Count(1)

FROM (

SELECT SESSION_USER

FROM sakai_session

WHERE session_start > date_sub(Curdate(), interval 31 day)

AND session_server NOT LIKE '%websrv%')

/*Number of users that have logged on at least once*/

SELECT Count(DISTINCT s.SESSION_USER) FROM sakai_session s WHERE session_server NOT LIKE '%websrv%'

/*Active courses in the last week*/

SELECT Count(DISTINCT(s.site_id))

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'course'

AND s.visits_date > date_sub(Curdate(), interval 1 week)

/*Number of course visits in the past 7 days*/

SELECT Sum(s.total_visits)

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'course'

AND s.visits_date > date_sub(Curdate(), interval 1 week) #active project sites IN the past 7 daysselect count(DISTINCT(s.site_id))

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'project'

AND s.visits_date > date_sub(curdate(), interval 1 week)

/*Number of portfolio visits in the last week*/

SELECT Sum(s.total_visits)

FROM sst_sitevisits s

JOIN sakai_site ss

ON (

ss.site_id = s.site_id)

WHERE ss.type = 'portfolio'

AND s.visits_date > date_sub(Curdate(), interval 1 week)

/*Total Number of Project Sites*/

SELECT Count(0)

FROM sakai_site ss

WHERE ss.type = 'project'

/*General Stats: Total number of users, guests, sits, projects and workspaces*/

SELECT "Total Users" AS property,

Count(user_id) AS amount

FROM sakai_user

UNION

SELECT "System Users" AS property,

Count(user_id) AS amount

FROM sakai_user

WHERE type='system'

UNION

SELECT "Registered users with e-mails" AS property,

Count(user_id) AS amount

FROM sakai_user

WHERE email IS NOT NULL

AND type='registered'

UNION

SELECT "Registered users without e-mails" AS property,

Count(user_id) AS amount

FROM sakai_user

WHERE email IS NULL

AND type='registered'

UNION

SELECT "Guests with e-mails" AS property,

Count(user_id) AS amount

FROM sakai_user

WHERE email IS NOT NULL

AND type='guest'

UNION

SELECT "Guests without e-mails" AS property,

Count(user_id) AS amount

FROM sakai_user

WHERE email IS NULL

AND type='guest'

UNION

SELECT "--------------------------------" AS property,

0 AS amount UNION

SELECT "Total sites" AS property,

count(site_id) AS amount

FROM sakai_site

UNION

SELECT "Total Projects" AS property,

count(site_id) AS amount

FROM sakai_site

WHERE type="project"

UNION

SELECT "Total My Workspaces" AS property,

count(site_id) AS amount

FROM sakai_site

WHERE title="My Workspace";

Add a Comment