How to Produce Moodle Reports which are like the ones in the Moodle Logs
Ever wondered why you can not get the Moodle database to produce a report just like it appears within Moodle graphical user interface logs?
Well that because rows like ‘Description’ is made from multiple parts.
Its bugged me for a while and googling turned up a blank, so I wrote the following, with a little help from a friend – Ta Jim.
SELECT from_unixtime(l.timecreated) Time, CONCAT(u.firstname, ' ', u.lastname) AS 'User full name', CONCAT(au.firstname, ' ', au.lastname) AS 'Affected user', u.username AS 'Username', l.contextid AS 'Event context', l.component AS 'Component', l.Eventname AS 'Event name', CONCAT('The user with id ',u.id,' ',l.action,' the ', l.objecttable,' ', CASE WHEN l.contextlevel = 10 THEN 'system' WHEN l.contextlevel = 50 THEN 'course' WHEN l.contextlevel = 30 THEN 'user' WHEN l.contextlevel = 40 THEN 'category' WHEN l.contextlevel = 70 THEN 'activity' WHEN l.contextlevel = 80 THEN 'block' END, ' with module id ', l.courseid) AS 'Description', l.origin AS 'Origin', l.ip AS 'IP address' FROM moodle.mdl_logstore_standard_log l INNER JOIN moodle.mdl_user u ON u.id = l.userid LEFT JOIN moodle.mdl_user AS au ON au.id = l.relateduserid #Change l.courseid to the internal id of moodle course you want to check out WHERE l.courseid = '10001' ORDER BY time DESC;
This will produce results from the database which are like the following from the Moodle Logs in the GUI
Time | User full name | Affected user | Event context | Component | Event name | Description | Origin | IP address |
---|---|---|---|---|---|---|---|---|
6 February 2020, 9:02 PM | Raymond Reid | – | Coursework: Business 101 | Coursework | Course module viewed | The user with id ‘1480’ viewed the ‘course’ activity with course module id ‘2100’. | web | x.x.x.x |
Hope it helps..