Help/ideas on Reporting dates, frequency logged on to a course

Help/ideas on Reporting dates, frequency logged on to a course

by tim st.clair -
Number of replies: 1
Picture of Plugin developers
I need some ideas on a complex reporting problem. A client has this scenario:

  • a user is given the url of a course (https://site.com/course/view.php?id=123) or the url of a module within that course (https://site.com/mod/page/view.php?id=456).
  • They open the link and are not logged on. They log on. The course or page opens.
  • They do stuff in the course, navigating, etc.
  • They close, or log off, or visit other courses.
  • In following days, weeks, they open the links again, follow the same process.
A report is needed that shows:

  • the 'first' time the user logged on and opened either the course homepage OR that (known) url. The client sees 'logging on' to be the same as 'opening the course', rather than 'the logon event', which could simplify things.
  • the 'most recent' time the user open the course (being either the home page, or the known url) after a logon event. So clicking around in the course, revisiting the home page multiple times wouldn't change the 'most recent' time. The user could have logged on, opened a different course, then opened THIS reporting course. It's the time of opening THIS course that is the date needed.
  • a count being the number of times that the user opened this course.
What I'm doing to find the 'first time' open is to look at the standard logstore and find events like \core\event\course_viewed for the home page and taking the earliest of these. For the 'known url' I can use various columns to find the record in the logstore.

SELECT * FROM mdl_logstore_standard_log WHERE
        (eventname = '\core\event\course_viewed' AND courseid=153 AND userid=11909)
         OR (target = 'course_module' and contextinstanceid = 907 and userid=11909 and courseid=153)
        ORDER BY timecreated
        LIMIT 1

For the 'latest time' its trickier. Do I

  • look up all logon events and the for each one (starting at the newest and working backwards) see if there's a newer event recorded about 'opening' or 'viewing' the course?
  • ball-park it using the user_lastaccess table, which will be the last time something happened with that user/course - pretty vague.
  • use the user_lastaccess AND standard log table to guess when the course most recently opened?
  • use some other value I haven't thought of for 'when the course or any of its content was opened'
For the number of times the course was opened, ... anyone got any ideas if this is even possible?
Average of ratings: -
In reply to tim st.clair

Re: Help/ideas on Reporting dates, frequency logged on to a course

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

I didn't really understood everything but I've do something that I hope will help you (look like my daily job)...
If not sorry.

it's in pgsql so for getting the timestamp in date format it something else

-- Give the first and last access to a course or activities of the course and the number of time
SELECT to_char(to_timestamp(min(timecreated)),'yyyy-mm-dd HH24:MI:SS') firsttime
,to_char(to_timestamp(max(timecreated)),'yyyy-mm-dd HH24:MI:SS') lasttime
,count(id) nb_time
FROM (SELECT *
FROM mdl_logstore_standard_log
WHERE (eventname='\core\event\course_viewed' AND courseid=4552 AND userid=52591)
OR (target='course_module' AND courseid=4552 AND userid=52591 )
) t1

-- Give the same but for all user of the course.
SELECT userid
,to_char(to_timestamp(min(timecreated)),'yyyy-mm-dd HH24:MI:SS') firsttime
,to_char(to_timestamp(max(timecreated)),'yyyy-mm-dd HH24:MI:SS') lasttime
,count(id) nb_time
FROM (SELECT *
FROM mdl_logstore_standard_log
WHERE (eventname='\core\event\course_viewed' AND courseid=4552)
OR (target='course_module' AND courseid=4552)
) t1
GROUP BY userid
ORDER BY nb_time desc

Have nice day.

Dominique.
Average of ratings: -