Difference between revisions of "ad-hoc contributed reports"

Jump to: navigation, search

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

(Lists "Totally Opened Courses" (visible, opened to guests, with no password))
 
(129 intermediate revisions by 10 users not shown)
Line 42: Line 42:
 
GROUP BY course.id
 
GROUP BY course.id
 
ORDER BY COUNT(course.id) DESC
 
ORDER BY COUNT(course.id) DESC
 +
</code>
 +
 +
=== Enrolment count in each Course ===
 +
 +
Shows the total number of enroled users of all roles in each course. Sorted by course name.
 +
 +
<code sql>
 +
SELECT c.fullname, COUNT(ue.id) AS Enroled
 +
FROM prefix_course AS c
 +
JOIN prefix_enrol AS en ON en.courseid = c.id
 +
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 +
GROUP BY c.id
 +
ORDER BY c.fullname
 
</code>
 
</code>
  
Line 237: Line 250:
  
 
=== List of users with language===
 
=== List of users with language===
 +
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
  
 
An issue with systems that do not have their default language set up properly is the need to do a mass change for all users to a localization. A common case is changing default English to American English.  
 
An issue with systems that do not have their default language set up properly is the need to do a mass change for all users to a localization. A common case is changing default English to American English.  
Line 244: Line 259:
 
SELECT username, lang from prefix_user  
 
SELECT username, lang from prefix_user  
 
</code>
 
</code>
 +
 +
NOTE: UPDATE commands require the ability to alter the database directly via tools like Adminer or PHPMyAdmin or other db tools.
  
 
This code will change the setting from 'en' to 'en_us' for all users:
 
This code will change the setting from 'en' to 'en_us' for all users:
Line 249: Line 266:
 
<code sql>
 
<code sql>
 
UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
 
UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
 +
</code>
 +
 +
To do this for only users who have a particular country set, use this as an example:
 +
<code sql>
 +
UPDATE prefix_user SET lang = 'en_us' WHERE country = 'US' AND lang = 'en'
 +
</code>
 +
 +
=== List of users with Authentication ===
 +
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
 +
 +
Sometimes you need to do mass changes of authentication methods. A common case is changing default manual to LDAP.
 +
 +
This will show you the Authentication setting for all users:
 +
<code sql>
 +
SELECT username, auth from prefix_user
 +
</code>
 +
 +
NOTE: UPDATE commands require the ability to alter the database directly via tools like Adminer or PHPMyAdmin or other db tools.
 +
 +
This code will change the setting from 'manual' to 'ldap' for all users except for the first two accounts which are Guest and Admin. (WARNING: it is bad practice to change your admin account from manual to an external method as failure of that external method will lock you out of Moodle as admin.)
 +
 +
<code sql>
 +
UPDATE prefix_user SET auth = 'ldap' WHERE auth = 'manual' AND id > 2
 
</code>
 
</code>
  
Line 268: Line 309:
  
 
FROM `mdl_role_capabilities` AS mrc
 
FROM `mdl_role_capabilities` AS mrc
 +
</code>
 +
 +
=== User's accumulative time spent in course ===
 +
A sum up of the time delta between logstore_standard_log user's records, considering the a 2 hour session limit.
 +
 +
Uses: current user's id %%USERID%% and current course's id %%COURSEID%% 
 +
 +
And also using a date filter (which can be ignored) 
 +
 +
The extra "User" field is used as a dummy field for the Line chart Series field, in which I use X=id, Series=Type, Y=delta.
 +
 +
<code sql>
 +
SELECT
 +
l.id,
 +
l.timecreated,
 +
DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y') AS dTime,
 +
@prevtime := (SELECT max(timecreated) FROM mdl_logstore_standard_log
 +
WHERE userid = %%USERID%% and id < l.id ORDER BY id ASC LIMIT 1) AS prev_time,
 +
IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime,
 +
l.timecreated-@prevtime AS delta,
 +
"User" as type
 +
 +
FROM prefix_logstore_standard_log as l,
 +
(SELECT @delta := 0) AS s_init
 +
# Change UserID
 +
WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%%
 +
%%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
 
</code>
 
</code>
  
Line 322: Line 390:
 
WHERE `action` LIKE '%login%' group by userid
 
WHERE `action` LIKE '%login%' group by userid
 
ORDER BY Activity DESC
 
ORDER BY Activity DESC
 +
</code>
 +
===Distinct user logins per month===
 +
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
 +
 +
The following will show you the months of the current calendar year with the total number of distinct, unique user logins per month. Change the year in the WHERE clause to the year you need.
 +
 +
<code sql>
 +
SELECT
 +
COUNT(DISTINCT l.userid) AS 'DistinctUserLogins',
 +
DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS 'Month'
 +
FROM prefix_logstore_standard_log l
 +
WHERE l.action = 'loggedin' AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2017'
 +
GROUP BY MONTH(FROM_UNIXTIME(l.timecreated))
 
</code>
 
</code>
  
 
===Total activity per course, per unique user on the last 24h===
 
===Total activity per course, per unique user on the last 24h===
 
<code sql>
 
<code sql>
Select
+
SELECT
    Count(Distinct userid) As countUsers
+
    COUNT(DISTINCT userid) AS countUsers
  , Count(course) As countVisits
+
  , COUNT(l.courseid) AS countVisits
  , concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
  , CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">', c.fullname, '</a>') AS Course
  
From prefix_log as l
+
FROM mdl_logstore_standard_log AS l
JOIN prefix_course as c on c.id = l.course
+
  JOIN mdl_course AS c ON c.id = l.courseid
Where course > 0 and FROM_UNIXTIME(time) >= DATE_SUB(NOW(), INTERVAL 1 DAY) and c.fullname LIKE '%תשעג%'
+
WHERE l.courseid > 0
Group By course
+
      AND FROM_UNIXTIME(l.timecreated) >= DATE_SUB(NOW(), INTERVAL 1 DAY)
 +
      AND c.fullname LIKE '%תשעו%'
 +
GROUP BY l.courseid
 
ORDER BY countVisits DESC
 
ORDER BY countVisits DESC
 
</code>
 
</code>
  
 
===Weekly Instructor Online Participation===
 
===Weekly Instructor Online Participation===
Contributed by Elizabeth Dalton
+
Contributed by Elizabeth Dalton, Granite State College
  
 
Displays participation of instructors in all courses per week of a term, including pre-term and post-term edits. An edit is defined as a change to the course, such as a discussion post, the grading of an assignment, or the uploading of file attachments, as well as alterations to course content.
 
Displays participation of instructors in all courses per week of a term, including pre-term and post-term edits. An edit is defined as a change to the course, such as a discussion post, the grading of an assignment, or the uploading of file attachments, as well as alterations to course content.
Line 347: Line 431:
  
 
At our institution, we include filters on the course name or category to constrain by terms. These are very specific to how course names and categories are constructed at our institution, so I've removed those elements from this code. Also, our terms are 12 weeks long. You would want to insert additional "SUM" lines for longer terms, or remove lines for shorter terms.
 
At our institution, we include filters on the course name or category to constrain by terms. These are very specific to how course names and categories are constructed at our institution, so I've removed those elements from this code. Also, our terms are 12 weeks long. You would want to insert additional "SUM" lines for longer terms, or remove lines for shorter terms.
 +
 +
'''Note''': This report can take a long time to run. While it can be run in Configurable Reports on demand, it may be more appropriate to implement it in the Ad Hoc Queries plugin as a scheduled report.
 +
 +
'''Note''': This version uses legacy (pre-2.7) logs. See below for post-2.7 Standard Logs version.
  
 
<code sql>
 
<code sql>
Line 401: Line 489:
 
</code>
 
</code>
  
 +
'''Note''': Post-2.7 log version:
  
===Module activity (Hits) between dates===
 
 
<code sql>
 
<code sql>
SELECT module, COUNT( * )  
+
SELECT  
FROM prefix_log AS l
+
c.shortname AS CourseID
WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN  '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
+
, cc.name AS Category
GROUP BY module
+
, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
</code>
+
 
 +
, (SELECT COUNT( ra2.userid ) AS Users2 FROM prefix_role_assignments AS ra2
 +
JOIN prefix_context AS ctx2 ON ra2.contextid = ctx2.id
 +
WHERE ra2.roleid = 5 AND ctx2.instanceid = c.id) AS Students
  
===Module activity (Instances and Hits) for each academic year===
+
, FROM_UNIXTIME(c.startdate) AS Course_Start_Date
<code sql>
 
SELECT name
 
  
,(SELECT COUNT(*)
+
, c.visible AS Visible
FROM mdl_log AS l
 
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
 
AND l.module = m.name AND l.action = 'add'
 
) AS "Added 2010"
 
  
,(SELECT COUNT(*)  
+
, COUNT(DISTINCT l.id) AS Edits
FROM mdl_log AS l
+
 
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
+
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
AND l.module = m.name
 
) AS "Used 2010"
 
  
,(SELECT COUNT(*)  
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
FROM mdl_log AS l
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00')
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
AND l.module = m.name AND l.action = 'add'
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
) AS "Added 2011"
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
  
,(SELECT COUNT(*)
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
FROM mdl_log AS l
 
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00')
 
AND l.module = m.name
 
) AS "Used 2011"
 
  
 +
, CONCAT('<a target="_new" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS Link
  
,(SELECT COUNT(*)
+
FROM prefix_user AS u
FROM mdl_log AS l
+
LEFT JOIN prefix_role_assignments AS ra ON u.id = ra.userid
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
+
LEFT JOIN prefix_context AS ctx ON ra.contextid = ctx.id
AND l.module = m.name AND l.action = 'add'
+
LEFT JOIN prefix_course AS c ON c.id = ctx.instanceid
) AS "Added 2012"
+
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
 +
 
 +
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')
  
,(SELECT COUNT(*)
+
WHERE ra.roleid =3
FROM mdl_log AS l
+
AND ctx.instanceid = c.id
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
+
AND c.shortname LIKE '%OL-%'
AND l.module = m.name
+
AND cc.idnumber LIKE '%current%'
) AS "Used 2012"
 
  
FROM mdl_modules AS m
+
GROUP BY u.idnumber, c.id
 +
#HAVING students > 0
 +
ORDER BY RIGHT(c.shortname,2), c.shortname
 
</code>
 
</code>
  
==Course Reports==
+
===Weekly Student Online Participation===
===Most Active courses===
+
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
Displays participation of students in the current course by week, including pre-term and post-term edits. An edit is defined as a change to the course, such as a discussion post, the submission of an assignment, or the completion of a quiz, as well as alterations to course content such as database entries (if permitted).
 +
 
 +
Links to three other reports are also provided:
 +
 
 +
* Logs: complete log entries for the student in the course, organized by date
 +
* Activity Outline: the "Outline Report" from the User Activity Reports, summarizing the student's activity in the course, organized by course content
 +
* Consolidated Activity Report: the "Complete Report" from the User Activity Reports, detailing the student's activity in the course, organized by course content (includes text of forum posts)
 +
 
 +
'''Note''': This should be defined as a "Global" report (visible from within all courses). At our institution, our terms are 12 weeks long. You would want to insert additional "SUM" lines for longer terms, or remove lines for shorter terms. We pull advisor names into student user profiles as part of our configuration. These lines are present in the code below, but are commented out, as they are very specific to your Moodle configuration.
 +
 
 +
'''Note''': This version of the report uses legacy (pre-2.7) logs. See below for a post-2.7 Standard Logs version.
 +
 
 
<code sql>
 
<code sql>
SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
+
SELECT  
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
+
u.lastname AS 'Last Name'
GROUP BY courseId
+
, u.firstname AS 'First Name'
ORDER BY hits DESC
+
COUNT(l.id) AS 'Edits'
</code>
+
 
 +
, SUM(IF((l.time-c.startdate)/7<0,1,0)) AS 'Before Term'
 +
 
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=0,1,0)) AS 'Week 1'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=1,1,0)) AS 'Week 2'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=2,1,0)) AS 'Week 3'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=3,1,0)) AS 'Week 4'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=4,1,0)) AS 'Week 5'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=5,1,0)) AS 'Week 6'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=6,1,0)) AS 'Week 7'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=7,1,0)) AS 'Week 8'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=8,1,0)) AS 'Week 9'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=9,1,0)) AS 'Week 10'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=10,1,0)) AS 'Week 11'
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))=11,1,0)) AS 'Week 12'
  
===Active courses, advanced===
+
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))>=15,1,0)) AS 'After Term'
Including: Teacher's name, link to the course, All types of log activities, special YEAR generated field, Activities and Resource count, enrolled Student count
 
<code sql>
 
SELECT COUNT(l.id) hits, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
  
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'
FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 
  
,CASE
+
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'
  WHEN c.fullname LIKE '%תשע' THEN 'תשע'
 
  WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
 
  WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
 
END AS Year
 
  
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
+
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'
  
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
FROM prefix_user AS u
 +
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+
JOIN prefix_course AS c ON c.id = ctx.instanceid
 +
JOIN prefix_course_categories as cc ON c.category = cc.id
  
FROM prefix_log l  
+
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%"
INNER JOIN prefix_course c ON l.course = c.id
+
 
GROUP BY c.id
+
WHERE ra.roleid =5
HAVING Modules > 2
+
AND ctx.instanceid = c.id
ORDER BY Year DESC, hits DESC
+
 
 +
AND c.id = %%COURSEID%%
 +
 
 +
GROUP BY u.idnumber
 +
 
 +
ORDER BY u.lastname, u.firstname
 
</code>
 
</code>
  
===Count unique teachers with courses that use at least X module (Moodle19)===
+
'''Note''': Post-2.7 (Standard Logs) version
You can remove the outer "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQL query and get the list of the Teachers and Courses.
+
 
 
<code sql>
 
<code sql>
SELECT COUNT(*)
+
SELECT  
FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher
+
u.lastname AS 'Last Name'
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules
+
, u.firstname AS 'First Name'
FROM prefix_course AS c
+
, COUNT(l.id) AS 'Edits'
JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
+
 
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
JOIN prefix_user AS u ON u.id = ra.userid
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE  ra.roleid = 3
 
GROUP BY u.id
 
HAVING Modules > 5) AS ActiveTeachers
 
</code>
 
  
===RESOURCE count for each COURSE===
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
<code sql>
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
SELECT COUNT(l.id) count, l.course, c.fullname coursename
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
GROUP BY course
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
ORDER BY count DESC
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
 +
 
 +
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
 +
 
 +
# Our institution stores academic advisor names and emails in custom profile fields
 +
#, CONCAT('<a href="mailto:',uce.data,'">',uid.data, '</a>')  AS 'Academic Advisor'
 +
 
 +
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS 'Logs'
 +
 
 +
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=outline">','Outline','</a>') AS 'Activity Outline'
 +
 
 +
, CONCAT('<a target="_blank" href="%%WWWROOT%%/report/outline/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'&mode=complete">','Activity','</a>') AS 'Consolidated Activity'
 +
 
 +
, CONCAT('<a target="_blank" href="%%WWWROOT%%/mod/forum/user.php',CHAR(63),'id=',u.id,'&course=',c.id,'">','Posts','</a>') AS 'Posts'
 +
 
 +
FROM prefix_user AS u
 +
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_course AS c ON c.id = ctx.instanceid
 +
JOIN prefix_course_categories as cc ON c.category = cc.id
 +
 
 +
# student academic coach - you can include custom profile field data with these methods
 +
# LEFT JOIN prefix_user_info_data as uid ON u.id = uid.userid AND uid.fieldid = '2'
 +
# student academic coach email
 +
# LEFT JOIN prefix_user_info_data as uce on u.id = uce.userid AND uce.fieldid = '6'
 +
 
 +
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')
 +
 
 +
WHERE ra.roleid =5
 +
AND ctx.instanceid = c.id
 +
 
 +
AND c.id = %%COURSEID%%
 +
 
 +
GROUP BY u.idnumber
 +
 
 +
ORDER BY u.lastname, u.firstname
 
</code>
 
</code>
  
===Common resource types count for each Category (Moodle19)===
+
===My Weekly Online Participation===
Including sub-categories in total count.
+
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
Displays participation of the '''current user''' in the '''current course''' by week, including pre-term and post-term submissions/edits. A submission/edit is defined as a change to the course, such as a discussion post, the submission of an assignment, or the completion of a quiz, as well as alterations to course content such as database entries or new course activities or resources (if permitted).
 +
 
 +
This report uses Standard Logs (post 2.7).
 +
 
 
<code sql>
 
<code sql>
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
+
SELECT  
,(SELECT COUNT( * )  
+
 
FROM prefix_resource AS r
+
l.component AS 'activity'
JOIN prefix_course AS c ON c.id = r.course
+
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
, COUNT(DISTINCT IF((l.timecreated-c.startdate)<0,l.id,NULL)) AS 'Before Term'
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%'
+
 
) AS Links
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=0,l.id,NULL)) AS 'Week 1'
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=1,l.id,NULL)) AS 'Week 2'
,(SELECT COUNT( * )  
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=2,l.id,NULL)) AS 'Week 3'
FROM prefix_resource AS r
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=3,l.id,NULL)) AS 'Week 4'
JOIN prefix_course AS c ON c.id = r.course
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=4,l.id,NULL)) AS 'Week 5'
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=5,l.id,NULL)) AS 'Week 6'
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference NOT LIKE 'http://%'
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=6,l.id,NULL)) AS 'Week 7'
) AS Files
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=7,l.id,NULL)) AS 'Week 8'
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=8,l.id,NULL)) AS 'Week 9'
,(SELECT COUNT( * )  
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=9,l.id,NULL)) AS 'Week 10'
FROM prefix_resource AS r
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=10,l.id,NULL)) AS 'Week 11'
JOIN prefix_course AS c ON c.id = r.course
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))=11,l.id,NULL)) AS 'Week 12'
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
 
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'directory'  
+
, COUNT(DISTINCT IF(FLOOR((l.timecreated - c.startdate)/(60*60*24*7))>=12,l.id,NULL)) AS 'After Term'
) AS Folders
 
 
,(SELECT COUNT( * )  
 
FROM prefix_resource AS r
 
JOIN prefix_course AS c ON c.id = r.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'html'  
 
) AS Pages
 
 
,(SELECT COUNT(*)  
 
FROM stats_log_context_role_course
 
WHERE roleid = 5 AND module = 'resource' AND category = mcc.id
 
) AS Hits
 
  
FROM prefix_course_categories AS mcc
+
,  COUNT(l.id) AS 'Total'
ORDER BY mcc.path
+
 
</code>
+
FROM prefix_user AS u
Where "stats_log_context_role_course" (in the above SQL query) is a VIEW generated by:
+
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
<code sql>
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
CREATE VIEW stats_log_context_role_course AS
+
JOIN prefix_course AS c ON c.id = ctx.instanceid
SELECT l.course, c.category, cc.path, l.module, l.action, ra.userid, ra.roleid
+
JOIN prefix_course_categories as cc ON c.category = cc.id
FROM prefix_log AS l
+
 
JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
+
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id  AND l.crud IN ('c','u')
JOIN prefix_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id
+
 
JOIN prefix_course AS c ON c.id = l.course
+
WHERE 1
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
AND ctx.instanceid = c.id
 +
 
 +
AND c.id = %%COURSEID%%
 +
AND u.id = %%USERID%%
 +
 
 +
GROUP BY l.component
 +
 
 +
ORDER BY l.component
 
</code>
 
</code>
  
Same query but for Moodle2+
+
===Faculty/Student Interactions===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
Returns a count of instructor and other-student responses to student activity for the specified time period. This report can help indicate whether students' comments are being responded to, as well as summarizing post activity by students during the specified time.
 +
 
 +
'''Note''': This version of the report uses legacy (pre-2.7) logs. See below for the post-2.7 Standard Logs version.
 +
 
 +
'''Note''': This should be defined as a "Global" report (visible from within all courses).
 +
 
 +
'''Note''': This report can take a long time to run.
 +
 
 +
 
 
<code sql>
 
<code sql>
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
+
SELECT  
mcc.path,
 
  
(SELECT COUNT(*)  
+
# Identify student
FROM prefix_url AS u
+
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
JOIN prefix_course AS c ON c.id = u.course
+
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
, IF((COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL) )>0) OR (COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL))>0) OR  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))),'YES','NO') AS 'Student Participated This week'
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
+
 
) AS URLs,
+
, IF((COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) )>0) OR (COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asg.id,NULL))>0) OR (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id  AND mts.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))),'YES','NO') AS 'Student Contacted This week'
  
(SELECT COUNT(*)
+
## Only posts within last 7 days
FROM prefix_folder AS f
 
JOIN prefix_course AS c ON c.id = f.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 
) AS FOLDERs,
 
  
(SELECT COUNT(*)  
+
# Count posts by student
FROM prefix_page AS p
+
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
JOIN prefix_course AS c ON c.id = p.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 
) AS PAGEs,
 
  
(SELECT COUNT(*)  
+
# Count replies to student posts by instructors
FROM prefix_book AS b
+
, COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Instr Replies - 7 days'
JOIN prefix_course AS c ON c.id = b.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 
) AS BOOKs,
 
  
(SELECT COUNT(*)  
+
# using link back to student posts on replies, get unique student IDs responded
FROM prefix_label AS l
+
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpsr.id,NULL)) - COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Stu Replies - 7 days'
JOIN prefix_course AS c ON c.id = l.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 
) AS LABELs,
 
  
(SELECT COUNT(*)  
+
# all replies
FROM prefix_tab AS t
+
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
JOIN prefix_course AS c ON c.id = t.course
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 
) AS TABs
 
  
FROM prefix_course_categories AS mcc
+
# add in count of graded assignments - 7 days
ORDER BY mcc.path
+
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
</code>
+
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
  
