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.

    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 ',,' ',l.action,' the ', l.objecttable,' ',
        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'
    ' 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 = l.userid
    LEFT JOIN moodle.mdl_user AS au ON = l.relateduserid
#Change l.courseid to the internal id of moodle course you want to check out
WHERE l.courseid = '10001'

This will produce results from the database which are like the following from the Moodle Logs in the GUI


User full name

Affected user

Event context


Event name



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

Leave a Reply

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

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

Google photo

You are commenting using your Google 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