log all activities of a user during the course.

log all activities of a user during the course.

by Rahila Umer -
Number of replies: 2

Hi, 

I want to know a query where I can see a user and name of all activities with their timestamps performed during the course. I have a query where It counts the activities but  instead I want to know the exact activity name with their time stamps for each student enrolled in the course. 

This is query that I am using for count of activities.  


select l.component AS activity , 

      COUNT(DISTINCT case when l.timecreated-c.startdate < 0 then l.id else NULL end) AS BeforeTerm ,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 0 then l.id else NULL end) AS Week1,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 1 then l.id else NULL end) AS Week2,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 2 then l.id else NULL end) AS Week3,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 3 then l.id else NULL end) AS Week4,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 4 then l.id else NULL end) AS Week5,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 5 then l.id else NULL end) AS Week6,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 6 then l.id else NULL end) AS Week7,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 7 then l.id else NULL end) AS Week8,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 8 then l.id else NULL end) AS Week9,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 9 then l.id else NULL end) AS Week10,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 10 then l.id else NULL end) AS Week11,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) = 11 then l.id else NULL end) AS Week12,

            COUNT(DISTINCT case when FLOOR((l.timecreated - c.startdate)/(60*60*24*7)) >= 12 then l.id else NULL end) AS AfterTerm,

            COUNT(l.id) AS Total

            FROM mdl_user AS u

            JOIN mdl_role_assignments AS ra ON u.id = ra.userid

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

            JOIN mdl_course AS c ON c.id = ctx.instanceid

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

            LEFT JOIN mdl_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')

            GROUP BY l.component

            ORDER BY l.component 


second question: 

I have two tables in my database one is mdl_log and second is mdl_standard_log. I also want to know the difference between both. 

Average of ratings: -
In reply to Rahila Umer

Re: log all activities of a user during the course.

by Elizabeth Dalton -

The difference between mdl_log and mdl_standard_log is that mdl_log is the old format (prior to 2.7) and mdl_standard_log is the new log format. Most sites have one or the other, but it is possible to have data in both, especially if you have upgraded a site from before 2.7.

In your query, do you want the name of the activity (e.g. "Statistical Reasoning Lesson 5") or the activity type (e.g. "Lesson")?

Activity names are in the mdl_course_modules table. Activity types are referenced by number in the mdl_course_modules table, and you need to join to mdl_modules to get them.

This query that you are using creates a separate column for each week of the course. If you don't care about counting actions per week, and you want a list of activities per user with timestamps, you can use a much simpler query, something like:

select
u.fullname AS student,
l.component AS activity ,
l.timecreated
FROM mdl_user AS u
JOIN mdl_role_assignments AS ra ON u.id = ra.userid
JOIN mdl_context AS ctx ON ra.contextid = ctx.id
JOIN mdl_course AS c ON c.id = ctx.instanceid
LEFT JOIN mdl_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')
GROUP BY l.userid, l.component
ORDER BY l.userid

Do you have multiple courses? This query would get the activities from all courses on your site. You might want to filter by l.courseid. You might also want to filter by role, e.g. to get only students you would limit to ra.roleid = 5.

Average of ratings: Useful (1)
In reply to Elizabeth Dalton

Re: log all activities of a user during the course.

by Rahila Umer -

thank you so much for your reply, its really helpful. 

Yes I need userid, Activity, Activity type, timestamp.


Average of ratings: -