Difference between revisions of "ad-hoc contributed reports"

Jump to: navigation, search

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

(Attempt to categorise reports so it's easier to find them! No changes just headings added :))
(Grade and Course Completion Reports)
 
(172 intermediate revisions by 19 users not shown)
Line 1: Line 1:
 +
{{Sitewide reports}}
 
==User and Role Report==
 
==User and Role Report==
===Detailed ACTIONs for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
+
 
 +
===Count number of distinct learners and teachers enrolled per category (including all its sub categories)===
 +
<code sql>SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) as teachers
 +
FROM prefix_course AS c #, mdl_course_categories AS cats
 +
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
 +
JOIN prefix_role_assignments  AS lra ON lra.contextid = ctx.id
 +
JOIN prefix_role_assignments  AS tra ON tra.contextid = ctx.id
 +
JOIN prefix_course_categories AS cats ON c.category = cats.id
 +
WHERE c.category = cats.id
 +
AND (
 +
cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID with the category id you want to count (eg: 80)
 +
OR cats.path LIKE '%/CATEGORYID'
 +
)
 +
AND lra.roleid=5
 +
AND tra.roleid=3</code>
 +
 
 +
===Detailed ACTIONs for each ROLE (TEACHER, NON-EDITING TEACHER and STUDENT)===
 
<code sql>
 
<code sql>
SELECT r.name,l.action, count( l.userid ) as counter
+
SELECT r.name, l.action, COUNT( l.userid ) AS counter
FROM `prefix_log` as l
+
FROM prefix_log AS l
JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid
+
JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
 +
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid AND ra.contextid = context.id
 
JOIN prefix_role AS r ON ra.roleid = r.id
 
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid IN (3,4,5)
+
WHERE ra.roleid IN ( 3, 4, 5 )  
GROUP BY roleid,l.action
+
GROUP BY roleid, l.action
ORDER BY counter desc
+
</code>
 +
 
 +
===Student (user) COUNT in each Course===
 +
Including (optional) filter by: year (if included in course fullname).
 +
<code sql>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
 +
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
 +
, COUNT(course.id) AS Students
 +
FROM prefix_role_assignments AS asg
 +
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
 +
JOIN prefix_user AS user ON user.id = asg.userid
 +
JOIN prefix_course AS course ON context.instanceid = course.id
 +
WHERE asg.roleid = 5
 +
# AND course.fullname LIKE '%2013%'
 +
GROUP BY course.id
 +
ORDER BY COUNT(course.id) DESC
 
</code>
 
</code>
  
===LIST of all site USERS by COURSE enrollment (Moodle 1.9.x)===
+
=== Enrolment count in each Course ===
 +
 
 +
Shows the total number of enroled users of all roles in each course. Sorted by course name.
  
Reports a site global list of all users enroled in each course
 
 
<code sql>
 
<code sql>
SELECT
+
SELECT c.fullname, COUNT(ue.id) AS Enroled
user.firstname AS Firstname,
+
FROM prefix_course AS c
user.lastname AS Lastname,
+
JOIN prefix_enrol AS en ON en.courseid = c.id
user.email AS Email,
+
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
user.city AS City,
+
GROUP BY c.id
course.fullname AS Course
+
ORDER BY c.fullname
,(SELECT name FROM prefix_role WHERE id=asg.roleid)
 
FROM
 
prefix_user AS user,
 
prefix_course AS course,
 
prefix_role_assignments AS asg
 
INNER JOIN prefix_context AS context ON asg.contextid=context.id  
 
WHERE
 
context.contextlevel = 50
 
AND
 
user.id=asg.userid
 
AND
 
context.instanceid=course.id
 
 
</code>
 
</code>
  
Line 51: Line 73:
 
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
 
JOIN prefix_user AS user2 ON ue.userid = user2.id
 
JOIN prefix_user AS user2 ON ue.userid = user2.id
 +
</code>
 +
 +
===Enrolled users,which did not login into the Course, even once (Moodle 2)===
 +
Designed forMoodle 2 table structure and uses special plugin filter : %%FILTER_SEARCHTEXT:table.field%%
 +
 +
<code sql>
 +
SELECT
 +
user2.id as ID,
 +
ul.timeaccess,
 +
user2.firstname AS Firstname,
 +
user2.lastname AS Lastname,
 +
user2.email AS Email,
 +
user2.city AS City,
 +
user2.idnumber AS IDNumber,
 +
user2.phone1 AS Phone,
 +
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=16 AND ul.timeaccess IS NULL
 +
%%FILTER_SEARCHTEXT:user2.firstname%%
 +
</code>
 +
 +
===Role assignments on categories===
 +
<code sql>
 +
SELECT
 +
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.id,'</a>') AS id,
 +
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.name,'</a>') AS category,
 +
cc.depth, cc.path, r.name AS role,
 +
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',usr.id,'">',usr.lastname,'</a>') AS name,
 +
usr.firstname, usr.username, usr.email
 +
FROM prefix_course_categories cc
 +
INNER JOIN prefix_context cx ON cc.id = cx.instanceid
 +
AND cx.contextlevel = '40'
 +
INNER JOIN prefix_role_assignments ra ON cx.id = ra.contextid
 +
INNER JOIN prefix_role r ON ra.roleid = r.id
 +
INNER JOIN prefix_user usr ON ra.userid = usr.id
 +
ORDER BY cc.depth, cc.path, usr.lastname, usr.firstname, r.name, cc.name
 
</code>
 
</code>
  
Line 67: Line 141:
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712837 Séverin Terrier] )
 
(By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712837 Séverin Terrier] )
 
<code sql>
 
<code sql>
SELECT id , category , fullname , shortname , enrollable
+
SELECT
FROM `prefix_course`
+
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id,
WHERE `guest` =1
+
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course',
AND `password` = ""
+
concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins',
AND `visible` =1
+
e.sortorder
 +
FROM prefix_enrol AS e, prefix_course AS c
 +
WHERE e.enrol='guest' AND e.status=0 AND e.password='' AND c.id=e.courseid AND c.visible=1
 
</code>
 
</code>
  
Line 85: Line 161:
 
SELECT COUNT(id) as Users  FROM `prefix_user`  
 
SELECT COUNT(id) as Users  FROM `prefix_user`  
 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
 +
</code>
 +
 +
===Lists the users who have only logged into the site once===
 +
<code sql>
 +
SELECT id, username, firstname, lastname, idnumber
 +
FROM prefix_user
 +
WHERE prefix_user.deleted = 0
 +
AND prefix_user.lastlogin = 0
 +
AND prefix_user.lastaccess > 0
 
</code>
 
</code>
  
Line 101: Line 186:
  
 
===Full User info (for deleted users)===
 
===Full User info (for deleted users)===
Including extra custom profile fields (from mdl_user_info_data)
+
Including extra custom profile fields (from prefix_user_info_data)
 
<code sql>
 
<code sql>
 
SELECT *  
 
SELECT *  
Line 119: Line 204:
 
JOIN prefix_user AS u ON u.id = ra.userid
 
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE u.id = 2
 
WHERE u.id = 2
 +
</code>
 +
 +
===List Users with extra info (email) in current course===
 +
blocks/configurable_reports replaces %%COURSEID%% with course id.
 +
<code sql>
 +
SELECT u.firstname, u.lastname, u.email
 +
FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
 +
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
 +
JOIN prefix_user AS u ON u.id = ra.userid
 
</code>
 
</code>
  
Line 145: Line 240:
 
</code>
 
</code>
  
==Log Acivity Reports==
+
===List of users who have been enrolled for more than 4 weeks===
 +
For Moodle 2.2 , by  Isuru Madushanka Weerarathna
 +
<code sql>
 +
SELECT uenr.userid As User, IF(enr.courseid=uenr.courseid ,'Y','N') As Enrolled,
 +
IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') As EnrolledMoreThan4Weeks
 +
FROM prefix_enrol As enr, prefix_user_enrolments AS uenr
 +
WHERE enr.id = uenr.enrolid AND enr.status = uenr.status
 +
</code>
 +
 
 +
=== List of users with language===
 +
 
 +
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.
 +
 
 +
This will show you the language setting for all users:
 +
<code sql>
 +
SELECT username, lang from prefix_user
 +
</code>
 +
 
 +
This code will change the setting from 'en' to 'en_us' for all users:
 +
 
 +
<code sql>
 +
UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
 +
</code>
 +
 
 +
=== List of users with Authentication ===
 +
 
 +
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>
 +
 
 +
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 you 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>
 +
 
 +
=== Compare role capability and permissions ===
 +
<code sql>
 +
SELECT DISTINCT mrc.capability
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '1' AND rc.contextid = '1') AS Manager
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '2' AND rc.contextid = '1') AS CourseCreator
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '3' AND rc.contextid = '1') AS Teacher
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '4' AND rc.contextid = '1') AS AssistantTeacher
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '5' AND rc.contextid = '1') AS Student
 +
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability
 +
  AND rc.roleid = '6' AND rc.contextid = '1') AS Guest
 +
 
 +
FROM `mdl_role_capabilities` AS mrc
 +
</code>
 +
 
 +
==Log Activity Reports==
 +
===Count all Active Users by ROLE in a course category (including all of its sub-categories)===
 +
<code sql>
 +
SELECT COUNT(DISTINCT l.userid) as active
 +
FROM mdl_course as c
 +
JOIN mdl_context AS ctx ON  ctx.instanceid=c.id
 +
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
 +
JOIN mdl_user_lastaccess as l ON ra.userid = l.userid
 +
JOIN mdl_course_categories AS cats ON c.category = cats.id
 +
WHERE c.category=cats.id AND (
 +
cats.path LIKE '%/80/%'
 +
OR cats.path LIKE '%/80'
 +
)
 +
AND ra.roleid=3  AND ctx.contextlevel=50  #ra.roleid= TEACHER 3, NON-EDITING TEACHER 4, STUDENT 5
 +
AND  l.timeaccess > (unix_timestamp() - ((60*60*24)*NO_OF_DAYS)) #NO_OF_DAYS change to number
 +
</code>
 
===Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
 
===Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
 
<code sql>
 
<code sql>
Line 162: Line 330:
  
 
<code sql>
 
<code sql>
SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d' ) AS grptimed ,
+
SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y-%m-%d' ) AS grptimed ,
 
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh  , count( l.userid ) AS counter  
 
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh  , count( l.userid ) AS counter  
 
