ad-hoc contributed reports

These are community contributed reports for the Custom_SQL_queries_report site-wide report plugin (module)
Everyone is welcome to add their own. until we find a better way to exchange reports between us.
Enjoy :-)

---

Detailed ACTIONs for each MODULE

SELECT module,action,count(id) as counter FROM prefix_log GROUP BY module,action ORDER BY module,counter desc

Detailed ACTIONs for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)

SELECT r.name,l.action, count( l.userid ) as counter FROM `prefix_log` as l JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid JOIN prefix_role AS r ON ra.roleid = r.id WHERE ra.roleid IN (3,4,5) GROUP BY roleid,l.action ORDER BY counter desc

Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)

SELECT l.action, count( l.userid ) as counter , r.name FROM `prefix_log` as l JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid JOIN `prefix_role` AS r ON ra.roleid = r.id WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' ) GROUP BY roleid,l.action order by r.name,counter desc

Most Active courses

SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id GROUP BY courseId ORDER BY hits DESC

RESOURCE count for each COURSE

SELECT COUNT(l.id) count, l.course, c.fullname coursename FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id GROUP BY course ORDER BY count DESC

Most popular ACTIVITY

SELECT COUNT(l.id) hits, module FROM prefix_log l WHERE module != 'login' AND module != 'course' AND module != 'role' GROUP BY module ORDER BY hits DESC

System wide use of ACTIVITIES and RESOURCES

SELECT count( cm.id ) AS counter, m.name FROM `prefix_course_modules` AS cm JOIN prefix_modules AS m ON cm.module = m.id GROUP BY cm.module ORDER BY counter DESC

LOG file ACTIONS per MODULE per COURSE (IDs)

select course,module,action,count(action) as summa from prefix_log where action <> 'new' group by course,action,module order by course,module,action

"Compose Web Page" RESOURCE count

SELECT course,prefix_course.fullname, COUNT(*) AS Total FROM `prefix_resource` JOIN `prefix_course` ON prefix_course.id = prefix_resource.course WHERE type='html' GROUP BY course

Total Activity of Roles:"Teacher" and "None-Editing Teacher" by Dates and by Hours

The output columns of this report table can be used as base for a Pivot-Table which will show the amount of activity per hour per days in 3D graph view.

SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d' ) AS grptimed , DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh , count( l.userid ) AS counter FROM `prefix_log` AS l JOIN prefix_user AS u ON u.id = l.userid JOIN prefix_role_assignments AS ra ON l.userid = ra.userid JOIN prefix_role AS r ON r.id = ra.roleid WHERE ra.roleid IN (3,4) GROUP BY grptimed,grptimeh ORDER BY grptimed,grptimeh

FORUM use Count per COURSE -- not including NEWS Forum!

SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course ORDER BY total desc

FORUM use Count per COURSE by type -- not including NEWS Forum!

SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course,prefix_forum.type ORDER BY total desc