ad-hoc contributed reports: Difference between revisions
(→=) |
|||
| Line 88: | Line 88: | ||
===Your Personal Resource View Report "Where did I leave off?"<br> | ===Your Personal Resource View Report "Where did I leave off?"<br> | ||
'''This report we wrote returns errors.''' It is showing resource views that never happened. '''DO NOT USE'''. I can delete it or people can look at the code here. I'll be posting it in the forums for some debug. | '''This report we wrote returns errors.''' It is showing resource views that never happened. '''DO NOT USE'''. I can delete it or people can look at the code here. I'll be posting it in the forums for some debug. | ||
Revision as of 13:45, 28 June 2010
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
===Your Personal Resource View Report "Where did I leave off?"
This report we wrote returns errors. It is showing resource views that never happened. DO NOT USE. I can delete it or people can look at the code here. I'll be posting it in the forums for some debug.
