Sakai Related Statistics

From UFP-UV Sakai Wiki

Jump to: navigation, search

Contents


Sites statistics

-- 1) Total ano lectivo 06/07 (2006-09-01 a 2007-06-30):  412 (305 course, 107 project)
-- 2) Total ano lectivo 07/08 (2007-09-01 a 2008-06-30):  752 (674 course, 78 project)
-- 3) Total ano lectivo 08/09 (2008-09-01 a 2009-06-30):  1039 (941 course, 98 project)
-- 4) Total ano lectivo 09/10 (2009-09-01 a 2010-06-30):  1244 (1132 course, 112 project) 
 
-- 1)
SELECT count(1),TYPE FROM SAKAI_SITE WHERE CREATEDON > '2006-09-01 00:00:00' AND CREATEDON < '2007-06-30 23:59:59' AND TYPE IS NOT NULL AND TYPE NOT IN ('ufp','ufpuv') GROUP BY TYPE;
-- 2)
SELECT count(1),TYPE FROM SAKAI_SITE WHERE CREATEDON > '2007-09-01 00:00:00' AND CREATEDON < '2008-06-30 23:59:59' AND TYPE IS NOT NULL AND TYPE NOT IN ('ufp','ufpuv') GROUP BY TYPE;
-- 3)
SELECT count(1),TYPE FROM SAKAI_SITE WHERE CREATEDON > '2008-09-01 00:00:00' AND CREATEDON < '2009-06-30 23:59:59' AND TYPE IS NOT NULL AND TYPE NOT IN ('ufp','ufpuv') GROUP BY TYPE;
-- 4)
SELECT count(1),TYPE FROM SAKAI_SITE WHERE CREATEDON > '2009-09-01 00:00:00' AND CREATEDON < '2010-06-30 23:59:59' AND TYPE IS NOT NULL AND TYPE NOT IN ('ufp','ufpuv') GROUP BY TYPE;

Users statistics

-- 1) Total ano lectivo 06/07 (2006-09-01 a 2007-06-30):  4455
--    a) Alunos (access + guest + NULL):  4102
--    b) Docentes (maintain):              353
-- 2) Total ano lectivo 07/08 (2007-09-01 a 2008-06-30):  5284
--    a) Alunos (access + guest + NULL):  4877
--    b) Docentes (maintain):              407
-- 3) Total ano lectivo 08/09 (2008-09-01 a 2009-06-30):  5950
--    a) Alunos (access + guest + NULL):  5413
--    b) Docentes (maintain):              537
-- 4) Total ano lectivo 09/10 (2008-09-01 a 2010-06-30):  6294
--    a) Alunos (access + guest + NULL):  5780
--    b) Docentes (maintain):              514
    
-- 1)
SELECT SU.TYPE, count(DISTINCT SESSION_USER) 
FROM sakai_archive.SAKAI_SESSION SS LEFT JOIN SAKAI_USER SU ON SS.SESSION_USER=SU.USER_ID LEFT JOIN SAKAI_USER_ID_MAP SUS ON SU.USER_ID=SUS.USER_ID WHERE SESSION_START > '2006-09-01 00:00:00' AND SESSION_START < '2007-06-30 23:59:59' GROUP BY SU.TYPE;
-- 2)
SELECT SU.TYPE, count(DISTINCT SESSION_USER) 
FROM sakai_archive.SAKAI_SESSION SS LEFT JOIN SAKAI_USER SU ON SS.SESSION_USER=SU.USER_ID LEFT JOIN SAKAI_USER_ID_MAP SUS ON SU.USER_ID=SUS.USER_ID WHERE SESSION_START > '2007-09-01 00:00:00' AND SESSION_START < '2008-06-30 23:59:59' GROUP BY SU.TYPE; 
-- 3)
SELECT SU.TYPE, count(DISTINCT SESSION_USER) 
FROM sakai_archive.SAKAI_SESSION SS LEFT JOIN SAKAI_USER SU ON SS.SESSION_USER=SU.USER_ID LEFT JOIN SAKAI_USER_ID_MAP SUS ON SU.USER_ID=SUS.USER_ID WHERE SESSION_START > '2008-09-01 00:00:00' AND SESSION_START < '2009-06-30 23:59:59' GROUP BY SU.TYPE; 
-- 4)
SELECT SU.TYPE, count(DISTINCT SESSION_USER) 
FROM sakai_archive.SAKAI_SESSION SS LEFT JOIN SAKAI_USER SU ON SS.SESSION_USER=SU.USER_ID LEFT JOIN SAKAI_USER_ID_MAP SUS ON SU.USER_ID=SUS.USER_ID WHERE SESSION_START > '2009-09-01 00:00:00' AND SESSION_START < '2010-06-30 23:59:59' GROUP BY SU.TYPE;

