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
Hope it helps..
Not so many... 
Hello, sorry for bothering you, but I was looking to extract the same logs as the picture, but the only problem is that, the “Event context” column is shown only as an Id and not as the text shown, if you could help it would be great.
Hi Andrea, that’s my fault for redacting the results to un-personalise the screenshot for my blog. Event context is a ID ‘l.contextid AS ‘Event context’. I’ve edited the post to show the output correctly.. I think one column moved over making it look like event context had information in it.
Hi, thanks for answering me, do you think that there is a way to extract the event context as a string like as shown in the https://docs.moodle.org/310/en/Logs site? Like the picture where it says “Course: Moodle 2.7 demo”, or if you have any suggestions or tips to do such thing it would be really helpfull, Thanks
Hi Andrea, does this help.. taken from here : https://docs.moodle.org/310/en/ad-hoc_contributed_reports
See lines 26 / 27 they need your parameters, I commented out line 11 as well as we do not use mdl_questionnaire
I plan on updating my above at some point – but this and mine should get you started.
SELECT u.id, ra.roleid,
CONCAT(u.lastname, ‘ ‘, u.firstname) AS ‘Student’
,COUNT(l.id) AS ‘Actions’
,l.component “Module type”
,l.objectid “Module ID”
,CASE
WHEN l.component = ‘mod_url’ THEN (SELECT u.name FROM mdl_url AS u WHERE u.id = l.objectid )
WHEN l.component = ‘mod_resource’ THEN (SELECT r.name FROM mdl_resource AS r WHERE r.id = l.objectid )
WHEN l.component = ‘mod_forum’ THEN (SELECT f.name FROM mdl_forum AS f WHERE f.id = l.objectid )
WHEN l.component = ‘mod_quiz’ THEN (SELECT q.name FROM mdl_quiz AS q WHERE q.id = l.objectid )
#WHEN l.component = ‘mod_questionnaire’ THEN (SELECT q.name FROM mdl_questionnaire AS q WHERE q.id = l.objectid )
END AS ‘Module name’
,(SELECT GROUP_CONCAT(g.name) FROM mdl_groups AS g
JOIN mdl_groups_members AS m ON g.id = m.groupid WHERE g.courseid = l.courseid AND m.userid = u.id) “user_groups”
,(SELECT s.name
FROM mdl_course_modules AS cm
JOIN mdl_course_sections AS s ON s.course = cm.course AND s.id = cm.section
WHERE cm.id = l.contextinstanceid) AS “Section name”
FROM moodle.mdl_logstore_standard_log AS l
JOIN mdl_user AS u ON u.id = l.userid
JOIN mdl_role_assignments AS ra ON ra.userid = l.userid
AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
#WHERE l.courseid = ‘13000’ — Uncomment and insert courseid
#AND u.id = ‘17000’ — Uncomment and insert Moodle userid
AND l.component IN (‘mod_url’, ‘mod_resource’, ‘mod_forum’, ‘mod_quiz’, ‘mod_questionnaire’)
GROUP BY u.id, l.component
ORDER BY u.lastname, u.firstname
Thank you very much, you are a really great person, I wish you the best
😃 Happy to help