SQL query ignoring time clause

SQL query ignoring time clause

by Gal Sapir -
Number of replies: 6

This query relates tonthe number of logins of students per month across the college. The query used to run well ,but since changing a host, it returns the accumulative number of logins since the beginning if the system and ignores the time clause. Any help modifying the query to reconise again the timestap will be appreciated.

SELECT category, l.course courseId, c.fullname coursename, COUNT(l.id) hits

FROM mdl_log l INNER JOIN mdl_course c ON l.course = c.id

WHERE time >= 1546300800 AND time  < 1548979200

AND l.userId LIKE '%20%'

OR l.userId LIKe '%10%'

GROUP BY courseId

ORDER BY category

Average of ratings: -
In reply to Gal Sapir

Re: SQL query ignoring time clause

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi Gal,

Just a question : which Moodle version do you use, and which logging method?

Because you refer to mdl_log, and that's the old (legacy) logging table.
Moodle 2.7 introduced a new logging table : mdl_logstore_standard_log.

If you use this new table (standard log since 2.7), you should change your query, to replace mdl_log by mdl_logstore_standard_log, course by courseid and time by timecreated.

HTH,
Séverin

Average of ratings: -
In reply to Séverin Terrier

Re: SQL query ignoring time clause

by Gal Sapir -

Hi Severin,

Thank you for commenting,

The Moodle version is 3.5, and I modified the query to use the new logging. The modified query is returning results, but some of them are redundant, for instance old courses that are still on the system but should not have much activity. The modified code is as follows:

SELECT category, l.courseid AS courseId, c.fullname AS 'Course Name' ,COUNT( l.userid) AS 'Users Hits'

FROM mdl_logstore_standard_log AS l INNER JOIN mdl_course AS c ON l.courseid = c.id

WHERE  (FROM_UNIXTIME(l.timecreated) BETWEEN '2019-01-01 00:00:00' AND '2019-01-31 00:00:00')

AND l.userid LIKE '%20%'

OR l.userid LIKe '%10%'

GROUP BY courseId

ORDER by category


Any feedback as how to make the query more accurate will be appreciated.

Average of ratings: -
In reply to Gal Sapir

Re: SQL query ignoring time clause

by Séverin Terrier -
Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Testers Picture of Translators

Hi Gal,

I'm not sure about what you want to report exactly, but your query does take care of dates only for userid containing 20 ; and counts all elements for userid containing 10. Is it really what you want?

You could try this query :

SELECT category, l.courseid AS courseId, c.fullname AS 'Course Name', origin, COUNT( l.userid) AS 'Users Hits'
FROM mdl_logstore_standard_log AS l INNER JOIN mdl_course AS c ON l.courseid = c.id
WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2019-01-01 00:00:00' AND '2019-01-31 00:00:00'
  AND (l.userid LIKE '%20%' OR l.userid LIKE '%10%')
GROUP BY courseId, origin
ORDER by category
It takes care of dates for userid containing 10 or 20.

It also reports origin of elements : web is for real action of users via web interface ; ws (web services) covers actions from Moodle App (and other processes using WS), and cli stands for all automated processes (cron...).

It can help you better understand what is active on your installation.

HTH,
Séverin

Average of ratings: -
In reply to Séverin Terrier

Re: SQL query ignoring time clause

by Gal Sapir -

Hi Severin,

Thank you for the suggestions and code.

The part of '%20%' and '%10%' is there to identify students, I understand there are more accurate ways to do that, also, it cuts out a number of courses from the results, which is not helpful as they should have been there.

With regards to what this query is trying to do, it is looking at students activity across all courses, and ideally, should return their views and posts. It should then enable a comparison with a particular course's statistics to see that there is a correlation.  At the moment the course's statistics are much higher than the report. Would that have to do with the report counting the logs differently?

Hope I have been clear enough. Generally, the more information that can be gathered about the type of activity the students do the better. In this respect, the 'origin' column looks interesting. Not sure what prompts the CLI in the context of students, but I will look into it further. 

Gal


Average of ratings: -
In reply to Gal Sapir

Re: SQL query ignoring time clause

by Gal Sapir -

Tried this:

SELECT category, l.courseid AS courseId, c.fullname AS 'Course Name', origin, COUNT( l.userid) AS 'Users Hits', DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m') 'Month'
FROM mdl_logstore_standard_log AS l
INNER JOIN mdl_course AS c ON l.courseid = c.id
JOIN mdl_role_assignments AS ra on ra.userid = l.userid
WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2018-09-01 00:00:00' AND '2019-02-28 00:00:00'
AND ra.roleid = 5
GROUP BY courseId, Month, origin
ORDER by category, Month
This returns reasonable results, however, I wasn't able to incorporate the following into the statement:
JOIN mdl_context AS ctx on ra.contextid = ctx.id
WHERE ctx.instanceid = c.id

This was returning an error. Does anyone have an idea why would that be?  It would be helpful to narrow the results down. I am not sure that the context is the right way to do that. Any advice will be welcome.  

Average of ratings: -
In reply to Gal Sapir

Re: SQL query ignoring time clause

by Gal Sapir -

This is the final Query I ended up with, it works but had to be adjusted. The main reason for the error messages, as I understand it, was the time it took the server to reply. So essentially the query returned too large a set, the server timed out and returned an error. A workaround was to break it into separate groups using courses ids. We took all 300+ courses and divided them into groups of about 40, and the server started to return results. 

This query also contains a 'hits per student' column which uses a subquery to divide the number of hits by the number of students on the course., It makes the query a bit large and it would be great to optimise that. Nevertheless, the query works well and returns results almost identical to the ones that come from Moodle's native Statistics and Reports.


#################################

SELECT category, cc.description AS 'Description', l.courseid AS 'courseId', c.fullname AS 'Course Name',  DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m') 'Month', COUNT( l.userid) AS UsersHits, (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra

JOIN mdl_context AS ctx ON ra.contextid = ctx.id

WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students, (COUNT( l.userid) DIV (SELECT Count( ra.userid ) AS Users FROM mdl_role_assignments AS ra

JOIN mdl_context AS ctx ON ra.contextid = ctx.id

WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) +1) as 'Hits per student'

FROM mdl_logstore_standard_log AS l

INNER JOIN mdl_course AS c ON l.courseid = c.id

JOIN mdl_role_assignments AS ra on ra.userid = l.userid

JOIN mdl_context AS ctx on ra.contextid = ctx.id

JOIN mdl_course_categories AS cc on cc.id = c.category

WHERE FROM_UNIXTIME(l.timecreated) BETWEEN '2019-04-23 00:00:00' AND '2019-05-25 00:00:00' #-- half term dates ----

AND ra.roleid = 5

AND ctx.contextlevel = '50'

AND ctx.instanceid = c.id

AND c.id IN (886, 451, 309, 1111, 803, 834, 570, 616, 318, 1039, 261, 937, 1129, 938, 296, 939, 896, 310, 1114, 805, 1062, 1063, 566, 942, 1124, 940, 313, 941, 1087, 252, 1146, 1094, 954, 850, 319, 777, 636, 678, 928, 55, 1206, 776, 773, 775) #--sample courses id's here

GROUP BY courseId

ORDER by 'Hits per student' ASC

Average of ratings: -