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