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

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 )

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