ad-hoc contributed reports: Difference between revisions
(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