FROM `prefix_log` AS l
 
FROM `prefix_log` AS l
Line 182: Line 350:
 
WHERE `action` LIKE '%login%' group by userid
 
WHERE `action` LIKE '%login%' group by userid
 
ORDER BY Activity DESC
 
ORDER BY Activity DESC
 +
</code>
 +
 +
===Total activity per course, per unique user on the last 24h===
 +
<code sql>
 +
Select
 +
    Count(Distinct userid) As countUsers
 +
  , Count(course) As countVisits
 +
  , concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
 +
From prefix_log as l
 +
JOIN prefix_course as c on c.id = l.course
 +
Where course > 0 and FROM_UNIXTIME(time) >= DATE_SUB(NOW(), INTERVAL 1 DAY) and c.fullname LIKE '%תשעג%'
 +
Group By course
 +
ORDER BY countVisits DESC
 +
</code>
 +
 +
===Weekly Instructor Online Participation===
 +
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.
 +
 +
* To specify a subject and/or course number, use % as a wildcard, e.g. ARTS% or ARTS501%
 +
* To match part of a last name, use %, e.g. Smi% will match "Smith", "Smile", etc.
 +
 +
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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.
 +
 +
<code sql>
 +
SELECT
 +
c.shortname AS CourseID
 +
, cc.name AS Category
 +
, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
 +
 +
, (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
 +
 +
, c.startdate AS Course_Start_Date
 +
 +
, c.visible AS Visible
 +
 +
,  COUNT(l.id) AS Edits
 +
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time)) - WEEK(FROM_UNIXTIME(c.startdate))<0,1,0)) AS BeforeTerm
 +
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=0,1,0)) AS Week1
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=1,1,0)) AS Week2
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=2,1,0)) AS Week3
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=3,1,0)) AS Week4
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=4,1,0)) AS Week5
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=5,1,0)) AS Week6
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=6,1,0)) AS Week7
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=7,1,0)) AS Week8
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=8,1,0)) AS Week9
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=9,1,0)) AS Week10
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=10,1,0)) AS Week11
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=11,1,0)) AS Week12
 +
 +
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))>=12,1,0)) AS AfterTerm
 +
 +
, 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
 +
 +
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
 +
 +
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%"
 +
 +
WHERE ra.roleid =3
 +
AND ctx.instanceid = c.id
 +
AND c.shortname LIKE :course
 +
AND u.lastname LIKE :last_name
 +
 +
GROUP BY u.idnumber, c.id
 +
HAVING students > 0
 +
ORDER BY c.shortname
 +
</code>
 +
 +
===Weekly Student Online Participation===
 +
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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.
 +
 +
<code sql>
 +
SELECT
 +
u.lastname AS 'Last Name'
 +
, u.firstname AS 'First Name'
 +
,  COUNT(l.id) AS 'Edits'
 +
 +
, 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'
 +
 +
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))>=15,1,0)) AS 'After Term'
 +
 +
#, 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'
 +
 +
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
 +
# 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_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%"
 +
 +
WHERE ra.roleid =5
 +
AND ctx.instanceid = c.id
 +
 +
AND c.id = %%COURSEID%%
 +
 +
GROUP BY u.idnumber
 +
 +
ORDER BY u.lastname, u.firstname
 +
</code>
 +
 +
===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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.
 +
 +