===Detailed Resource COUNT by Teacher in each course===
+
# Messages between students and instructors - 7 days
 +
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))) AS 'Msg Stu to Instr - 7 days'
 +
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id  AND mts.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))) AS 'Msg Instr to Stu - 7 days'
  
Including (optional) filter by: year, semester and course id.
+
## All posts in course so far
 +
# Count posts by student
 +
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
  
<code sql>
+
# Count replies to student posts by instructors
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
+
, COUNT(DISTINCT fpi.id) AS 'Forum Instr Replies - to date'
, c.id
 
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 
  FROM prefix_role_assignments AS ra
 
  JOIN prefix_user AS u ON ra.userid = u.id
 
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 
  
, (CASE
+
# using link back to student posts on replies, get unique student IDs responded
WHEN c.fullname LIKE '%תשעב%' THEN '2012'
+
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Forum Stu Replies - to date'
WHEN c.fullname LIKE '%תשעא%' THEN '2011'
 
END ) as Year
 
, (CASE
 
WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A'
 
WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B'
 
WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C'
 
END ) as Semester
 
,COUNT(c.id) AS Total
 
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs
 
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs
 
  
FROM `prefix_resource` as r
+
# all replies
JOIN `prefix_course` AS c on c.id = r.course
+
, COUNT(DISTINCT fpsr.id) AS 'Forum All Replies - to date'
#WHERE type= 'file' and reference NOT LIKE 'http://%'  
 
  
#WHERE 1=1
+
# add in count of graded assignments - whole course
#%%FILTER_YEARS:c.fullname%%
+
, COUNT(DISTINCT asb.id) AS 'Assign Submit - to date'
#AND c.fullname LIKE '%2013%'
+
, COUNT(DISTINCT asg.id) AS 'Assign Grades - to date'
 +
 
 +
# Messages between students and instructors - to date
 +
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Msg Stu to Instr - to date'
 +
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Msg Instr to Stu - to date'
 +
 
 +
## JOINS
 +
 
 +
# Start by getting all the students in the course
 +
FROM prefix_user AS allstu
 +
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
 +
JOIN prefix_context AS ctx  ON ras.contextid = ctx.id
 +
JOIN prefix_course AS c ON c.id = ctx.instanceid
 +
JOIN prefix_course_categories as cc ON c.category = cc.id
 +
 
 +
# Now we get the forums and forum discussions from this course only
 +
LEFT JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
 +
LEFT JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
  
GROUP BY course
+
# These are forum discussion posts just by students within specified time
ORDER BY COUNT(c.id) DESC
+
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
</code>
 
  
===Courses that are defined as using GROUPs===
+
# Separately, we connect the instructors of the courses
<code sql>
+
# We can use the context we have already gotten for the students
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
+
LEFT JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
 
FROM `prefix_course` AS c
 
WHERE groupmode > 0
 
</code>
 
  
===Courses with Groups===
+
# Now we will connect to posts by instructors that are replies to student posts
 +
# This is a left join, because we don't want to eliminate any students from the list
 +
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
  
List of all courses with Groups in them (groupmode > 0). You can also use groupmode=1 to list just Separate type groups or groupmode=2 to list Visible type groups.
+
# To get identities of only those students who were replied to:
 +
# Connect from instr replies back up to parent posts by students again
 +
# This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
 +
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
  
<code sql>
+
# We also want to know if students are replying to one another
SELECT c.shortname, g.name, c.groupmode
+
# These are posts that are replies to student posts
FROM prefix_course AS c
+
# Again, a left join
JOIN prefix_groups AS g ON c.id = g.courseid
+
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
WHERE c.groupmode > 0
 
</code>
 
  
===Groups in course with member list===
+
# get the activity modules
 +
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
  
List the groups in a course (replace the # by the course id number) with the members of each group.
+
# get the assignments
 +
LEFT JOIN prefix_assign AS a ON  cm.instance = a.id
 +
LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
 +
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
  
<code sql>
+
# We care about messages that involve both the instructor and students of this course
SELECT c.shortname, g.name AS Groupname, u.username
+
# messages from instructor to students:
FROM prefix_course AS c
+
# LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
JOIN prefix_groups AS g ON g.courseid = c.id
+
# LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
JOIN prefix_groups_members AS m ON g.id = m.groupid
 
JOIN prefix_user AS u ON m.userid = u.id
 
WHERE c.id = #
 
</code>
 
  
===Group Export===
+
WHERE 
 +
c.id = %%COURSEID%%
  
There's a [[Import_groups|group import]] function, but no export. Use this to give you a report with the proper column order and headings to export to a csv file you can then import into another course to replicate the groups. This is a simple version with just the main fields: groupname, description, enrolment key.
+
# GROUP BY c.shortname , allstu.id
 +
GROUP BY allstu.id
  
<code sql>
+
ORDER BY allstu.lastname
SELECT g.name AS groupname, g.description, g.enrolmentkey
 
FROM prefix_groups AS g
 
JOIN prefix_course as c ON g.courseid = c.id
 
WHERE c.id = #
 
 
</code>
 
</code>
  
===List all Courses in and below a certain category===
+
'''Note''': Post-2.7 Standard Logs version
Use this SQL code to retrieve all courses that exist in or under a set category.
 
  
$s should be the id of the category you want to know about...
 
 
<code sql>
 
<code sql>
SELECT prefix_course. * , prefix_course_categories. *
+
SELECT  
FROM prefix_course, prefix_course_categories
 
WHERE prefix_course.category = prefix_course_categories.id
 
AND (
 
prefix_course_categories.path LIKE '/$s/%'
 
OR prefix_course_categories.path LIKE '/$s'
 
)
 
</code>
 
  
===List all Categories in one level below a certain category===
+
# Identify student
Use this PHP code to retrieve a list of all categories below a certain category.
+
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
  
$s should be the id of the top level category you are interested in.
+
, IF((COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL) )>0) OR (COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL))>0) OR  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))),'YES','NO') AS 'Student Participated This week'
<code php>
 
<?php
 
  
require_once('./config.php');
+
, IF((COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) )>0) OR (COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asg.id,NULL))>0) OR (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id  AND mts.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))),'YES','NO') AS 'Student Contacted This week'
  
$parent_id = $s;
+
## Only posts within last 7 days
  
$categories= array();
+
# Count posts by student
 +
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
  
$categories = get_categories($parent_id);
+
# Count replies to student posts by instructors
 +
, COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Instr Replies - 7 days'
  
echo '<ol>';
+
# using link back to student posts on replies, get unique student IDs responded
foreach ($categories as $category)
+
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP() - (7*24*60*60)),fpsr.id,NULL)) - COUNT(DISTINCT IF(fpi.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpi.id,NULL) ) AS 'Forum Stu Replies - 7 days'
        {
 
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
 
        }
 
echo '</ol>';
 
  
?>
+
# all replies
</code>
+
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
  
===Blog activity per Course (not including VIEW)===
+
# add in count of graded assignments - 7 days
Filter activity logging to some specific Course Categories!
+
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
+ link course name to actual course (for quick reference)
+
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP() - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
(you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
 
<code sql>
 
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
 
,m.name ,count(cm.id) as counter
 
,(SELECT Count( ra.userid ) AS Users
 
FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
WHERE ra.roleid = 5
 
AND ctx.instanceid = c.id
 
) AS Students
 
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
 
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id
 
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 
GROUP BY cm.course,cm.module order by counter desc
 
</code>
 
  
===Student's posts content in all course blogs (oublog)===
+
# Messages between students and instructors - 7 days
<code sql>
+
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id AND mfs.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))) AS 'Msg Stu to Instr - 7 days'
SELECT
+
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id  AND mts.timecreated > (UNIX_TIMESTAMP()  - (7*24*60*60))) AS 'Msg Instr to Stu - 7 days'
b.name
 
,op.title
 
,op.message
 
,( SELECT CONCAT(u.firstname, ' ',u.lastname) FROM prefix_user AS u WHERE u.id = oi.userid) AS "Username"
 
  
FROM prefix_oublog_posts AS op
+
## All posts in course so far
JOIN prefix_oublog_instances AS oi ON oi.id = op.oubloginstancesid
+
# Count posts by student
JOIN prefix_oublog as b ON b.id = oi.oublogid
+
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
JOIN prefix_course AS c ON b.course = c.id
+
 
 +
# Count replies to student posts by instructors
 +
, COUNT(DISTINCT fpi.id) AS 'Forum Instr Replies - to date'
 +
 
 +
# using link back to student posts on replies, get unique student IDs responded
 +
, COUNT(DISTINCT fpsr.id) - COUNT(DISTINCT fpi.id) AS 'Forum Stu Replies - to date'
 +
 
 +
# all replies
 +
, COUNT(DISTINCT fpsr.id) AS 'Forum All Replies - to date'
 +
 
 +
# add in count of graded assignments - whole course
 +
, COUNT(DISTINCT asb.id) AS 'Assign Submit - to date'
 +
, COUNT(DISTINCT asg.id) AS 'Assign Grades - to date'
 +
 
 +
# Messages between students and instructors - to date
 +
,  (SELECT COUNT(DISTINCT mfs.id) FROM prefix_message AS mfs WHERE mfs.useridfrom = allstu.id AND mfs.useridto = instr.id ) AS 'Msg Stu to Instr - to date'
 +
, (SELECT COUNT(DISTINCT mts.id) FROM prefix_message AS mts WHERE mts.useridfrom = instr.id AND mts.useridto = allstu.id) AS 'Msg Instr to Stu - to date'
 +
 
 +
## JOINS
 +
 
 +
# Start by getting all the students in the course
 +
FROM prefix_user AS allstu
 +
JOIN prefix_role_assignments AS ras ON allstu.id = ras.userid AND ras.roleid = 5
 +
JOIN prefix_context AS ctx  ON ras.contextid = ctx.id
 +
JOIN prefix_course AS c ON c.id = ctx.instanceid
 +
JOIN prefix_course_categories as cc ON c.category = cc.id
 +
 
 +
# Now we get the forums and forum discussions from this course only
 +
JOIN prefix_forum AS frm ON frm.course = c.id AND c.id = %%COURSEID%%
 +
JOIN prefix_forum_discussions AS fd ON fd.course = %%COURSEID%% AND fd.forum = frm.id
 +
 
 +
# These are forum discussion posts just by students within specified time
 +
LEFT JOIN prefix_forum_posts AS fps ON fps.userid = allstu.id AND fps.discussion = fd.id
 +
 
 +
# Separately, we connect the instructors of the courses
 +
# We can use the context we have already gotten for the students
 +
JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
 +
JOIN prefix_user AS instr ON instr.id = rai.userid AND rai.roleid =3
 +
 
 +
# Now we will connect to posts by instructors that are replies to student posts
 +
# This is a left join, because we don't want to eliminate any students from the list
 +
LEFT JOIN prefix_forum_posts AS fpi ON fpi.discussion = fd.id AND fpi.userid = instr.id AND fpi.parent = fps.id
 +
 
 +
# To get identities of only those students who were replied to:
 +
# Connect from instr replies back up to parent posts by students again
 +
# This has to be a LEFT JOIN, we know these posts exist but don't eliminate non-responded students
 +
LEFT JOIN prefix_forum_posts AS fpir ON fpir.id = fpi.parent
 +
 
 +
# We also want to know if students are replying to one another
 +
# These are posts that are replies to student posts
 +
# Again, a left join
 +
LEFT JOIN prefix_forum_posts AS fpsr ON fpsr.discussion = fd.id AND fpsr.parent = fps.id
 +
 
 +
# get the activity modules
 +
JOIN prefix_course_modules AS cm ON c.id = cm.course
 +
 
 +
# get the assignments
 +
JOIN prefix_assign AS a ON  cm.instance = a.id
 +
LEFT JOIN prefix_assign_submission AS asb ON a.id = asb.assignment AND asb.userid=allstu.id
 +
LEFT JOIN prefix_assign_grades AS asg ON asg.assignment = a.id AND asg.userid = allstu.id AND asg.assignment = asb.assignment
 +
 
 +
WHERE 
 +
c.id = %%COURSEID%%
 +
 
 +
# GROUP BY c.shortname , allstu.id
 +
GROUP BY allstu.id
  
WHERE c.id = %%COURSEID%%
+
ORDER BY allstu.lastname
 
</code>
 
</code>
  
===All Courses which uploaded a Syllabus file===
+
===Student Resource Usage===
+ under specific Category
+
Contributed by Elizabeth Dalton, Granite State College
+ show first Teacher in that course
+
 
+ link Course's fullname to actual course
+
Displays usage by students of all activities and resources in the current course by activity. Only activities and sections which are visible in the course are included. This version requires the new "Standard Logs" from Moodle 2.7+.
 +
 
 +
'''Note''': This should be defined as a "Global" report (visible from within all courses).
 +
 
 
<code sql>
 
<code sql>
SELECT
+
SELECT  
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
cs.section AS 'Week'
,c.shortname,r.name
+
, cs.name AS 'Section Name'
,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
+
, m.name AS 'item type'
FROM prefix_role_assignments AS ra
+
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
, CONCAT(
JOIN prefix_user as u ON u.id = ra.userid
+
COALESCE(a.name, ''),  
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
+
COALESCE(b.name,''),  
FROM prefix_resource as r
+
COALESCE(cert.name,''),
JOIN prefix_course as c ON r.course = c.id
+
COALESCE(chat.name,''),  
WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )  
+
COALESCE(choice.name,''),
AND c.category IN (10,18,26,13,28)
+
COALESCE(data.name,''),
</code>
+
COALESCE(feedback.name,''),
+
COALESCE(folder.name,''),
===Site-wide completed SCORM activities by Course name===
+
COALESCE(forum.name,''),  
This report will list all completed attempts for all SCORM activities. It is ordered first by Course name, then student's last name, then student's first name, then attempt number. Please note: the FROM_UNIXTIME command is for MySQL.
+
COALESCE(glossary.name,''),
<code sql>
+
COALESCE(imscp.name,''),
SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
+
COALESCE(lesson.name,''),
FROM prefix_scorm_scoes_track AS st
+
COALESCE(p.name,''),
JOIN prefix_user AS u ON st.userid=u.id
+
COALESCE(questionnaire.name,''),
JOIN prefix_scorm AS sc ON sc.id=st.scormid
+
COALESCE(quiz.name,''),
JOIN prefix_course AS c ON c.id=sc.course
+
COALESCE(cr.name,''),
WHERE st.value='completed'  
+
COALESCE(scorm.name,''),
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
+
COALESCE(survey.name,''),
</code>
+
COALESCE(url.name,''),
 +
COALESCE(wiki.name,''),
 +
COALESCE(workshop.name,''),
 +
COALESCE(kalvidassign.name,''),
 +
COALESCE(attendance.name,''),  
 +
COALESCE(checklist.name,''),  
 +
COALESCE(flashcard.name,''),
 +
COALESCE(lti.name,''),
 +
COALESCE(oublog.name,''),  
 +
COALESCE(ouwiki.name,''),  
 +
COALESCE(subpage.name,''),  
 +
COALESCE(journal.name,''),  
 +
COALESCE(lightboxgallery.name,''),
 +
COALESCE(elluminate.name,''),
 +
COALESCE(adaptivequiz.name,''),
 +
COALESCE(hotpot.name,''),
 +
COALESCE(wiziq.name,''),
 +
COALESCE(turnitintooltwo.name,''),  
 +
COALESCE(kvr.name,'')
 +
) AS 'item name'
  
==Module instances + Module HITs by role teacher and student in course==
 
<code sql>
 
SELECT
 
m.name AS "Module name"
 
, COUNT(*) AS "Module count"
 
  
,(SELECT COUNT(*)  
+
, SUM(IF(l.crud IN ('r'),1,0)) AS 'total views'
FROM prefix_log AS l
+
, SUM(IF(l.crud IN ('c','u'),1,0)) AS 'total submissions'
WHERE l.course = cm.course AND l.module = m.name ) AS "Hits"
+
, COUNT(DISTINCT IF(l.crud IN ('r'),u.id,NULL)) AS 'count of students who viewed'
 +
, COUNT(DISTINCT IF(l.crud IN ('c','u'),u.id,NULL)) AS 'count of students who submitted'
  
,(SELECT COUNT(*)
+
FROM prefix_user AS u
FROM prefix_log AS l
+
JOIN prefix_role_assignments AS ra ON u.id = ra.userid
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5  
+
JOIN prefix_course AS c ON c.id = ctx.instanceid
WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs"
+
JOIN prefix_course_categories as cc ON c.category = cc.id
 +
 
 +
JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section <= 14 #AND cs.section > 0
 +
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
 +
JOIN prefix_modules AS m ON m.id = cm.module AND m.name NOT LIKE 'label'
 +
 
 +
LEFT JOIN prefix_assign AS a ON a.id = cm.instance AND m.name = 'assign'
 +
LEFT JOIN prefix_book AS b ON b.id = cm.instance AND m.name = 'book'
 +
LEFT JOIN prefix_certificate AS cert ON cert.id = cm.instance AND m.name = 'certificate'
 +
LEFT JOIN prefix_chat AS chat ON chat.id = cm.instance AND m.name = 'chat'
 +
LEFT JOIN prefix_choice AS choice ON choice.id = cm.instance AND m.name = 'choice'
 +
LEFT JOIN prefix_data AS data ON data.id = cm.instance AND m.name = 'data'
 +
LEFT JOIN prefix_feedback AS feedback ON feedback.id = cm.instance AND m.name = 'feedback'
 +
LEFT JOIN prefix_folder AS folder ON folder.id = cm.instance AND m.name = 'folder'
 +
LEFT JOIN prefix_forum AS forum ON forum.id = cm.instance AND m.name = 'forum'
 +
LEFT JOIN prefix_glossary AS glossary ON glossary.id = cm.instance AND m.name = 'glossary'
 +
LEFT JOIN prefix_imscp AS imscp ON imscp.id = cm.instance AND m.name = 'imscp'
 +
LEFT JOIN prefix_lesson AS lesson ON lesson.id = cm.instance AND m.name = 'lesson'
 +
LEFT JOIN prefix_page AS p ON p.id = cm.instance AND m.name = 'page'
 +
LEFT JOIN prefix_questionnaire AS questionnaire ON questionnaire.id = cm.instance AND m.name = 'questionnaire'
 +
LEFT JOIN prefix_quiz AS quiz ON quiz.id = cm.instance AND m.name = 'quiz'
 +
LEFT JOIN prefix_resource AS cr ON cr.id = cm.instance AND m.name = 'resource'
 +
LEFT JOIN prefix_scorm AS scorm ON scorm.id = cm.instance AND m.name = 'scorm'
 +
LEFT JOIN prefix_survey AS survey ON survey.id = cm.instance AND m.name = 'survey'
 +
LEFT JOIN prefix_url AS url ON url.id = cm.instance AND m.name = 'url'
 +
LEFT JOIN prefix_wiki AS wiki ON wiki.id = cm.instance AND m.name = 'wiki'
 +
LEFT JOIN prefix_workshop AS workshop ON workshop.id = cm.instance AND m.name = 'workshop'
 +
LEFT JOIN prefix_kalvidassign AS kalvidassign ON kalvidassign.id = cm.instance AND m.name = 'kalvidassign'
 +
LEFT JOIN prefix_kalvidres AS kvr ON kvr.id = cm.instance AND m.name = 'kalvidres'
 +
LEFT JOIN prefix_attendance AS attendance ON attendance.id = cm.instance AND m.name = 'attendance'
 +
LEFT JOIN prefix_checklist AS checklist ON checklist.id = cm.instance AND m.name = 'checklist'
 +
LEFT JOIN prefix_flashcard AS flashcard ON flashcard.id = cm.instance AND m.name = 'flashcard'
 +
LEFT JOIN prefix_lti AS lti ON lti.id = cm.instance AND m.name = 'lti'
 +
LEFT JOIN prefix_oublog AS oublog ON oublog.id = cm.instance AND m.name = 'oublog'
 +
LEFT JOIN prefix_ouwiki AS ouwiki ON ouwiki.id = cm.instance AND m.name = 'ouwiki'
 +
LEFT JOIN prefix_subpage AS subpage ON subpage.id = cm.instance AND m.name = 'subpage'
 +
LEFT JOIN prefix_journal AS journal ON journal.id = cm.instance AND m.name = 'journal'
 +
LEFT JOIN prefix_lightboxgallery AS lightboxgallery ON lightboxgallery.id = cm.instance AND m.name = 'lightboxgallery'
 +
LEFT JOIN prefix_elluminate AS elluminate ON elluminate.id = cm.instance AND m.name = 'elluminate'
 +
LEFT JOIN prefix_adaptivequiz AS adaptivequiz ON adaptivequiz.id = cm.instance AND m.name = 'adaptivequiz'
 +
LEFT JOIN prefix_hotpot AS hotpot ON hotpot.id = cm.instance AND m.name = 'hotpot'
 +
LEFT JOIN prefix_wiziq AS wiziq ON wiziq.id = cm.instance AND m.name = 'wiziq'
 +
LEFT JOIN prefix_turnitintooltwo AS turnitintooltwo ON turnitintooltwo.id = cm.instance AND m.name = 'turnitintooltwo'
 +
 
 +
LEFT JOIN prefix_logstore_standard_log AS l ON l.userid = u.id AND l.courseid = c.id
 +
 
 +
 
 +
WHERE ra.roleid =5
 +
AND ctx.instanceid = c.id
 +
AND cs.visible = 1
 +
AND cm.visible = 1
 +
 
 +
AND c.id = %%COURSEID%%
  
,(SELECT COUNT(*)
+
GROUP BY cm.id
FROM prefix_log AS l
 
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
 
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
 
WHERE l.course = cm.course AND l.module = m.name) AS "Teachers HITs"
 
  
FROM mdl_course_modules AS cm
+
ORDER BY cs.section
JOIN mdl_modules AS m on m.id = cm.module
 
WHERE cm.course = '%%COURSEID%%'
 
GROUP BY cm.module
 
 
</code>
 
</code>
  
==Grade and Course Completion Reports==
+
===Module activity (Hits) between dates===
===Site-Wide Grade Report with All Items===
 
Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports
 
 
<code sql>
 
<code sql>
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
+
SELECT module, COUNT( * )
c.fullname AS 'Course',
+
FROM prefix_log AS l
cc.name AS 'Category',
+
WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN  '2012-10-01 00:00:00' AND  '2013-09-31 00:00:00')
 +
