Hello everyone,
I'm currently focusing on student online behaviors (e.g. who viewed what, when etc) and found the logging done by the system helpful but somewhat limited for my purposes. I'm trying to write a php script which will query data from all the relevant tables and then output counts per course, user, and resource.
My questions:
#1. which tables are actually involved?
Obviously mdl_user (id, firstname, lastname), mdl_course (id, fullname, shortname), mdl_log (id, userid, course, info), mdl_resources (id, name). Are there any other tables that one should take into account and if so which fields from those tables?
#2. which are the relations among the fields?
I've looked at the database schema and could not find any direct relation between the mdl_log table and the mdl_resources table. For example, it appears that the mdl_log.info field is somewhat related to the mdl_resources.name field but the two fields are not of the same data type and thus cannot be combined in a query?
The information provided on this site related to the database schema
(http://docs.moodle.org/en/Development:Database_Schema & http://docs.moodle.org /en/Development:Database_schema_introductionand) was unfortunately of little help.
Am I missing something? I'm somewhat confused as to how to proceed and would greatly appreciate any suggestions.
cheers,
Ilias
Ilias,
Is processing log files still an issue for you? I have been doing a bit of this recently, and have just stumbled across your post.
If you illustrated your desired output with some made up data with informatively named column headings, that might help someone write some sql for you?
You can combine mdl_log and mdl_resources in a query (in mysql anyway) by having the following in the WHERE clause of your query:
mdl_log.module = 'resource' and mdl_log.info = mdl_resource.id
Sacha
Is processing log files still an issue for you? I have been doing a bit of this recently, and have just stumbled across your post.
If you illustrated your desired output with some made up data with informatively named column headings, that might help someone write some sql for you?
You can combine mdl_log and mdl_resources in a query (in mysql anyway) by having the following in the WHERE clause of your query:
mdl_log.module = 'resource' and mdl_log.info = mdl_resource.id
Sacha
Hi,
we are about to develop a statistic and data mining tool for moodle and have an similar issue.
I suppose the log.info field to be the resource id when module is resource. But as i test that on the database i recognize that this is not correct for all entrys. For 961636 entrys for resources with action=view, the info field gives the right id only in 883283 cases.
Now we wonder what happens in the entrys where the info field gives wrong values.
Ben
we are about to develop a statistic and data mining tool for moodle and have an similar issue.
I suppose the log.info field to be the resource id when module is resource. But as i test that on the database i recognize that this is not correct for all entrys. For 961636 entrys for resources with action=view, the info field gives the right id only in 883283 cases.
Now we wonder what happens in the entrys where the info field gives wrong values.
Ben
Hi,
we made a case studie on our data model, with questions like: how many views are there for a resource before a concretly date.
To solve your question we would have to add a "after date" condition to that query, to get the count of views for one day. I think we should make that query automated to create weekly/monthly overviews without making a query for each singel day.
Or we could make a overview how many views are there on wich resource on one singel day. The result would be a graph, how many students and/or teachers viewed wich resource in the given timeframe.
This may be useful features, i will add them to our question catalog. Other ideas for questions to the database are welcome.
we made a case studie on our data model, with questions like: how many views are there for a resource before a concretly date.
To solve your question we would have to add a "after date" condition to that query, to get the count of views for one day. I think we should make that query automated to create weekly/monthly overviews without making a query for each singel day.
Or we could make a overview how many views are there on wich resource on one singel day. The result would be a graph, how many students and/or teachers viewed wich resource in the given timeframe.
This may be useful features, i will add them to our question catalog. Other ideas for questions to the database are welcome.
Have you developed your stats / data mining tool? We're looking into developing a similar tool, and would like to build on work already done. Would appreciate an update on what you've done and any "open issues" you're still working on - might be good opportunity for collaboration.
Best, John
thanks mdl_log.module = 'resource' and mdl_log.info = mdl_resource.id is what I looking for
--