'''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>
 +
SELECT
 +
 +
# Identify student
 +
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
 +
 +
, 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'
 +
 +
, 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'
 +
 +
## Only posts within last 7 days
 +
 +
# Count posts by student
 +
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
 +
 +
# 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'
 +
 +
# using link back to student posts on replies, get unique student IDs responded
 +
, 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'
 +
 +
# all replies
 +
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
 +
 +
# add in count of graded assignments - 7 days
 +
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
 +
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
 +
 +
# 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'
 +
 +
## All posts in course so far
 +
# Count posts by student
 +
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
 +
 +
# 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
 +
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
 +
 +
# 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
 +
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
 +
LEFT 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
 +
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
 +
 +
# 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
 +
 +
# We care about messages that involve both the instructor and students of this course
 +
# messages from instructor to students:
 +
# LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
 +
# LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
 +
 +
WHERE 
 +
c.id = %%COURSEID%%
 +
 +
# GROUP BY c.shortname , allstu.id
 +
GROUP BY allstu.id
 +
 +
ORDER BY allstu.lastname
 +
</code>
 +
 +
===Module activity (Hits) between dates===
 +
<code sql>
 +
SELECT module, COUNT( * )
 +
FROM prefix_log AS l
 +
WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN  '2012-10-01 00:00:00' AND  '2013-09-31 00:00:00')
 +
GROUP BY module
 +
</code>
 +
 +
===Module activity (Instances and Hits) for each academic year===
 +
<code sql>
 +
SELECT name
 +
 +
,(SELECT COUNT(*)
 +
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(*)
 +
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
 +
) AS "Used 2010"
 +
 +
,(SELECT COUNT(*)
 +
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 AND l.action = 'add'
 +
) AS "Added 2011"
 +
 +
,(SELECT COUNT(*)
 +
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"
 +
 +
 +
,(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 AND l.action = 'add'
 +
) AS "Added 2012"
 +
 +
,(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"
 +
 +
FROM mdl_modules AS m
 +
</code>
 +
 +
===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>
 +
SELECT COUNT(DISTINCT userid) AS "Unique User Logins"
 +
,DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y /%m / %d") AS "Year / Month / Day", "Graph"
 +
FROM `mdl_logstore_standard_log`
 +
WHERE action LIKE 'loggedin'
 +
#AND timecreated >  UNIX_TIMESTAMP('2015-01-01 00:00:00') # optional start date
 +
#AND timecreated <= UNIX_TIMESTAMP('2015-01-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>
 +
 +
OR...
 +
 +
Unique users and page views on the last 7 days:
 +
<code sql>
 +
SELECT
 +
  DAYOFMONTH(FROM_UNIXTIME(timecreated)) 'Day',
 +
  COUNT(DISTINCT ip) 'unique ip' ,
 +
  COUNT(DISTINCT userid) 'unique userid' ,
 +
  COUNT(*) 'page requests'
 +
FROM mdl_logstore_standard_log
 +
WHERE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(timecreated), NOW()) < 7 AND action = 'viewed'
 +
GROUP BY DAYOFMONTH(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),
 +
 +
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.
 +
<code sql>
 +
SELECT DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y-%m-%d") AS "Datez"
 +
,COUNT(DISTINCT userid) AS "Unique Users"
 +
,ROUND(COUNT(*)/10) "User Hits (K)"
 +
,SUM(IF(component='mod_quiz',1,0)) "Quizzes"
 +
,SUM(IF(component='mod_forum' or component='mod_forumng',1,0)) "Forums"
 +
,SUM(IF(component='mod_assign',1,0)) "Assignments"
 +
,SUM(IF(component='mod_oublog',1,0)) "Blogs"
 +
,SUM(IF(component='mod_resource',1,0)) "Files (Resource)"
 +
,SUM(IF(component='mod_url',1,0)) "Links (Resource)"
 +
,SUM(IF(component='mod_page',1,0)) "Pages (Resource)"
 +
 +
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>
 +
 +
===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>
 +
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',
 +
  course.id,'">',course.fullname,'</a>') AS Course
 +
 +
#,course.shortname
 +
 +
,CASE
 +
  WHEN course.fullname LIKE '%2012%' THEN '2012'
 +
  WHEN course.fullname LIKE '%2013%' THEN '2013'
 +
  WHEN course.fullname LIKE '%2014%' THEN '2014'
 +
  WHEN course.fullname LIKE '%2015%' THEN '2015'
 +
END AS Year
 +
 +
,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
 +
 +
,IF(course.startdate>0, DATE_FORMAT(FROM_UNIXTIME(startdate), '%d-%m-%Y'), 'no date') AS "Course Start Date"
 +
 +
,(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 = course.id
 +
) AS Students
 +
 +
,(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 = 4 AND ctx.instanceid = course.id
 +
) AS "Assistant teacher"
 +
 +
,(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
 +
 +
# 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
 +
 +
#,(SELECT COUNT(*)
 +
#FROM mdl_logstore_standard_log AS l
 +
#JOIN mdl_role_assignments AS ra ON ra.contextid= l.contextid AND ra.userid= l.userid AND ra.roleid = 5
 +
#WHERE l.courseid = course.id ) AS "Student HITs"
 +
 +
#,(SELECT COUNT(*)
 +
#FROM mdl_logstore_standard_log AS l
 +
#JOIN mdl_role_assignments AS ra ON ra.contextid= l.contextid AND ra.userid= l.userid AND ra.roleid = 3
 +
#WHERE l.courseid = course.id ) AS "Teacher HITs"
 +
 +
,(SELECT COUNT(*) FROM mdl_log AS l WHERE l.course = course.id) AS 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 = 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"
 +
 +
,(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
 +
 +
,(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 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 ( 'oublog') ) "Num Blogs"
 +
 +
,(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"
 +
 +
,(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 ('resource', 'folder', 'url', 'tab', 'file', 'book', 'page') ) Resources
 +
 +
,(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 ('forum', 'forumng', 'oublog', 'page', 'file', 'url', 'wiki' , 'ouwiki') ) "Basic Activities"
 +
 +
,(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 ('advmindmap', 'assign', 'attendance', 'book', 'choice', 'folder', 'tab', 'glossary', 'questionnaire', 'quiz', 'label' ) ) "Avarage Activities"
 +
 +
,(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:~%%
 +
 +
WHERE course.fullname LIKE '%2015%'
 +
 +
HAVING Modules > 2
 +
ORDER BY UniqueModules DESC
 +
</code>
 +
 +
===Learners (students) with most active learning portfolios===
 +
Trying to get a list of the most active learners and see the complete activity report (portfolio)
 +
<code sql>
 +
SELECT count(*) "Hits", l.userid, l.courseid,
 +
CONCAT('<a target="_new" href="%%WWWROOT%%/report/outline/user.php?id=', u.id ,'&course=', l.courseid, '&mode=complete">',u.firstname ,' ',u.lastname,'</a>') AS Username
 +
 +
FROM mdl_logstore_standard_log AS l
 +
JOIN mdl_role_assignments AS ra ON l.userid = ra.userid AND l.contextid = ra.contextid
 +
JOIN mdl_role AS r ON r.id = ra.roleid
 +
JOIN mdl_user AS u ON l.userid = u.id
 +
WHERE ra.roleid = 5
 +
GROUP BY l.userid, l.courseid
 +
ORDER BY COUNT(*) DESC
 
</code>
 
</code>
  
Line 187: Line 867:
 
===Most Active courses===
 
===Most Active courses===
 
<code sql>
 
<code sql>
SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
+
SELECT count(l.userid) AS Views
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
+
FROM `mdl_logstore_standard_log` l, `mdl_user` u, `mdl_role_assignments` r
GROUP BY courseId
+
WHERE l.courseid=35
ORDER BY hits 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>
 +
 
 +
===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>
 +
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
 +
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
 +
  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
 +
 
 +
,(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_log l  
 +
INNER JOIN prefix_course c ON l.course = c.id
 +
GROUP BY c.id
 +
HAVING Modules > 2
 +
ORDER BY Year DESC, hits DESC
 +
</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
 +
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
 +
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>
  
Line 199: Line 932:
 
GROUP BY course
 
GROUP BY course
 
ORDER BY count DESC
 
ORDER BY count DESC
 +
</code>
 +
 +
===Common resource types count for each Category (Moodle19)===
 +
Including sub-categories in total count.
 +
<code sql>
 +
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
 +
,(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 = 'file' AND r.reference LIKE 'http://%'
 +
) AS Links
 +
 +
,(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 = 'file' AND r.reference NOT LIKE 'http://%'
 +
) AS Files
 +
 +
,(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 = '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
 +
 +
FROM prefix_course_categories AS mcc
 +
ORDER BY mcc.path
 +
</code>
 +
Where "stats_log_context_role_course" (in the above SQL query) is a VIEW generated by:
 +
<code sql>
 +
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>
 +
 +
Same query but for Moodle2+
 +
<code sql>
 +
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
 +
mcc.path,
 +
 +
(SELECT COUNT(*)
 +
FROM prefix_url AS u
 +
JOIN prefix_course AS c ON c.id = u.course
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
 +
) AS URLs,
 +
 +
(SELECT COUNT(*)
 +
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(*)
 +
FROM prefix_page AS p
 +
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(*)
 +
FROM prefix_book AS b
 +
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(*)
 +
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,
 +
 +
(SELECT COUNT(*)
 +
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
 +
 +
FROM prefix_course_categories AS mcc
 +
ORDER BY mcc.path
 +
</code>
 +
 +
===Detailed Resource COUNT by Teacher in each course===
 +
 +
Including (optional) filter by: year, semester and course id.
 +
 +
<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
 +
 +
, (CASE
 +
WHEN c.fullname LIKE '%תשעב%' THEN '2012'
 +
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
 +
JOIN `prefix_course` AS c on c.id = r.course
 +
#WHERE type= 'file' and reference NOT LIKE 'http://%'
 +
 +
#WHERE 1=1
 +
#%%FILTER_YEARS:c.fullname%%
 +
#AND c.fullname LIKE '%2013%'
 +
 +
GROUP BY course
 +
ORDER BY COUNT(c.id) DESC
 
</code>
 
</code>
  
Line 204: Line 1,077:
 
<code sql>
 
<code sql>
 
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 +
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
 +
,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
 
  FROM `prefix_course` AS c
 
  FROM `prefix_course` AS c
 
WHERE groupmode > 0
 
WHERE groupmode > 0
 +
</code>
 +
 +
===Courses with Groups===
 +
 +
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.
 +
 +
<code sql>
 +
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
 +
 +
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 user2 ON ue.userid = user2.id
 +
JOIN prefix_groups AS g ON g.courseid = course.id
 +
 +
WHERE ue.enrolid NOT IN (select userid from prefix_groups_members WHERE g.id=groupid)
 +
 +
ORDER BY Course, Lastname
 +
</code>
 +
 +
===Groups in course with member list===
 +
 +
List the groups in a course (replace the # by the course id number) with the members of each group.
 +
 +
<code sql>
 +
SELECT c.shortname, g.name AS Groupname, u.username
 +
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>
 +
 +
===Group Export===
 +
 +
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.
 +
 +
<code sql>
 +
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>
  
Line 220: Line 1,155:
 
OR prefix_course_categories.path LIKE '/$s'
 
OR prefix_course_categories.path LIKE '/$s'
 
)
 
)
 +
</code>
 +
 +
===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.
 +
 +
$s should be the id of the top level category you are interested in.
 +
<code php>
 +
<?php
 +
 +
require_once('./config.php');
 +
 +
$parent_id = $s;
 +
 +
$categories= array();
 +
 +
$categories = get_categories($parent_id);
 +
 +
echo '<ol>';
 +
foreach ($categories as $category)
 +
        {
 +
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
 +
        }
 +
echo '</ol>';
 +
 +
?>
 
</code>
 
</code>
  
Line 239: Line 1,199:
 
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 
GROUP BY cm.course,cm.module order by counter desc
 
GROUP BY cm.course,cm.module order by counter desc
 +
</code>
 +
 +
===Student's posts content in all course blogs (oublog)===
 +
<code sql>
 +
SELECT
 +
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
 +
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
 +
 +
WHERE c.id = %%COURSEID%%
 
</code>
 
</code>
  
Line 258: Line 1,234:
 
WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )  
 
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)
 
AND c.category IN (10,18,26,13,28)
 +
</code>
 +
 +
===Site-wide completed SCORM activities by Course 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.
 +
<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 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
 +
 +
</code>
 +
 +
===List course resources accumulative file size and count===
 +
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"
 +
 +
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>
 
</code>
  
==Grade Reports==
+
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.
 +
And also setup (add) a FILTER_COURSES filter.
 +
<code sql>
 +
SELECT
 +
id ,CONCAT('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', contextid, '/', component, '/', filearea, '/', itemid, '/', filename, '">', filename,'</a>') AS "File"
 +
,filesize, mimetype ,author, license, timecreated, component, filearea, filepath
 +
 
 +
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>
 +
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"
 +
 
 +
FROM mdl_course AS c
 +
</code>
 +
 
 +
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>
 +
 
 +
==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.
 +
 
 +
<code sql>
 +
SELECT
 +
 +
cs.section AS 'Week'
 +
, cs.name AS 'Section Name'
 +
 
 +
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT LIKE 'label'),cm.id,NULL)) AS 'Ungraded Resources'
 +
 
 +
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
 +
 
 +
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'
 +
 
 +
FROM prefix_course AS c
 +
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
 +
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
 +
 
 +
WHERE
 +
cs.visible = 1
 +
AND cm.visible = 1
 +
AND c.id = %%COURSEID%%
 +
 
 +
GROUP BY cs.section
 +
ORDER BY cs.section
 +
 
 +
</code>
 +
 
 +
===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>
 +
SELECT
 +
 
 +
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('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
 +
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 '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'
 +
 
 +
,(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'
 +
 
 +
, 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'
 +
#, 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'
 +
 
 +
#, 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
 +
 
 +
#, IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO','') AS 'Instructor Details Block Updated' # HTML block has string 'dit this block'
 +
 
 +
#, 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
 +
 
 +
, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format' # change this if you want to test for a different format
 +
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'
 +
 
 +
, cfw.value AS 'weeks defined in course settings'
 +
 
 +
, 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)'
 +
 
 +
, 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
 +
 
 +
#,(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
 +
 
 +
#,(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
 +
 
 +
,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
 +
FROM prefix_resource
 +
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 TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
 +
FROM prefix_resource
 +
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo
 +
 
 +
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', f.id,NULL)),'YES','NO' ) AS 'Announcement Forum Visible'
 +
 
 +
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', fd.id,NULL)),'YES','NO' ) AS 'Announcement posted'
 +
 
 +
FROM prefix_course AS c
 +
LEFT JOIN prefix_course_categories as cc ON c.category = cc.id
 +
LEFT JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid
 +
 
 +
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
 +
 
 +
GROUP BY c.shortname
 +
</code>
 +
 
 +
==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(*)
 +
FROM prefix_log AS l
 +
WHERE l.course = cm.course AND l.module = m.name ) AS "Hits"
 +
 
 +
,(SELECT COUNT(*)
 +
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 = 5
 +
WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs"
 +
 
 +
,(SELECT COUNT(*)
 +
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
 +
JOIN mdl_modules AS m on m.id = cm.module
 +
WHERE cm.course = '%%COURSEID%%'
 +
GROUP BY cm.module
 +
</code>
 +
 
 +
==Grade and Course Completion Reports==
 
===Site-Wide Grade Report with All Items===
 
===Site-Wide Grade Report with All Items===
Shows grades for all course items along with course totals for each student.
+
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 u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',  
Line 288: Line 1,613:
 
ORDER BY lastname
 
ORDER BY lastname
 
</code>
 
</code>
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD:
+
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line
 
<code>
 
<code>
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time
+
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>
  
 
===Site-Wide Grade Report with Just Course Totals===
 
===Site-Wide Grade Report with Just Course Totals===
A second site-wide grade report for all students that just shows course totals.
+
A second site-wide grade report for all students that just shows course totals. 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 u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',  
Line 305: Line 1,634:
  
 
ROUND(gg.finalgrade,2) AS Grade,
 
ROUND(gg.finalgrade,2) AS Grade,
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
+
DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
  
 
FROM prefix_course AS c
 
FROM prefix_course AS c
Line 319: Line 1,648:
 
ORDER BY lastname
 
ORDER BY lastname
 
</code>
 
</code>
For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD:
+
 
<code>
+
For MySQL users:
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time
+
<code sql>
 +
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
 +
c.fullname AS 'Course',
 +
cc.name AS 'Category',
 +
CASE
 +
  WHEN gi.itemtype = 'course'
 +
  THEN CONCAT(c.fullname, ' - Total')
 +
  ELSE gi.itemname
 +
END AS 'Item Name',
 +
 
 +
ROUND(gg.finalgrade,2) AS Grade,
 +
FROM_UNIXTIME(gg.timemodified) AS TIME
 +
 +
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
 +
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
 +
ORDER BY lastname
 
</code>
 
</code>
  
Line 346: Line 1,697:
 
</code>
 
</code>
  
 +
===User Course Completion===
 +
 +
A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.
 +
 +
<code sql>
 +
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>
 +
 +
===User Course Completion with Criteria===
 +
 +
A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
 +
 +
<code sql>
 +
SELECT u.username AS user,
 +
c.shortname AS course,
 +
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
 +
CASE
 +
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"
 +
END AS aggregation,
 +
CASE
 +
WHEN p.criteriatype = 1 THEN "Self"
 +
WHEN p.criteriatype = 2 THEN "By Date"
 +
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>
 +
 +
===Courses with Completion Enabled and their settings===
 +
 +
List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.
 +
 +
<code sql>
 +
 +
SELECT c.shortname AS Course,
 +
CASE
 +
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"
 +
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===
 +
 +
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>
 +
 +
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
 +
 +
</code>
 +
 +
===Course Completion Report with Groups===
 +
 +
Contributed by Benjamin Yun, C2 Education
 +
 +
This report outputs a lists of all users enrolled in courses that have course completion enabled and includes:
 +
*User info
 +
*Course info (course name, category, start date)
 +
*Enrollment info (enroll date and group)
 +
*Completion date
 +
 +
Particularly useful to see the success rate of course, especially those with multiple teachers, each running their own groups.
 +
 +
Two filters are including to further drill down.
 +
 +
<code sql>
 +
 +
SELECT u.username AS 'User Name',
 +
CONCAT(u.lastname,', ',u.firstname) AS 'Full Name',
 +
u.email AS 'Email',
 +
cc.name AS 'Course Category',
 +
c.fullname AS 'Course Name',
 +
g.name AS 'Group',
 +
DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%m/%d/%Y') AS 'Course Start Date',
 +
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
 +
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date'
 +
 +
FROM prefix_course AS c
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
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
 +
JOIN prefix_course_completions AS p ON c.id = p.course AND u.id = p.userid
 +
 +
WHERE c.enablecompletion = 1
 +
%%FILTER_SUBCATEGORIES:cc.path%%
 +
%%FILTER_COURSES:Course%%
 +
ORDER BY u.lastname
 +
 +
</code>
 +
 +
===Scales used in activities===
 +
<code sql>
 +
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
 +
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>
 +
 +
===List all gradebook categories and grade items for course id XXX===
 +
<code sql>
 +
SELECT
 +
gc.courseid, gc.parent, gc.depth, gc.path, gc.fullname "Category",
 +
CASE gc.aggregation
 +
  WHEN 0 THEN 'Mean OF Grades'
 +
  WHEN 2 THEN 'Median OF Grades'
 +
  WHEN 6 THEN 'Highest Grade'
 +
  WHEN 8 THEN 'Mode OF Grades'
 +
  WHEN 10 THEN 'Weighted Mean OF Grades'
 +
  WHEN 11 THEN 'Simple Weighted Mean OF Grades'
 +
  WHEN 12 THEN 'Mean OF Grades (WITH extra credits)'
 +
  WHEN 13 THEN 'SUM OF Grades'
 +
END AS 'aggregation name'
 +
,gi.itemname, gi.itemtype, gi.itemmodule, gi.iteminstance, floor(gi.aggregationcoef2*100) "weight", floor(gi.grademin), floor(gi.grademax), gi.multfactor, gi.plusfactor
 +
 +
FROM mdl_grade_categories gc
 +
JOIN mdl_grade_items gi ON gi.categoryid = gc.id
 +
WHERE gc.courseid = XXX
 +
</code>
  
 
==Activity Module Reports==
 
==Activity Module Reports==
 +
 +
===How many SCORM activities are used in each Course===
 +
<code sql>
 +
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>
  
 
===Detailed ACTIONs for each MODULE===
 
===Detailed ACTIONs for each MODULE===
Line 427: Line 1,981:
 
</code>
 
</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)===
 
===Detailed WIKI activity (per wiki per course)===
Line 512: Line 2,126:
 
<code sql>
 
<code sql>
 
SELECT
 
SELECT
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y/%m/%d' ) AS Date,
+
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date,
 
prefix_certificate_issues.classname AS Topic,
 
prefix_certificate_issues.classname AS Topic,
 
prefix_certificate.name AS Certificate,
 
prefix_certificate.name AS Certificate,
Line 559: Line 2,173:
 
HAVING Blogs > 0
 
HAVING Blogs > 0
 
ORDER BY Blogs DESC
 
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 ===
 +
 +
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>
 
</code>
  
Line 607: Line 2,294:
  
 
order by c.fullname, a.name, u.lastname
 
order by c.fullname, a.name, u.lastname
 +
</code>
 +
 +
===Assignments (and Quizzes) waiting to be graded===
 +
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/29/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>
 
</code>
  
Line 696: Line 2,538:
 
GROUP BY r.course
 
GROUP BY r.course
 
ORDER BY COUNT(*) DESC
 
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>
 
</code>
  
 
==Forum Module Reports==
 
==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!===
 
===FORUM use Count per COURSE -- not including NEWS Forum!===
 
<code sql>
 
<code sql>
Line 717: Line 2,591:
 
GROUP BY prefix_forum.course,prefix_forum.type
 
GROUP BY prefix_forum.course,prefix_forum.type
 
ORDER BY total desc
 
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>
 
</code>
  
Line 805: Line 2,709:
 
GROUP BY fd.forum
 
GROUP BY fd.forum
 
ORDER BY count(fd.id) DESC
 
ORDER BY count(fd.id) DESC
 +
</code>
 +
 +
===Cantidad de foros que han sido posteados por profesor===
 +
Queriamos saber cuales son las acciones del profesor dentro de los foros de cada curso, por ello se hizo este informe.
 +
<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>
 
</code>
  
Line 814: Line 2,752:
 
                 qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
 
                 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,
 
                 u.firstname, u.lastname, u.email,
FROM mdl_quiz qu, mdl_course co, mdl_role re, mdl_context ct, mdl_role_assignments ra, mdl_user u
+
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
 
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
 
                 qu.course = co.id AND
 
                 qu.course = co.id AND
Line 824: Line 2,762:
 
   
 
   
 
SELECT Count('x') As NumOfStudents
 
SELECT Count('x') As NumOfStudents
                                 FROM mdl_role_assignments a
+
                                 FROM prefix_role_assignments a
                                 JOIN mdl_user u ON userid = u.id
+
                                 JOIN prefix_user u ON userid = u.id
                                 WHERE roleid = 5 AND contextid = (SELECT id FROM mdl_context WHERE instanceid = 668 AND contextlevel = 50)
+
                                 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
 +
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===
 +
Which is basically and indication to teachers using Moodle to hold offline grades inside Moodle's Gradebook,
 +
So grades could be uploaded into an administrative SIS. Use with Configurable Reports.
 +
<code sql>
 +
SELECT COUNT( * )
 +
,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course
 +
FROM  prefix_grade_items AS gi
 +
JOIN prefix_course as c ON c.id = gi.courseid
 +
WHERE  `itemtype` =  'manual'
 +
GROUP BY courseid
 +
</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%'
 +
<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>
 +
 
 +
 
 +
===List Questions in each Quiz===
 +
 
 +
<code sql>
 +
SELECT quiz.id,quiz.name, q.id, q.name
 +
FROM mdl_quiz AS quiz
 +
JOIN mdl_question AS q ON FIND_IN_SET(q.id, quiz.questions)
 +
WHERE quiz.course = %%COURSEID%%
 +
ORDER BY quiz.id ASC
 +
</code>
 +
 
 +
 
 +
===Quiz activity research===
 +
This report was made to extract student full activity in quizzes for an academic research about adapting instructional design teaching methods in online learning. The students do not use the Quiz module as a standard quiz but more as Study booklets or mini courses with embedded questions and hints to assist students evaluate their progress (Similar to what you expect to find in a SCORM activity)
 +
 
 +
<code sql>
 +
SELECT
 +
cm.course "course_id", cm.id "moduel_id", q.id "quiz_id", q.name "quiz_name",
 +
 +
CASE q.grademethod
 +
      WHEN 1 THEN "GRADEHIGHEST"
 +
      WHEN 2 THEN "GRADEAVERAGE"
 +
      WHEN 3 THEN "ATTEMPTFIRST"
 +
      WHEN 4 THEN "ATTEMPTLAST"
 +
END "grade method"
 +
 
 +
, q.attempts "quiz_attempts_allowed", cm.groupmode "group_mode"
 +
, qa.id "attempt_id", qa.state "attempt_state", qa.sumgrades "attempt_grade", qg.grade "user_final_grade", q.grade "quiz_max_grade"
 +
,(SELECT GROUP_CONCAT(g.name) FROM mdl_groups AS g
 +
JOIN mdl_groups_members AS m ON g.id = m.groupid WHERE g.courseid = q.course AND m.userid = u.id) "user_groups",
 +
DATE_FORMAT(FROM_UNIXTIME(qa.timestart), '%d-%m-%Y %h:%k') "attempt_start",
 +
DATE_FORMAT(FROM_UNIXTIME(qa.timefinish), '%d-%m-%Y %h:%k') "attempt_finish",
 +
u.id "user_id", u.firstname, u.lastname,
 +
question.id "question_id", question.name "question_name",
 +
qas.state "question_step_state",qas.fraction "question_grade", qh.hint, question.qtype "question_type"
 +
 
 +
FROM mdl_quiz as q
 +
JOIN mdl_course_modules as cm ON cm.instance = q.id and cm.module = 14
 +
JOIN mdl_quiz_attempts qa ON q.id = qa.quiz
 +
LEFT JOIN mdl_quiz_grades as qg ON qg.quiz = q.id and qg.userid = qa.userid
 +
JOIN mdl_user as u ON u.id = qa.userid
 +
JOIN mdl_question_usages as qu ON qu.id = qa.uniqueid
 +
JOIN mdl_question_attempts as qatt ON qatt.questionusageid = qu.id
 +
JOIN mdl_question as question ON question.id = qatt.questionid
 +
JOIN mdl_question_attempt_steps as qas ON qas.questionattemptid = qatt.id
 +
LEFT JOIN mdl_question_hints as qh ON qh.questionid = q.id
 +
#WHERE q.id = "SOME QUIZ ID"
 +
WHERE cm.course = "SOME COURSE ID"
 +
</code>
 +
 
 +
===Quiz Usage in Courses by Date===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 
 +
This report lists the courses containing quizzes with the course start date between the two values, and provides a summary of the types of questions in the quizzes in each course and whether question randomization and answer randomization functions were used.
 +
 
 +
"Multiple Choice" questions include true/false and matching question types.
 +
 
 +
"Short Answer" are questions that accept a single phrase.
 +
 
 +
"Other" questions include fixed numerical, calculated, essay, and various drag and drop types.
 +
 
 +
"Min Quiz Age" and "Max Quiz Age" provide data about the last modified date for the quizzes in the course, compared to the course start date. The values are expressed in units of days. A negative value indicates that a quiz was edited after the start of the course. A value greater than 90 days indicates that the quiz may have been used in an earlier term (cohort) without modification.
 +
 
 +
'''Note''': In Configurable Reports, the Date Filter is not applied until the "Apply" button is clicked.
 +
 
 +
<code sql>
 +
SELECT
 +
 
 +
c.shortname AS 'Course'
 +
#, u.lastname AS 'Instructor'
 +
, COUNT(DISTINCT q.id) AS 'Quizzes'
 +
, COUNT(DISTINCT qu.id) AS 'Questions'
 +
, SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 ))  AS 'multichoice'
 +
 
 +
, SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'
 +
 
 +
, COUNT( qu.id) - SUM(IF (qu.qtype = 'multichoice', 1, 0 )) - SUM(IF (qu.qtype = 'truefalse', 1, 0 )) - SUM(IF (qu.qtype = 'match', 1, 0 )) - SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'Other'
 +
 
 +
, (SUM(IF (qu.qtype = 'multichoice', 1, 0 )) + SUM(IF (qu.qtype = 'truefalse', 1, 0 )) + SUM(IF (qu.qtype = 'match', 1, 0 )))/COUNT( qu.id) AS 'Percent MC'
 +
 
 +
#, SUM(IF (qu.qtype = 'numerical', 1, 0 )) AS 'numerical'
 +
#, SUM(IF (qu.qtype LIKE 'calc%', 1, 0 )) AS 'calculated'
 +
#, SUM(IF (qu.qtype = 'random', 1, 0 )) AS 'random'
 +
#, SUM(IF (qu.qtype = 'shortanswer', 1, 0 )) AS 'shortanswer'
 +
#, SUM(IF (qu.qtype = 'essay', 1, 0 )) AS 'essay'
 +
 
 +
 
 +
, IF(q.shufflequestions > 0,'Yes','No') AS 'Randomized Questions'
 +
, IF(q.shuffleanswers > 0,'Yes','No') AS 'Randomized Answers'
 +
 +
#, FROM_UNIXTIME(c.startdate) AS 'Course Start Date'
 +
#, FROM_UNIXTIME(MIN(q.timemodified)) AS 'Last Modified'
 +
 
 +
#, DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(MIN(q.timemodified))) AS 'Quiz age'
 +
 
 +
, MIN(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Min Quiz Age'
 +
, MAX(DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified))) AS 'Max Quiz Age'
 +
 
 +
#, SUM(IF (DATEDIFF(FROM_UNIXTIME(c.startdate),FROM_UNIXTIME(q.timemodified)) < 90, 1,0)) AS 'new quizzes'
 +
 
 +
FROM prefix_quiz AS q
 +
JOIN prefix_course AS c on c.id = q.course
 +
JOIN prefix_quiz_question_instances AS qqi ON qqi.quiz = q.id
 +
LEFT JOIN prefix_question AS qu ON qu.id = qqi.question
 +
 
 +
WHERE
 +
1
 +
%%FILTER_STARTTIME:c.startdate:>%% %%FILTER_ENDTIME:c.startdate:<%%
 +
 
 +
GROUP BY c.id
 +
 
 +
ORDER BY c.shortname
 
</code>
 
</code>
 +
 +
==SCORM Activity Reports==
 +
 +
===Lists All completed SCORM activites by Course 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.
 +
<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>
 +
 +
== Badges==
 +
 +
=== 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.
 +
 +
<code sql>
 +
SELECT u.username, b.name AS badgename,
 +
CASE
 +
WHEN b.courseid IS NOT NULL THEN
 +
(SELECT c.shortname
 +
    FROM prefix_course AS c
 +
    WHERE c.id = b.courseid)
 +
WHEN b.courseid IS NULL THEN "*"
 +
END AS Context,
 +
CASE
 +
  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.
 +
 +
=== All badges available in the system, with Earned count ===
 +
 +
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.
 +
 +
<code sql>
 +
SELECT b.id, b.name, b.description,
 +
CASE
 +
WHEN b.type = 1 THEN "System"
 +
WHEN b.type = 2 THEN "Course"
 +
END AS Context,
 +
CASE
 +
WHEN b.courseid IS NOT NULL THEN
 +
(SELECT c.shortname
 +
    FROM prefix_course AS c
 +
    WHERE c.id = b.courseid)
 +
WHEN b.courseid IS NULL THEN "*"
 +
END AS Course,
 +
CASE
 +
WHEN b.status = 0 OR b.status = 2 THEN "No"
 +
WHEN b.status = 1 OR b.status = 3 THEN "Yes"
 +
WHEN b.status = 4 THEN "x"
 +
END AS Available,
 +
CASE
 +
WHEN b.status = 0 OR b.status = 1 THEN "0"
 +
WHEN b.status = 2 OR b.status = 3 OR b.status = 4 THEN
 +
(SELECT COUNT(*)
 +
  FROM prefix_badge_issued AS d
 +
  WHERE d.badgeid = b.id
 +
)
 +
END AS Earned
 +
FROM prefix_badge AS b
 +
 +
</code>
 +
 +
=== Badges Leaderboard ===
 +
 +
A simple list of usernames and how many badges they have earned overall.
 +
 +
<code sql>
 +
SELECT u.username, (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.userid = u.id) AS earned
 +
FROM prefix_user AS u
 +
ORDER BY earned DESC, u.username ASC
 +
</code>
 +
 +
==Administrator Reports==
 +
 +
===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.
 +
 +
<code sql>
 +
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===
 +
 +
How to get a list of all users and which cohorts they belong to.
 +
 +
<code sql>
 +
SELECT u.firstname, u.lastname, h.idnumber, h.name
 +
FROM prefix_cohort AS h
 +
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
 +
JOIN prefix_user AS u ON hm.userid = u.id
 +
ORDER BY u.firstname
 +
</code>
 +
 +
 +
===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>
 +
SELECT
 +
 +
YEAR( FROM_UNIXTIME( `timecreated` ) ) AS YEAR, COUNT( * ) AS Counter
 +
 +
, (SELECT COUNT( DISTINCT course )
 +
FROM prefix_log AS l
 +
WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) )
 +
) AS "Active"
 +
 +
,(SELECT COUNT(*) FROM (
 +
SELECT COUNT( * ),time
 +
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 YEAR( FROM_UNIXTIME( `timecreated` ) )
 +
</code>
 +
 +
===Users created And Active users by Year===
 +
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
 +
 +
YEAR( FROM_UNIXTIME( `firstaccess` ) ) AS YEAR, COUNT( * ) AS Counter
 +
 +
, (SELECT COUNT( DISTINCT userid )
 +
FROM prefix_log AS l
 +
WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) )
 +
) AS "Active"
 +
 +
,(SELECT COUNT(*) FROM (
 +
SELECT COUNT( * ),time
 +
FROM prefix_log AS l
 +
GROUP BY userid
 +
HAVING COUNT(*) > 500) AS users_log
 +
WHERE YEAR( FROM_UNIXTIME( users_log.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) )
 +
) AS "Active_MoreThan500Hits"
 +
 +
FROM `prefix_user`
 +
GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
 +
</code>
 +
 +
===Course Aggregation Report===
 +
Contributed by Elizabeth Dalton, Granite State College
 +
 +
If you are considering upgrading from Moodle 2.6 to 2.8 or later, your grades may be changed. This report can help quantify and identify the courses at risk of changes.
 +
 +
In particular, be on the lookout for any courses with the following combinations of parameters, which are known to cause changes in calculations:
 +
 +
# mean of grades set with aggregate with subcategory.
 +
# Simple weighted mean of grades with aggregate with sub category and drop the lowest
 +
# Sum of grades drop the lowest
 +
 +
Also review:
 +
https://tracker.moodle.org/browse/MDL-48618
 +
https://tracker.moodle.org/browse/MDL-48634
 +
https://tracker.moodle.org/browse/MDL-49257
 +
https://tracker.moodle.org/browse/MDL-50089
 +
https://tracker.moodle.org/browse/MDL-50062
 +
 +
<code sql>
 +
SELECT
 +
 +
COUNT(c.shortname) AS 'Count of Courses'
 +
 +
# If you want to display all the courses for each aggregation type, uncomment the next line and change GROUP BY settings
 +
#, c.shortname AS 'course name'
 +
 +
# If you need to display grade categories for each aggregation type, uncomment the next line and change GROUP BY settings
 +
#, gc.fullname AS 'grade category name'
 +
 +
, gc.aggregation AS 'aggregation method'
 +
 +
#These aggregation text strings appear to be hard-coded. I couldn't find a table for them. If you have aggregation types I haven't included here, they'll be blank in your report results.
 +
, CASE gc.aggregation
 +
  WHEN 0 THEN 'Mean of Grades'
 +
  WHEN 2 THEN 'Median of Grades'
 +
  WHEN 6 THEN 'Highest Grade'
 +
  WHEN 8 THEN 'Mode of Grades'
 +
  WHEN 10 THEN 'Weighted Mean of Grades'
 +
  WHEN 11 THEN 'Simple Weighted Mean of Grades'
 +
  WHEN 12 THEN 'Mean of Grades (with extra credits)'
 +
  WHEN 13 THEN 'Sum of Grades'
 +
END AS 'aggregation name'
 +
 +
# Note that gc.aggregatesubcats column is eliminated in 2.8 and later per MDL-47503, so comment that line on updated systems or you'll get an error
 +
, gc.keephigh AS 'keep high'
 +
, gc.droplow AS 'dr0p low'
 +
, gc.aggregateonlygraded AS 'Aggregate only graded'
 +
, gc.aggregateoutcomes AS 'aggregate outcomes'
 +
, gc.aggregatesubcats AS 'aggregate subcategories'
 +
 +
# If you are displaying data about individual courses, you may want to know how old they are
 +
#, FROM_UNIXTIME(c.startdate) AS 'course start date'
 +
 +
# If you are trying to use this report to check to see if final grades have changed after an upgrade, you might want these data items, but calculations can still change later when the courses are actually viewed. Also, you'll need to uncomment the necessary JOINs below
 +
#, gi.itemname AS 'grade item'
 +
#, gg.finalgrade AS 'final grade'
 +
 +
FROM
 +
 +
prefix_course AS c
 +
JOIN prefix_grade_categories AS gc ON gc.courseid = c.id
 +
JOIN prefix_course_categories AS cc ON cc.id = c.category
 +
 +
#LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id #AND gi.categoryid=gc.id
 +
#LEFT JOIN prefix_grade_grades AS gg ON gg.itemid = gi.id AND gg.userid = u.id
 +
 +
WHERE
 +
1
 +
#AND gc.aggregation = 13 #only the dreaded Sum of Grades aggregations
 +
#AND gc.depth = 1 # if for some reason you only want course aggregations, not subcategories
 +
 +
 +
GROUP BY gc.aggregation, gc.keephigh, gc.droplow, gc.aggregateonlygraded, gc.aggregateoutcomes, gc.aggregatesubcats
 +
 +
</code>
 +
 +
== Useful sub queries ==
 +
 +
=== All teachers in the course ===
 +
<code sql>
 +
,(SELECT GROUP_CONCAT( CONCAT( u.firstname,  " ", u.lastname ) )
 +
FROM prefix_course ic
 +
JOIN prefix_context con ON con.instanceid = ic.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 ic.id = c.id
 +
GROUP BY ic.id
 +
) AS TeacherNames
 +
</code>
 +
 +
=== Researching Moodle DB ===
 +
Find all the tables in "moodle" db that have "grade" as part of any of their field (column) names.
 +
<code sql>
 +
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
 +
  FROM INFORMATION_SCHEMA.COLUMNS
 +
  WHERE
 +
  table_schema = "moodle"
 +
  AND column_name LIKE "%grade%"
 +
</code>
 +
 +
==See also==
 +
* [https://github.com/jleyva/moodle-configurable_reports_repository Configurable Reports Repository on GitHub]
 +
* [https://docs.moodle.org/dev/Database_Schema Database_Schema] - some automatically generated diagrams.
 +
 +
[[Category:Contributed code]]
 +
 +
[[es:Reportes específicos hechos por usuarios]]

Latest revision as of 20:48, 17 November 2016

Contents

User and Role Report

Count number of distinct learners and teachers enrolled per category (including all its sub categories)

SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers
FROM prefix_course AS c #, mdl_course_categories AS cats
LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
JOIN prefix_role_assignments  AS lra ON lra.contextid = ctx.id
JOIN prefix_role_assignments  AS tra ON tra.contextid = ctx.id
JOIN prefix_course_categories AS cats ON c.category = cats.id
WHERE c.category = cats.id
AND (
	cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID WITH the category id you want TO COUNT (eg: 80)
	OR cats.path LIKE '%/CATEGORYID'
	)
AND lra.roleid=5
AND tra.roleid=3

Detailed ACTIONs for each ROLE (TEACHER, NON-EDITING TEACHER and STUDENT)

SELECT r.name, l.action, COUNT( l.userid ) AS counter
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 l.userid = ra.userid AND ra.contextid = context.id
JOIN prefix_role AS r ON ra.roleid = r.id
WHERE ra.roleid IN ( 3, 4, 5 ) 
GROUP BY roleid, l.action

Student (user) COUNT in each Course

Including (optional) filter by: year (if included in course fullname).

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course
,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users
, COUNT(course.id) AS Students
FROM prefix_role_assignments AS asg
JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50
JOIN prefix_user AS USER ON USER.id = asg.userid
JOIN prefix_course AS course ON context.instanceid = course.id
WHERE asg.roleid = 5 
# AND course.fullname LIKE '%2013%'
GROUP BY course.id
ORDER BY COUNT(course.id) DESC

Enrolment count in each Course

Shows the total number of enroled users of all roles in each course. Sorted by course name.

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

LIST of all site USERS by COURSE enrollment (Moodle 2.x)

SELECT
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
 
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 user2 ON ue.userid = user2.id

Enrolled users,which did not login into the Course, even once (Moodle 2)

Designed forMoodle 2 table structure and uses special plugin filter : %%FILTER_SEARCHTEXT:table.field%%

SELECT
user2.id AS ID,
ul.timeaccess,
user2.firstname AS Firstname,
user2.lastname AS Lastname,
user2.email AS Email,
user2.city AS City,
user2.idnumber AS IDNumber,
user2.phone1 AS Phone,
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=16 AND ul.timeaccess IS NULL
%%FILTER_SEARCHTEXT:user2.firstname%%

Role assignments on categories

SELECT 
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.name,'</a>') AS category,
cc.depth, cc.path, r.name AS ROLE,
concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',usr.id,'">',usr.lastname,'</a>') AS name,
usr.firstname, usr.username, usr.email
FROM prefix_course_categories cc
INNER JOIN prefix_context cx ON cc.id = cx.instanceid
AND cx.contextlevel = '40'
INNER JOIN prefix_role_assignments ra ON cx.id = ra.contextid
INNER JOIN prefix_role r ON ra.roleid = r.id
INNER JOIN prefix_user usr ON ra.userid = usr.id
ORDER BY cc.depth, cc.path, usr.lastname, usr.firstname, r.name, cc.name

Permissions Overides on Categories

(By: Séverin Terrier )

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

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

(By: Séverin Terrier )

SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course',
concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins',
e.sortorder
FROM prefix_enrol AS e, prefix_course AS c
WHERE e.enrol='guest' AND e.status=0 AND e.password='' AND c.id=e.courseid AND c.visible=1

Lists "loggedin users" from the last 120 days

SELECT id,username,FROM_UNIXTIME(`lastlogin`) AS days 
FROM `prefix_user` 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120

and user count for that same population:

SELECT COUNT(id) AS Users  FROM `prefix_user` 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120

Lists the users who have only logged into the site once

SELECT id, username, firstname, lastname, idnumber
FROM prefix_user
WHERE prefix_user.deleted = 0
AND prefix_user.lastlogin = 0 
AND prefix_user.lastaccess > 0

Students in all courses of some institute

What is the status (deleted or not) of all Students (roleid = 5) in all courses of some Institute

SELECT c.id, c.fullname, u.firstname, u.lastname, u.deleted
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 ra.roleid =5
AND ctx.instanceid = c.id
AND u.institution = 'please enter school name here'

Full User info (for deleted users)

Including extra custom profile fields (from prefix_user_info_data)

SELECT * 
FROM prefix_user AS u 
JOIN prefix_user_info_data AS uid ON uid.userid = u.id 
JOIN prefix_user_info_field AS uif ON (uid.fieldid = uif.id AND uif.shortname = 'class')
WHERE `deleted` = "1" AND `institution`="your school name" AND `department` = "your department" AND `data` = "class level and number"

User's courses

change "u.id = 2" with a new user id

SELECT u.firstname, u.lastname, c.id, c.fullname
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 = 2

List Users with extra info (email) in current course

blocks/configurable_reports replaces %%COURSEID%% with course id.

SELECT u.firstname, u.lastname, u.email
FROM prefix_role_assignments AS ra
JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
JOIN prefix_user AS u ON u.id = ra.userid

Special Roles

SELECT ra.roleid,r.name
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_role_assignments AS ra
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN prefix_course AS c ON ctx.instanceid = c.id
WHERE ra.roleid > 6

Courses without Teachers

Actually, shows the number of Teachers in a course.

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(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 = c.id) AS Teachers
FROM prefix_course AS c
ORDER BY Teachers ASC

List of users who have been enrolled for more than 4 weeks

For Moodle 2.2 , by Isuru Madushanka Weerarathna

SELECT uenr.userid AS USER, IF(enr.courseid=uenr.courseid ,'Y','N') AS Enrolled, 
IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') AS EnrolledMoreThan4Weeks
FROM prefix_enrol AS enr, prefix_user_enrolments AS uenr
WHERE enr.id = uenr.enrolid AND enr.status = uenr.status

List of users with language

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.

This will show you the language setting for all users:

SELECT username, lang FROM prefix_user

This code will change the setting from 'en' to 'en_us' for all users:

UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'

List of users with Authentication

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:

SELECT username, auth FROM prefix_user

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 you admin account from manual to an external method as failure of that external method will lock you out of Moodle as admin.)

UPDATE prefix_user SET auth = 'ldap' WHERE auth = 'manual' AND id > 2

Compare role capability and permissions

SELECT DISTINCT mrc.capability 
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '1' AND rc.contextid = '1') AS Manager
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '2' AND rc.contextid = '1') AS CourseCreator
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '3' AND rc.contextid = '1') AS Teacher
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '4' AND rc.contextid = '1') AS AssistantTeacher
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '5' AND rc.contextid = '1') AS Student
,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability 
  AND rc.roleid = '6' AND rc.contextid = '1') AS Guest
 
FROM `mdl_role_capabilities` AS mrc

Log Activity Reports

Count all Active Users by ROLE in a course category (including all of its sub-categories)

SELECT COUNT(DISTINCT l.userid) AS active
FROM mdl_course AS c
JOIN mdl_context AS ctx ON  ctx.instanceid=c.id
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user_lastaccess AS l ON ra.userid = l.userid
JOIN mdl_course_categories AS cats ON c.category = cats.id
WHERE c.category=cats.id AND (
	cats.path LIKE '%/80/%'
	OR cats.path LIKE '%/80'
) 
AND ra.roleid=3  AND ctx.contextlevel=50  #ra.roleid= TEACHER 3, NON-EDITING TEACHER 4, STUDENT 5
AND  l.timeaccess > (unix_timestamp() - ((60*60*24)*NO_OF_DAYS)) #NO_OF_DAYS CHANGE TO NUMBER

Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)

SELECT l.action, COUNT( l.userid ) AS counter , r.name
FROM `prefix_log` AS l
JOIN `prefix_role_assignments` AS ra ON l.userid = ra.userid
JOIN `prefix_role` AS r ON ra.roleid = r.id
WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' )
GROUP BY roleid,l.action
ORDER BY r.name,counter DESC

Total Activity of Roles:"Teacher" and "None-Editing Teacher" by Dates and by Hours

The output columns of this report table can be used as base for a Pivot-Table which will show the amount of activity per hour per days in 3D graph view.

SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y-%m-%d' ) AS grptimed ,
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh  , COUNT( l.userid ) AS counter 
FROM `prefix_log` AS l
JOIN prefix_user AS u ON u.id = l.userid
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_role AS r ON r.id = ra.roleid
WHERE ra.roleid IN (3,4)
GROUP BY grptimed,grptimeh
ORDER BY grptimed,grptimeh

How many LOGINs per user and user's Activity

+ link username to a user activity graph report

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Username
,COUNT(*) AS logins
,(SELECT COUNT(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity 
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id 
WHERE `action` LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC

Total activity per course, per unique user on the last 24h

SELECT
     COUNT(DISTINCT userid) AS countUsers
   , COUNT(course) AS countVisits
   , concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
 FROM prefix_log AS l
 JOIN prefix_course AS c ON c.id = l.course
 WHERE course > 0 AND FROM_UNIXTIME(TIME) >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND c.fullname LIKE '%תשעג%'
 GROUP BY course
ORDER BY countVisits DESC

Weekly Instructor Online Participation

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.

  • To specify a subject and/or course number, use % as a wildcard, e.g. ARTS% or ARTS501%
  • To match part of a last name, use %, e.g. Smi% will match "Smith", "Smile", etc.

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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.

SELECT 
c.shortname AS CourseID
, cc.name AS Category
, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
 
, (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
 
, c.startdate AS Course_Start_Date
 
, c.visible AS Visible
 
,  COUNT(l.id) AS Edits
 
, SUM(IF(WEEK(FROM_UNIXTIME(l.time)) - WEEK(FROM_UNIXTIME(c.startdate))<0,1,0)) AS BeforeTerm
 
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=0,1,0)) AS Week1
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=1,1,0)) AS Week2
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=2,1,0)) AS Week3
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=3,1,0)) AS Week4
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=4,1,0)) AS Week5
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=5,1,0)) AS Week6
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=6,1,0)) AS Week7
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=7,1,0)) AS Week8
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=8,1,0)) AS Week9
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=9,1,0)) AS Week10
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=10,1,0)) AS Week11
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=11,1,0)) AS Week12
 
, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))>=12,1,0)) AS AfterTerm
 
, 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
 
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
 
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%"
 
WHERE ra.roleid =3
AND ctx.instanceid = c.id
AND c.shortname LIKE :course
AND u.lastname LIKE :last_name
 
GROUP BY u.idnumber, c.id
HAVING students > 0
ORDER BY c.shortname

Weekly Student Online Participation

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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.

SELECT 
u.lastname AS 'Last Name'
, u.firstname AS 'First Name'
,  COUNT(l.id) AS 'Edits'
 
, 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'
 
, SUM(IF(FLOOR((l.time - c.startdate)/(60*60*24*7))>=15,1,0)) AS 'After Term'
 
#, 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'
 
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
# 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_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%"
 
WHERE ra.roleid =5
AND ctx.instanceid = c.id
 
AND c.id = %%COURSEID%%
 
GROUP BY u.idnumber
 
ORDER BY u.lastname, u.firstname

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 report uses legacy (pre-2.7) logs. A conversion to the new log system is in the works.

Note: This should be defined as a "Global" report (visible from within all courses).

Note: This report can take a long time to run.


SELECT 
 
# Identify student
CONCAT('<a target="_blank" href="%%WWWROOT%%/message/index.php?id=' , allstu.id , '">' , allstu.firstname , ' ' , allstu.lastname , '</a>' ) AS 'Student - click to send message'
 
, 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'
 
, 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'
 
## ONLY posts WITHIN LAST 7 days
 
# COUNT posts BY student
, COUNT(DISTINCT IF(fps.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fps.id,NULL)) AS 'Forum Stu Posts - 7 days'
 
# 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'
 
# USING link back TO student posts ON replies, GET UNIQUE student IDs responded
, 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'
 
# ALL replies
, COUNT(DISTINCT IF(fpsr.created > (UNIX_TIMESTAMP()  - (7*24*60*60)),fpsr.id,NULL)) AS 'Forum All Replies - 7 days'
 
# ADD IN COUNT OF graded assignments - 7 days
, COUNT(DISTINCT IF(asb.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asb.id,NULL)) AS 'Assign Submit - 7 days'
, COUNT(DISTINCT IF(asg.timemodified > (UNIX_TIMESTAMP()  - (7*24*60*60)),asg.id,NULL)) AS 'Assign Grades - 7 days'
 
# 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'
 
## ALL posts IN course so far
# COUNT posts BY student
, COUNT(DISTINCT fps.id) AS 'Forum Stu Posts - to date'
 
# 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
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
 
# 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
LEFT JOIN prefix_role_assignments AS rai ON rai.contextid = ctx.id
LEFT 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
LEFT JOIN prefix_course_modules AS cm ON c.id = cm.course
 
# 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 
 
# We care about messages that involve BOTH the instructor AND students OF this course
# messages FROM instructor TO students:
# LEFT JOIN prefix_message AS mts ON mts.useridfrom = instr.id AND mts.useridto = allstu.id
# LEFT JOIN prefix_message AS mfs ON mfs.useridfrom = instr.id AND mfs.useridto = allstu.id
 
WHERE  
c.id = %%COURSEID%% 
 
# GROUP BY c.shortname , allstu.id
GROUP BY allstu.id
 
ORDER BY allstu.lastname

Module activity (Hits) between dates

SELECT module, COUNT( * ) 
FROM prefix_log AS l
WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN  '2012-10-01 00:00:00' AND  '2013-09-31 00:00:00')
GROUP BY module

Module activity (Instances and Hits) for each academic year

SELECT name
 
,(SELECT COUNT(*) 
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(*) 
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
) AS "Used 2010"
 
,(SELECT COUNT(*) 
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 AND l.action = 'add'
) AS "Added 2011"
 
,(SELECT COUNT(*) 
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"
 
 
,(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 AND l.action = 'add'
) AS "Added 2012"
 
,(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"
 
FROM mdl_modules AS m

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)

SELECT COUNT(DISTINCT userid) AS "Unique User Logins"
,DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y /%m / %d") AS "Year / Month / Day", "Graph" 
FROM `mdl_logstore_standard_log` 
WHERE action LIKE 'loggedin'
#AND timecreated >  UNIX_TIMESTAMP('2015-01-01 00:00:00') # optional START DATE
#AND timecreated <= UNIX_TIMESTAMP('2015-01-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))

OR...

Unique users and page views on the last 7 days:

SELECT 
  DAYOFMONTH(FROM_UNIXTIME(timecreated)) 'Day', 
  COUNT(DISTINCT ip) 'unique ip' ,
  COUNT(DISTINCT userid) 'unique userid' , 
  COUNT(*) 'page requests' 
FROM mdl_logstore_standard_log 
WHERE TIMESTAMPDIFF(DAY, FROM_UNIXTIME(timecreated), NOW()) < 7 AND action = 'viewed'
GROUP BY DAYOFMONTH(FROM_UNIXTIME(timecreated))

And...

Counting user's global and unique hits per day + counting individual usage of specific activities and resources (on that day),

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.

SELECT DATE_FORMAT(FROM_UNIXTIME(timecreated), "%y-%m-%d") AS "Datez"
,COUNT(DISTINCT userid) AS "Unique Users"
,ROUND(COUNT(*)/10) "User Hits (K)"
,SUM(IF(component='mod_quiz',1,0)) "Quizzes"
,SUM(IF(component='mod_forum' OR component='mod_forumng',1,0)) "Forums"
,SUM(IF(component='mod_assign',1,0)) "Assignments"
,SUM(IF(component='mod_oublog',1,0)) "Blogs"
,SUM(IF(component='mod_resource',1,0)) "Files (Resource)"
,SUM(IF(component='mod_url',1,0)) "Links (Resource)"
,SUM(IF(component='mod_page',1,0)) "Pages (Resource)"
 
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))

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.

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',
			  course.id,'">',course.fullname,'</a>') AS Course 
 
#,course.shortname
 
,CASE 
  WHEN course.fullname LIKE '%2012%' THEN '2012'
  WHEN course.fullname LIKE '%2013%' THEN '2013' 
  WHEN course.fullname LIKE '%2014%' THEN '2014'
  WHEN course.fullname LIKE '%2015%' THEN '2015'
END AS YEAR
 
,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
 
,IF(course.startdate>0, DATE_FORMAT(FROM_UNIXTIME(startdate), '%d-%m-%Y'), 'no date') AS "Course Start Date" 
 
,(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 = course.id
) AS Students
 
,(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 = 4 AND ctx.instanceid = course.id
) AS "Assistant teacher"
 
,(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
 
# 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
 
#,(SELECT COUNT(*)
#FROM mdl_logstore_standard_log AS l
#JOIN mdl_role_assignments AS ra ON ra.contextid= l.contextid AND ra.userid= l.userid AND ra.roleid = 5 
#WHERE l.courseid = course.id ) AS "Student HITs"
 
#,(SELECT COUNT(*)
#FROM mdl_logstore_standard_log AS l
#JOIN mdl_role_assignments AS ra ON ra.contextid= l.contextid AND ra.userid= l.userid AND ra.roleid = 3 
#WHERE l.courseid = course.id ) AS "Teacher HITs"
 
,(SELECT COUNT(*) FROM mdl_log AS l WHERE l.course = course.id) AS 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 = 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"
 
,(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
 
,(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 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 ( 'oublog') ) "Num Blogs"
 
,(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"
 
,(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 ('resource', 'folder', 'url', 'tab', 'file', 'book', 'page') ) Resources
 
,(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 ('forum', 'forumng', 'oublog', 'page', 'file', 'url', 'wiki' , 'ouwiki') ) "Basic Activities"
 
,(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 ('advmindmap', 'assign', 'attendance', 'book', 'choice', 'folder', 'tab', 'glossary', 'questionnaire', 'quiz', 'label' ) ) "Avarage Activities"
 
,(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:~%%
 
WHERE course.fullname LIKE '%2015%' 
 
HAVING Modules > 2
ORDER BY UniqueModules DESC

Learners (students) with most active learning portfolios

Trying to get a list of the most active learners and see the complete activity report (portfolio)

SELECT COUNT(*) "Hits", l.userid, l.courseid, 
CONCAT('<a target="_new" href="%%WWWROOT%%/report/outline/user.php?id=', u.id ,'&course=', l.courseid, '&mode=complete">',u.firstname ,' ',u.lastname,'</a>') AS Username
 
FROM mdl_logstore_standard_log AS l
JOIN mdl_role_assignments AS ra ON l.userid = ra.userid AND l.contextid = ra.contextid
JOIN mdl_role AS r ON r.id = ra.roleid
JOIN mdl_user AS u ON l.userid = u.id
WHERE ra.roleid = 5
GROUP BY l.userid, l.courseid
ORDER BY COUNT(*) DESC

Course Reports

Most Active courses

SELECT COUNT(l.userid) AS Views
FROM `mdl_logstore_standard_log` l, `mdl_user` u, `mdl_role_assignments` r
WHERE l.courseid=35
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

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

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
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 
  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
 
,(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_log l 
INNER JOIN prefix_course c ON l.course = c.id
GROUP BY c.id
HAVING Modules > 2
ORDER BY YEAR DESC, hits DESC

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.

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
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
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

RESOURCE count for each COURSE

SELECT COUNT(l.id) COUNT, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c ON l.course = c.id
GROUP BY course
ORDER BY COUNT DESC

Common resource types count for each Category (Moodle19)

Including sub-categories in total count.

SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
,(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 = 'file' AND r.reference LIKE 'http://%'
) AS Links
 
,(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 = 'file' AND r.reference NOT LIKE 'http://%'
) AS Files
 
,(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 = '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
 
FROM prefix_course_categories AS mcc
ORDER BY mcc.path

Where "stats_log_context_role_course" (in the above SQL query) is a VIEW generated by:

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

Same query but for Moodle2+

SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
mcc.path,
 
(SELECT COUNT(*) 
FROM prefix_url AS u
JOIN prefix_course AS c ON c.id = u.course
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
) AS URLs,
 
(SELECT COUNT(*) 
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(*) 
FROM prefix_page AS p
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(*) 
FROM prefix_book AS b
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(*) 
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,
 
(SELECT COUNT(*) 
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
 
FROM prefix_course_categories AS mcc
ORDER BY mcc.path

Detailed Resource COUNT by Teacher in each course

Including (optional) filter by: year, semester and course id.

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
 
, (CASE 
WHEN c.fullname LIKE '%תשעב%' THEN '2012' 
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 
JOIN `prefix_course` AS c ON c.id = r.course
#WHERE TYPE= 'file' AND reference NOT LIKE 'http://%' 
 
#WHERE 1=1
#%%FILTER_YEARS:c.fullname%%
#AND c.fullname LIKE '%2013%'
 
GROUP BY course
ORDER BY COUNT(c.id) DESC

Courses that are defined as using GROUPs

SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
,(SELECT COUNT(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
 FROM `prefix_course` AS c
WHERE groupmode > 0

Courses with Groups

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.

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

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.

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
 
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 user2 ON ue.userid = user2.id
JOIN prefix_groups AS g ON g.courseid = course.id
 
WHERE ue.enrolid NOT IN (SELECT userid FROM prefix_groups_members WHERE g.id=groupid)
 
ORDER BY Course, Lastname

Groups in course with member list

List the groups in a course (replace the # by the course id number) with the members of each group.

SELECT c.shortname, g.name AS Groupname, u.username
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 = #

Group Export

There's a 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.

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 = #

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...

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'
)

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.

$s should be the id of the top level category you are interested in.

<?php
 
require_once('./config.php');
 
$parent_id = $s;
 
$categories= array();
 
$categories = get_categories($parent_id);
 
echo '<ol>';
foreach ($categories as $category)
        {
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
        }
echo '</ol>';
 
?>

Blog activity per Course (not including VIEW)

Filter activity logging to some specific Course Categories! + 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)

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

Student's posts content in all course blogs (oublog)

SELECT 
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
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
 
WHERE c.id = %%COURSEID%%

All Courses which uploaded a Syllabus file

+ under specific Category + show first Teacher in that course + link Course's fullname to actual course

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
FROM prefix_resource AS r 
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)

Site-wide completed SCORM activities by Course 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.

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

All users enrolled in a course without a role

Identifies All users that are enrolled in a course but are not assigned a role.

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 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

List course resources accumulative file size and count

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.

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"
 
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

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. And also setup (add) a FILTER_COURSES filter.

SELECT 
id ,CONCAT('<a target="_new" href="%%WWWROOT%%/pluginfile.php/', contextid, '/', component, '/', filearea, '/', itemid, '/', filename, '">', filename,'</a>') AS "File"
,filesize, mimetype ,author, license, timecreated, component, filearea, filepath
 
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
                           )
                )

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!)

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"
 
FROM mdl_course AS c

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)

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'

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.

SELECT
 
cs.section AS 'Week'
, cs.name AS 'Section Name'
 
, COUNT(DISTINCT IF((gi.id IS NULL) AND (m.name NOT LIKE 'label'),cm.id,NULL)) AS 'Ungraded Resources'
 
, COUNT(DISTINCT IF(m.name LIKE 'forum', cm.id, NULL)) AS 'Forums'
 
, COUNT(DISTINCT IF(gi.id, cm.id, NULL)) AS 'Graded Activities'
 
FROM prefix_course AS c
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
LEFT JOIN prefix_grade_items AS gi ON gi.courseid = c.id AND gi.itemmodule = m.name AND gi.iteminstance = cm.instance
 
WHERE 
cs.visible = 1
AND cm.visible = 1
AND c.id = %%COURSEID%%
 
GROUP BY cs.section
ORDER BY cs.section

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.

SELECT
 
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

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:

  1. "Required blocks" include Instructor Block (mooprofile), Activities, and the Research block.
  2. "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.
  3. All courses should be in the "Collapsed Topics" format with the "Weeks" structure.
  4. "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.
  5. We recommend that each week contain at least one forum, at least one graded activity, and at least one ungraded resource.
  6. "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.

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('<a target="_blank" href="%%WWWROOT%%/user/view.php',CHAR(63),'id=',u.id,'">',u.lastname,', ', u.firstname,'</a>')
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 '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'
 
,(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'
 
, 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'
#, 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'
 
#, 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
 
#, IF(bi.configdata LIKE '%ZGl0IHRoaXMgYmxvY2s%','NO','') AS 'Instructor Details Block Updated' # HTML block has string 'dit this block'
 
#, 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
 
, IF(c.format='topcoll','YES', c.format) AS 'Collapsed Topics course format' # CHANGE this IF you want TO test FOR a different format
, IF(cfo.value = 2, 'YES','NO') AS 'weeks structure'
 
, cfw.value AS 'weeks defined in course settings'
 
, 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)'
 
, 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
 
#,(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
 
#,(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
 
,(SELECT FROM_UNIXTIME(MAX(prefix_resource.timemodified))
FROM prefix_resource
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 TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo
 
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', f.id,NULL)),'YES','NO' ) AS 'Announcement Forum Visible'
 
, IF(COUNT(DISTINCT IF(f.type LIKE 'news', fd.id,NULL)),'YES','NO' ) AS 'Announcement posted'
 
FROM prefix_course AS c
LEFT JOIN prefix_course_categories AS cc ON c.category = cc.id
LEFT JOIN prefix_context AS ctxx ON c.id = ctxx.instanceid 
 
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
 
GROUP BY c.shortname

Module instances + Module HITs by role teacher and student in course

SELECT 
m.name AS "Module name"
, COUNT(*) AS "Module count"
 
,(SELECT COUNT(*) 
FROM prefix_log AS l 
WHERE l.course = cm.course AND l.module = m.name ) AS "Hits"
 
,(SELECT COUNT(*)
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 = 5 
WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs"
 
,(SELECT COUNT(*)
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
JOIN mdl_modules AS m ON m.id = cm.module
WHERE cm.course = '%%COURSEID%%'
GROUP BY cm.module

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

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', 
c.fullname AS 'Course', 
cc.name AS 'Category',
 
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,gi.timemodified,'1970-01-01') AS TIME
 
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
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 
ORDER BY lastname

For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line

DATEADD(ss,gi.timemodified,'1970-01-01') AS Time

with

DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time

Site-Wide Grade Report with Just Course Totals

A second site-wide grade report for all students that just shows course totals. Works with ad-hoc reports or Configurable Reports

SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', 
cc.name AS 'Category',
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
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.itemtype = 'course'
 
ORDER BY lastname

For MySQL users:

SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name', 
c.fullname AS 'Course', 
cc.name AS 'Category',
CASE 
  WHEN gi.itemtype = 'course' 
   THEN CONCAT(c.fullname, ' - Total')
  ELSE gi.itemname
END AS 'Item Name',
 
ROUND(gg.finalgrade,2) AS Grade,
FROM_UNIXTIME(gg.timemodified) AS TIME
 
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
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 
ORDER BY lastname

Learner report by Learner with grades

Which Learners in which course and what are the grades

SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category', 
CASE WHEN gi.itemtype = 'Course'    
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,
 
IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass
 
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 
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

User Course Completion

A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.

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

User Course Completion with Criteria

A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.

SELECT u.username AS USER, 
c.shortname AS course,
DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
CASE
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"
END AS aggregation,
CASE 
WHEN p.criteriatype = 1 THEN "Self"
WHEN p.criteriatype = 2 THEN "By Date"
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

Courses with Completion Enabled and their settings

List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.

SELECT c.shortname AS Course, 
CASE
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"
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

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

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

Course Completion Report with Groups

Contributed by Benjamin Yun, C2 Education

This report outputs a lists of all users enrolled in courses that have course completion enabled and includes:

  • User info
  • Course info (course name, category, start date)
  • Enrollment info (enroll date and group)
  • Completion date

Particularly useful to see the success rate of course, especially those with multiple teachers, each running their own groups.

Two filters are including to further drill down.

SELECT u.username AS 'User Name',
CONCAT(u.lastname,', ',u.firstname) AS 'Full Name',
u.email AS 'Email',
cc.name AS 'Course Category',
c.fullname AS 'Course Name',
g.name AS 'Group',
DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%m/%d/%Y') AS 'Course Start Date',
DATE_FORMAT(FROM_UNIXTIME(p.timeenrolled),'%m/%d/%Y') AS 'Enrollment Date',
DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%m/%d/%Y') AS 'Completion Date'
 
FROM prefix_course AS c
JOIN prefix_course_categories AS cc ON cc.id = c.category
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
JOIN prefix_course_completions AS p ON c.id = p.course AND u.id = p.userid
 
WHERE c.enablecompletion = 1
%%FILTER_SUBCATEGORIES:cc.path%% 
%%FILTER_COURSES:Course%%
ORDER BY u.lastname

Scales used in activities

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
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

List all gradebook categories and grade items for course id XXX

SELECT 
gc.courseid, gc.parent, gc.depth, gc.path, gc