GROUP BY module
 +
</code>
  
CASE
+
===Module activity (Instances and Hits) for each academic year===
  WHEN gi.itemtype = 'course'
+
<code sql>
  THEN c.fullname + ' Course Total'
+
SELECT name
  ELSE gi.itemname
 
END AS 'Item Name',
 
  
ROUND(gg.finalgrade,2) AS Grade,
+
,(SELECT COUNT(*)  
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
+
FROM mdl_log AS l
 +
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
 +
AND l.module = m.name AND l.action = 'add'
 +
) AS "Added 2010"
  
FROM prefix_course AS c
+
,(SELECT COUNT(*)
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
FROM mdl_log AS l
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
JOIN prefix_user AS u ON u.id = ra.userid
+
AND l.module = m.name
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
+
) AS "Used 2010"
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
 
JOIN prefix_course_categories as cc ON cc.id = c.category
 
  
WHERE  gi.courseid = c.id
+
,(SELECT COUNT(*)
ORDER BY lastname
+
FROM mdl_log AS l
</code>
+
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00')
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line
+
AND l.module = m.name AND l.action = 'add'
<code>
+
) AS "Added 2011"
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
 
</code>
 
with
 
<code>
 
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time
 
</code>
 
  
===Site-Wide Grade Report with Just Course Totals===
+
,(SELECT COUNT(*)
A second site-wide grade report for all students that just shows course totals. Works with ad-hoc reports or Configurable Reports
+
FROM mdl_log AS l
<code sql>
+
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00')
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
+
AND l.module = m.name
cc.name AS 'Category',
+
) AS "Used 2011"
CASE
 
  WHEN gi.itemtype = 'course'
 
  THEN c.fullname + ' Course Total'
 
  ELSE gi.itemname
 
END AS 'Item Name',
 
  
ROUND(gg.finalgrade,2) AS Grade,
 
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
 
  
FROM prefix_course AS c
+
,(SELECT COUNT(*)
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
FROM mdl_log AS l
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
JOIN prefix_user AS u ON u.id = ra.userid
+
AND l.module = m.name AND l.action = 'add'
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
+
) AS "Added 2012"
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
 
JOIN prefix_course_categories as cc ON cc.id = c.category
 
  
WHERE gi.courseid = c.id AND gi.itemtype = 'course'
+
,(SELECT COUNT(*)
 +
FROM mdl_log AS l
 +
WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
 +
AND l.module = m.name
 +
) AS "Used 2012"
  
ORDER BY lastname
+
FROM mdl_modules AS m
 
</code>
 
</code>
  
For MySQL users:
+
===Unique user sessions per day and month + graph===
 +
The "graph" column is used when displaying a graph (which needs at least three columns to pick from)
 
<code sql>
 
<code sql>
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',  
+
SELECT COUNT(DISTINCT userid) AS "Unique User Logins"
c.fullname AS 'Course',
+
,DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y /%m / %d") AS "Year / Month / Day", "Graph"
cc.name AS 'Category',
+
FROM `mdl_logstore_standard_log`
CASE
+
WHERE action LIKE 'loggedin'
  WHEN gi.itemtype = 'course'  
+
#AND timecreated >  UNIX_TIMESTAMP('2015-01-01 00:00:00') # optional start date
  THEN CONCAT(c.fullname, ' - Total')
+
#AND timecreated <= UNIX_TIMESTAMP('2015-01-31 23:59:00') # optional end date
  ELSE gi.itemname
+
GROUP BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
END AS 'Item Name',
+
ORDER BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
 +
</code>
 +
 
 +
And...
 +
 
 +
Counting user's global and unique hits per day + counting individual usage of specific activities and resources (on that day),
  
ROUND(gg.finalgrade,2) AS Grade,
+
And since I am using phpMyAdmin's "Display Graph" feature (at the bottom of the query's output page), I have scaled down the "User Hits" by 10 to fit the graph. that's it.
FROM_UNIXTIME(gg.timemodified) AS TIME
+
<code sql>
+
SELECT DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y-%m-%d") AS "Datez"
FROM prefix_course AS c
+
,COUNT(DISTINCT userid) AS "Unique Users"
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
,ROUND(COUNT(*)/10) "User Hits (K)"
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
,SUM(IF(component='mod_quiz',1,0)) "Quizzes"
JOIN prefix_user AS u ON u.id = ra.userid
+
,SUM(IF(component='mod_forum' or component='mod_forumng',1,0)) "Forums"
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
+
,SUM(IF(component='mod_assign',1,0)) "Assignments"
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
+
,SUM(IF(component='mod_oublog',1,0)) "Blogs"
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
,SUM(IF(component='mod_resource',1,0)) "Files (Resource)"
+
,SUM(IF(component='mod_url',1,0)) "Links (Resource)"
WHERE  gi.courseid = c.id
+
,SUM(IF(component='mod_page',1,0)) "Pages (Resource)"
ORDER BY lastname
+
 
 +
FROM `mdl_logstore_standard_log`
 +
WHERE 1=1
 +
AND timecreated > UNIX_TIMESTAMP('2015-03-01 00:00:00') # optional START DATE
 +
AND timecreated <= UNIX_TIMESTAMP('2015-05-31 23:59:00') # optional END DATE
 +
GROUP BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
 +
ORDER BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
 
</code>
 
</code>
  
===Learner report by Learner with grades===
+
===System wide, daily unique user hits for the last 7 days===
Which Learners in which course and what are the grades
 
 
<code sql>
 
<code sql>
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
+
SELECT
CASE WHEN gi.itemtype = 'Course'   
+
  DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%m%d') 'Day'
THEN c.fullname + ' Course Total'
+
  ,COUNT(DISTINCT l.userid) AS 'Distinct Users Hits'
ELSE gi.itemname
+
  ,COUNT( l.userid) AS 'Users Hits'
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as Percentage,
 
 
 
if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass
 
  
FROM prefix_course AS c
+
FROM mdl_logstore_standard_log AS l
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
WHERE l.courseid > 1
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
      AND FROM_UNIXTIME(l.timecreated) >= DATE_SUB(NOW(), INTERVAL 7 DAY)
JOIN prefix_user AS u ON u.id = ra.userid
+
GROUP BY DAY(FROM_UNIXTIME(timecreated))
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
 
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
 
JOIN prefix_course_categories AS cc ON cc.id = c.category
 
WHERE gi.courseid = c.id and gi.itemname != 'Attendance'
 
ORDER BY `Name` ASC
 
 
</code>
 
</code>
  
===User Course Completion===
+
===User detailed activity in course modules===
 +
Considering only several modules: url, resource, forum, quiz, questionnaire.
  
A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.  
+
<code sql>
 +
SELECT u.id, ra.roleid,
 +
CONCAT(u.lastname, ' ', u.firstname) AS 'Student'
 +
,COUNT(l.id) AS 'Actions'
 +
,l.component "Module type"
 +
,l.objectid "Module ID"
 +
,CASE
 +
  WHEN l.component = 'mod_url' THEN (SELECT u.name FROM mdl_url AS u WHERE u.id = l.objectid )
 +
  WHEN l.component = 'mod_resource' THEN (SELECT r.name FROM mdl_resource AS r WHERE r.id = l.objectid )
 +
  WHEN l.component = 'mod_forum' THEN (SELECT f.name FROM mdl_forum AS f WHERE f.id = l.objectid )
 +
  WHEN l.component = 'mod_quiz' THEN (SELECT q.name FROM mdl_quiz AS q WHERE q.id = l.objectid )
 +
  WHEN l.component = 'mod_questionnaire' THEN (SELECT q.name FROM mdl_questionnaire AS q WHERE q.id = l.objectid )
 +
END AS 'Module name'
  
 +
,(SELECT GROUP_CONCAT(g.name) FROM mdl_groups AS g
 +
JOIN mdl_groups_members AS m ON g.id = m.groupid WHERE g.courseid = l.courseid AND m.userid = u.id) "user_groups"
 +
 +
,(SELECT s.name
 +
  FROM mdl_course_modules AS cm
 +
  JOIN mdl_course_sections AS s ON s.course = cm.course AND s.id = cm.section
 +
  WHERE cm.id = l.contextinstanceid) AS "Section name"
 +
 +
FROM mdl_logstore_standard_log AS l 
 +
JOIN mdl_user AS u ON u.id = l.userid
 +
JOIN mdl_role_assignments AS ra ON ra.userid = l.userid
 +
  AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
 +
WHERE l.courseid = %%COURSEID%%
 +
  AND l.component IN ('mod_url', 'mod_resource', 'mod_forum', 'mod_quiz', 'mod_questionnaire')
 +
  %%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
 +
 +
GROUP BY u.id, l.component
 +
ORDER BY u.lastname, u.firstname
 +
</code>
 +
 +
===What teachers and courses considered active?===
 +
This report display several calculations and parameters that help the Online academic training team find teachers that might need more support getting their courses more supporting of online learning pedagogical methodologies. 
 
<code sql>
 
<code sql>
SELECT u.username, c.shortname,
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
+
  course.id,'">',course.fullname,'</a>') AS Course
),'%Y-%m-%d')  
+
 
AS completed
+
#,course.shortname
FROM prefix_course_completions AS p
+
 
JOIN prefix_course AS c ON p.course = c.id
+
,CASE
JOIN prefix_user AS u ON p.userid = u.id
+
  WHEN course.fullname LIKE '%2012%' THEN '2012'
WHERE c.enablecompletion = 1
+
  WHEN course.fullname LIKE '%2013%' THEN '2013'
ORDER BY u.username
+
  WHEN course.fullname LIKE '%2014%' THEN '2014'
</code>
+
  WHEN course.fullname LIKE '%2015%' THEN '2015'
 +
END AS Year
  
===User Course Completion with Criteria===
+
,CASE
 +
  WHEN course.fullname LIKE '%semester a%' THEN 'Spring semester'
 +
  WHEN course.fullname LIKE '%semester b%' THEN 'Fall semester'
 +
  WHEN course.fullname LIKE '%semester s%' THEN 'Summer semester'
 +
END AS Semester
  
A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
+
,IF(course.startdate>0, DATE_FORMAT(FROM_UNIXTIME(startdate), '%d-%m-%Y'), 'no date') AS "Course Start Date"
  
<code sql>
+
,(SELECT COUNT( ra.userid ) AS Users
SELECT u.username AS user,
+
FROM prefix_role_assignments AS ra
c.shortname AS course,
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
+
WHERE ra.roleid = 5 AND ctx.instanceid = course.id
CASE
+
) AS Students
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any"
+
 
ELSE "All"
+
,(SELECT COUNT( ra.userid ) AS Users
END AS aggregation,
+
FROM prefix_role_assignments AS ra
CASE
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHEN p.criteriatype = 1 THEN "Self"
+
WHERE ra.roleid = 4 AND ctx.instanceid = course.id
WHEN p.criteriatype = 2 THEN "By Date"
+
) AS "Assistant teacher"
WHEN p.criteriatype = 3 THEN "Unenrol Status"
 
WHEN p.criteriatype = 4 THEN "Activity"
 
WHEN p.criteriatype = 5 THEN "Duration"
 
WHEN p.criteriatype = 6 THEN "Course Grade"
 
WHEN p.criteriatype = 7 THEN "Approve by Role"
 
WHEN p.criteriatype = 8 THEN "Previous Course"
 
END AS criteriatype,
 
CASE
 
WHEN p.criteriatype = 1 THEN "*"
 
WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
 
WHEN p.criteriatype = 3 THEN t.unenroled
 
WHEN p.criteriatype = 4 THEN
 
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
 
WHEN p.criteriatype = 5 THEN p.enrolperiod
 
WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2))
 
WHEN p.criteriatype = 7 THEN p.role
 
WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance)
 
END AS criteriadetail
 
FROM prefix_course_completion_crit_compl AS t
 
JOIN prefix_user AS u ON t.userid = u.id
 
JOIN prefix_course AS c ON t.course = c.id
 
JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id
 
  
</code>
+
,(SELECT COUNT( ra.userid ) AS Users
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 3 AND ctx.instanceid = course.id
 +
) AS Teachers
  
===Courses with Completion Enabled and their settings===
+
# Uncomment to use the new Moodle 2.8+ logstore
 +
#,(SELECT COUNT(*) FROM mdl_logstore_standard_log AS l WHERE l.courseid = course.id) AS Hits
  
List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.
+
#,(SELECT COUNT(*)
 +
#FROM mdl_logstore_standard_log AS l
 +
#JOIN mdl_role_assignments AS ra ON ra.userid= l.userid AND ra.roleid = 5 AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
 +
#WHERE l.courseid = course.id ) AS "Student HITs"
  
<code sql>
+
#,(SELECT COUNT(*)
 +
#FROM mdl_logstore_standard_log AS l
 +
#JOIN mdl_role_assignments AS ra ON ra.userid= l.userid AND ra.roleid = 3 AND ra.contextid = (SELECT id FROM mdl_context WHERE instanceid = l.courseid AND contextlevel = 50)
 +
#WHERE l.courseid = course.id ) AS "Teacher HITs"
  
SELECT c.shortname AS Course,  
+
,(SELECT COUNT(*) FROM mdl_log AS l WHERE l.course = course.id) AS Hits
CASE
 
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS WHERE (a.course = t.course AND a.criteriatype IS NULL)) = 2 THEN "All"
 
ELSE "Any"
 
END AS Course_Aggregation,
 
CASE
 
WHEN t.criteriatype = 1 THEN "Self completion"
 
WHEN t.criteriatype = 2 THEN "Date done by"
 
WHEN t.criteriatype = 3 THEN "Unenrolement"
 
WHEN t.criteriatype = 4 THEN "Activity completion" 
 
WHEN t.criteriatype = 5 THEN "Duration in days"
 
WHEN t.criteriatype = 6 THEN "Final grade"   
 
WHEN t.criteriatype = 7 THEN "Approve by role"
 
WHEN t.criteriatype = 8 THEN "Previous course"
 
END AS Criteria_type,
 
CASE
 
WHEN t.criteriatype = 1 THEN "On"
 
WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
 
WHEN t.criteriatype = 3 THEN "On"
 
WHEN t.criteriatype = 4 THEN
 
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
 
WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
 
WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
 
WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role)
 
WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance)
 
END AS Criteria_detail
 
FROM prefix_course_completion_criteria as t
 
JOIN prefix_course AS c ON t.course = c.id
 
WHERE c.enablecompletion = 1
 
ORDER BY course
 
</code>
 
  
===Course Completion Report with custom dates===
+
,(SELECT COUNT(*)
 +
FROM mdl_log AS l
 +
JOIN mdl_context AS con ON con.instanceid= l.course AND con.contextlevel=50
 +
JOIN mdl_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5
 +
WHERE l.course = course.id) AS "Students HITs"
 +
 +
,(SELECT COUNT(*)
 +
FROM mdl_log AS l
 +
JOIN mdl_context AS con ON con.instanceid= l.course AND con.contextlevel=50
 +
JOIN mdl_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
 +
WHERE l.course = course.id) AS "Teachers HITs"
  
List of users who completed multiple or single course/s from a start date to end date chosen by the user. The output gives username, name, course name, completion date and score
+
,(SELECT GROUP_CONCAT( CONCAT( u.firstname,  " ", u.lastname ) )
 +
FROM prefix_course c
 +
JOIN prefix_context con ON con.instanceid = c.id
 +
JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
 +
JOIN prefix_role r ON ra.roleid = r.id
 +
JOIN prefix_user u ON u.id = ra.userid
 +
WHERE r.id = 3 AND c.id = course.id
 +
GROUP BY c.id
 +
) AS Teachers
 +
 
 +
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = course.id) Modules
 +
 
 +
,(SELECT COUNT(DISTINCT cm.module) FROM prefix_course_modules cm
 +
  WHERE cm.course = course.id) UniqueModules
 +
 
 +
,(SELECT GROUP_CONCAT(DISTINCT m.name)
 +
  FROM prefix_course_modules cm
 +
  JOIN mdl_modules as m ON m.id = cm.module
 +
  WHERE cm.course = course.id) UniqueModuleNames
  
<code sql>
+
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
 +
  WHERE cm.course = course.id AND m.name IN ( 'ouwiki', 'wiki') ) "Num Wikis"
  
SELECT u.username AS 'User Name',
+
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
+
  WHERE cm.course = course.id AND m.name IN ( 'oublog') ) "Num Blogs"
c.shortname AS 'Course Name',
 
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date',
 
ROUND(c4.gradefinal,2) AS 'Score'
 
FROM prefix_course_completions AS p
 
JOIN prefix_course AS c ON p.course = c.id
 
JOIN prefix_user AS u ON p.userid = u.id
 
JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid
 
WHERE c.enablecompletion = 1  AND (p.timecompleted IS NOT NULL OR p.timecompleted !='')  
 
AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date)
 
GROUP BY u.username
 
ORDER BY c.shortname
 
  
</code>
+
,(SELECT COUNT(*) FROM mdl_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
 +
  WHERE cm.course = course.id AND m.name IN ( 'forum', 'forumng') ) "Num Forums"
  
===Scales used in activities===
+
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
<code sql>
+
  WHERE cm.course = course.id AND m.name IN ('resource', 'folder', 'url', 'tab', 'file', 'book', 'page') ) Resources
SELECT scale.name
 
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',gi.itemmodule,'/view.php?id=',cm.id,'">',gi.itemname,'</a>') AS "Module View"
 
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/modedit.php?up','date=',cm.id,'">',gi.itemname,'</a>') AS "Module Settings"
 
  
FROM prefix_grade_items AS gi
+
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
JOIN prefix_course AS c ON c.id = gi.courseid
+
  WHERE cm.course = course.id AND m.name IN ('forum', 'forumng', 'oublog', 'page', 'file', 'url', 'wiki' , 'ouwiki') ) "Basic Activities"
JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance
+
 
JOIN prefix_scale AS scale ON scale.id = gi.scaleid
+
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
WHERE gi.scaleid IS NOT NULL
+
  WHERE cm.course = course.id AND m.name IN ('advmindmap', 'assign', 'attendance', 'book', 'choice', 'folder', 'tab', 'glossary', 'questionnaire', 'quiz', 'label' ) ) "Avarage Activities"
</code>
+
 
 +
,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN mdl_modules as m ON m.id = cm.module
 +
  WHERE cm.course = course.id AND m.name IN ('elluminate', 'game', 'workshop') ) "Advanced Activities"
 +
 
 +
FROM prefix_course AS course
 +
 
 +
#WHERE course.shortname LIKE '%2015%'
 +
#WHERE 1=1
 +
#%%FILTER_SEARCHTEXT:course.shortname:~%%
  
==Activity Module Reports==
+
WHERE course.fullname LIKE '%2015%'
  
===How many SCORM activities are used in each Course===
+
HAVING Modules > 2
<code sql>
+
ORDER BY UniqueModules DESC
SELECT cm.course,c.fullname ,m.name
 
,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
 
 
FROM `prefix_course_modules` as cm
 
  JOIN prefix_modules as m ON cm.module=m.id
 
  JOIN prefix_course as c ON cm.course = c.id
 
WHERE m.name LIKE '%scorm%'
 
GROUP BY cm.course,cm.module
 
ORDER BY count(cm.id) desc
 
 
</code>
 
</code>
  
===Detailed ACTIONs for each MODULE===
+
==Course Reports==
 +
===Most Active courses===
 
<code sql>
 
<code sql>
SELECT module,action,count(id) as counter
+
SELECT count(l.userid) AS Views
FROM prefix_log
+
FROM `mdl_logstore_standard_log` l, `mdl_user` u, `mdl_role_assignments` r
GROUP BY module,action
+
WHERE l.courseid=35
ORDER BY module,counter desc
+
AND l.userid = u.id
 +
AND (l.timecreated > UNIX_TIMESTAMP('2015-01-01 00:00:00') AND l.timecreated <= UNIX_TIMESTAMP('2015-01-31 23:59:59'))AND r.contextid= (
 +
SELECT id
 +
FROM mdl_context
 +
WHERE contextlevel=50 AND instanceid=l.courseid
 +
)
 +
AND r.roleid=5
 +
AND r.userid = u.id
 
</code>
 
</code>
  
===Most popular ACTIVITY===
+
===Active courses, advanced===
 +
Including: Teacher's name, link to the course, All types of log activities, special YEAR generated field, Activities and Resource count, enrolled Student count
 
<code sql>
 
<code sql>
SELECT COUNT(l.id) hits, module
+
SELECT COUNT(l.id) hits, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_log l
 
WHERE module != 'login' AND module != 'course' AND module != 'role'
 
GROUP BY module
 
ORDER BY hits DESC
 
</code>
 
  
===System wide use of ACTIVITIES and RESOURCES===
+
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
<code sql>
+
FROM prefix_role_assignments AS ra
SELECT count( cm.id ) AS counter, m.name
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
FROM `prefix_course_modules` AS cm
+
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_modules AS m ON cm.module = m.id
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
GROUP BY cm.module
 
