wiki:Covid19LmsAdmin

The following contains how to take statistics from Moodle instances.

SQL
SELECT A.Date,A.UserLogins,B.DistinctUserLogins,C.NoOfActions FROM 
(SELECT 
COUNT(l.userid) AS 'UserLogins', 
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%D %M %Y') AS 'Date'
FROM prefix_logstore_standard_log l
WHERE l.action = 'loggedin'  AND l.timecreated >= UNIX_TIMESTAMP('2020-03-20') GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%D %M %Y') )A 
JOIN 
(SELECT 
COUNT(DISTINCT ll.userid) AS 'DistinctUserLogins', 
DATE_FORMAT(FROM_UNIXTIME(ll.timecreated), '%D %M %Y') AS 'Date'
FROM prefix_logstore_standard_log ll
WHERE ll.action = 'loggedin' AND ll.timecreated >= UNIX_TIMESTAMP('2020-03-20') GROUP BY DATE_FORMAT(FROM_UNIXTIME(ll.timecreated), '%D %M %Y') )B
USING(Date)
JOIN
(
SELECT 
COUNT(lll.action) AS 'NoOfActions',DATE_FORMAT(FROM_UNIXTIME(lll.timecreated), '%D %M %Y') AS 'Date' 
FROM prefix_logstore_standard_log lll
WHERE lll.timecreated >= UNIX_TIMESTAMP('2020-03-20') GROUP BY DATE_FORMAT(FROM_UNIXTIME(lll.timecreated), '%D %M %Y')
)C USING(Date)

And the video guide https://youtu.be/w1H9bVvHZok

Credits:

Iroshini Ratnapala, University of Peradeniya

Buddhika Godakuru, University of Kelaniya

Hasmi AJM, South Eastern University

Last modified 7 weeks ago Last modified on Apr 7, 2020, 3:49:35 PM