{"id":468,"date":"2013-03-10T09:47:39","date_gmt":"2013-03-10T09:47:39","guid":{"rendered":"https:\/\/osama.alkadi.net\/?p=468"},"modified":"2018-09-10T10:22:30","modified_gmt":"2018-09-10T10:22:30","slug":"sakai-2-8-x-stats-sql-queries","status":"publish","type":"post","link":"https:\/\/osama.alkadi.net\/?p=468","title":{"rendered":"Sakai 2.8.x &#8211; Stats SQL queries"},"content":{"rendered":"<pre class=\"nums:false lang:mysql decode:true\">\/*Number of unique logins in the last 365 days with ANU alias*\/ \r\nSELECT&amp;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\r\nAND \r\nsakai_user_id_map.eid LIKE 'u%' \r\nAND \r\nsst_userstats.login_date BETWEEN '2016-01-24' \r\nAND \r\n'2017-01-023' \r\n\r\n\/* Number of users with more than 15 login in the past 365 days matched with uid*\/\r\nSELECT Count(session_id) AS c,\r\n       SESSION_USER, \r\n       sakai_user_id_map.`eid` &amp;nbsp;FROM sakai_session INNER JOIN sakai_user_id_map ON sakai_session.SESSION_USER = sakai_user_id_map.user_id WHERE (\r\n  session_start &gt; date_sub(curdate(), interval 365 day) \r\n) \r\nAND \r\nsakai_user_id_map.eid LIKE 'u%' GROUP BY SESSION_USER HAVING c &gt; 15 ORDER BY c; \r\n\r\n\/*Active project sites in the past 7 days*\/\r\nSELECT DISTINCT s.site_id, \r\n                title \r\nFROM            sst_sitevisits s \r\nJOIN            sakai_site ss \r\nON              ( \r\n                                ss.site_id = s.site_id) \r\nWHERE           ss.type = 'project' \r\nAND             s.visits_date &gt; date_sub(Curdate(), interval 1 week) \r\n\r\n\/*Number of project site visits in the past 7 days*\/\r\nSELECT Sum(s.total_visits) \r\nFROM   sst_sitevisits s \r\nJOIN   sakai_site ss \r\nON     ( \r\n              ss.site_id = s.site_id) \r\nWHERE  ss.type = 'project' \r\nAND    s.visits_date &gt; date_sub(Curdate(), interval 1 week) #total number OF resources IN the past 28 weeks.SELECT&amp;nbsp;s.resource_count, ss.title FROM sst_resources s JOIN sakai_site ss ON (\r\n  ss.site_id = s.site_id \r\n) \r\nWHERE s.`resource_action` = 'new' \r\nAND \r\ns.resource_date &gt; date_sub(curdate(), interval 28 week) \r\n\r\n\/*Number of unique logins in the last 7 days*\/\r\nSELECT Count(1)&amp;nbsp;FROM ( \r\n                SELECT DISTINCT SESSION_USER \r\n                FROM            sakai_session \r\n                WHERE           session_start &gt; date_sub(curdate(), interval 7 day) \r\n                AND             session_server NOT LIKE '%websrv%') \r\n\/*Number of logins in the last month*\/\r\nSELECT Count(1) \r\nFROM   ( \r\n              SELECT SESSION_USER \r\n              FROM   sakai_session \r\n              WHERE  session_start &gt; date_sub(Curdate(), interval 31 day) \r\n              AND    session_server NOT LIKE '%websrv%') \r\n\r\n\/*Number of users that have logged on at least once*\/\r\nSELECT Count(DISTINCT s.SESSION_USER)&amp;nbsp;FROM sakai_session s WHERE session_server NOT LIKE '%websrv%'\r\n\r\n\/*Active courses in the last week*\/\r\nSELECT Count(DISTINCT(s.site_id)) \r\nFROM   sst_sitevisits s \r\nJOIN   sakai_site ss \r\nON     ( \r\n              ss.site_id = s.site_id) \r\nWHERE  ss.type = 'course' \r\nAND    s.visits_date &gt; date_sub(Curdate(), interval 1 week) \r\n\r\n\/*Number of course visits in the past 7 days*\/\r\nSELECT Sum(s.total_visits) \r\nFROM   sst_sitevisits s \r\nJOIN   sakai_site ss \r\nON     ( \r\n              ss.site_id = s.site_id) \r\nWHERE  ss.type = 'course' \r\nAND    s.visits_date &gt; date_sub(Curdate(), interval 1 week) #active project sites IN the past 7 daysselect count(DISTINCT(s.site_id))\r\nFROM   sst_sitevisits s \r\nJOIN   sakai_site ss \r\nON     ( \r\n              ss.site_id = s.site_id) \r\nWHERE  ss.type = 'project' \r\nAND    s.visits_date &gt; date_sub(curdate(), interval 1 week) \r\n\r\n\/*Number of portfolio visits in the last week*\/\r\nSELECT Sum(s.total_visits) \r\nFROM   sst_sitevisits s \r\nJOIN   sakai_site ss \r\nON     ( \r\n              ss.site_id = s.site_id) \r\nWHERE  ss.type = 'portfolio' \r\nAND    s.visits_date &gt; date_sub(Curdate(), interval 1 week) \r\n\r\n\/*Total Number of Project Sites*\/\r\nSELECT Count(0) \r\nFROM   sakai_site ss \r\nWHERE  ss.type = 'project'\r\n\r\n\/*General Stats: Total number of users, guests, sits, projects and workspaces*\/\r\nSELECT \"Total Users\"  AS property,\r\n       Count(user_id) AS amount \r\nFROM   sakai_user \r\nUNION \r\nSELECT \"System Users\" AS property, \r\n       Count(user_id) AS amount \r\nFROM   sakai_user \r\nWHERE  type='system' \r\nUNION \r\nSELECT \"Registered users with e-mails\" AS property, \r\n       Count(user_id)                  AS amount \r\nFROM   sakai_user \r\nWHERE  email IS NOT NULL \r\nAND    type='registered' \r\nUNION \r\nSELECT \"Registered users without e-mails\" AS property, \r\n       Count(user_id)                     AS amount \r\nFROM   sakai_user \r\nWHERE  email IS NULL \r\nAND    type='registered' \r\nUNION \r\nSELECT \"Guests with e-mails\" AS property, \r\n       Count(user_id)        AS amount \r\nFROM   sakai_user \r\nWHERE  email IS NOT NULL \r\nAND    type='guest' \r\nUNION \r\nSELECT \"Guests without e-mails\" AS property, \r\n       Count(user_id)           AS amount \r\nFROM   sakai_user \r\nWHERE  email IS NULL \r\nAND    type='guest' \r\nUNION \r\nSELECT \"--------------------------------\" AS property, \r\n       0                                  AS amount&amp;nbsp;UNION \r\nSELECT \"Total sites\"  AS property, \r\n       count(site_id) AS amount \r\nFROM   sakai_site \r\nUNION \r\nSELECT \"Total Projects\" AS property, \r\n       count(site_id)   AS amount \r\nFROM   sakai_site \r\nWHERE  type=\"project\" \r\nUNION \r\nSELECT \"Total My Workspaces\" AS property, \r\n       count(site_id)        AS amount \r\nFROM   sakai_site \r\nWHERE  title=\"My Workspace\";<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>\/*Number of unique logins in the last 365 days with ANU alias*\/ SELECT&amp;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 &#8216;u%&#8217; AND sst_userstats.login_date BETWEEN &#8216;2016-01-24&#8217; AND &#8216;2017-01-023&#8217; \/* 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` &amp;nbsp;FROM sakai_session<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-468","post","type-post","status-publish","format-standard","hentry","category-miscellaneous"],"_links":{"self":[{"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/posts\/468","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=468"}],"version-history":[{"count":10,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/posts\/468\/revisions"}],"predecessor-version":[{"id":478,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=\/wp\/v2\/posts\/468\/revisions\/478"}],"wp:attachment":[{"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=468"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=468"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/osama.alkadi.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=468"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}