ORDER BY counter DESC
 
</code>
 
  
===LOG file ACTIONS per MODULE per COURSE (IDs)===
+
,CASE
<code sql>
+
  WHEN c.fullname LIKE '%תשע' THEN 'תשע'
select course,module,action,count(action) as summa from prefix_log
+
  WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
where action <> 'new'
+
  WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
group by course,action,module
+
END AS Year
order by course,module,action
 
</code>
 
  
===System Wide usage count of various course Activities===
+
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
(Tested and works fine in Moodle 2.x)
 
Like: Forum, Wiki, Blog, Assignment, Database,
 
#Within specific category
 
#Teacher name in course
 
  
<code sql>
+
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
  
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
FROM prefix_log l
FROM prefix_role_assignments AS ra
+
INNER JOIN prefix_course c ON l.course = c.id
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
GROUP BY c.id
JOIN prefix_user AS u ON u.id = ra.userid
+
#The following line restricts the courses returned to those having more than 2 modules. Adjust based on your needs.
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+
HAVING Modules > 2
 +
ORDER BY Year DESC, hits DESC
 +
</code>
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
=== Least active or probably empty courses===
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 
  
,(SELECT count( m.name ) AS count FROM
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
It is difficult to know sometimes when a course is actually empty or was never really in use. Other than the simple case where the course was created and never touched again, in which case the course timecreated and timemodified will be the same, many courses created as shells for teachers or other users may be used once or a few times and have few or no test users enrollments in them. This query helps you see the range of such courses, showing you how many days if any it was used after initial creation, and how many user are enrolled. It denotes a course never ever modified by "-1" instead of "0" so you can sort those to the top. By default it limits this to courses used within 60 days of creation, and to courses with 3 or less enrollments (for example, teacher and assistant and test student account only.) You can easily adjust these numbers. The query includes a link to the course as well.  
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
 
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
<code sql>
JOIN prefix_modules AS m ON cm.module = m.id
+
SELECT
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
+
c.fullname,
 
+
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'CourseLink',
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
DATE_FORMAT(FROM_UNIXTIME(c.timecreated), '%Y-%m-%d %H:%i') AS 'Timecreated',
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
DATE_FORMAT(FROM_UNIXTIME(c.timemodified), '%Y-%m-%d %H:%i') AS 'Timemodified',
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+
CASE
 +
WHEN c.timecreated = c.timemodified THEN '-1'
 +
ELSE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated))
 +
END AS 'DateDifference',
 +
COUNT(ue.id) AS Enroled
 +
FROM prefix_course AS c
 +
JOIN prefix_enrol AS en ON en.courseid = c.id
 +
LEFT JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 +
WHERE DATEDIFF(FROM_UNIXTIME(c.timemodified),FROM_UNIXTIME(c.timecreated) ) < 60
 +
GROUP BY c.id
 +
HAVING COUNT(ue.id) <= 3
 +
ORDER BY c.fullname
 +
</code>
  
 +
===Count unique teachers with courses that use at least X module (Moodle19)===
 +
You can remove the outer "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQL query and get the list of the Teachers and Courses.
 +
<code sql>
 +
SELECT COUNT(*)
 +
FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher
 +
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules
 
FROM prefix_course AS c
 
FROM prefix_course AS c
WHERE c.category IN ( 18)
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
+
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE  ra.roleid = 3
 +
GROUP BY u.id
 +
HAVING Modules > 5) AS ActiveTeachers
 
</code>
 
</code>
  
===Course wiki usage/activity over the last 6 semesters===
+
===RESOURCE count for each COURSE===
 
<code sql>
 
<code sql>
SELECT "Courses with Wikis"
+
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
 +
</code>
  
