Little bit of spying

Maybe under slight influence of the latest James Bond movie, but more probably because I was asked for it, I provide you with SQL query to spy on your teachers. This asnwers a question which courses have a teacher (or teachers) and when was the first time and last time they visited their courses.
It could be used for 1.8+ version of Moodle, with default setting of roles (as roleid = 3 means "teacher" role).
I don't know about speed of this thing, so be careful of running this on production servers under heavy load. Use it lightly, no whipping of lazy teachers please

	SELECT cc.name kategorie, c.fullname course_name, c.shortname course_short_name, c.id as courseid,
	u.id as userid, u.firstname, u.lastname, u.username, u.email,
	DATE_FORMAT(FROM_UNIXTIME(l.mintime ), '%e %b %Y %H:%i') as oldest_login,
	DATE_FORMAT(FROM_UNIXTIME(l.maxtime ), '%e %b %Y %H:%i') as newest_login
	FROM mdl_course c
	JOIN mdl_course_categories cc
	ON c.category = cc.id
	LEFT JOIN mdl_context con
	ON c.id = con.instanceid
	JOIN mdl_role_assignments ra
	ON ra.contextid = con.id
	JOIN mdl_user u
	ON u.id = ra.userid
	LEFT JOIN (SELECT userid, course, max(time) as maxtime, min(time) as
	FROM mdl_log
	GROUP BY userid, course, module, action
	HAVING module = 'course' AND action = 'view') l
	ON l.course = c.id AND u.id = l.userid
	WHERE con.contextlevel = 50 AND ra.roleid = 3

view raw gistfile1.sql hosted with ❤ by GitHub

Btw I used Adminer, very lightweight MySql tool, for running this. This can come really handy, if you don't have anything stronger at hand.