Using the Moodle database as a source of staff activity

Kate one of our Technology Enhanced Learning Advisor s (TERA) asked if I could provide her with details of how many staff in her faculty are using Moodle, assignments, quizzes and Turnitin assignments. While I didn’t have any reports to hand I do have access to a copy of the Moodle database that is a only a day behind the live data.
As our Moodle, shortname is the unit name we use in our student records system with the year and instance it is very easy to create a SQL statement to bring back quizzes for that faculty.
As I said, our Moodle short name has the following format
For most of the courses, the first number in the unit code shows the owning Faculty, but we do have units that do not follow this pattern and in those cases, the SQL becomes a little more complex.
So an example a Moodle shortname would be 399Z999_1415_9Z3F so we can construct a like statement that only returns shortnames that start with a 3 have 1415 as the year and all instances
So the SQL statement would be
SELECT mdl_course.shortname, mdl_course.fullname ,mdl_quiz.course, mdl_quiz.intro, from_unixtime(mdl_quiz.timeopen) ,
FROM mmu01moodle.mdl_quiz, mdl_course
where mdl_course.shortname like ‘3%1415%’ and;

The relationship between the ID column in the course table and the course column in the quiz table allows you to bring back meaningful data such as the Moodle fullname, making the results more meaningful for the reader. The logic for Moodle assignments is the same but of course you need to replace the references to the quiz table with references to the assign table.
As with most Turnitin issues Turnitin is more difficult not only do you have Turnitintool table you have the Turnitin_parts table and the parts table can and often does have different due dates to those stored in the mail Turnitintool table. But I think that is for another blog