,(SELECT count( m.name ) AS count FROM
+
===Common resource types count for each Category (Moodle19)===
prefix_course_modules AS cm
+
Including sub-categories in total count.
JOIN prefix_modules AS m ON cm.module = m.id
+
<code sql>
JOIN prefix_course AS c ON c.id = cm.course
+
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
,(SELECT COUNT( * )
and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester A%') AS '2010 <br/> Semester A'
+
FROM prefix_resource AS r
 
+
JOIN prefix_course AS c ON c.id = r.course
,(SELECT count( m.name ) AS count FROM  
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
prefix_course_modules AS cm
+
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%'
JOIN prefix_modules AS m ON cm.module = m.id
+
) AS Links
JOIN prefix_course AS c ON c.id = cm.course
+
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
,(SELECT COUNT( * )  
  and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester B%') AS '2010 <br/> Semester B'
+
FROM prefix_resource AS r
 
+
JOIN prefix_course AS c ON c.id = r.course
,(SELECT count( m.name ) AS count FROM  
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
prefix_course_modules AS cm
+
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference NOT LIKE 'http://%'
JOIN prefix_modules AS m ON cm.module = m.id
+
) AS Files
JOIN prefix_course AS c ON c.id = cm.course
+
   
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
,(SELECT COUNT( * )
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעא <br/> סמסטר א'
+
FROM prefix_resource AS r
 +
JOIN prefix_course AS c ON c.id = r.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'directory'  
 +
) AS Folders
 +
 +
,(SELECT COUNT( * )  
 +
FROM prefix_resource AS r
 +
JOIN prefix_course AS c ON c.id = r.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'html'  
 +
) AS Pages
 +
 +
,(SELECT COUNT(*)
 +
FROM stats_log_context_role_course
 +
WHERE roleid = 5 AND module = 'resource' AND category = mcc.id
 +
) AS Hits
  
,(SELECT count( m.name ) AS count FROM
+
FROM prefix_course_categories AS mcc
prefix_course_modules AS cm
+
ORDER BY mcc.path
JOIN prefix_modules AS m ON cm.module = m.id
+
</code>
JOIN prefix_course AS c ON c.id = cm.course
+
Where "stats_log_context_role_course" (in the above SQL query) is a VIEW generated by:
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
<code sql>
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעא <br/> סמסטר ב'
+
CREATE VIEW stats_log_context_role_course AS
 +
SELECT l.course, c.category, cc.path, l.module, l.action, ra.userid, ra.roleid
 +
FROM prefix_log AS l
 +
JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
 +
JOIN prefix_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id
 +
JOIN prefix_course AS c ON c.id = l.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
</code>
  
,(SELECT count( m.name ) AS count FROM
+
Same query but for Moodle2+
prefix_course_modules AS cm
+
<code sql>
JOIN prefix_modules AS m ON cm.module = m.id
+
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
JOIN prefix_course AS c ON c.id = cm.course
+
mcc.path,
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעב <br/> סמסטר א'
 
  
,(SELECT count( m.name ) AS count FROM  
+
(SELECT COUNT(*)  
prefix_course_modules AS cm
+
FROM prefix_url AS u
JOIN prefix_modules AS m ON cm.module = m.id
+
JOIN prefix_course AS c ON c.id = u.course
JOIN prefix_course AS c ON c.id = cm.course
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעב <br/> סמסטר ב'
+
) AS URLs,
  
,(SELECT count( m.name ) AS count FROM  
+
(SELECT COUNT(*)  
prefix_course_modules AS cm
+
FROM prefix_folder AS f
JOIN prefix_modules AS m ON cm.module = m.id
+
JOIN prefix_course AS c ON c.id = f.course
JOIN prefix_course AS c ON c.id = cm.course
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעג <br/> סמסטר א'
+
) AS FOLDERs,
  
,(SELECT count( m.name ) AS count FROM  
+
(SELECT COUNT(*)  
prefix_course_modules AS cm
+
FROM prefix_page AS p
JOIN prefix_modules AS m ON cm.module = m.id
+
JOIN prefix_course AS c ON c.id = p.course
JOIN prefix_course AS c ON c.id = cm.course
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעג <br/> סמסטר ב'
+
) AS PAGEs,
</code>
 
  
===Detailed WIKI activity (per wiki per course)===
+
(SELECT COUNT(*)  
Including Number of Students in course (for reference)
+
FROM prefix_book AS b
<code sql>
+
JOIN prefix_course AS c ON c.id = b.course
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID 
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
,(SELECT Count( ra.userid ) AS Users
+
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
FROM prefix_role_assignments AS ra
+
) AS BOOKs,
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students
 
,m.name
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD'
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT
 
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)'
 
FROM `prefix_course_modules` as cm
 
JOIN prefix_modules as m ON cm.module=m.id
 
JOIN prefix_course as c ON cm.course = c.id
 
WHERE m.name LIKE '%wiki%'
 
GROUP BY cm.course,cm.module
 
ORDER BY 'All (NO View)' DESC
 
</code>
 
  
===Wiki usage, system wide===
+
(SELECT COUNT(*)
(you can filter the output by selecting some specific course categories : "WHERE c.category IN ( 8,13,15)")
+
FROM prefix_label AS l
 +
JOIN prefix_course AS c ON c.id = l.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 +
) AS LABELs,
  
<code sql>
+
(SELECT COUNT(*)
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
FROM prefix_tab AS t
 +
JOIN prefix_course AS c ON c.id = t.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 +
) AS TABs
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
FROM prefix_course_categories AS mcc
JOIN prefix_modules AS m ON cm.module = m.id
+
ORDER BY mcc.path
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
+
</code>
  
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity<br/>ALL'
+
===Detailed Resource COUNT by Teacher in each course===
  
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity<br/>ADD'
+
Including (optional) filter by: year, semester and course id.
  
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity<br/>EDIT'
+
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
 +
, c.id
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
  
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity<br/>ANNOTATE'
+
, (CASE
 
+
WHEN c.fullname LIKE '%תשעב%' THEN '2012'
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity<br/>Comments'
+
WHEN c.fullname LIKE '%תשעא%' THEN '2011'
 
+
END ) as Year
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
, (CASE
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A'
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+
WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B'
 +
WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C'
 +
END ) as Semester
 +
,COUNT(c.id) AS Total
 +
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs
 +
,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs
  
,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp
+
FROM `prefix_resource` as r
JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid
+
JOIN `prefix_course` AS c on c.id = r.course
WHERE ouw.course = c.id GROUP BY ouw.course  ) as OUWikiPages
+
#WHERE type= 'file' and reference NOT LIKE 'http://%'
  
,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
+
#WHERE 1=1
JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages
+
#%%FILTER_YEARS:c.fullname%%
 +
#AND c.fullname LIKE '%2013%'
  
FROM prefix_course AS c
+
GROUP BY course
WHERE c.category IN ( 8,13,15)
+
ORDER BY COUNT(c.id) DESC
HAVING Wikis > 0
 
ORDER BY 'WikiActivity<br/>ALL' DESC
 
 
</code>
 
</code>
  
===Aggregated Teacher activity by "WEB2" Modules===
+
===Courses that are defined as using GROUPs===
(Tested and works fine in Moodle 2.x)
 
The NV column shows activity without VIEW log activity
 
 
<code sql>
 
<code sql>
SELECT ra.userid, u.firstname,u.lastname
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki
+
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV
+
,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum
+
FROM `prefix_course` AS c
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV
+
WHERE groupmode > 0
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog
 
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV
 
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment
 
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV
 
FROM prefix_role_assignments AS ra
 
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE ra.roleid = 3
 
GROUP BY ra.userid
 
 
</code>
 
</code>
  
===List all the certificates issued, sort by variables in the custom profile fields===
+
===Courses with Groups===
Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.
 
  
<code sql>
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
SELECT
 
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date,
 
prefix_certificate_issues.classname AS Topic,
 
prefix_certificate.name AS Certificate,
 
prefix_certificate_issues.studentname as Name,
 
prefix_user_info_data.data AS Units
 
  
FROM
+
List of all courses with Groups in them (groupmode > 0). You can also use groupmode=1 to list just Separate type groups or groupmode=2 to list Visible type groups.
prefix_certificate_issues
 
  
INNER JOIN prefix_user_info_data
+
<code sql>
on prefix_certificate_issues.userid = prefix_user_info_data.userid
+
SELECT c.shortname, g.name, c.groupmode
 +
FROM prefix_course AS c
 +
JOIN prefix_groups AS g ON c.id = g.courseid
 +
WHERE c.groupmode > 0
 +
</code>
 +
 
 +
===Users enrolled in a course with groups but not assigned a group ===
 +
 
 +
Displays by course all enrolled users that have not been assigned a group in courses that have groups. NOTE: This needs to be optimized.
 +
 
 +
<code sql>
 +
SELECT DISTINCT
 +
user2.firstname AS Firstname,
 +
user2.lastname AS Lastname,
 +
user2.email AS Email,
 +
user2.city AS City,
 +
course.fullname AS Course
 +
,(SELECT shortname FROM prefix_role WHERE id=en.roleid) AS ROLE
 +
,(SELECT name FROM prefix_role WHERE id=en.roleid) AS RoleName
  
INNER JOIN prefix_certificate
+
FROM prefix_course AS course
on prefix_certificate_issues.certificateid = prefix_certificate.id
+
JOIN prefix_enrol AS en ON en.courseid = course.id
 +
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 +
JOIN prefix_user AS user2 ON ue.userid = user2.id
 +
JOIN prefix_groups AS g ON g.courseid = course.id
  
WHERE prefix_user_info_data.data='Unit 1'
+
WHERE ue.enrolid NOT IN (select userid from prefix_groups_members WHERE g.id=groupid)
OR prefix_user_info_data.data='Unit 2'
 
OR prefix_user_info_data.data='Unit 3'
 
  
ORDER BY Units, Name, Topic ASC
+
ORDER BY Course, Lastname
 
</code>
 
</code>
  
===Counter Blog usage in Courses,system wide===
+
===Groups in course with member list===
What teachers in what courses, uses blogs and how many + student count in that course.
 
<code sql>
 
  
SELECT ( @counter := @counter+1) as counter,
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 
  
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+
List the groups in a course (replace the # by the course id number) with the members of each group.
  FROM prefix_role_assignments AS ra
+
 
  JOIN prefix_user AS u ON ra.userid = u.id
+
<code sql>
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+
SELECT c.shortname, g.name AS Groupname, u.username
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+
FROM prefix_course AS c
 +
JOIN prefix_groups AS g ON g.courseid = c.id
 +
JOIN prefix_groups_members AS m ON g.id = m.groupid
 +
JOIN prefix_user AS u ON m.userid = u.id
 +
WHERE c.id = #
 +
</code>
 +
 
 +
Note: if you are using Configurable Reports block and want to perform this query on the current course you are in, then you can use a WHERE clause like this:
 +
<code sql>
 +
WHERE c.id = %%COURSEID%%
 +
</code>
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
===Group Export===
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 
  
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
  
FROM prefix_course AS c, (SELECT @counter := 0) as s_init
+
There's a [[Import_groups|group import]] function, but no export. Use this to give you a report with the proper column order and headings to export to a csv file you can then import into another course to replicate the groups. This is a simple version with just the main fields: groupname, description, enrolment key.
WHERE c.category IN ( 8,13,15)
 
HAVING Blogs > 0
 
ORDER BY Blogs DESC
 
</code>
 
  
=== Elluminate (Blackboard Collaborate) - system wide usage===
 
 
<code sql>
 
<code sql>
SELECT e.name As Session ,er.recordingsize
+
SELECT g.name AS groupname, g.description, g.enrolmentkey
,c.fullname As Course
+
FROM prefix_groups AS g
,u.firstname,u.lastname
+
JOIN prefix_course as c ON g.courseid = c.id
,DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%d-%m-%Y') AS dTimeStart
+
WHERE c.id = #
,concat('<a target="_new" href="%%WWWROOT%%/moodle/mod/elluminate/loadrecording.php?id=',er.id,'">Show</a>') AS RecordedSession
 
 
 
FROM prefix_elluminate_recordings AS er
 
JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid
 
JOIN prefix_course as c ON c.id = e.course
 
JOIN prefix_user AS u ON u.id = e.creator
 
ORDER BY er.recordingsize DESC
 
 
</code>
 
</code>
 
+
Note: if you are using Configurable Reports block and want to perform this query on the current course you are in, then you can use a WHERE clause like this:
 
 
=== Choice ===
 
 
 
Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.
 
 
 
 
<code sql>
 
<code sql>
SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
+
WHERE c.id = %%COURSEID%%
FROM prefix_choice AS h
 
JOIN prefix_course AS c ON h.course = c.id
 
JOIN prefix_choice_answers AS a ON h.id = a.choiceid
 
JOIN prefix_user AS u ON a.userid = u.id
 
JOIN prefix_choice_options AS o ON a.optionid = o.id
 
 
</code>
 
</code>
  
=== Assignment type usage in courses ===
+
===List all Courses in and below a certain category===
 +
Use this SQL code to retrieve all courses that exist in or under a set category.
 +
 
 +
$s should be the id of the category you want to know about...
 
<code sql>
 
<code sql>
SELECT  
+
SELECT prefix_course. * , prefix_course_categories. *
 +
FROM prefix_course, prefix_course_categories
 +
WHERE prefix_course.category = prefix_course_categories.id
 +
AND (
 +
prefix_course_categories.path LIKE '/$s/%'
 +
OR prefix_course_categories.path LIKE '/$s'
 +
)
 +
</code>
  
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments"
+
===List all Categories in one level below a certain category===
 +
Use this PHP code to retrieve a list of all categories below a certain category.
  
,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments
+
$s should be the id of the top level category you are interested in.
 +
<code php>
 +
<?php
  
,(SELECT COUNT(*)
+
require_once('./config.php');
FROM prefix_assign_plugin_config AS apc
 
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 
WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 
#GROUP BY apc.plugin
 
) AS "File Assignments"
 
  
,(SELECT COUNT(*)
+
$parent_id = $s;
FROM prefix_assign_plugin_config AS apc
 
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 
WHERE iassign.course = c.id AND apc.plugin = 'onlinetext' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 
) AS "Online Assignments"
 
  
,(SELECT COUNT(*)
+
$categories= array();
FROM prefix_assign_plugin_config AS apc
 
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 
WHERE iassign.course = c.id AND apc.plugin = 'pdf' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 
) AS "PDF Assignments"
 
  
,(SELECT COUNT(*)
+
$categories = get_categories($parent_id);
FROM prefix_assign_plugin_config AS apc
 
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 
WHERE iassign.course = c.id AND apc.plugin = 'offline' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 
) AS "Offline Assignments"
 
  
,(SELECT COUNT(*)  
+
echo '<ol>';
FROM prefix_assign_plugin_config AS apc
+
foreach ($categories as $category)
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
+
        {
WHERE iassign.course = c.id AND apc.plugin = 'comments' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
+
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
) AS "Assignments Comments"
+
        }
 +
echo '</ol>';
  
FROM prefix_assign AS assign
+
?>
JOIN prefix_course AS c ON c.id = assign.course
 
GROUP BY c.id
 
 
</code>
 
</code>
  
==Assignment Module Reports==
+
===Blog activity per Course (not including VIEW)===
===All Ungraded Assignments===
+
Filter activity logging to some specific Course Categories!
Returns all the submitted assignments that still need grading
+
+ link course name to actual course (for quick reference)
 +
(you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
 
<code sql>
 
<code sql>
select
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
u.firstname AS "First",
+
,m.name ,count(cm.id) as counter
u.lastname AS "Last",
+
,(SELECT Count( ra.userid ) AS Users
c.fullname AS "Course",
+
FROM prefix_role_assignments AS ra
a.name AS "Assignment"
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5
 +
AND ctx.instanceid = c.id
 +
) AS Students
 +
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
 +
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id
 +
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 +
GROUP BY cm.course,cm.module order by counter desc
 +
</code>
  
from prefix_assignment_submissions as asb
+
===Student's posts content in all course blogs (oublog)===
join prefix_assignment as a ON a.id = asb.assignment
+
<code sql>
join prefix_user as u ON u.id = asb.userid
+
SELECT
join prefix_course as c ON c.id = a.course
+
b.name
join prefix_course_modules as cm ON c.id = cm.course
+
,op.title
 +
,op.message
 +
,( SELECT CONCAT(u.firstname, ' ',u.lastname) FROM prefix_user AS u WHERE u.id = oi.userid) AS "Username"
  
where asb.grade < 0 and cm.instance = a.id
+
FROM prefix_oublog_posts AS op
and cm.module = 1
+
JOIN prefix_oublog_instances AS oi ON oi.id = op.oubloginstancesid
 +
JOIN prefix_oublog as b ON b.id = oi.oublogid
 +
JOIN prefix_course AS c ON b.course = c.id
  
order by c.fullname, a.name, u.lastname
+
WHERE c.id = %%COURSEID%%
 
</code>
 
</code>
  
===All Ungraded Assignments w/ Link===
+
===All Courses which uploaded a Syllabus file===
Returns all the submitted assignments that still need grading, along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle.
+
+ under specific Category
 +
+ show first Teacher in that course
 +
+ link Course's fullname to actual course
 
<code sql>
 
<code sql>
select
+
SELECT
u.firstname AS "First",
+
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
u.lastname AS "Last",
+
,c.shortname,r.name
c.fullname AS "Course",
+
,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
a.name AS "Assignment",
+
FROM prefix_role_assignments AS ra
 
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
+
JOIN prefix_user as u ON u.id = ra.userid
+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)  
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
+
FROM prefix_resource as r
AS "Assignmentlink"
+
JOIN prefix_course as c ON r.course = c.id
 
+
WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )
 
+
AND c.category IN (10,18,26,13,28)
from prefix_assignment_submissions as asb
+
</code>
join prefix_assignment as a ON a.id = asb.assignment
+
join prefix_user as u ON u.id = asb.userid
+
===Site-wide completed SCORM activities by Course name===
join prefix_course as c ON c.id = a.course
+
This report will list all completed attempts for all SCORM activities. It is ordered first by Course name, then student's last name, then student's first name, then attempt number. Please note: the FROM_UNIXTIME command is for MySQL.
join prefix_course_modules as cm ON c.id = cm.course
+
<code sql>
 +
SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
 +
FROM prefix_scorm_scoes_track AS st
 +
JOIN prefix_user AS u ON st.userid=u.id
 +
JOIN prefix_scorm AS sc ON sc.id=st.scormid
 +
JOIN prefix_course AS c ON c.id=sc.course
 +
WHERE st.value='completed'
 +
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
 +
</code>
 +
===All users enrolled in a course without a role===
 +
Identifies All users that are enrolled in a course but are not assigned a role.
 +
<code sql>
 +
SELECT
 +
user.firstname AS Firstname,
 +
user.lastname AS Lastname,
 +
user.idnumber Employee_ID,
 +
course.fullname AS Course
 +
 
 +
FROM prefix_course AS course  
 +
JOIN prefix_enrol AS en ON en.courseid = course.id
 +
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 +
JOIN prefix_user as user ON user.id = ue.userid
  
where asb.grade < 0 and cm.instance = a.id and cm.module = 1
+
WHERE user.id NOT IN (
 +
SELECT u.id
 +
FROM prefix_course AS c
 +
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
 +
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 +
JOIN prefix_role AS r ON r.id = ra.roleid
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE c.id=course.id
 +
)
 +
ORDER BY Course, Lastname, Firstname
  
order by c.fullname, a.name, u.lastname
 
 
</code>
 
</code>
  
===Assignments (and Quizzes) waiting to be graded===
+
===List course resources accumulative file size and count===
This report requires a YEAR filter to be added (Available when using the latest block/configurable_reports)
+
This is the main (first) report, which has a link (alias) to a second report (the following on this page) which list each file in the course.
 +
<code sql>
 +
SELECT c.id "CourseID", context.id "ContextID"
 +
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=', c.id, '">', c.fullname ,'</a>') AS "Course Name"
 +
, COUNT(*) "Course Files" , ROUND( SUM( f.filesize ) /1048576 ) AS file_size_MB
 +
,CONCAT('<a target="_new" href="%%WWWROOT%%/blocks/configurable_reports/viewreport.php?alias=coursefiles&courseid=1&filter_courses=', c.id, '">List files</a>') AS "List Files"
  
Which you can always remove, to make this query work on earlier versions.
+
FROM mdl_files AS f
 +
JOIN mdl_context AS context ON context.id = f.contextid
 +
JOIN mdl_course AS c ON c.id = (
 +
  SELECT instanceid
 +
  FROM mdl_context
 +
  WHERE id = SUBSTRING_INDEX( SUBSTRING_INDEX( context.path, '/' , -2 ) , '/', 1 ) )
 +
WHERE filesize >0
 +
GROUP BY c.id
 +
</code>
  
The report includes:  
+
With this report, you will have to define "alias" report property to "coursefiles" for it to be able to be called from the above report.
*number of quizzes
+
And also setup (add) a FILTER_COURSES filter.
*unFinished Quiz attempts
+
<code sql>
*Finished Quiz attempts
+
SELECT
*number of students
+
id ,CONCAT('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', contextid, '/', component, '/', filearea, '/', itemid, '/', filename, '">', filename,'</a>') AS "File"
*number of Assignments
+
,filesize, mimetype ,author, license, timecreated, component, filearea, filepath
*number of submitted answers by students
+
 
*number of unchecked assignments (waiting for the Teacher) in a Course.
+
FROM mdl_files AS f
 +
WHERE filesize >0
 +
            AND f.contextid
 +
            IN (  SELECT id
 +
                    FROM mdl_context
 +
                    WHERE path
 +
                    LIKE (  SELECT CONCAT('%/',id,'/%')
 +
                                  AS contextquery
 +
                                FROM mdl_context
 +
                              WHERE 1=1
 +
        %%FILTER_COURSES:instanceid%%
 +
                                AND contextlevel = 50
 +
                          )
 +
                )
 +
</code>
 +
 
 +
===Which courses has redundant topics===
 +
This report list several "active topics" calculations, per course. which should give an administrator some indications for which topics/sections/weeks are filled with resources and activities and which ones are empty and not used (usually, at the end of the course).
 +
 
 +
The following, second SQL query, could be used to "trim" down those redundant course topics/sections/weeks by updating the course format's numsection (Number of sections) setting. (It's a per course format setting!)
  
 
<code sql>
 
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
SELECT id, format,
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">', c.fullname,'</a>') AS Course  
 +
 
 +
,(SELECT value  FROM  `mdl_course_format_options` WHERE  `courseid` = c.id AND `format` = c.format AND `name` = 'numsections' ) AS "numsections"
 +
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND `sequence` !=  '' ) AS "Non empty sections count"
 +
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id ) AS "Total section count"
 +
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND sequence IS NOT NULL) AS "Non NULL sections count"
 +
,(SELECT COUNT(*) FROM  `mdl_course_sections` WHERE  `course` = c.id AND name != '') AS "Non empty section Name count"
 +
,(SELECT COUNT(*) FROM mdl_course_modules cm WHERE cm.course = c.id) "Modules count"
  
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
FROM mdl_course AS c
FROM prefix_role_assignments AS ra
+
</code>
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 
 
,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
 
  
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
+
The following SQL REPLACE query is used for "fixing" (updating) the "numsections" of a specific course format "onetopics" (you can always change it, or discard it to use this SQL REPLACE on all course formats)
 +
<code sql>
 +
REPLACE INTO `mdl_course_format_options` (`id`, `courseid`, `format`, `sectionid`, `name`, `value`)
 +
SELECT NULL, c.id, 'onetopic', '0', 'numsections', (SELECT COUNT(*) FROM `mdl_course_sections` WHERE `course` = c.id AND name != '')
 +
FROM `mdl_course` c where format = 'onetopic'
 +
</code>
  
,(SELECT COUNT(*)
+
===Hidden Courses with Students Enrolled===
FROM prefix_course_modules cm
+
Contributed by Eric Strom
JOIN prefix_modules as m ON m.id = cm.module
 
WHERE m.name LIKE 'quiz' AND cm.course = c.id
 
GROUP BY cm.course
 
) AS 'nQuizzes'
 
  
,(SELECT COUNT(*)
+
This query identifies courses with student enrollment that are currently hidden from students. Includes the defined course start date, count of students and instructors, and a clickable email link of instructor (first found record if more than one).
FROM prefix_quiz_attempts AS qa
+
 
JOIN prefix_quiz AS q ON q.id = qa.quiz
+
<code sql>
WHERE q.course = c.id
+
SELECT c.visible AS Visible,
AND qa.timefinish = 0
+
DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
GROUP BY q.course) AS 'unFinished Quiz attempts'
+
concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
 +
c.id,'">',c.idnumber,'</a>') AS Course_ID,
  
,(SELECT COUNT(*)
+
(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
FROM prefix_quiz_attempts AS qa
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_quiz AS q ON q.id = qa.quiz
+
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students,
WHERE q.course = c.id
 
AND qa.timefinish > 0
 
GROUP BY q.course) AS 'finished quiz attempts'
 
  
,(SELECT Count( ra.userid ) AS Users
+
(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
FROM prefix_role_assignments AS ra
 
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Instructors,
AND ctx.instanceid = c.id
 
) AS nStudents
 
 
  
,(
+
(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
SELECT count(a.id)
+
  FROM prefix_role_assignments AS ra
FROM prefix_assignment AS a
+
  JOIN prefix_user AS u ON ra.userid = u.id
JOIN prefix_course_modules AS cm ON a.course = cm.course
+
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
+
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email',
) nAssignments
 
  
,(
+
now() AS Report_Timestamp
SELECT count(*)
+
 
FROM prefix_assignment AS a
+
FROM prefix_course AS c
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
+
WHERE c.visible = 0 AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
GROUP BY a.course
+
ORDER BY StartDate, Instructor_Email, Course_ID
) 'Open <br/>Assignments'
+
</code>
 +
 
 +
 
 +
==Course Design Reports==
 +
 
 +
These are reports which summarize course design aspects, such as activity and resource modules per section, types of activities used, etc.
 +
 
 +
===Course Content/Week===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
This report assumes that the first 14 sections in a course, not including the "0" or "Welcome" section, correspond to weeks (with "Subsections" given numbers much higher in the sequence). Of those sections, each is checked to count the number of:
 +
 
 +
    Forums
 +
    Graded Activities (may include Forums)
 +
    Resources (not including a Label)
 +
 
 +
Totals of each of these types of content elements per section are provided.
 +
 
 +
'''Note''': Only visible resources and activities are counted.
 +
'''Note''': this is a "Global" report.
  
, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)'
+
<code sql>
 +
SELECT
 
   
 
   
,(
+
cs.section AS 'Week'
SELECT count(asb.id)
+
, cs.name AS 'Section Name'
FROM prefix_assignment_submissions AS asb
+
 
JOIN prefix_assignment AS a ON a.id = asb.assignment
+
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT LIKE 'label'),cm.id,NULL)) AS 'Ungraded Resources'
JOIN prefix_course_modules AS cm ON a.course = cm.course
+
 
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
+
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
) 'unChecked  <br/>Submissions'  
+
 
+
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'
,(
+
 
SELECT count(asb.id)
+
FROM prefix_course AS c
FROM prefix_assignment_submissions AS asb
+
JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.section <= 14 AND cs.section > 0
JOIN prefix_assignment AS a ON a.id = asb.assignment
+
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
JOIN prefix_course_modules AS cm ON a.course = cm.course
+
JOIN prefix_modules AS m ON m.id = cm.module
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
+
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
) 'Submitted  <br/>Assignments'
+
 
+
WHERE
FROM prefix_course AS c
+
cs.visible = 1
LEFT JOIN (
+
AND cm.visible = 1
SELECT course, count(*) AS iAssignments
+
AND c.id = %%COURSEID%%
FROM prefix_assignment AS a
 
GROUP BY a.course
 
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
 
  
LEFT JOIN (
+
GROUP BY cs.section
SELECT course, count(*) AS iOpenAssignments
+
ORDER BY cs.section
FROM prefix_assignment AS a
 
WHERE FROM_UNIXTIME(a.timedue) > NOW()
 
GROUP BY a.course
 
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
 
  
WHERE 1=1 
 
#AND c.fullname LIKE '%תשעג%'
 
%%FILTER_YEARS:c.fullname%%
 
## You can enable the SEMESTER filter as well,
 
## by uncommenting the following line:
 
## %%FILTER_SEMESTERS:c.fullname%%
 
ORDER BY 'Open <br/>Assignments' DESC
 
 
</code>
 
</code>
  
===Who is using "Single File Upload" assignment===
+
===Assignments and Weights===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
Returns a list of grade book categories for the current course, grade book weightings, the first type of assignment included in the category, a count of different assignment types for each category, and a count of assignments for each category.
 +
 
 +
Categories with weights of 0 are not included in this report.
 +
 
 +
Only visible activities are included in this report.
 +
 
 +
'''Note''': This is designed to be a "Global" report in Configurable Reports.
 
<code sql>
 
<code sql>
SELECT  
+
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
 
 +
IF(gc.parent IS NOT NULL, gc.fullname, 'None') AS 'Grade Book Category'
 +
, IF(gc.parent IS NOT NULL, ROUND(gic.aggregationcoef, 2), ROUND(SUM(DISTINCT gi.aggregationcoef), 2)+ROUND(SUM(DISTINCT mgi.aggregationcoef), 2)) AS 'Category weight'
 +
 
 +
, CONCAT_WS(', ',GROUP_CONCAT(DISTINCT gi.itemmodule SEPARATOR ', '), IF(mgi.id, 'manual',NULL)) AS 'Activity Types'
 +
, COUNT(DISTINCT gi.itemmodule) + IF(mgi.id,1,0) AS 'Different Activity Types'
 +
, CONCAT_WS('<br>', GROUP_CONCAT(DISTINCT gi.itemname ORDER BY gi.itemname SEPARATOR '<br>'), GROUP_CONCAT(DISTINCT mgi.itemname ORDER BY mgi.itemname SEPARATOR '<br>')) AS 'Activity Names'
 +
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) + COUNT(DISTINCT mgi.id) AS 'Activity Count'
 +
 
 +
FROM prefix_course AS c
 +
 
 +
#get grade categories
 +
LEFT JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
 +
# back from categories to grade items to get aggregations and weights
 +
JOIN prefix_grade_items AS gic ON gic.courseid = c.id AND gic.itemtype = 'category' AND gic.aggregationcoef != 0 AND (LOCATE(gic.iteminstance, gc.path) OR (gc.parent IS NULL))
 +
 
 +
# attach activities to course
 +
JOIN prefix_course_modules AS cm ON cm.course = c.id
 +
# attach grade items to activities
 +
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.iteminstance = cm.instance AND gi.itemtype = 'mod' AND gi.categoryid = gc.id AND gi.hidden != 1
 +
 
 +
# attach manual grade items to course-- they don't have modules
 +
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual' AND mgi.categoryid = gc.id
 +
 
 +
WHERE
 +
cm.visible = 1
 +
AND c.id = %%COURSEID%%
 +
 
 +
GROUP BY gc.id
 +
ORDER BY gc.id
 +
 
 +
</code>
 +
 
 +
===Pre-Term Course Review===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
Provides an overview of the readiness of ONLINE, HYBRID, and BLENDED courses in the Staging category and all subcategories. Links to each course are provided. Other details:
 +
 
 +
#  "Required blocks" include Instructor Block (mooprofile), Activities, and the Research block.
 +
#    "Instructor Details" block is not the "Instructor" block (mooprofile) automatically provided by the system. It is an optional block that can be edited by the instructor. If not edited to remove boilerplate text, it should be hidden.
 +
#    All courses should be in the "Collapsed Topics" format with the "Weeks" structure.
 +
#    "Weeks defined in course settings" is taken from our SIS when the course shells are created, but can be edited by faculty. "# of weeks named and visible" should usually match or exceed this value.
 +
#    We recommend that each week contain at least one forum, at least one graded activity, and at least one ungraded resource.
 +
#    "Syllabus updated" date is for the first attached file found with the text "syllabus" in the name. The "Days ago" calculation is included for convenience.
 +
 
 +
'''Note''': At our institution, we construct categories each term, and insert a text string "staging" in the Category ID for pre-term courses during the preparation or "staging" phase of course development. We remove this text string (and change it to "production") when courses go live at the start of the new term.
 +
 
 +
<code sql>
 +
SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS Course
 +
 
 +
#,RIGHT(c.idnumber,2) AS Type # Specific to GSC "Instructional Method" storage
 +
 
 +
#, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section # Specific to GSC
 
   
 
   
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
,(SELECT CONCAT('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
 
FROM prefix_role_assignments AS ra
 
FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_user AS u ON u.id = ra.userid
 
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Instructor'
 +
 
 +
,(SELECT IF((u2.description IS NULL) OR (u2.description LIKE ''),'NO', 'YES')
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u2 ON u2.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Bio'
  
,ass.name as "Assignment Name"
+
,(SELECT IF(u3.picture > 0,'YES','NO')
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u3 ON u3.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS 'Profile Has Picture'
  
FROM
+
, IF(((bpi.visible IS NULL) OR (bpi.visible !=0)) AND ((bpm.visible IS NULL) OR (bpm.visible !=0)) AND ((bpa.visible IS NULL) OR (bpa.visible !=0)) AND ((bpr.visible IS NULL) OR (bpr.visible !=0)),'YES','NO') AS 'Required blocks visible'
prefix_assignment as ass
+
#, IF((bpm.visible IS NULL) OR (bpm.visible !=0),'YES','NO') AS 'Messages block visible'
 +
#, IF((bpa.visible IS NULL) OR (bpa.visible !=0),'YES','NO') AS 'activities block visible'
 +
#, IF((bpr.visible IS NULL) OR (bpr.visible !=0),'YES','NO') AS 'research block visible'
  
JOIN
+
#, IF(SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)) AND (bip.visible !=0),'YES','') AS 'Instructor Details Block visible' # This is a hack based on UUencoded string data from the title of HTML "Instructor Details" block
prefix_course as c ON c.id = ass.course
 
  
WHERE `assignmenttype` LIKE 'uploadsingle'
+
#, IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO','') AS 'Instructor Details Block Updated' # HTML block has string 'dit this block'
</code>
 
  
==Resource Module Reports==
+
#, IF(COUNT(bi.id) -  SUM(IF(bi.configdata LIKE 'Tzo4OiJzdGRDbGFzcyI6Mzp7czo1OiJ0aXRsZSI7czoxODoiSW5zdHJ1Y3RvciBEZXRhaWxzI%',1,0)),'YES','') AS 'possible extra instructor blocks' #looking for any HTML block with "instructor" in the title
===List "Recently uploaded files"===
+
 
see what users are uploading
+
, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format' # change this if you want to test for a different format
<code sql>
+
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'
SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info  
+
 
FROM `prefix_log`
+
, cfw.value AS 'weeks defined in course settings'
WHERE `action` LIKE 'upload'  
+
 
ORDER BY `prefix_log`.`time` DESC
+
, COUNT(DISTINCT IF(((cs.name IS NOT NULL) AND (cs.visible = 1) AND (cs.section != '0') AND (cs.sequence IS NOT NULL)),cs.id,NULL)) AS '# of weeks named & visible (includes orphans)'
</code>
+
 
 +
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
 +
, COUNT(DISTINCT IF(m.name LIKE 'forum' ,cs.id , NULL)) AS 'Weeks with Forum'
 +
 
 +
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Activities'
 +
, COUNT(DISTINCT IF(gi.id, cs.id, NULL)) AS 'Weeks with Activities'
 +
, COUNT(DISTINCT mgi.id) AS 'Manual Grade Items'
 +
 
 +
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')),cm.id,NULL)) AS 'Resources'
 +
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT IN ('forum','label')), cs.id, NULL)) AS 'Weeks with Resources'
 +
 
 +
# Here are some other things you could check for per course
 +
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 +
   
 +
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
 +
   
 +
#,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assign%') AS Assignments
  
===List Courses that loaded a specific file: "X"===
+
#,(SELECT COUNT(prefix_resource.id) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files
Did the Teacher (probably) uploaded course's Syllabus ?
 
<code sql>
 
SELECT c.id, c.fullname  FROM `prefix_log` as l
 
JOIN prefix_course as c ON c.id = l.course  
 
WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
 
</code>
 
  
===All resources that link to some specific external website===
+
#,(SELECT COUNT(prefix_url.id) FROM prefix_url JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links
+ link to course
+
 
+ who's the teacher
+
,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
+ link to external resource
+
FROM prefix_resource
<code sql>
+
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS SyllabusDate
SELECT
+
 
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
,c.shortname,r.name
+
FROM prefix_resource
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo
FROM prefix_role_assignments AS ra
+
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', f.id,NULL)),'YES','NO' ) AS 'Announcement Forum Visible'
JOIN prefix_user AS u ON u.id = ra.userid
+
 
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', fd.id,NULL)),'YES','NO' ) AS 'Announcement posted'
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
+
 
FROM prefix_resource AS r
+
FROM prefix_course AS c
JOIN prefix_course AS c ON r.course = c.id
+
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'  
+
LEFT JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid
</code>
+
 
 +
LEFT JOIN prefix_block_positions AS bpi ON bpi.contextid = ctxx.id AND bpi.blockinstanceid = '43692' # mooprofile
 +
LEFT JOIN prefix_block_positions AS bpm ON bpm.contextid = ctxx.id AND bpm.blockinstanceid = '43962' # messages
 +
LEFT JOIN prefix_block_positions AS bpa ON bpa.contextid = ctxx.id AND bpa.blockinstanceid = '43963' # activities
 +
LEFT JOIN prefix_block_positions AS bpr ON bpr.contextid = ctxx.id AND bpr.blockinstanceid = '38368' # html research help
 +
 
 +
LEFT JOIN prefix_course_sections AS cs ON cs.course = c.id AND cs.visible = 1 AND cs.sequence IS NOT NULL
 +
LEFT JOIN prefix_course_modules AS cm ON cm.course = c.id AND cm.section = cs.id
 +
LEFT JOIN prefix_modules AS m ON m.id = cm.module
 +
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
 +
 
 +
LEFT JOIN prefix_forum AS f ON f.course = c.id AND cm.instance = f.id AND cm.visible = 1
 +
LEFT JOIN prefix_forum_discussions AS fd ON fd.forum = f.id
 +
 
 +
# attach manual grade items to course-- they don't have modules
 +
LEFT JOIN prefix_grade_items AS mgi ON mgi.courseid = c.id and mgi.itemtype = 'manual'
 +
 
 +
LEFT JOIN prefix_course_format_options AS cfo ON cfo.courseid = c.id AND cfo.name = 'layoutstructure'
 +
LEFT JOIN prefix_course_format_options AS cfw ON cfw.courseid = c.id AND cfw.name = 'numsections'
 +
 
 +
LEFT JOIN prefix_block_instances AS bi ON bi.parentcontextid = ctxx.id AND bi.blockname = 'html' AND (bi.configdata LIKE '%SW5zdHJ1Y3Rvc%' or bi.configdata LIKE '%bnN0cnVjdG9y%')
 +
LEFT JOIN prefix_block_positions AS bip ON bip.blockinstanceid = bi.id
 +
 
 +
WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY')
 +
# AND substring(cc.path,2,2) IN ('26') # Staging
 +
#AND substring(cc.path,2,3) IN ('158') # UG
 +
AND cc.idnumber LIKE '%staging%'
 +
AND ctxx.contextlevel = 50
  
==="Compose Web Page" RESOURCE count===
+
GROUP BY c.shortname
<code sql>
 
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
 
 
</code>
 
</code>
  
===Resource count in courses===
+
===Module instances + Module HITs by role teacher and student in course===
+ (First)Teacher name
 
+ Where course is inside some specific Categories
 
 
<code sql>
 
<code sql>
 
SELECT  
 
SELECT  
COUNT(*) AS count
+
m.name AS "Module name"
,r.course
+
, COUNT(*) AS "Module count"
,c.shortname shortname
 
,c.fullname coursename
 
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 
  FROM prefix_role_assignments AS ra
 
  JOIN prefix_user as u ON ra.userid = u.id
 
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 
  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 
FROM prefix_resource r
 
JOIN prefix_course c ON r.course = c.id
 
WHERE c.category IN (10,13,28,18,26)
 
GROUP BY r.course
 
ORDER BY COUNT(*) DESC
 
</code>
 
  
==Forum Module Reports==
+
,(SELECT COUNT(*)  
===print all User's post in course Forums===
+
FROM prefix_log AS l
@@COURSEID@@ is a variable the is replace by the current CourseID you are running the sql report from. if you are using the latest block/configurable_reports ! (You can always change it to a fixed course or remove it to display all courses.)
+
WHERE l.course = cm.course AND l.module = m.name ) AS "Hits"
<code sql>
 
SELECT  
 
concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') As Fullname
 
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
 
,count(*) as Posts
 
,(SELECT count(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum as iforum ON iforum.id = ifd.forum  WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion
 
  
FROM prefix_forum_posts AS fp
+
,(SELECT COUNT(*)
JOIN prefix_user as u ON u.id = fp.userid
+
FROM prefix_log AS l
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id  
+
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
JOIN prefix_forum AS f ON f.id = fd.forum
+
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5
JOIN prefix_course as c ON c.id = fd.course  
+
WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs"
WHERE fd.course = '@@COURSEID@@'
+
 
GROUP BY f.id,u.id
+
,(SELECT COUNT(*)
ORDER BY u.id
+
FROM prefix_log AS l
</code>
+
JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
===FORUM use Count per COURSE -- not including NEWS Forum!===
+
JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
<code sql>
+
WHERE l.course = cm.course AND l.module = m.name) AS "Teachers HITs"
SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
+
 
INNER JOIN prefix_course
+
FROM mdl_course_modules AS cm
ON prefix_course.id = prefix_forum.course
+
JOIN mdl_modules AS m on m.id = cm.module
WHERE NOT(prefix_forum.type = 'news')
+
WHERE cm.course = '%%COURSEID%%'
GROUP BY prefix_forum.course
+
GROUP BY cm.module
ORDER BY total desc
 
 
</code>
 
</code>
  
===FORUM use Count per COURSE by type -- not including NEWS Forum!===
+
==Grade and Course Completion Reports==
 +
===Site-Wide Grade Report with All Items===
 +
Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports
 
<code sql>
 
<code sql>
SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
+
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',  
INNER JOIN prefix_course
+
c.fullname AS 'Course',  
ON prefix_course.id = prefix_forum.course
+
cc.name AS 'Category',
WHERE NOT(prefix_forum.type = 'news')
+
 
GROUP BY prefix_forum.course,prefix_forum.type
+
CASE
ORDER BY total desc
+
  WHEN gi.itemtype = 'course'
</code>
+
  THEN c.fullname + ' Course Total'
 +
  ELSE gi.itemname
 +
END AS 'Item Name',
 +
 
 +
ROUND(gg.finalgrade,2) AS Grade,
 +
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
  
===Forum activity - system wide===
+
FROM prefix_course AS c
<code sql>
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
+
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+
JOIN prefix_user AS u ON u.id = ra.userid
  FROM prefix_role_assignments AS ra
+
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
  JOIN prefix_user AS u ON ra.userid = u.id
+
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+
JOIN prefix_course_categories as cc ON cc.id = c.category
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 
,c.fullname as Course
 
,f.type
 
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
, fd.forum, f.name,count(*) AS cPostAndDisc
 
,(SELECT count(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion
 
FROM prefix_forum_posts AS fp
 
JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion
 
JOIN prefix_forum AS f ON f.id = fd.forum
 
JOIN prefix_course AS c ON c.id = f.course
 
WHERE f.type != 'news' AND c.fullname LIKE '%2013%'
 
## WHERE 1=1
 
## %%FILTER_YEARS:c.fullname%%
 
## You can enable the SEMESTER filter as well,
 
## by uncommenting the following line:
 
## %%FILTER_SEMESTERS:c.fullname%%
 
  
GROUP BY fd.forum
+
WHERE  gi.courseid = c.id
ORDER BY count( * ) DESC
+
ORDER BY lastname
 +
</code>
 +
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line
 +
<code>
 +
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
 +
</code>
 +
with
 +
<code>
 +
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time
 
</code>
 
</code>
  
===Activity In Forums===
+
===Site-Wide Grade Report with Just Course Totals===
Trying to figure out how much real activity we have in Forums by aggregating:
+
A second site-wide grade report for all students that just shows course totals. Works with ad-hoc reports or Configurable Reports
Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers , Number of Students, ratio between unique Student posts and the number of students in the Course...
 
 
<code sql>
 
<code sql>
SELECT c.fullname,f.name,f.type
+
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
+
cc.name AS 'Category',
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
+
CASE
,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
+
  WHEN gi.itemtype = 'course'
,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
+
  THEN c.fullname + ' Course Total'
,(SELECT Count( ra.userid ) AS Students
+
  ELSE gi.itemname
FROM prefix_role_assignments AS ra
+
END AS 'Item Name',
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
 
WHERE ra.roleid =5
+
ROUND(gg.finalgrade,2) AS Grade,
AND ctx.instanceid = c.id
+
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
) AS StudentsCount
+
 
,(SELECT Count( ra.userid ) AS Teachers
+
FROM prefix_course AS c
FROM prefix_role_assignments AS ra
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
WHERE ra.roleid =3
+
JOIN prefix_user AS u ON u.id = ra.userid
AND ctx.instanceid = c.id
+
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
) AS 'Teacher<br/>Count'
+
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
,(SELECT Count( ra.userid ) AS Users
+
JOIN prefix_course_categories as cc ON cc.id = c.category
FROM prefix_role_assignments AS ra
+
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
WHERE  gi.courseid = c.id AND gi.itemtype = 'course'
WHERE ra.roleid IN (3,5)
 
AND ctx.instanceid = c.id
 
) AS UserCount
 
, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
 
, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
 
FROM prefix_forum as f
 
JOIN prefix_course as c ON f.course = c.id
 
WHERE `type` != 'news'
 
ORDER BY StudentPostUsage DESC
 
</code>
 
  
===All Forum type:NEWS===
+
ORDER BY lastname
<code sql>
 
SELECT f.id, f.name
 
FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
JOIN prefix_forum AS f ON cm.instance = f.id
 
WHERE m.name = 'forum'
 
AND f.type = 'news'
 
 
</code>
 
</code>
  
===All new forum NEWS items (discussions) from all my Courses===
+
For MySQL users:
change "userid = 26" and "id = 26" to a new user id
 
 
<code sql>
 
<code sql>
SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
+
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
FROM prefix_forum_discussions as fd
+
c.fullname AS 'Course',
JOIN prefix_forum as f ON f.id = fd.forum
+
cc.name AS 'Category',
JOIN prefix_course as c ON c.id = f.course
+
CASE
JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
+
  WHEN gi.itemtype = 'course'  
WHERE fd.timemodified > ul.timeaccess 
+
  THEN CONCAT(c.fullname, ' - Total')
AND fd.forum IN (SELECT f.id
+
  ELSE gi.itemname
FROM prefix_course_modules AS cm
+
END AS 'Item Name',
JOIN prefix_modules AS m ON cm.module = m.id
 
JOIN prefix_forum AS f ON cm.instance = f.id
 
WHERE m.name = 'forum'
 
AND f.type = 'news')
 
  AND c.id IN (SELECT c.id
 
  FROM prefix_course AS c
 
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
 
  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 
  JOIN prefix_user AS u ON u.id = ra.userid
 
  WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
 
</code>
 
  
 
+
ROUND(gg.finalgrade,2) AS Grade,
===News Forum - Discussions COUNT===
+
FROM_UNIXTIME(gg.timemodified) AS TIME
Which is actually... How much instructions students get from their teachers
+
<code sql>
+
FROM prefix_course AS c
SELECT c.shortname ,
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+
JOIN prefix_user AS u ON u.id = ra.userid
  FROM prefix_role_assignments AS ra
+
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
  JOIN prefix_user AS u ON ra.userid = u.id
+
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
+
WHERE gi.courseid = c.id
FROM prefix_forum_discussions AS fd
+
ORDER BY lastname
INNER JOIN prefix_forum AS f ON f.id = fd.forum
 
INNER JOIN prefix_course AS c ON c.id = f.course
 
WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
 
GROUP BY fd.forum
 
ORDER BY count(fd.id) DESC
 
 
</code>
 
</code>
  
===Cantidad de foros que han sido posteados por profesor===
+
===Learner report by Learner with grades===
Queriamos saber cuales son las acciones del profesor dentro de los foros de cada curso, por ello se hizo este informe.
+
Which Learners in which course and what are the grades
 
<code sql>
 
<code sql>
SELECT  
+
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',  
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS curso,
+
CASE WHEN gi.itemtype = 'Course'   
CONCAT(u.firstname ,' ',u.lastname) AS Facilitador,
+
THEN c.fullname + ' Course Total' 
 +
ELSE gi.itemname
 +
END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS Max, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) as Percentage,
  
(SELECT COUNT( m.name ) AS COUNT FROM
+
if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass
prefix_course_modules AS cm
+
 
JOIN prefix_modules AS m ON cm.module = m.id
+
FROM prefix_course AS c
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS foros,
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
 +
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
JOIN prefix_grade_grades AS gg ON gg.userid = u.id
 +
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE gi.courseid = c.id and gi.itemname != 'Attendance'
 +
ORDER BY `Name` ASC
 +
</code>
  
COUNT(*) AS Posts
+
===User Course Completion===
  
FROM prefix_forum_posts AS fp
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
 
JOIN prefix_forum AS f ON f.id = fd.forum
 
JOIN prefix_course AS c ON c.id = fd.course
 
JOIN prefix_user AS u ON u.id = fp.userid
 
  
WHERE fp.userid =
+
A very simple report with a list of course completion status by username. Completions are noted by date, blank otherwise.  
(
 
select distinct prefix_user.id
 
from prefix_user
 
join prefix_role_assignments as ra on ra.userid = prefix_user.id
 
where ra.roleid = 3
 
and userid = fp.userid
 
limit 1
 
)
 
  
and c.shortname like '%2014-2-1%'
+
<code sql>
GROUP BY c.id, u.id
+
SELECT
 +
  u.username,
 +
  c.shortname
 +
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%Y-%m-%d') AS completed
 +
FROM prefix_course_completions AS p
 +
JOIN prefix_course AS c ON p.course = c.id
 +
JOIN prefix_user AS u ON p.userid = u.id
 +
WHERE c.enablecompletion = 1
 +
ORDER BY u.username
 
</code>
 
</code>
  
==Quiz Module Reports==
+
===User Course Completion with Criteria===
===Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes===
+
 
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
 +
 
 +
A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
 +
 
 
<code sql>
 
<code sql>
SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
+
SELECT u.username AS user,  
                co.fullname AS course_fullname, co.shortname AS course_shortname,
+
c.shortname AS course,
                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
+
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
                u.firstname, u.lastname, u.email,
+
CASE
FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
+
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any"
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
+
ELSE "All"
                qu.course = co.id AND
+
END AS aggregation,
                co.id = ct.instanceid AND
+
CASE
                ra.roleid = re.id AND
+
WHEN p.criteriatype = 1 THEN "Self"
                re.name = 'Teacher' AND
+
WHEN p.criteriatype = 2 THEN "By Date"
                ra.contextid = ct.id AND
+
WHEN p.criteriatype = 3 THEN "Unenrol Status"
                ra.userid = u.id
+
WHEN p.criteriatype = 4 THEN "Activity"
+
WHEN p.criteriatype = 5 THEN "Duration"
SELECT Count('x') As NumOfStudents
+
WHEN p.criteriatype = 6 THEN "Course Grade"
                                FROM prefix_role_assignments a
+
WHEN p.criteriatype = 7 THEN "Approve by Role"
                                JOIN prefix_user u ON userid = u.id
+
WHEN p.criteriatype = 8 THEN "Previous Course"
                                WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)
+
END AS criteriatype,
 +
CASE
 +
WHEN p.criteriatype = 1 THEN "*"
 +
WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
 +
WHEN p.criteriatype = 3 THEN t.unenroled
 +
WHEN p.criteriatype = 4 THEN
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
 +
WHEN p.criteriatype = 5 THEN p.enrolperiod
 +
WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2))
 +
WHEN p.criteriatype = 7 THEN p.role
 +
WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance)
 +
END AS criteriadetail
 +
FROM prefix_course_completion_crit_compl AS t
 +
JOIN prefix_user AS u ON t.userid = u.id
 +
JOIN prefix_course AS c ON t.course = c.id
 +
JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id
 +
 
 
</code>
 
</code>
  
===Number of Quizes per Course===
+
===Courses with Completion Enabled and their settings===
<code sql>
+
 
SELECT count(*)
+
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes
 
  
FROM prefix_course_modules cm
+
List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.
JOIN prefix_course c ON c.id = cm.course
 
JOIN prefix_modules as m ON m.id = cm.module
 
WHERE m.name LIKE 'quiz'
 
GROUP BY c.id
 
</code>
 
  
===List all MultiAnswer (Cloze) Questions===
 
 
<code sql>
 
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
 
,question.id question_id, question.questiontext
 
FROM  prefix_question question
 
JOIN prefix_quiz_question_instances qqi ON question.id = qqi.question
 
JOIN prefix_quiz quiz ON qqi.quiz = quiz.id
 
WHERE  `qtype` LIKE  'multianswer'
 
</code>
 
  
===List courses with MANUAL grades===
+
SELECT c.shortname AS Course,
Which is basically and indication to teachers using Moodle to hold offline grades inside Moodle's Gradebook,
+
CASE
So grades could be uploaded into an administrative SIS. Use with Configurable Reports.
+
WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = t.course AND a.criteriatype IS NULL)) = 2 THEN "All"
<code sql>
+
ELSE "Any"
SELECT COUNT( * )
+
END AS Course_Aggregation,
,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course
+
CASE
FROM prefix_grade_items AS gi
+
WHEN t.criteriatype = 1 THEN "Self completion"
JOIN prefix_course as c ON c.id = gi.courseid
+
WHEN t.criteriatype = 2 THEN "Date done by"
WHERE `itemtype` = 'manual'
+
WHEN t.criteriatype = 3 THEN "Unenrolement"
GROUP BY courseid
+
WHEN t.criteriatype = 4 THEN "Activity completion" 
 +
WHEN t.criteriatype = 5 THEN "Duration in days"
 +
WHEN t.criteriatype = 6 THEN "Final grade"   
 +
WHEN t.criteriatype = 7 THEN "Approve by role"
 +
WHEN t.criteriatype = 8 THEN "Previous course"
 +
END AS Criteria_type,
 +
CASE
 +
WHEN t.criteriatype = 1 THEN "On"
 +
WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
 +
WHEN t.criteriatype = 3 THEN "On"
 +
WHEN t.criteriatype = 4 THEN
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
 +
WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
 +
WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
 +
WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role)
 +
WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance)
 +
END AS Criteria_detail
 +
FROM prefix_course_completion_criteria as t
 +
JOIN prefix_course AS c ON t.course = c.id
 +
WHERE c.enablecompletion = 1
 +
ORDER BY course
 
</code>
 
</code>
===List the users that did not took the Quiz===
+
 
Do not forget to change "c.id = 14" and q.name LIKE '%quiz name goes here%'
+
===Course Completion Report with custom dates===
 +
 
 +
List of users who completed multiple or single course/s from a start date to end date chosen by the user. The output gives username, name, course name, completion date and score
 +
 
 
<code sql>
 
<code sql>
SELECT
 
user2.id AS ID,
 
ul.timeaccess,
 
user2.firstname AS Firstname,
 
user2.lastname AS Lastname,
 
user2.email AS Email,
 
user2.username AS IDNumber,
 
user2.institution AS Institution,
 
 
IF (user2.lastaccess = 0,'never',
 
DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess
 
 
,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess
 
 
,(SELECT r.name
 
FROM  prefix_user_enrolments AS uenrol
 
JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
 
JOIN prefix_role AS r ON e.id = r.id
 
WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName
 
 
FROM prefix_user_enrolments AS ue
 
JOIN prefix_enrol AS e ON e.id = ue.enrolid
 
JOIN prefix_course AS c ON c.id = e.courseid
 
JOIN prefix_user AS user2 ON user2 .id = ue.userid
 
LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id
 
WHERE c.id=14 and ue.userid NOT IN (SELECT qa.userid FROM prefix_quiz_attempts AS qa
 
JOIN prefix_quiz AS q ON qa.quiz = q.id
 
JOIN prefix_course AS c ON q.course = c.id
 
WHERE c.id = 14 AND q.name LIKE '%quiz name goes here%')
 
</code>
 
  
 +
SELECT u.username AS 'User Name',
 +
CONCAT(u.firstname , ' ' , u.lastname) AS 'Name',
 +
c.shortname AS 'Course Name',
 +
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date',
 +
ROUND(c4.gradefinal,2) AS 'Score'
 +
FROM prefix_course_completions AS p
 +
JOIN prefix_course AS c ON p.course = c.id
 +
JOIN prefix_user AS u ON p.userid = u.id
 +
JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid
 +
WHERE c.enablecompletion = 1  AND (p.timecompleted IS NOT NULL OR p.timecompleted !='')
 +
AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date)
 +
GROUP BY u.username
 +
ORDER BY c.shortname
  
===List Questions in each Quiz===
+
</code>
  
 +
===Scales used in activities===
 
<code sql>
 
<code sql>
SELECT quiz.id,quiz.name, q.id, q.name
+
SELECT scale.name
FROM mdl_quiz AS quiz
+
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
JOIN mdl_question AS q ON FIND_IN_SET(q.id, quiz.questions)
+
,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',gi.itemmodule,'/view.php?id=',cm.id,'">',gi.itemname,'</a>') AS "Module View"
WHERE quiz.course = %%COURSEID%%
+
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/modedit.php?up','date=',cm.id,'">',gi.itemname,'</a>') AS "Module Settings"
ORDER BY quiz.id ASC
+
 
 +
FROM prefix_grade_items AS gi
 +
JOIN prefix_course AS c ON c.id = gi.courseid
 +
JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance
 +
JOIN prefix_scale AS scale ON scale.id = gi.scaleid
 +
WHERE gi.scaleid IS NOT NULL
 
</code>
 
</code>
  
==SCORM Activity Reports==
 
  
===Lists All completed SCORM activites by Course name===
+
===Extra Credit Items by Name Only===
This report will list all completed attempts for all SCORM activities. It is ordered first by Course name, then student's last name, then student's first name, then attempt number. Please note: the FROM_UNIXTIME command is for MySQL.
+
Contributed by Eric Strom
 +
 
 +
This query identifies grade items in visible courses with student enrollment that have "extra credit" in the name of the item but set as extra credit in the grade settings. Includes the defined course start date, count of students and instructors, and a clickable email link of instructor (first found record if more than one).
 +
 
 
<code sql>
 
<code sql>
SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
+
SELECT DATE(FROM_UNIXTIME(c.startdate)) AS StartDate,
FROM prefix_scorm_scoes_track AS st
+
concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php',CHAR(63),'id=',
JOIN prefix_user AS u ON st.userid=u.id
+
c.id,'">',c.idnumber,'</a>') AS Course_ID, gi.itemname AS Item_Name
JOIN prefix_scorm AS sc ON sc.id=st.scormid
+
 
JOIN prefix_course AS c ON c.id=sc.course
+
,(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
WHERE st.value='completed'  
+
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
+
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
 
 +
,(SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Instructors
 +
 
 +
,(SELECT DISTINCT concat('<a href="mailto:',u.email,'">',u.email,'</a>')
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 'Instructor_Email'
 +
 
 +
,now() AS Report_Timestamp
 +
 
 +
FROM prefix_grade_items AS gi
 +
JOIN prefix_course AS c ON gi.courseid = c.id
 +
 
 +
WHERE gi.itemname LIKE '%extra credit%'
 +
AND gi.gradetype = '1'
 +
AND gi.hidden = '0'
 +
AND gi.aggregationcoef = '0'  
 +
AND c.visible = 1
 +
AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
 +
 
 +
GROUP BY Course_ID, gi.id
 +
ORDER BY StartDate, Course_ID
 +
 +
%%FILTER_SEARCHTEXT:Course_ID:~%%
 
</code>
 
</code>
  
== Badges==
+
===Site Wide Number of Courses Completed by User===
 
+
Contributed by Ken St. John
=== All badges issued, by User ===
 
  
This report will show you all the badges on a site that have been issued, both site and all courses, by the username of each user issued a badge. Includes the type of criteria passed (activity, course completion, manual), date issued, date expires, and a direct link to that issued badge page so you can see all the other details for that badge.
+
Simple report that shows the number of completed courses for all users site wide
  
 
<code sql>
 
<code sql>
SELECT u.username, b.name AS badgename,  
+
SELECT u.lastname, u.firstname,
CASE
+
COUNT(p.timecompleted) AS TotalCompletions
WHEN b.courseid IS NOT NULL THEN
+
FROM prefix_course_completions AS p
(SELECT c.shortname
+
JOIN prefix_user AS u ON p.userid = u.id
    FROM prefix_course AS c
+
GROUP BY p.userid
    WHERE c.id = b.courseid)
+
ORDER BY u.lastname
WHEN b.courseid IS NULL THEN "*"
+
</code>
END AS Context,
+
 
CASE
+
==Activity Module Reports==
  WHEN t.criteriatype = 1 AND t.method = 1 THEN "Activity Completion (All)"
 
  WHEN t.criteriatype = 1 AND t.method = 2 THEN "Activity Completion (Any)"
 
  WHEN t.criteriatype = 2 AND t.method = 2 THEN "Manual Award"
 
  WHEN t.criteriatype = 4 AND t.method = 1 THEN "Course Completion (All)"
 
  WHEN t.criteriatype = 4 AND t.method = 2 THEN "Course Completion (Any)"
 
  ELSE CONCAT ('Other: ', t.criteriatype)
 
END AS Criteriatype,
 
DATE_FORMAT( FROM_UNIXTIME( d.dateissued ) , '%Y-%m-%d' ) AS dateissued,
 
DATE_FORMAT( FROM_UNIXTIME( d.dateexpire ), '%Y-%m-%d' ) AS dateexpires,
 
CONCAT ('<a target="_new" href="%%WWWROOT%%/badges/badge.php?hash=',d.uniquehash,'">link</a>') AS Details
 
FROM prefix_badge_issued AS d
 
JOIN prefix_badge AS b ON d.badgeid = b.id
 
JOIN prefix_user AS u ON d.userid = u.id
 
JOIN prefix_badge_criteria AS t on b.id = t.badgeid
 
WHERE t.criteriatype <> 0
 
ORDER BY u.username
 
</code>
 
  
Please note: the FROM_UNIXTIME command is for MySQL.
+
=== User activity completions with dates===
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
  
=== All badges available in the system, with Earned count ===
+
This report shows the users completion status of activities across all courses. It is intended to be uses with Configurable Reports filters for user, start and end times, and also to be able to search the Module names.
  
Report of all badges in the system, with badge name and description, context, course shortname if a course badge, whether it is active and available, and a count of how many users have been issued that badge.
+
Note: The CASE statement with module numbers may differ on different systems, depending on the number give to the module when the site was created or the module added to the site. These are common default numbers, but you should check your id numbers for them in the course_modules table and adjust as required. You can also add other, third-party plugins too if you wish.  
  
 
<code sql>
 
<code sql>
SELECT b.id, b.name, b.description,
+
SELECT
 +
u.username As 'User',
 +
c.shortname AS 'Course',
 +
m.name AS Activitytype,
 +
CASE
 +
    WHEN cm.module = 1 THEN (SELECT a1.name FROM prefix_assign a1            WHERE a1.id = cm.instance)
 +
    WHEN cm.module = 2 THEN (SELECT a2.name FROM prefix_assignment a2    WHERE a2.id = cm.instance)
 +
    WHEN cm.module = 3 THEN (SELECT a3.name FROM prefix_book a3              WHERE a3.id = cm.instance)
 +
    WHEN cm.module = 4 THEN (SELECT a4.name FROM prefix_chat a4                WHERE a4.id = cm.instance)
 +
    WHEN cm.module = 5 THEN (SELECT a5.name FROM prefix_choice a5            WHERE a5.id = cm.instance)
 +
    WHEN cm.module = 6 THEN (SELECT a6.name FROM prefix_data a6                WHERE a6.id = cm.instance)
 +
    WHEN cm.module = 7 THEN (SELECT a7.name FROM prefix_feedback a7        WHERE a7.id = cm.instance)
 +
    WHEN cm.module = 8 THEN (SELECT a8.name FROM prefix_folder a8              WHERE a8.id = cm.instance)
 +
    WHEN cm.module = 9 THEN (SELECT a9.name FROM prefix_forum a9              WHERE a9.id = cm.instance)
 +
    WHEN cm.module = 10 THEN (SELECT a10.name FROM prefix_glossary a10        WHERE a10.id = cm.instance)
 +
    WHEN cm.module = 11 THEN (SELECT a11.name FROM prefix_imscp  a11          WHERE a11.id = cm.instance)
 +
    WHEN cm.module = 12 THEN (SELECT a12.name FROM prefix_label a12              WHERE a12.id = cm.instance)
 +
    WHEN cm.module = 13 THEN (SELECT a13.name FROM prefix_lesson a13            WHERE a13.id = cm.instance)
 +
    WHEN cm.module = 14 THEN (SELECT a14.name FROM prefix_lti a14                    WHERE a14.id = cm.instance)
 +
    WHEN cm.module = 15 THEN (SELECT a15.name FROM prefix_page a15              WHERE a15.id = cm.instance)
 +
    WHEN cm.module = 16 THEN (SELECT a16.name FROM prefix_quiz  a16              WHERE a16.id = cm.instance)
 +
    WHEN cm.module = 17 THEN (SELECT a17.name FROM prefix_resource a17        WHERE a17.id = cm.instance)
 +
    WHEN cm.module = 18 THEN (SELECT a18.name FROM prefix_scorm a18            WHERE a18.id = cm.instance)
 +
    WHEN cm.module = 19 THEN (SELECT a19.name FROM prefix_survey a19            WHERE a19.id = cm.instance)
 +
    WHEN cm.module = 20 THEN (SELECT a20.name FROM prefix_url a20                      WHERE a20.id = cm.instance)
 +
    WHEN cm.module = 21 THEN (SELECT a21.name FROM prefix_wiki a21                    WHERE a21.id = cm.instance)
 +
    WHEN cm.module = 22 THEN (SELECT a22.name FROM prefix_workshop a22          WHERE a22.id = cm.instance)
 +
END AS Actvityname,
 +
# cm.section AS Coursesection,
 +
CASE
 +
    WHEN cm.completion = 0 THEN '0 None'
 +
    WHEN cm.completion = 1 THEN '1 Self'
 +
    WHEN cm.completion = 2 THEN '2 Auto'
 +
END AS Activtycompletiontype,  
 
CASE
 
CASE
WHEN b.type = 1 THEN "System"
+
  WHEN cmc.completionstate = 0 THEN 'In Progress'
WHEN b.type = 2 THEN "Course"
+
  WHEN cmc.completionstate = 1 THEN 'Completed'
END AS Context,
+
  WHEN cmc.completionstate = 2 THEN 'Completed with Pass'
CASE
+
  WHEN cmc.completionstate = 3 THEN 'Completed with Fail'
WHEN b.courseid IS NOT NULL THEN  
+
  ELSE 'Unknown'
(SELECT c.shortname
+
END AS 'Progress',
    FROM prefix_course AS c
+
DATE_FORMAT(FROM_UNIXTIME(cmc.timemodified), '%Y-%m-%d %H:%i') AS 'When'
    WHERE c.id = b.courseid)
+
FROM prefix_course_modules_completion cmc
WHEN b.courseid IS NULL THEN "*"
+
JOIN prefix_user u ON cmc.userid = u.id
END AS Course,
+
JOIN prefix_course_modules cm ON cmc.coursemoduleid = cm.id
CASE
+
JOIN prefix_course c ON cm.course = c.id
WHEN b.status = 0 OR b.status = 2 THEN "No"
+
JOIN prefix_modules m ON cm.module = m.id
WHEN b.status = 1 OR b.status = 3 THEN "Yes"
+
# skip the predefined admin and guest user
WHEN b.status = 4 THEN "x"
+
WHERE u.id > 2
END AS Available,
+
# config reports filters
CASE
+
%%FILTER_USERS:u.username%%
WHEN b.status = 0 OR b.status = 1 THEN "0"
+
%%FILTER_SEARCHTEXT:m.name:~%%
WHEN b.status = 2 OR b.status = 3 OR b.status = 4 THEN
+
%%FILTER_STARTTIME:cmc.timemodified:>%% %%FILTER_ENDTIME:cmc.timemodified:<%%
(SELECT COUNT(*)
+
 
  FROM prefix_badge_issued AS d
+
ORDER BY u.username
  WHERE d.badgeid = b.id
 
)
 
END AS Earned
 
FROM prefix_badge AS b
 
  
 
</code>
 
</code>
  
=== Badges Leaderboard ===
+
===How many SCORM activities are used in each Course===
 
 
A simple list of usernames and how many badges they have earned overall.
 
 
 
 
<code sql>
 
<code sql>
SELECT u.username, (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.userid = u.id) AS earned
+
SELECT cm.course,c.fullname ,m.name
FROM prefix_user AS u
+
,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
ORDER BY earned DESC, u.username ASC
+
 +
FROM `prefix_course_modules` as cm
 +
  JOIN prefix_modules as m ON cm.module=m.id
 +
  JOIN prefix_course as c ON cm.course = c.id  
 +
WHERE m.name LIKE '%scorm%'
 +
GROUP BY cm.course,cm.module
 +
ORDER BY count(cm.id) desc
 
</code>
 
</code>
  
==Administrator Reports==
+
===SCORM Usage by Course Start Date===
 
+
Contributed by Elizabeth Dalton, Granite State College
===Config changes in Export friendly form===
 
  
The Administrative report Config changes is very useful but it would be nice to have it in a format that could be easily exported in one listing. Here is code to do that.
+
Report of number of inclusions of SCORM activities in courses, filtered by course start date.
  
 
<code sql>
 
<code sql>
 
SELECT  
 
SELECT  
DATE_FORMAT( FROM_UNIXTIME( g.timemodified ) , '%Y-%m-%d' ) AS date,
 
u.username AS user,
 
g.name AS setting,
 
CASE
 
WHEN g.plugin IS NULL THEN "core"
 
ELSE g.plugin
 
END AS plugin,
 
g.value AS new_value,
 
g.oldvalue AS original_value
 
FROM prefix_config_log  AS g
 
JOIN prefix_user AS u ON g.userid = u.id
 
ORDER BY date DESC
 
</code>
 
  
===Cohorts by user===
+
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'
 +
 
 +
, cc.name AS 'Category'
 +
, scm.name AS 'Sample Activity Name'
 +
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
 +
, COUNT(DISTINCT cm.id) AS 'Resources Used'
 +
#, FROM_UNIXTIME(cm.added) AS 'resource added'
  
How to get a list of all users and which cohorts they belong to.
 
  
<code sql>
+
FROM prefix_course_modules AS cm
SELECT u.firstname, u.lastname, h.idnumber, h.name
+
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'SCO%'
FROM prefix_cohort AS h
+
 
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
+
JOIN prefix_course AS c ON c.id = cm.course
JOIN prefix_user AS u ON hm.userid = u.id
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
ORDER BY u.firstname
+
JOIN prefix_scorm AS scm ON scm.id = cm.instance
 +
 
 +
WHERE
 +
1
 +
 
 +
%%FILTER_STARTTIME:c.startdate:>%%
 +
%%FILTER_ENDTIME:c.startdate:<%%
 +
 
 +
GROUP BY c.shortname, m.name
 +
ORDER BY c.startdate, c.shortname
 
</code>
 
</code>
  
 +
=== LTI (External Tool) Usage by Course Start Date===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 +
Report of number of inclusions of  LTI (External Tool) Usage activities in courses, filtered by course start date.
  
===Courses created And Active courses by Year===
 
Active courses is counting course that have at least one Hit, And "Active_MoreThan100Hits" counts courses that have at least 100 Hits
 
 
<code sql>
 
<code sql>
 
SELECT  
 
SELECT  
  
YEAR( FROM_UNIXTIME( `timecreated` ) ) AS YEAR, COUNT( * ) AS Counter
+
CONCAT('<a target="_blank" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.shortname,'</a>') AS 'course'
 +
 
 +
, cc.name AS 'Category'
 +
, lti.name AS 'Sample Activity Name'
 +
, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
 +
, COUNT(DISTINCT cm.id) AS 'Resources Used'
 +
#, FROM_UNIXTIME(cm.added) AS 'resource added'
 +
 
 +
 
 +
FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id AND m.name LIKE 'lti'
  
, (SELECT COUNT( DISTINCT course )
+
JOIN prefix_course AS c ON c.id = cm.course
FROM prefix_log AS l
+
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) )
+
JOIN prefix_lti AS lti ON lti.id = cm.instance
) AS "Active"
+
WHERE
 +
1
  
,(SELECT COUNT(*) FROM (
+
%%FILTER_STARTTIME:c.startdate:>%%
SELECT COUNT( * ),time
+
%%FILTER_ENDTIME:c.startdate:<%%
FROM prefix_log AS l
 
GROUP BY course
 
HAVING COUNT(*) > 100) AS courses_log
 
WHERE YEAR( FROM_UNIXTIME( courses_log.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) )
 
) AS "Active_MoreThan100Hits"
 
  
FROM `prefix_course`
+
GROUP BY c.shortname, m.name
GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
+
ORDER BY c.startdate, c.shortname
 
</code>
 
</code>
  
===Users created And Active users by Year===
+
===Detailed ACTIONs for each MODULE===
Active users is counting users that have at least one Hit, And "Active_MoreThan500Hits" counts users that have at least 500 Hits
+
<code sql>
 +
SELECT module,action,count(id) as counter
 +
FROM prefix_log
 +
GROUP BY module,action
 +
ORDER BY module,counter desc
 +
</code>
 +
 
 +
===Most popular ACTIVITY===
 +
<code sql>
 +
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
 +
</code>
 +
 
 +
===System wide use of ACTIVITIES and RESOURCES===
 
<code sql>
 
<code sql>
SELECT  
+
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
 +
</code>
 +
 
 +
===LOG file ACTIONS per MODULE per COURSE (IDs)===
 +
<code sql>
 +
select course,module,action,count(action) as summa from prefix_log
 +
where action <> 'new'
 +
group by course,action,module
 +
order by course,module,action
 +
</code>
 +
 
 +
===System Wide usage count of various course Activities===
 +
(Tested and works fine in Moodle 2.x)
 +
Like: Forum, Wiki, Blog, Assignment, Database,
 +
#Within specific category
 +
#Teacher name in course
 +
 
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 +
 
 +
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
 +
 
 +
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
 
 +
FROM prefix_course AS c
 +
WHERE c.category IN ( 18)
 +
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
 +
</code>
 +
 
 +
===Course wiki usage/activity over the last 6 semesters===
 +
<code sql>
 +
SELECT "Courses with Wikis"
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester A%') AS '2010 <br/> Semester A'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester B%') AS '2010 <br/> Semester B'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעא <br/> סמסטר א'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעא <br/> סמסטר ב'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעב <br/> סמסטר א'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעב <br/> סמסטר ב'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעג <br/> סמסטר א'
 +
 
 +
,(SELECT count( m.name ) AS count FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_course AS c ON c.id = cm.course
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%'
 +
and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעג <br/> סמסטר ב'
 +
</code>
 +
 
 +
===Detailed WIKI activity (per wiki per course)===
 +
Including Number of Students in course (for reference)
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID 
 +
,(SELECT Count( ra.userid ) AS Users
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students
 +
,m.name
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD'
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT
 +
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)'
 +
FROM `prefix_course_modules` as cm
 +
JOIN prefix_modules as m ON cm.module=m.id
 +
JOIN prefix_course as c ON cm.course = c.id
 +
WHERE m.name LIKE '%wiki%'
 +
GROUP BY cm.course,cm.module
 +
ORDER BY 'All (NO View)' DESC
 +
</code>
 +
 
 +
===Wiki usage, system wide===
 +
(you can filter the output by selecting some specific course categories : "WHERE c.category IN ( 8,13,15)")
 +
 
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
 +
 
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity<br/>ALL'
 +
 
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity<br/>ADD'
 +
 
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity<br/>EDIT'
 +
 
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity<br/>ANNOTATE'
 +
 
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity<br/>Comments'
 +
 
 +
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
 
 +
,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp
 +
JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid
 +
WHERE ouw.course = c.id GROUP BY ouw.course  ) as OUWikiPages
 +
 
 +
,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
 +
JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages
 +
 
 +
FROM prefix_course AS c
 +
WHERE c.category IN ( 8,13,15)
 +
HAVING Wikis > 0
 +
ORDER BY 'WikiActivity<br/>ALL' DESC
 +
</code>
 +
 
 +
===Aggregated Teacher activity by "WEB2" Modules===
 +
(Tested and works fine in Moodle 2.x)
 +
The NV column shows activity without VIEW log activity
 +
<code sql>
 +
SELECT ra.userid, u.firstname,u.lastname
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment
 +
,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE ra.roleid = 3
 +
GROUP BY ra.userid
 +
</code>
 +
 
 +
===List all the certificates issued, sort by variables in the custom profile fields===
 +
Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.
 +
 
 +
<code sql>
 +
SELECT
 +
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date,
 +
prefix_certificate_issues.classname AS Topic,
 +
prefix_certificate.name AS Certificate,
 +
prefix_certificate_issues.studentname as Name,
 +
prefix_user_info_data.data AS Units
 +
 
 +
FROM
 +
prefix_certificate_issues
 +
 
 +
INNER JOIN prefix_user_info_data
 +
on prefix_certificate_issues.userid = prefix_user_info_data.userid
 +
 
 +
INNER JOIN prefix_certificate
 +
on prefix_certificate_issues.certificateid = prefix_certificate.id
 +
 
 +
WHERE prefix_user_info_data.data='Unit 1'
 +
OR prefix_user_info_data.data='Unit 2'
 +
OR prefix_user_info_data.data='Unit 3'
 +
 
 +
ORDER BY Units, Name, Topic ASC
 +
</code>
 +
 
 +
 
 +
=== All Simple Certificates Earned in the Site===
 +
 
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
 +
 
 +
Basic report of all certificates earned with the Simple Certificate plugin module in the whole site, sorted by most recent first. (Note: this uses the MySQL [http://www.mysqltutorial.org/mysql-date_format/ DATE_FORMAT] function.)
 +
 
 +
<code sql>
 +
SELECT
 +
CONCAT (u.firstname, ' ',u.lastname) As 'User',
 +
c.fullname AS 'Course',
 +
sc.name AS 'Certificate',
 +
DATE_FORMAT( FROM_UNIXTIME(sci.timecreated), '%Y-%m-%d' ) As 'Date Awarded'
 +
# sci.code 'CertificateId'
 +
FROM prefix_simplecertificate_issues sci
 +
JOIN prefix_user u ON sci.userid = u.id
 +
JOIN prefix_simplecertificate sc ON sci.certificateid = sc.id
 +
JOIN prefix_course AS c ON sc.course = c.id
 +
ORDER BY sci.timecreated DESC
 +
</code>
 +
 
 +
If you want to limit this to the most recent ones, you can add a condition to limit it to a certain number of days past. For example, adding this WHERE clause (above the ORDER BY) will show only those earned in the last 30 days:
 +
<code sql>
 +
WHERE DATEDIFF(NOW(),FROM_UNIXTIME(sci.timecreated) ) < 30
 +
</code>
 +
 
 +
===Counter Blog usage in Courses,system wide===
 +
What teachers in what courses, uses blogs and how many + student count in that course.
 +
<code sql>
 +
 
 +
SELECT ( @counter := @counter+1) as counter,
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 +
 
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 +
 
 +
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
 
 +
FROM prefix_course AS c, (SELECT @counter := 0) as s_init
 +
WHERE c.category IN ( 8,13,15)
 +
HAVING Blogs > 0
 +
ORDER BY Blogs DESC
 +
</code>
 +
 
 +
=== Elluminate (Blackboard Collaborate) - system wide usage===
 +
<code sql>
 +
SELECT e.name As Session ,er.recordingsize
 +
,c.fullname As Course
 +
,u.firstname,u.lastname
 +
,DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%d-%m-%Y') AS dTimeStart
 +
,concat('<a target="_new" href="%%WWWROOT%%/moodle/mod/elluminate/loadrecording.php?id=',er.id,'">Show</a>') AS RecordedSession
 +
 
 +
FROM prefix_elluminate_recordings AS er
 +
JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid
 +
JOIN prefix_course as c ON c.id = e.course
 +
JOIN prefix_user AS u ON u.id = e.creator
 +
ORDER BY er.recordingsize DESC
 +
</code>
 +
 
 +
 
 +
=== Choice ===
 +
 
 +
Contributed by: [https://moodle.org/user/profile.php?id=88992 Randy Thornton]
 +
 
 +
Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.
 +
 
 +
<code sql>
 +
SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
 +
FROM prefix_choice AS h
 +
JOIN prefix_course AS c ON h.course = c.id
 +
JOIN prefix_choice_answers AS a ON h.id = a.choiceid
 +
JOIN prefix_user AS u ON a.userid = u.id
 +
JOIN prefix_choice_options AS o ON a.optionid = o.id
 +
</code>
 +
 
 +
=== Assignment type usage in courses ===
 +
<code sql>
 +
SELECT
 +
 
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments"
 +
 
 +
,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_assign_plugin_config AS apc
 +
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 +
WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 +
#GROUP BY apc.plugin
 +
) AS "File Assignments"
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_assign_plugin_config AS apc
 +
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 +
WHERE iassign.course = c.id AND apc.plugin = 'onlinetext' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 +
) AS "Online Assignments"
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_assign_plugin_config AS apc
 +
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 +
WHERE iassign.course = c.id AND apc.plugin = 'pdf' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 +
) AS "PDF Assignments"
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_assign_plugin_config AS apc
 +
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 +
WHERE iassign.course = c.id AND apc.plugin = 'offline' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 +
) AS "Offline Assignments"
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_assign_plugin_config AS apc
 +
JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
 +
WHERE iassign.course = c.id AND apc.plugin = 'comments' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
 +
) AS "Assignments Comments"
 +
 
 +
