Sakai 2.8.x – Stats SQL queries
Posted On March 10, 2013
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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"; |