Sessions statistics

-- A) Hourly usage
SELECT hour(session_start), count(hour(session_start)) AS users FROM SAKAI_SESSION GROUP BY hour(session_start);
 
-- B) Total users (logged in)
SELECT count(1) FROM SAKAI_REALM WHERE REALM_ID LIKE "/site/~%" AND REALM_ID NOT IN   (SELECT REALM_ID FROM SAKAI_REALM WHERE REALM_ID LIKE "%@%");
 
-- C) Distinct users in the last 7 days
SELECT count(1) AS unique_users FROM (SELECT DISTINCT  SESSION_USER FROM SAKAI_SESSION WHERE SESSION_START > DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS unique_users;
 
-- D) User logins in the last 7 days
SELECT count(1) AS user_logins FROM (SELECT SESSION_USER FROM SAKAI_SESSION WHERE SESSION_START > DATE_SUB(CURDATE(), INTERVAL 7 DAY)) AS unique_users;
 
-- E) Distinct users on a given month
SELECT count(1) AS unique_users FROM (SELECT DISTINCT  SESSION_USER FROM SAKAI_SESSION 
WHERE SESSION_START > '2007-03-01'
AND SESSION_START < DATE_ADD('2007-03-01', INTERVAL 1 MONTH)) AS unique_users;
	
-- E.1) Distinct users by month (grouped by month)
SELECT count(DISTINCT  SESSION_USER) AS unique_users, MONTH(SESSION_START) FROM SAKAI_SESSION 
WHERE SESSION_START > '2007-01-01'
GROUP BY MONTH(SESSION_START);
 
-- F) User logins in the last month
SELECT count(1) AS unique_users FROM (SELECT SESSION_USER FROM SAKAI_SESSION WHERE SESSION_START > DATE_SUB(CURDATE(), INTERVAL 31 DAY)) AS unique_users;
 
-- G) Number of users with more than 15 login
SELECT count(session_id) AS c,session_user FROM SAKAI_SESSION GROUP BY session_user HAVING c > 15 ORDER BY c;
 
-- H) Total of sessions
SELECT count(session_id) FROM SAKAI_SESSION;
 
-- I) Sessions between 2 and 5 min
SELECT count(*) FROM (SELECT TIMEDIFF(SESSION_END,SESSION_START) AS t FROM SAKAI_SESSION HAVING t >= '00:02:00' AND t < '00:05:00') AS m;
 
-- J) Sessions between 5 and 10 min
SELECT count(*) FROM (SELECT TIMEDIFF(SESSION_END,SESSION_START) AS t FROM SAKAI_SESSION HAVING t >= '00:05:00' AND t < '00:10:00') AS m;
 
-- K) Total resources
SELECT count(*) FROM CONTENT_RESOURCE;
 
-- L) Total assignments
SELECT count(ASSIGNMENT_ID) FROM ASSIGNMENT_ASSIGNMENT;
 
-- M) Total assignment submissions
SELECT count(SUBMISSION_ID) FROM ASSIGNMENT_SUBMISSION;

Accesses by browser

Browser accesses WITH version