FROM prefix_assign AS assign
 +
JOIN prefix_course AS c ON c.id = assign.course
 +
GROUP BY c.id
 +
</code>
 +
 
 +
==Assignment Module Reports==
 +
===All Ungraded Assignments===
 +
 
 +
'''NOTE: This query is for the deprecated old Assignment module from Moodle 2.2, not the new Assignments module. Please update this query if you are the author or it will be removed as the 2.2 Assignment module is no longer supported since release 2.7.
 +
''' See: [https://docs.moodle.org/dev/Moodle_2.7_release_notes#Assignment]
 +
 
 +
Returns all the submitted assignments that still need grading
 +
<code sql>
 +
select
 +
u.firstname AS "First",
 +
u.lastname AS "Last",
 +
c.fullname AS "Course",
 +
a.name AS "Assignment"
 +
 
 +
from prefix_assignment_submissions as asb
 +
join prefix_assignment as a ON a.id = asb.assignment
 +
join prefix_user as u ON u.id = asb.userid
 +
join prefix_course as c ON c.id = a.course
 +
join prefix_course_modules as cm ON c.id = cm.course
 +
 
 +
where asb.grade < 0 and cm.instance = a.id
 +
and cm.module = 1
 +
 
 +
order by c.fullname, a.name, u.lastname
 +
</code>
 +
 
 +
===All Ungraded Assignments w/ Link===
 +
 
 +
'''NOTE: This query is for the deprecated old Assignment module from Moodle 2.2, not the new Assignments module. Please update this query if you are the author or it will be removed as the 2.2 Assignment module is no longer supported since release 2.7.
 +
''' See: [https://docs.moodle.org/dev/Moodle_2.7_release_notes#Assignment]
 +
 
 +
 
 +
Returns all the submitted assignments that still need grading, along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle.
 +
<code sql>
 +
select
 +
u.firstname AS "First",
 +
u.lastname AS "Last",
 +
c.fullname AS "Course",
 +
a.name AS "Assignment",
 +
 
 +
'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
 +
+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
 +
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
 +
AS "Assignmentlink"
 +
 
 +
 
 +
from prefix_assignment_submissions as asb
 +
join prefix_assignment as a ON a.id = asb.assignment
 +
join prefix_user as u ON u.id = asb.userid
 +
join prefix_course as c ON c.id = a.course
 +
join prefix_course_modules as cm ON c.id = cm.course
 +
 
 +
where asb.grade < 0 and cm.instance = a.id and cm.module = 1
 +
 
 +
order by c.fullname, a.name, u.lastname
 +
</code>
 +
 
 +
===Assignments (and Quizzes) waiting to be graded===
 +
 
 +
'''NOTE: This query is for the deprecated old Assignment module from Moodle 2.2, not the new Assignments module. Please update this query if you are the author or it will be removed as the 2.2 Assignment module is no longer supported since release 2.7.
 +
''' See: [https://docs.moodle.org/dev/Moodle_2.7_release_notes#Assignment]
 +
 
 +
 
 +
This report requires a YEAR filter to be added (Available when using the latest block/configurable_reports)
 +
 
 +
Which you can always remove, to make this query work on earlier versions.
 +
 
 +
The report includes:
 +
*number of quizzes
 +
*unFinished Quiz attempts
 +
*Finished Quiz attempts
 +
*number of students
 +
*number of Assignments
 +
*number of submitted answers by students
 +
*number of unchecked assignments (waiting for the Teacher) in a Course.
 +
 
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
 
 +
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 +
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
 +
 
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_course_modules cm
 +
JOIN prefix_modules as m ON m.id = cm.module
 +
WHERE m.name LIKE 'quiz' AND cm.course = c.id
 +
GROUP BY cm.course
 +
) AS 'nQuizzes'
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_quiz_attempts AS qa
 +
JOIN prefix_quiz AS q ON q.id = qa.quiz
 +
WHERE q.course = c.id
 +
AND qa.timefinish = 0
 +
GROUP BY q.course) AS 'unFinished Quiz attempts'
 +
 
 +
,(SELECT COUNT(*)
 +
FROM prefix_quiz_attempts AS qa
 +
JOIN prefix_quiz AS q ON q.id = qa.quiz
 +
WHERE q.course = c.id
 +
AND qa.timefinish > 0
 +
GROUP BY q.course) AS 'finished quiz attempts'
 +
 
 +
,(SELECT Count( ra.userid ) AS Users
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5
 +
AND ctx.instanceid = c.id
 +
) AS nStudents
 +
 +
 
 +
,(
 +
SELECT count(a.id)
 +
FROM prefix_assignment AS a
 +
JOIN prefix_course_modules AS cm ON a.course = cm.course
 +
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
 +
) nAssignments
 +
 
 +
,(
 +
SELECT count(*)
 +
FROM prefix_assignment AS a
 +
WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
 +
GROUP BY a.course
 +
) 'Open <br/>Assignments'
 +
 
 +
, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)'
 +
 +
