Hello All,
I have been asked to produce a detailed report of moodle participation outside normal class hours (9am - 5pm) and at weekends over a period of 6 months. Would anyone have any ideas as to the best way of producing this data? I will probably base it upon courses with the most participation.
Many thanks in advance,
M.
M - This is a tricky one. I'm not sure how many records you have in your database. but the time stored in the database is a timestamp (# of seconds since jan 1 1970). there are plenty of conversion tools to convert that number. If you are a programmer and a database person , you could do this lots of ways, create a mirror table storing the moodle log record id, the timestamp, then the hour and minute of the day, or a "check" field if that time was within the 9am - 5pm. Then you could create a view joining the two tables (but that might be slow).
You could export all the data, and then "prune the records" out side the time you had. How many thousands/millions of records do you have?
I'm guessing these solutions are probably much more complicated than you'd like.
Also, do you have an example of what that detailed report would contain? there is so much activity (logins, course views, resource views, quiz attempts, ...)
Steve
I'm currently also digging into Moodle and "learning analytics". While Moodle is able to produce a lot and really detailled data, I wonder what kind of data you are expected to come up with. As far as I know, Moodle produces data that can be tracked down to the single participant. If you don't need this, I would rather try to use some web analytics software like Google Analytics or Piwik (piwik.org).
Though I would say that both approaches need a thorough analysis.
Ugh, this is a nasty one.
Google analytics and such does help immensly in these situation. However, this can be useful only after implementing. To get information from old records you'll need to do it the 'old school' way.
I'm no SQL expert, but here is my suggestion bor the basic search:
SELECT * FROM mdl_log
WHERE course=[course id]
AND time >= UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 6 MONTH))
AND (DAYOFWEEK(FROM_UNIXTIME(time)) = 7 OR DAYOFWEEK(FROM_UNIXTIME(time)) = 1)
AND (HOUR(FROM_UNIXTIME(time)) < 9 OR HOUR(FROM_UNIXTIME(time)) >= 17)
Since the date/time functions work with formatted time there is a lot of convertion from the timestamp in the logs.
Furthermore, I didn't include the daylight saving time option because I've only used it in PHP and have no idea how to calculate it with SQL commands.
Remember, this query is only the start. You'll need to decide how much of it will be in SQL and how much in PHP.
Either way, this will result in a (relatively) long processing time, much longer than the usual server timeout. So, if you're planning to run it throught a browser I'd suggest printing something every now and then to keep the page alive.
Good use of TIME feature in this query
You could make it a little faster by only retrieving columns that are required (mdl_log has some columns you probably don't need), and you could alter the INTERVAL from 6 MONTH to 1 MONTH or EVEN 1 WEEK and export that data.
Using something like MySQL Workbench might be a good way to approach this task.
