SQL Query to create a log report

SQL Query to create a log report

by Subhash Ariyadasa -
Number of replies: 11

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.

Attachment Capture.JPG
Average of ratings: -
In reply to Subhash Ariyadasa

Re: SQL Query to create a log report

by Subhash Ariyadasa -

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: -
In reply to Subhash Ariyadasa

Re: SQL Query to create a log report

by Elizabeth Dalton -

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: -
In reply to Elizabeth Dalton

Re: SQL Query to create a log report

by Sreenivasula Reddy Dorshala -

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)
In reply to Elizabeth Dalton

Re: SQL Query to create a log report

by Rahul Barpha -

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)
In reply to Rahul Barpha

Re: SQL Query to create a log report

by Subhash Ariyadasa -
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: -
In reply to Elizabeth Dalton

Re: SQL Query to create a log report

by Subhash Ariyadasa -

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: -
In reply to Subhash Ariyadasa

Re: SQL Query to create a log report

by Elizabeth Dalton -

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)
In reply to Elizabeth Dalton

Re: SQL Query to create a log report

by Subhash Ariyadasa -

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: -
In reply to Subhash Ariyadasa

Re: SQL Query to create a log report

by Elizabeth Dalton -

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)
In reply to Elizabeth Dalton

Re: SQL Query to create a log report

by Subhash Ariyadasa -

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: -
In reply to Subhash Ariyadasa

Re: SQL Query to create a log report

by Elizabeth Dalton -

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