,(
 +
SELECT count(asb.id)
 +
FROM prefix_assignment_submissions AS asb
 +
JOIN prefix_assignment AS a ON a.id = asb.assignment
 +
JOIN prefix_course_modules AS cm ON a.course = cm.course
 +
WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id
 +
) 'unChecked  <br/>Submissions'
 +
 +
,(
 +
SELECT count(asb.id)
 +
FROM prefix_assignment_submissions AS asb
 +
JOIN prefix_assignment AS a ON a.id = asb.assignment
 +
JOIN prefix_course_modules AS cm ON a.course = cm.course
 +
WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
 +
) 'Submitted  <br/>Assignments'
 +
 +
FROM prefix_course AS c
 +
LEFT JOIN (
 +
SELECT course, count(*) AS iAssignments
 +
FROM prefix_assignment AS a
 +
GROUP BY a.course
 +
) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
 +
 
 +
LEFT JOIN (
 +
SELECT course, count(*) AS iOpenAssignments
 +
FROM prefix_assignment AS a
 +
WHERE FROM_UNIXTIME(a.timedue) > NOW()
 +
GROUP BY a.course
 +
) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
 +
 
 +
WHERE 1=1 
 +
#AND c.fullname LIKE '%תשעג%'
 +
%%FILTER_YEARS:c.fullname%%
 +
