Difference between revisions of "ad-hoc contributed reports"

Jump to: navigation, search
(Lists "Totally Opened Courses" (visible, opened to guets, with no password))
m (Typos)
Line 85: Line 85:
 
GROUP BY course
 
GROUP BY course
 
</code>
 
</code>
 +
 
===Total Activity of Roles:"Teacher" and "None-Editing Teacher" by Dates and by Hours===
 
===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
 
The output columns of this report table can be used as base for a Pivot-Table
Line 110: Line 111:
 
ORDER BY total desc
 
ORDER BY total desc
 
</code>
 
</code>
 +
 
===FORUM use Count per COURSE by type -- not including NEWS Forum!===
 
===FORUM use Count per COURSE by type -- not including NEWS Forum!===
 
<code sql>
 
<code sql>
Line 119: Line 121:
 
ORDER BY total desc
 
ORDER BY total desc
 
</code>
 
</code>
 +
 
===LIST of all site USERS by COURSE enrolment===
 
===LIST of all site USERS by COURSE enrolment===
 
Reports a site global list of all users enroled in each course
 
Reports a site global list of all users enroled in each course
Line 141: Line 144:
 
</code>
 
</code>
  
==Permissions Overides on Categories==
+
===Permissions Overides on Categories===
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712834 Séverin Terrier] )
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712834 Séverin Terrier] )
 
<code sql>
 
<code sql>
Line 152: Line 155:
 
</code>
 
</code>
  
==Lists "Totally Opened Courses" (visible, opened to guets, with no password)==
+
===Lists "Totally Opened Courses" (visible, opened to guests, with no password)===
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712837 Séverin Terrier] )
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712837 Séverin Terrier] )
 
<code sql>
 
<code sql>

Revision as of 15:35, 23 November 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

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

LIST of all site USERS by COURSE enrolment

Reports a site global list of all users enroled in each course

SELECT
USER.firstname AS Firstname,
USER.lastname AS Lastname,
USER.email AS Email,
USER.city AS City,
course.fullname AS Course
FROM
prefix_user AS USER,
prefix_course AS course,
prefix_role_assignments AS asg
INNER JOIN prefix_context AS context ON asg.contextid=context.id   
WHERE
context.contextlevel = 50
AND
USER.id=asg.userid
AND
context.instanceid=course.id

Permissions Overides on Categories

(By: Séverin Terrier )

SELECT rc.id, ct.instanceid, ccat.name, rc.roleid, rc.capability, rc.permission, 
DATE_FORMAT( FROM_UNIXTIME( rc.timemodified ) , '%Y-%m-%d' ) AS timemodified, rc.modifierid, ct.instanceid, ct.path, ct.depth
FROM `prefix_role_capabilities` AS rc
INNER JOIN `prefix_context` AS ct ON rc.contextid = ct.id
INNER JOIN `prefix_course_categories` AS ccat ON ccat.id = ct.instanceid
AND `contextlevel` =40

Lists "Totally Opened Courses" (visible, opened to guests, with no password)

(By: Séverin Terrier )

SELECT id , category , fullname , shortname , enrollable
FROM `prefix_course`
WHERE `guest` =1
AND `password` = ""
AND `visible` =1