# Browser accesses WITH version
# (adjust data interval below)
SELECT 	( 
	CASE 
	    WHEN LOCATE('MSIE',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('MSIE',SESSION_USER_AGENT),6) 
	    WHEN LOCATE('CHROME',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('CHROME',SESSION_USER_AGENT),10) 
	    WHEN LOCATE('FIREFOX',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('FIREFOX',SESSION_USER_AGENT),11) 
	    WHEN LOCATE('SAFARI',SESSION_USER_AGENT)!=0 THEN CONCAT( 'Safari ', SUBSTR(SESSION_USER_AGENT, LOCATE('VERSION',SESSION_USER_AGENT)+8,3) ) 
	    WHEN LOCATE('OPERA',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('OPERA',SESSION_USER_AGENT),9) 
	    WHEN LOCATE('KONQUEROR',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('KONQUEROR',SESSION_USER_AGENT),13) 
	    WHEN LOCATE('NETSCAPE',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('NETSCAPE',SESSION_USER_AGENT),12) 
	    WHEN LOCATE('MIDP',SESSION_USER_AGENT)!=0 THEN 'Mobile Phone (MIDP)' 
	    WHEN LOCATE('SEAMONKEY',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('SEAMONKEY',SESSION_USER_AGENT),13) 
	    WHEN LOCATE('GALEON',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('GALEON',SESSION_USER_AGENT),10) 
	    WHEN LOCATE('CAMINO',SESSION_USER_AGENT)!=0 THEN SUBSTR(SESSION_USER_AGENT, LOCATE('CAMINO',SESSION_USER_AGENT),10) 
	    WHEN LOCATE('PLAYSTATION 3',SESSION_USER_AGENT)!=0 THEN 'Sony PS3' 
	    WHEN LOCATE('PLAYSTATION PORTABLE',SESSION_USER_AGENT)!=0 THEN 'Sony PSP' 
	    WHEN LOCATE('Microsoft Data Access Internet Publishing Provider',SESSION_USER_AGENT)!=0 THEN 'WebDAV' 
	    WHEN LOCATE('Microsoft Office',SESSION_USER_AGENT)!=0 THEN 'WebDAV' 
	    WHEN LOCATE('WEBDAV',SESSION_USER_AGENT)!=0 THEN 'WebDAV' 
	    WHEN LOCATE('DAVFS',SESSION_USER_AGENT)!=0 THEN 'WebDAV' 
	    WHEN LOCATE('GECKO',SESSION_USER_AGENT)!=0 THEN 'Other Mozilla/Geck based' 
	ELSE 'Other'    
	-- ELSE SESSION_USER_AGENT
	END 
	) AS BROWSER, 
	(
	CASE 
	    WHEN LOCATE('10.',SESSION_IP)=1 THEN 'Internal'
	    ELSE 'External'
	END
	) AS NETWORK,
	count(SESSION_ID) AS TOTAL 
FROM 	sakai_archive.SAKAI_SESSION 
WHERE	SESSION_START > '2009-09-01 00:00:00' 
AND	SESSION_START < '2010-09-01 00:00:00' 
-- and	LOCATE('10.',SESSION_IP)!=1 -- <= External sessions ONLY
AND	LOCATE('10.',SESSION_IP)=1 -- <= Internal sessions ONLY
AND	SESSION_USER_AGENT NOT LIKE '%SakaiLogin%' 
AND	SESSION_USER_AGENT NOT LIKE '%Password Reset%' 
AND	SESSION_USER_AGENT NOT LIKE '%Sync%' 
AND	SESSION_USER_AGENT NOT LIKE '%Wget%' 
GROUP BY BROWSER,NETWORK
ORDER BY TOTAL DESC;


Browser accesses WITHOUT version

# Browser accesses WITHOUT version
# (adjust data interval below)
SELECT 	(
	CASE 
	    WHEN LOCATE('MSIE',SESSION_USER_AGENT)!=0 THEN 'MSIE'
	    WHEN LOCATE('CHROME',SESSION_USER_AGENT)!=0 THEN 'Chrome'
	    WHEN LOCATE('FIREFOX',SESSION_USER_AGENT)!=0 THEN 'Firefox'
	    WHEN LOCATE('SAFARI',SESSION_USER_AGENT)!=0 THEN 'Safari'
	    WHEN LOCATE('OPERA',SESSION_USER_AGENT)!=0 THEN 'Opera'
	    WHEN LOCATE('KONQUEROR',SESSION_USER_AGENT)!=0 THEN 'Konqueror'
	    WHEN LOCATE('NETSCAPE',SESSION_USER_AGENT)!=0 THEN 'Netscape'
	    WHEN LOCATE('MIDP',SESSION_USER_AGENT)!=0 THEN 'Mobile Phone (MIDP)'
	    WHEN LOCATE('SEAMONKEY',SESSION_USER_AGENT)!=0 THEN 'SeaMonkey'
	    WHEN LOCATE('GALEON',SESSION_USER_AGENT)!=0 THEN 'Galeon'
	    WHEN LOCATE('CAMINO',SESSION_USER_AGENT)!=0 THEN 'Camino'
	    WHEN LOCATE('PLAYSTATION 3',SESSION_USER_AGENT)!=0 THEN 'Sony PS3'
	    WHEN LOCATE('PLAYSTATION PORTABLE',SESSION_USER_AGENT)!=0 THEN 'Sony PSP'
	    WHEN LOCATE('Microsoft Data Access Internet Publishing Provider',SESSION_USER_AGENT)!=0 THEN 'WebDAV'
	    WHEN LOCATE('Microsoft Office',SESSION_USER_AGENT)!=0 THEN 'WebDAV'
	    WHEN LOCATE('WEBDAV',SESSION_USER_AGENT)!=0 THEN 'WebDAV'
	    WHEN LOCATE('DAVFS',SESSION_USER_AGENT)!=0 THEN 'WebDAV'
	    WHEN LOCATE('GECKO',SESSION_USER_AGENT)!=0 THEN 'Other Mozilla/Geck based'
	ELSE 'Other'    
	-- ELSE SESSION_USER_AGENT
	END
	) AS BROWSER,
	(
	CASE 
	    WHEN LOCATE('10.',SESSION_IP)=1 THEN 'Internal'
	    ELSE 'External'
	END
	) AS NETWORK,
	count(SESSION_ID) AS TOTAL
