Confusing log entries (screenshot) - please help!

Confusing log entries (screenshot) - please help!

by Fearghal Murphy -
Number of replies: 12
Hi,

I ran this query to find out course views within given times..but the result seems to spit out multiples of exactly the same thing..anybody know why?
I can get the valid number by group by the log id but I'd still like to know where i'm going wrong.... Thanks guys.

mdlscrn.png
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Samuli Karevaara -
If you are only interested in the log table entries, you don't need the other tables in your query at all. The info column of the log table contains the course id, if the module column is "course".

Your query is doing an implicit JOIN and it's returning the (same) log row for each role assignment in the course. Fetch the columns from the others tables as well, that way you can see that the returned rows are not exactly the same, only the log part if the same.

For example this page has some explanations about the "implicit JOIN statements" in the bottom of the article.
Average of ratings: -
In reply to Samuli Karevaara

Re: Confusing log entries (screenshot) - please help!

by Fearghal Murphy -
Hi Samuli

Thanks for answering..

Sorry I wasn't exactly clear on what I need...I need to get stats on each type of user so I thought its necessary to include the role assignments.

ie.
User Admin Teacher Student
Views 345 675 1567

I have tried JOIN already but it seems that brings up the same results...
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
The bit you are missing is that you are getting the user's role assignements from every course, not just the one in the logs.

However, since you only provided your SQL in a screenshot, it is not possible for me to edit it for you. The key bit would be to add 'mdl_context ctx' to the list of tables, then add

AND a.contextid = ctx.id
AND ctx.contextlevel = 50
AND ctx.instanceid = l.course

to the WHERE clause.

(You would be more consistent with the rest of Moodle if you used ra as a table alias for the role_assignements table.)

Average of ratings: -
In reply to Tim Hunt

Re: Confusing log entries (screenshot) - please help!

by Fearghal Murphy -
Hi Tim,

Thanks for that,...I tried what said..but when i visit a course it doesn't increment the views at all..Below is my SQL

SELECT r.id,r.name,count(*) FROM mdl_role r
JOIN mdl_role_assignments a ON r.id=a.roleid
JOIN mdl_log l ON l.userid=a.userid JOIN mdl_context ctx
ON a.contextid = ctx.id
WHERE (l.time BETWEEN 1225497600 AND 1229299200)
AND ctx.contextlevel = 50
AND ctx.instanceid = l.course
AND l.action = 'view'
AND l.module = 'course'
AND l.course = 11
AND r.id=1
GROUP BY r.id

This is meant to get the total Administrator view for course id 11 between given timestamps.


thanks for your time..!
Fearghal
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Tim Hunt -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Is it as simple as being a problem with the end time?
Average of ratings: -
In reply to Tim Hunt

Re: Confusing log entries (screenshot) - please help!

by Samuli Karevaara -
1229299200 = Mon, 15 Dec 2008 00:00:00 GMT so probably not...
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Samuli Karevaara -
Those queries detect only the actions of those who have a direct role assingment in the course, not the actions of the users who have "inherited" a course access permission from a higher level context.

If you are logged in as the site admin, then there might not be a context assignment for you in that course. Try adding yourself to the course as an admin, click around in the course and then run the queries again.
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Stanislav Tsymbalov -
Enjoy!

SELECT r.id,r.name,count(*) FROM mdl_role r
LEFT JOIN mdl_role_assignments a ON r.id=a.roleid
LEFT JOIN mdl_log l ON l.userid=a.userid
WHERE (l.time BETWEEN 1225497600 AND 1229299200)
AND l.action = 'view'
AND l.module = 'course'
AND l.course = 11
GROUP BY r.id
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Fearghal Murphy -
It seems the role assignments table is bringing back two different context ids..what do they mean? Do I have to GROUP By mdl_log.id to get an accurate answer?

For instance ...

mdlscrn2.jpg
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Stanislav Tsymbalov -
Try it!

(I have different time and course in my MySQL tables. Please enter yourself.)
Attachment MYSQL.jpg
Average of ratings: -
In reply to Stanislav Tsymbalov

Re: Confusing log entries (screenshot) - please help!

by Fearghal Murphy -
Hi Stanislav,

That looks great, however..when I visit a course once, the count will go up two...i think it has something to do with the context as mentioned previously in this forum...Any ideas?

F
Average of ratings: -
In reply to Fearghal Murphy

Re: Confusing log entries (screenshot) - please help!

by Stanislav Tsymbalov -
One user may have a lot of roles.
For instance, you have two roles (Admin, Teacher, etc).
When you enroled to course, one row added to log tables. But:
Admin +1
Teacher +1
Because you Admin and Teacher.
Average of ratings: -