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..

6 comments

  1. Andrea

    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.

    • Raymond Reid

      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.

      • Andrea

        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

  2. Raymond Reid

    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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s