Moodle research

SQL Query to create a log report

 
Picture of Subhash Ariyadasa
SQL Query to create a log report
 

Hi All,

These days I'm doing some research study relevant to educational mining. Therefore, I'm analyzing moodle logs relevant to our institution. I'm using 'mdl_logstore_standard_log' table here and it's having more than 3 millions of data. Normally, when we open Logs page under Site Administration > Reports, we'll get a report which is actually having a human friendly format. But in the database side, 'mdl_logstore_standard_log' table doesn't have that much friendly format. Therefore, I'm planning to create a separate table to summarize this 'mdl_logstore_standard_log' data into format which is somewhat similar to moodle GUI Logs environment. Can someone help me on this by providing a SQL query to create following fields by merging necessary tables? (see attached figure)

I already did some try on this. But I couldn't find where it takes these 'Event Context', 'Component', 'Event Name', 'Description' data.

Please help on this.

Thank You.


 
Average of ratings: -
Picture of Subhash Ariyadasa
Re: SQL Query to create a log report
 

I'm having the following query, but still I'm struggling to get data for 'Event Context', 'Component', 'Event Name', 'Description' fields.

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

please help on this.


 
Average of ratings: -
Picture of Elizabeth Dalton
Re: SQL Query to create a log report
Moodle HQParticularly helpful MoodlersPlugin developers

Hi Subhash,

What kind of problem are you having? Have you tried a SELECT * to get all the fields in the log table so you can see what is available?

 
Average of ratings: -
Picture of sreenu eabyas
Re: SQL Query to create a log report
 

Hi Subhash,

Please go through the below sql query

SELECT 

FROM_UNIXTIME(l.timecreated,'%d %b, %H:%i') AS `Time`,

CONCAT(u.firstname, ' ', u.lastname) AS `User full name`,

(select CONCAT(u2.firstname, ' ', u2.lastname) from mdl_user u2 where u2.id = l.relateduserid ) as affected_user,

l.component,

l.eventname,

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 as event_context,

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

 
Average of ratings: Useful (1)
Picture of Rahul Barpha
Re: SQL Query to create a log report
 

I have checked your query and it is working. For require field you can use below query.

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`,contextid as 'Event Context',Component,Eventname as 'Event Name',

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

LIMIT 5


This query working on lots of data so may take some time. for Testing you can use 'LIMIT '.



 
Average of ratings: Useful (1)
Picture of Subhash Ariyadasa
Re: SQL Query to create a log report
 
Dear Rahul & Sreenu,

Thank you for both of your support on this. But actually I need to generate same kind of report, which we'll get through Moodle GUI reports -> logs interface. Herewith I have attached a sample report that I'm going to produce here using a SQL query.

Please help on this.


 
 
Average of ratings: -
Picture of Subhash Ariyadasa
Re: SQL Query to create a log report
 

Dear Elizabeth,

Thank you very much for considering my issue. Actually, I need to get a same kind of a report which we can get through moodle GUI by going through Site Administrator -> Reports -> logs, using a SQL query. I attached here a sample output which I need to have here.

Please help on this.

Sample Log File

 
Average of ratings: -
Picture of Elizabeth Dalton
Re: SQL Query to create a log report
Moodle HQParticularly helpful MoodlersPlugin developers

Subhash,

Is your goal to provide this information to users who are not admins?

Do you need to have the hot links, e.g. for the user full name -> user profile, etc.?

It can be done, but you will be exposing information without the protection Moodle provides.

If you want to duplicate this report, you will need to concatenate text strings with HTML elements in your SQL. You may find this example helpful: https://docs.moodle.org/32/en/ad-hoc_contributed_reports#Weekly_Student_Online_Participation

This example is designed to work within the Configurable Reports block or the Ad Hoc Queries admin report.

 
Average of ratings: Useful (1)
Picture of Subhash Ariyadasa
Re: SQL Query to create a log report
 

Thank you Elizabeth. The provided link will be more useful for me. I'll go through it and if I need further help, I'll come back to you. Also Elizabeth, this is not to provide this information to the users; this extraction is only to do our research work.  A research group including me, doing a research on this educational mining side using moodle log files. Then we need to get some extracted data from this standard_log db and we though if we can extract like this logs report, it will be more convenient. Since we have more than 3 million data in this table, it's difficult to get this report from GUI level. That's why we though to use this SQL level approach to generate this report.

Thank you again for your help and I'll come back to you if I need further help on this.

 
Average of ratings: -
Picture of Elizabeth Dalton
Re: SQL Query to create a log report
Moodle HQParticularly helpful MoodlersPlugin developers

Subhash, the information displayed in the GUI is logically the same as the database table itself (but the table has more columns). The transformations in the GUI make the report more human-readable, but you may be better off using the raw table as a starting point for data mining. The full name of the user doesn't matter to data mining algorithms, for example.

You may be interested to see what we are doing with Project Inspire, as announced in a post pinned to the top of this forum. Our code is open, and you may get some ideas for indicators.

Best regards,

Elizabeth 

 
Average of ratings: Useful (1)
Picture of Subhash Ariyadasa
Re: SQL Query to create a log report
 

Thank you Elizabeth. Totally agreed with your suggestions and I think you put me on to the right track, when I was searching for it. Actually, your thoughts opened me a new view on this Elizabeth and we'll try to work with the raw data first. Then, we'll see how things are going on and if we encountered any issues, we'll come back here again.

 
Average of ratings: -
Picture of Elizabeth Dalton
Re: SQL Query to create a log report
Moodle HQParticularly helpful MoodlersPlugin developers

You are welcome, Subhash. I am happy to help. smile

Again, you may want to look at Project Inspire: http://moodle.org/project_inspire

We have an "Anonymise" local plugin that can de-identify your log records (and other tables) so you do not risk exposing personally identifying information while conducting your research. This can be critical to conduct (or publish) research in the US or the EU, and is good practice in general.

We welcome all researchers to exchange ideas about learning analytics and data mining within Project Inspire. Our goal is to develop high-quality, open source machine learning tools for learning analytics to improve student success. There is much to learn in this area, and I think we will all make more progress by working together. We will be providing de-identified open data sets as part of this initiative, and this may be helpful to many researchers such as yourself, for validating algorithms outside of a single institution.

Best regards,

Elizabeth

 
Average of ratings: -