It is easy to determine how many users are logging in, but that doesn't tell me the courses they are using.
I had one guess as to a possibility for determining which courses are active. I thought that one could query on `mdl_course` to find where `timemodified` NOT LIKE 0? However, that doesn't seem to change as I add information to an activity, or even when I add a resource.
Is there a different table I could query to determine which courses are actively being used?
What do you mean by "actively being used". I suggest some simple ways, hope that helps:
(1) In each course, go to the Administration block and click on Participants, you will see the number of users. To compare with the number of users in other courses, you can have the first idea about how many users there are in each course.
(2) Go to Site Administrator > Logs
- The whole site (ex. domain.com)
- All participants
- All days
Then at the fourth drop down menu, choose the title of each course.
By this way, you will see the logs of the course from the beginning. Howver, this way only works if at the beginning, you did set "loglifetime" in Administration > Configuration > Variables to "never delete logs"
Hope this helps, and sorry for my bad English.
Dinh Lu Giang
I have over 600 courses that have automatically been created (and the users enrolled via the external database enrollment), so I need some way to do this more efficiently. I can't really do this effectively by looking at each course, so I was hoping to do this behind the scenes some way.
Good question, by "actively being used" I mean that they have been modified in any way since they were created at the beginning of the term. That might mean posts to the news forum, resources added, activities added, activities on those activities, etc. Basically that the teacher has added to or modified the page in any way.
Does that make sense?
(it seems that the "timemodified" in the mdl_course table just referrs to the settings being changed, not necessarily any activity in the course.)
Sorry that i don't know about database.
Good luck anyway
An appropriate SQL query would be:
SELECT * FROM `mdl_log` GROUP BY `course` HAVING `time` = MAX(`time`)
ORDER BY `time` DESC LIMIT 0 , 100
which would return the 100 "most active courses" based on the date of their last log entry.
Unfortunately there doesn't appear to be a way to feed this into print_logs to get a nice log page up - I do think that moodle could easily perform far more powerful log analysis than just simple overviews, given the type of data it collects.
Thank you, that's closer. I have a couple of questions...
1) What is the date format? I see 10 digit numbers.
2) This also seems to show if someone has only clicked into the course (not necessarily that the course is being used per se), is there any way to be more specific?
I should be more clear about what statistics I am looking for. I have about 690 courses that are created automatically (from our registrar data) at the beginning of the semester. I don't know how many of these course are actually being used in any given semester.
When I do the above query, I get any course that anyone has even just clicked into, right? I think that is too loose a constraint for the type of usage data I am looking for. My guess is that I need something that will tell me if a teacher has added or modified anything in a given course. I think that would qualify as "real" activity.
Does that make sense? Is there a way to do that? Thank you!
Here is one SQL query that gives all of the courses that have over 100 records in the logs table:
SELECT c.fullname, count(course) AS actions, course as courseid
FROM mdl_log l, mdl_course c
WHERE l.course = c.id
GROUP BY course
HAVING actions > 100
ORDER BY actions DESC;
It lists the fullname,number of records in the logs table and the course id respectively. Replace mdl_ if you have a different table prefix. This is the "all time top". You can also loose the "HAVING logs > 100" and put "LIMIT 100" at the end (before the semicolon, that is). That would give you the all time top 100 list.
The times in Moodle database are Unix timestamps. If you wish to check which courses are the most active ones this year, you can do:
SELECT c.fullname, count(course) AS actions, course AS courseid
FROM mdl_log l, mdl_course c
WHERE l.course = c.id AND time > UNIX_TIMESTAMP('2006-01-01')
GROUP BY course
ORDER BY actions DESC
If you want just the January (for example), replace the WHERE line with
WHERE l.course = c.id AND time BETWEEN UNIX_TIMESTAMP('2006-01-01') AND UNIX_TIMESTAMP('2006-02-01')
The timestamps withour a time return the midnight, so you have to use the "first of February" to get the "very end of january". The format is 'yyyy-mm-dd hh:mm:ss' if you want to be more specific.
Wow, thank you, that is very helpful!