## You can enable the SEMESTER filter as well,
 +
## by uncommenting the following line:
 +
## %%FILTER_SEMESTERS:c.fullname%%
 +
ORDER BY 'Open <br/>Assignments' DESC
 +
</code>
 +
 
 +
===Rubrics without zero values in criteria===
 +
Contributed by Eric Strom
 +
 
 +
Rubric calculations in Moodle can fail to align with instructors expectations if they lack a zero value for each criterion used in the assessment. From documentation at https://docs.moodle.org/32/en/Rubrics#Grade_calculation:
 +
 
 +
"For example, when the teacher in the previous example chose both levels with 1 point, the plain sum would be 2 points. But that is actually the lowest possible score so it maps to the grade 0 in Moodle.
 +
TIP: To avoid confusion from this sort of thing, we recommend including a level with 0 points in every rubric criterion."
 +
 
 +
This report identifies rubrics having criteria without a zero value level and the courses they live in. This also refines to only assignments with active rubrics that are visible to students in the course. Links to the each rubric id is the direct link to edit the rubric. Fix by adding a zero level for each criteria that is missing it. In general, the grading changes that result will be in the students' favor.
 +
 
 +
Includes search filter of course idnumber.
 +
 
 +
<code sql>
 +
SELECT cat.name AS Department, concat('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',
 +
c.id,'">',c.idnumber,'</a>') AS Course_ID,
 +
c.fullname AS Course_Name,
 +
concat('<a target="_new" href="%%WWWROOT%%/grade/grading/form/rubric/edit.php',CHAR(63),'areaid=',gd.areaid,'">',gd.areaid,'</a>') AS Rubric
 +
FROM prefix_course AS c
 +
JOIN prefix_course_categories AS cat
 +
ON cat.id = c.category
 +
JOIN prefix_course_modules AS cm
 +
ON c.id=cm.course
 +
JOIN prefix_context AS ctx
 +
ON cm.id = ctx.instanceid
 +
JOIN prefix_grading_areas AS garea
 +
ON ctx.id = garea.contextid
 +
JOIN prefix_grading_definitions AS gd
 +
ON garea.id = gd.areaid
 +
JOIN prefix_gradingform_rubric_criteria AS crit
 +
ON gd.id = crit.definitionid
 +
JOIN prefix_gradingform_rubric_levels AS levels
 +
ON levels.criterionid = crit.id
 +
WHERE cm.visible='1' AND garea.activemethod = 'rubric' AND (crit.id NOT IN
 +
(SELECT crit.id
 +
FROM prefix_gradingform_rubric_criteria AS crit
 +
JOIN prefix_gradingform_rubric_levels AS levels
 +
ON levels.criterionid = crit.id WHERE levels.score = '0'))
 +
 
 +
GROUP BY Rubric
 +
ORDER BY Course_ID, Rubric
 +
 
 +
%%FILTER_SEARCHTEXT:c.idnumber:~%%
 +
</code>
 +
 
 +
===Who is using "Single File Upload" assignment===
 +
<code sql>
 +
SELECT
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
 +
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 +
 
 +
,ass.name as "Assignment Name"
 +
 
 +
FROM
 +
prefix_assignment as ass
 +
 
 +
JOIN
 +
prefix_course as c ON c.id = ass.course
 +
 
 +
WHERE `assignmenttype` LIKE 'uploadsingle'
 +
</code>
 +
 
 +
==Feedback Module Reports==
 +
===List the answers to all the Feedback activities within the current course, submitted by the current user===
 +
<code sql>
 +
SELECT /* crs.fullname as "Course name", f.name AS "Journal name", CONCAT(u.firstname,' ',UPPER(u.lastname)) as "Participant", */ /* include these fields if you want to check the composition of the recordset */
 +
DATE_FORMAT(FROM_UNIXTIME(c.timemodified),'%W %e %M, %Y') as "Answer Date",
 +
CASE i.typ WHEN 'label' THEN i.presentation ELSE i.name END as "Topic",  /* usually labels are used as section titles, so you'd want them present in the recordset */
 +
v.value as "My Answer"
 +
 
 +
FROM prefix_feedback AS f
 +
INNER JOIN prefix_course as crs on crs.id=f.course %%FILTER_COURSES:f.course%%
 +
INNER JOIN prefix_feedback_item AS i ON f.id=i.feedback
 +
INNER JOIN prefix_feedback_completed AS c on f.id=c.feedback %%FILTER_COURSEUSER:c.userid%%
 +
LEFT JOIN prefix_feedback_value AS v on v.completed=c.id AND v.item=i.id
 +
INNER JOIN prefix_user AS u on c.userid=u.id
 +
 
 +
WHERE c.id = %%COURSEID%% AND u.id = %%USERID%%  AND c.anonymous_response = 1  /* This clause limits the recordset to the current course and the current user and includes/ excludes the anonymous responses as needed */
 +
 
 +
ORDER BY f.id, c.timemodified, i.id
 +
</code>
 +
 
 +
 
 +
==Resource Module Reports==
 +
===List "Recently uploaded files"===
 +
see what users are uploading
 +
<code sql>
 +
SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info 
 +
FROM `prefix_log`
 +
WHERE `action` LIKE 'upload'
 +
ORDER BY `prefix_log`.`time`  DESC
 +
</code>
 +
 
 +
===List Courses that loaded a specific file: "X"===
 +
Did the Teacher (probably) uploaded course's Syllabus ?
 +
<code sql>
 +
SELECT c.id, c.fullname  FROM `prefix_log` as l
 +
JOIN prefix_course as c ON c.id = l.course
 +
WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
 +
</code>
 +
 
 +
===All resources that link to some specific external website===
 +
+ link to course
 +
+ who's the teacher
 +
+ link to external resource
 +
<code sql>
 +
SELECT
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
,c.shortname,r.name
 +
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
JOIN prefix_user AS u ON u.id = ra.userid
 +
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
 +
FROM prefix_resource AS r
 +
JOIN prefix_course AS c ON r.course = c.id
 +
WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'
 +
</code>
 +
 
 +
==="Compose Web Page" RESOURCE count===
 +
<code sql>
 +
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
 +
</code>
 +
 
 +
===Resource count in courses===
 +
+ (First)Teacher name
 +
+ Where course is inside some specific Categories
 +
<code sql>
 +
SELECT
 +
COUNT(*) AS count
 +
,r.course
 +
,c.shortname shortname
 +
,c.fullname coursename
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user as u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 +
FROM prefix_resource r
 +
JOIN prefix_course c ON r.course = c.id
 +
WHERE c.category IN (10,13,28,18,26)
 +
GROUP BY r.course
 +
ORDER BY COUNT(*) DESC
 +
</code>
 +
 
 +
===Delete all the automated backup files===
 +
Prepare bash cli script to delete all the automated backup files on the file system. (clean up some disk space)
 +
<code sql>
 +
SELECT CONCAT( 'rm -f /var/moodledatanew/filedir/', SUBSTRING( contenthash, 1, 2 ) , '/', SUBSTRING( contenthash, 3, 2 ) , '/', contenthash )
 +
FROM `mdl_files`
 +
WHERE `filename` LIKE '%mbz%'
 +
AND filearea = 'automated'
 +
</code>
 +
 
 +
Find out how much disk space is used by all automated backup files:
 +
<code sql>
 +
SELECT SUM(filesize)/(1024*1024*1024) FROM `mdl_files` WHERE  `filename` LIKE '%mbz%' AND filearea =  'automated'
 +
</code>
 +
 
 +
==Forum Module Reports==
 +
===print all User's post in course Forums===
 +
%%COURSEID%% is a variable the is replace by the current CourseID you are running the sql report from. if you are using the latest block/configurable_reports ! (You can always change it to a fixed course or remove it to display all courses.)
 +
<code sql>
 +
SELECT
 +
concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') As Fullname
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
 +
,count(*) as Posts
 +
,(SELECT count(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum as iforum ON iforum.id = ifd.forum  WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion
 +
 
 +
FROM prefix_forum_posts AS fp
 +
JOIN prefix_user as u ON u.id = fp.userid
 +
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
 +
JOIN prefix_forum AS f ON f.id = fd.forum
 +
JOIN prefix_course as c ON c.id = fd.course
 +
WHERE fd.course = %%COURSEID%%
 +
GROUP BY f.id,u.id
 +
ORDER BY u.id
 +
</code>
 +
 
 +
===FORUM use Count per COURSE -- not including NEWS Forum!===
 +
<code sql>
 +
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
 +
</code>
 +
 
 +
===FORUM use Count per COURSE by type -- not including NEWS Forum!===
 +
<code sql>
 +
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
 +
</code>
 +
 
 +
===Forum activity - system wide===
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 +
,c.fullname as Course
 +
,f.type
 +
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
, fd.forum, f.name,count(*) AS cPostAndDisc
 +
,(SELECT count(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion
 +
FROM prefix_forum_posts AS fp
 +
JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion
 +
JOIN prefix_forum AS f ON f.id = fd.forum
 +
JOIN prefix_course AS c ON c.id = f.course
 +
WHERE f.type != 'news' AND c.fullname LIKE '%2013%'
 +
## WHERE 1=1
 +
## %%FILTER_YEARS:c.fullname%%
 +
## You can enable the SEMESTER filter as well,
 +
## by uncommenting the following line:
 +
## %%FILTER_SEMESTERS:c.fullname%%
 +
 
 +
GROUP BY fd.forum
 +
ORDER BY count( * ) DESC
 +
</code>
 +
 
 +
===Activity In Forums===
 +
Trying to figure out how much real activity we have in Forums by aggregating:
 +
Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers , Number of Students, ratio between unique Student posts and the number of students in the Course...
 +
<code sql>
 +
SELECT c.fullname,f.name,f.type
 +
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
 +
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
 +
,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
 +
,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
 +
,(SELECT Count( ra.userid ) AS Students
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid =5
 +
AND ctx.instanceid = c.id
 +
) AS StudentsCount
 +
,(SELECT Count( ra.userid ) AS Teachers
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid =3
 +
AND ctx.instanceid = c.id
 +
) AS 'Teacher<br/>Count'
 +
,(SELECT Count( ra.userid ) AS Users
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid IN (3,5)
 +
AND ctx.instanceid = c.id
 +
) AS UserCount
 +
, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
 +
, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
 +
FROM prefix_forum as f
 +
JOIN prefix_course as c ON f.course = c.id
 +
WHERE `type` != 'news'
 +
ORDER BY StudentPostUsage DESC
 +
</code>
 +
 
 +
===All Forum type:NEWS===
 +
<code sql>
 +
SELECT f.id, f.name
 +
FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_forum AS f ON cm.instance = f.id
 +
WHERE m.name = 'forum'
 +
AND f.type = 'news'
 +
</code>
 +
 
 +
===All new forum NEWS items (discussions) from all my Courses===
 +
change "userid = 26" and "id = 26" to a new user id
 +
<code sql>
 +
SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
 +
FROM prefix_forum_discussions as fd
 +
JOIN prefix_forum as f ON f.id = fd.forum
 +
JOIN prefix_course as c ON c.id = f.course
 +
JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
 +
WHERE fd.timemodified > ul.timeaccess 
 +
AND fd.forum IN (SELECT f.id
 +
FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_forum AS f ON cm.instance = f.id
 +
WHERE m.name = 'forum'
 +
AND f.type = 'news')
 +
  AND c.id IN (SELECT c.id
 +
  FROM prefix_course AS c
 +
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
 +
  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 +
  JOIN prefix_user AS u ON u.id = ra.userid
 +
  WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
 +
</code>
 +
 
 +
 
 +
===News Forum - Discussions COUNT===
 +
Which is actually... How much instructions students get from their teachers
 +
<code sql>
 +
SELECT c.shortname ,
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
 +
FROM prefix_forum_discussions AS fd
 +
INNER JOIN prefix_forum AS f ON f.id = fd.forum
 +
INNER JOIN prefix_course AS c ON c.id = f.course
 +
WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
 +
GROUP BY fd.forum
 +
ORDER BY count(fd.id) DESC
 +
</code>
 +
 
 +
===Cantidad de foros que han sido posteados por profesor===
 +
 
 +
(Number of forums that have been posted by teacher/Google translator)
 +
 
 +
Queriamos saber cuales son las acciones del profesor dentro de los foros de cada curso, por ello se hizo este informe.
 +
 
 +
(We wanted to know what the teacher's actions are in the forums of each course, so this report was made. /Google translator)
 +
<code sql>
 +
SELECT
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS curso,
 +
CONCAT(u.firstname ,' ',u.lastname) AS Facilitador,
 +
 
 +
(SELECT COUNT( m.name ) AS COUNT FROM
 +
prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS foros,
 +
 
 +
COUNT(*) AS Posts
 +
 
 +
FROM prefix_forum_posts AS fp
 +
JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
 +
JOIN prefix_forum AS f ON f.id = fd.forum
 +
JOIN prefix_course AS c ON c.id = fd.course
 +
JOIN prefix_user AS u ON u.id = fp.userid
 +
 
 +
WHERE fp.userid =
 +
(
 +
select distinct prefix_user.id
 +
from prefix_user
 +
join prefix_role_assignments as ra on ra.userid = prefix_user.id
 +
where ra.roleid = 3
 +
and userid = fp.userid
 +
limit 1
 +
)
 +
 
 +
and c.shortname like '%2014-2-1%'
 +
GROUP BY c.id, u.id
 +
</code>
 +
 
 +
 
 +
===List all the Posts in all the Forums that got high rating===
 +
We setup a scale that let teachers and students Rate forum post with "Important, interesting, valuable, not rated" scale
 +
And then add a link to the following report at the begining of the course "Link to all interesting posts"
 +
<code sql>
 +
SELECT
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',f.id,'">',f.name,'</a>') AS 'Forum name,
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fd.id,'#p',fp.id,'">',fp.subject,'</a>') AS 'Post link',
 +
SUM(r.rating) AS 'Rating'
 +
FROM mdl_rating AS r
 +
  JOIN mdl_forum_posts AS fp ON fp.id = r.itemid
 +
  JOIN mdl_forum_discussions AS fd ON fd.id = fp.discussion
 +
  JOIN mdl_forum AS f ON f.id = fd.forum
 +
WHERE r.component = 'mod_forum' AND r.ratingarea = 'post' AND f.course = %%COURSEID%%
 +
GROUP BY r.itemid
 +
ORDER BY SUM(r.rating) DESC
 +
</code>
 +
 
 +
 
 +
==Quiz Module Reports==
 +
===Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes===
 +
<code sql>
 +
SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
 +
                co.fullname AS course_fullname, co.shortname AS course_shortname,
 +
                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
 +
                u.firstname, u.lastname, u.email,
 +
FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
 +
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
 +
                qu.course = co.id AND
 +
                co.id = ct.instanceid AND
 +
                ra.roleid = re.id AND
 +
                re.name = 'Teacher' AND
 +
                ra.contextid = ct.id AND
 +
                ra.userid = u.id
 +
 +
SELECT Count('x') As NumOfStudents
 +
                                FROM prefix_role_assignments a
 +
                                JOIN prefix_user u ON userid = u.id
 +
                                WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)
 +
</code>
 +
 
 +
===Number of Quizes per Course===
 +
<code sql>
 +
SELECT count(*)
 +
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes
 +
 
 +
FROM prefix_course_modules cm
 +
JOIN prefix_course c ON c.id = cm.course
 +
JOIN prefix_modules as m ON m.id = cm.module
 +
WHERE m.name LIKE 'quiz'
 +
GROUP BY c.id
 +
</code>
 +
 
 +
===List all MultiAnswer (Cloze) Questions===
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
 +
,question.id question_id, question.questiontext
 +