Note: You are currently viewing documentation for Moodle 3.11. Up-to-date documentation for the latest stable version of Moodle may be available here: ad-hoc contributed reports.

ad-hoc contributed reports: Difference between revisions

From MoodleDocs
(New page: ===Detailed ACTIONs for each MODULE=== <code sql> SELECT module,action,count(id) as counter FROM prefix_log GROUP BY module,action ORDER BY module,counter desc </code> ===Detailed ACTIONs...)
 
Line 1: Line 1:
===Detailed ACTIONs for each MODULE===
===Detailed ACTIONs for each MODULE===
<code sql>
<code sql>
SELECT module,action,count(id) as counter FROM prefix_log GROUP BY module,action ORDER BY module,counter desc
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
</code>
</code>



Revision as of 06:12, 2 June 2010

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