FROM 	sakai_archive.SAKAI_SESSION 
WHERE	SESSION_START > '2009-09-01 00:00:00'
AND	SESSION_START < '2010-09-01 00:00:00'
-- and	LOCATE('10.',SESSION_IP)!=1 -- <= External sessions ONLY
AND	LOCATE('10.',SESSION_IP)=1 -- <= Internal sessions ONLY
AND	SESSION_USER_AGENT NOT LIKE '%SakaiLogin%'
AND	SESSION_USER_AGENT NOT LIKE '%Password Reset%'
AND	SESSION_USER_AGENT NOT LIKE '%Sync%'
AND	SESSION_USER_AGENT NOT LIKE '%Wget%'
GROUP BY BROWSER,NETWORK
ORDER BY TOTAL DESC;

Browser accesses by network (internal/external)

# Browser accesses by network (internal/external)
# (adjust data interval below)
SELECT  (
	CASE 
	    WHEN LOCATE('10.',SESSION_IP)=1 THEN 'Internal'
	    ELSE 'External'
	END
	) AS NETWORK,
	count(SESSION_ID) AS TOTAL
FROM 	sakai_archive.SAKAI_SESSION 
WHERE	SESSION_START > '2009-09-01 00:00:00'
AND	SESSION_START < '2010-09-01 00:00:00'
AND	SESSION_USER_AGENT NOT LIKE '%SakaiLogin%'
AND	SESSION_USER_AGENT NOT LIKE '%Password Reset%'
AND	SESSION_USER_AGENT NOT LIKE '%Sync%'
AND	SESSION_USER_AGENT NOT LIKE '%Wget%'
GROUP BY NETWORK
ORDER BY TOTAL DESC;


Other

Stats for acmout

# 1. ÁREAS DE DISCIPLINA
 
## a) quantas foram criadas até ao momento ==> 4011
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='course';
 
## b) desse total, quantas foram criadas no ano lectivo de 2008-2009 ==> 946
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='course' AND CREATEDON>='2008-09-01 00:00:00' AND CREATEDON<'2009-09-01 00:00:00';
 
 
# 2. ÁREAS DE PROJECTO
## 2.1 quantas foram criadas até ao momento, divididas por: ==> 356
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='project';
 
### 2.1 a) áreas administrativas ==> 17
SELECT count(*) FROM SAKAI_SITE S, SAKAI_USER_ID_MAP SUIM, GEPUV.EMPLOYEE E
WHERE TYPE='project' AND S.CREATEDBY=SUIM.USER_ID
AND E.EMAIL=SUIM.EID;
 
### 2.1 b) coordenação ==> 41
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='project' 
AND (TITLE LIKE '%coord%' OR SHORT_DESC LIKE '%coord%' OR DESCRIPTION LIKE '%coord%');
 
### 2.1 c) apoio a projectos ==> 298
# (todas as não incluídas nas estatísticas anteriores)
 
 
## 2.2 quantas foram criadas no ano lectivo de 2008-2009, divididas por: ==> 103
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='project' AND CREATEDON>='2008-09-01 00:00:00' AND CREATEDON<'2009-09-01 00:00:00';
 
### 2.2 a) áreas administrativas ==> 3
SELECT count(*) FROM SAKAI_SITE S, SAKAI_USER_ID_MAP SUIM, GEPUV.EMPLOYEE E
WHERE TYPE='project' AND S.CREATEDBY=SUIM.USER_ID
AND E.EMAIL=SUIM.EID
AND CREATEDON>='2008-09-01 00:00:00' AND CREATEDON<'2009-09-01 00:00:00';
 
### 2.2 b) coordenação ==> 17
SELECT count(*) FROM SAKAI_SITE WHERE TYPE='project' 
AND (TITLE LIKE '%coord%' OR SHORT_DESC LIKE '%coord%' OR DESCRIPTION LIKE '%coord%')
AND CREATEDON>='2008-09-01 00:00:00' AND CREATEDON<'2009-09-01 00:00:00';
 
### 2.2 c) apoio a projectos ==> 83
# (todas as não incluídas nas estatísticas anteriores) 

Stats for nribeiro

See "SIS SQL Queries" page.

Personal tools