CODEWITHSUNDEEP
loggingmoodlemoodle-api
Gizmapps
Gizmapps

Reputation: 69

Struggle to make the right query to the Moodle database table "mdl_logstore_standard_log" in order to export logs as it happens from the GUI

My problem is that I am trying to export a csv file from the Moodle DB "mdl_logstore_standard_log" with the exact order as the Logs page under Site Administration > Reports.

The Moodle GUI report fields order

One idea to achieve such a thing is through a query like this below:

SELECT 
FROM_UNIXTIME(l.timecreated,'%d %b, %H:%i') AS `Time`,
CONCAT(u.firstname, ' ', u.lastname) AS `User full name`,
CONCAT(au.firstname, ' ', au.lastname) AS `Affected user`,
l.origin AS `Origin`,
l.ip AS `IP address`
FROM mdl_logstore_standard_log AS l
LEFT JOIN mdl_user AS u ON u.id = l.userid
LEFT JOIN mdl_user AS au ON au.id = l.relateduserid

Unfortunately the query above does not behave as i expect. I need the exact data from the GUI report of moodle. Is there any idea if this query is already implemented in a php file in moodle or somebody tried this before?

Upvotes: 2

Views: 2445

Answers (1)

wp78de
wp78de

Reputation: 19000

The web form does not rely on a single query but a series of queries and PHP code to create the Log Report. If you are interested, I have logged two such series here: Todays Logs and All Logs.

Most queries in the log are dedicated to viewing permissions, etc. The main part can be found at the end, starting roughly here:

SELECT COUNT('x') FROM mdl_course
SELECT id,shortname,fullname,category FROM mdl_course   ORDER BY fullname
SELECT u.id, u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname
              FROM mdl_user u
              JOIN (SELECT DISTINCT eu1_u.id
              FROM mdl_user eu1_u                
             WHERE 1 = 1 AND eu1_u.deleted = 0) je ON je.id = u.id
             WHERE u.deleted = 0 ORDER BY u.lastname, u.firstname, u.id LIMIT 0, 1001
SELECT COUNT('x') FROM mdl_logstore_standard_log WHERE anonymous = 0
SELECT * FROM mdl_logstore_standard_log WHERE anonymous = 0 ORDER BY timecreated DESC, id DESC LIMIT 0, 100
SELECT id,firstnamephonetic,lastnamephonetic,middlename,alternatename,firstname,lastname FROM mdl_user WHERE id = '2'
SELECT * FROM mdl_logstore_standard_log WHERE anonymous = 0 ORDER BY timecreated DESC, id DESC LIMIT 0, 100
SELECT * FROM mdl_user WHERE id = '2' AND deleted = '0'

That's it. So basically, you are doing it correctly. However, you need to pretty-print the remaining columns, namely contextid, component, eventname, and other, yourself:

SELECT FROM_UNIXTIME(l.timecreated, '%d %b, %H:%i') AS `Time`
    ,CONCAT (u.firstname, ' ', u.lastname) AS `User FULL name`
    ,CONCAT (au.firstname, ,' ', au.lastname) AS `Affected user`
    ,l.contextid AS EventContext
    ,l.component
    ,l.eventname
    ,other AS description
    ,l.origin AS `Origin`
    ,l.ip AS `IP address`
FROM mdl_logstore_standard_log AS l
LEFT JOIN mdl_user AS u ON u.id = l.userid
LEFT JOIN mdl_user AS au ON au.id = l.relateduserid
WHERE l.anonymous = 0
ORDER BY l.timecreated DESC
    ,l.id DESC

If this has something anything to do with automation and not merely reporting, I would refrain from a tedious translation and just work with the raw data as selected here.

Upvotes: 3

Related Questions