ad-hoc contributed reports: Difference between revisions

Latest revision as of 17:06, 30 November 2017

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

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'

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

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.

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

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

Course Reports

Most Active courses

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

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

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 '

';
foreach ($categories as $category)
{
echo '<a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a>';
}
echo '

';

?>

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

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,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 ORDER BY lastname For MySQL users, you'll need to use the MySQL FROM_UNIXTIME function instead of DATEADD. Replace the line: DATEADD(ss,gg.timemodified,'1970-01-01') AS Time with: FROM_UNIXTIME(gg.timemodified) AS Time And: u.firstname + ' ' + u.lastname AS 'Display Name', with: CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',

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

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

Activity Module Reports

How many SCORM activities are used in each Course

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

Detailed ACTIONs for each MODULE

SELECT module,action,count(id) as counter FROM prefix_log GROUP BY module,action ORDER BY module,counter desc

Most popular ACTIVITY

SELECT COUNT(l.id) hits, module FROM prefix_log l WHERE module != 'login' AND module != 'course' AND module != 'role' GROUP BY module ORDER BY hits DESC

System wide use of ACTIVITIES and RESOURCES

SELECT count( cm.id ) AS counter, m.name FROM `prefix_course_modules` AS cm JOIN prefix_modules AS m ON cm.module = m.id GROUP BY cm.module ORDER BY counter DESC

LOG file ACTIONS per MODULE per COURSE (IDs)

select course,module,action,count(action) as summa from prefix_log where action <> 'new' group by course,action,module order by course,module,action

System Wide usage count of various course Activities

(Tested and works fine in Moodle 2.x) Like: Forum, Wiki, Blog, Assignment, Database,

Within specific category
Teacher name in course

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis ,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments

,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students

FROM prefix_course AS c WHERE c.category IN ( 18) ORDER BY Wikis DESC,Blogs DESC, Forums DESC

Course wiki usage/activity over the last 6 semesters

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 
 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 
 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 'תשעא 
 סמסטר א'

,(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 'תשעא 
 סמסטר ב'

,(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 'תשעב 
 סמסטר א'

,(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 'תשעב 
 סמסטר ב'

,(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 'תשעג 
 סמסטר א'

,(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 'תשעג 
 סמסטר ב'

Detailed WIKI activity (per wiki per course)

Including Number of Students in course (for reference) SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID ,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students ,m.name , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E' , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD' , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT , ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)' FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id WHERE m.name LIKE '%wiki%' GROUP BY cm.course,cm.module ORDER BY 'All (NO View)' DESC

Wiki usage, system wide

(you can filter the output by selecting some specific course categories : "WHERE c.category IN ( 8,13,15)")

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis

,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity ALL'

,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity ADD'

,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity EDIT'

,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity ANNOTATE'

,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity Comments'

,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students

,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid WHERE ouw.course = c.id GROUP BY ouw.course ) as OUWikiPages

,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages

FROM prefix_course AS c WHERE c.category IN ( 8,13,15) HAVING Wikis > 0 ORDER BY 'WikiActivity ALL' DESC

Aggregated Teacher activity by "WEB2" Modules

(Tested and works fine in Moodle 2.x) The NV column shows activity without VIEW log activity SELECT ra.userid, u.firstname,u.lastname ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment ,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV FROM prefix_role_assignments AS ra JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 GROUP BY ra.userid

List all the certificates issued, sort by variables in the custom profile fields

Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.

SELECT DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date, prefix_certificate_issues.classname AS Topic, prefix_certificate.name AS Certificate, prefix_certificate_issues.studentname as Name, prefix_user_info_data.data AS Units

FROM prefix_certificate_issues

INNER JOIN prefix_user_info_data on prefix_certificate_issues.userid = prefix_user_info_data.userid

INNER JOIN prefix_certificate on prefix_certificate_issues.certificateid = prefix_certificate.id

WHERE prefix_user_info_data.data='Unit 1' OR prefix_user_info_data.data='Unit 2' OR prefix_user_info_data.data='Unit 3'

ORDER BY Units, Name, Topic ASC

Counter Blog usage in Courses,system wide

What teachers in what courses, uses blogs and how many + student count in that course.

SELECT ( @counter := @counter+1) as counter, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course

,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)

 FROM prefix_role_assignments AS ra
 JOIN prefix_user AS u ON ra.userid = u.id
 JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher

,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students

FROM prefix_course AS c, (SELECT @counter := 0) as s_init WHERE c.category IN ( 8,13,15) HAVING Blogs > 0 ORDER BY Blogs DESC

Elluminate (Blackboard Collaborate) - system wide usage

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

Choice

Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.

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

Assignment type usage in courses

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

Assign Module Reports

All Ungraded Assign using rownum Ranking w/ Link

(By: Ben Haensel )

For the Ungraded Assign (mod_assign) query below to work, teaching staff must use the default Assign sort, or logout and log back in after sorting. Otherwise, grade links will direct to the wrong user.

This report takes resubmitted Assign into account
This report is specific to the course in which it's run
Requirement: User must use the default Assign sort

SELECT u.firstname AS "First", u.lastname AS "Last", a.name AS "Assignment", convert(FROM_UNIXTIME(asb.timemodified),datetime) as LastSubmission, case when asb.timemodified > g.timemodified then 'resubmitted' else asb.status end AS "Status",

concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?rownum=',

(select x.rank from (SELECT @curRank := @curRank + 1 AS rank, id from (select user2.id FROM prefix_user AS user2 INNER JOIN prefix_user_enrolments AS ue ON user2.id = ue.userid INNER JOIN prefix_enrol AS en ON ue.enrolid = en.id INNER JOIN prefix_course AS course ON en.courseid = course.id where course.id = '%%COURSEID%%') as G, (select @curRank:= -1) r order by id asc) as x where x.id = asb.userid),

'&useridlist=',asb.userid,'&id=',cm.id,'&action=grade','">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON c.id = en.courseid JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id JOIN prefix_grade_items gi ON c.id = gi.courseid JOIN prefix_grade_grades AS g on gi.id = g.itemid

JOIN prefix_assign AS a ON c.id = a.course JOIN prefix_assign_submission AS asb ON asb.assignment = a.id JOIN prefix_user AS u ON u.id = asb.userid

WHERE c.id = '%%COURSEID%%' AND ue.userid = u.id AND cm.module = 1 AND cm.instance = a.id AND gi.itemmodule = 'assign' AND gi.iteminstance = a.id AND g.userid = u.id AND (g.finalgrade is NULL or asb.timemodified > g.timemodified)

ORDER BY LastSubmission

All Ungraded Assign using modification for Link

(By: Ben Haensel )

For this version of the Ungraded Assign (mod_assign) query below to work, this modification to the ROWNUM logic must be implemented so that using ROWNUM in the concatenated URL string is not necessary. In BlueSky School's 2.7 Moodle instance, we have not experienced issues with this suggested change. For more information regarding the rownum logic discussion, visit these Moodle Tracker pages:

Please note the following about this query:

This report takes resubmitted Assign into account
This report is specific to the course in which it's run
Requirement: The Assign mod must be updated (see github) to use a URL string without the rownum logic

SELECT u.firstname AS "First", u.lastname AS "Last", a.name AS "Assignment", convert(FROM_UNIXTIME(asb.timemodified),datetime) as LastSubmission, case when asb.timemodified > g.timemodified then 'resubmitted' else asb.status end AS "Status",

concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id, '&userid=',asb.userid,'&action=grade','">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON c.id = en.courseid JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id JOIN prefix_grade_items gi ON c.id = gi.courseid JOIN prefix_grade_grades AS g on gi.id = g.itemid

JOIN prefix_assign AS a ON c.id = a.course JOIN prefix_assign_submission AS asb ON asb.assignment = a.id JOIN prefix_user AS u ON u.id = asb.userid

WHERE c.id = '%%COURSEID%%' AND ue.userid = u.id AND cm.module = 1 AND cm.instance = a.id AND gi.itemmodule = 'assign' AND gi.iteminstance = a.id AND g.userid = u.id AND (g.finalgrade is NULL or asb.timemodified > g.timemodified)

ORDER BY LastSubmission

Assignment Module Reports

All Ungraded Assignments

Returns all the submitted assignments that still need grading select u.firstname AS "First", u.lastname AS "Last", c.fullname AS "Course", a.name AS "Assignment"

from prefix_assignment_submissions as asb join prefix_assignment as a ON a.id = asb.assignment join prefix_user as u ON u.id = asb.userid join prefix_course as c ON c.id = a.course join prefix_course_modules as cm ON c.id = cm.course

where asb.grade < 0 and cm.instance = a.id and cm.module = 1

order by c.fullname, a.name, u.lastname

All Ungraded Assignments w/ Link

Returns all the submitted assignments that still need grading, along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle. select u.firstname AS "First", u.lastname AS "Last", c.fullname AS "Course", a.name AS "Assignment",

'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) + + 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar) + '&mode=single&filter=0&offset=2">' + a.name + '</a>' AS "Assignmentlink"

from prefix_assignment_submissions as asb join prefix_assignment as a ON a.id = asb.assignment join prefix_user as u ON u.id = asb.userid join prefix_course as c ON c.id = a.course join prefix_course_modules as cm ON c.id = cm.course

where asb.grade < 0 and cm.instance = a.id and cm.module = 1

order by c.fullname, a.name, u.lastname

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.

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

, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished Assignments (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 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 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 Assignments' DESC

Who is using "Single File Upload" assignment

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course

,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher

,ass.name as "Assignment Name"

FROM prefix_assignment as ass

JOIN prefix_course as c ON c.id = ass.course

WHERE `assignmenttype` LIKE 'uploadsingle'

Resource Module Reports

List "Recently uploaded files"

see what users are uploading SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info FROM `prefix_log` WHERE `action` LIKE 'upload' ORDER BY `prefix_log`.`time` DESC

List Courses that loaded a specific file: "X"

Did the Teacher (probably) uploaded course's Syllabus ? SELECT c.id, c.fullname FROM `prefix_log` as l JOIN prefix_course as c ON c.id = l.course WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id

All resources that link to some specific external website

+ link to course + who's the teacher + link to external resource SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,c.shortname,r.name ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher ,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource FROM prefix_resource AS r JOIN prefix_course AS c ON r.course = c.id WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'

"Compose Web Page" RESOURCE count

SELECT course,prefix_course.fullname, COUNT(*) AS Total FROM `prefix_resource` JOIN `prefix_course` ON prefix_course.id = prefix_resource.course WHERE type='html' GROUP BY course

Resource count in courses

+ (First)Teacher name + Where course is inside some specific Categories SELECT COUNT(*) AS count ,r.course ,c.shortname shortname ,c.fullname coursename ,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)

 FROM prefix_role_assignments AS ra
 JOIN prefix_user as u ON ra.userid = u.id
 JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher

FROM prefix_resource r JOIN prefix_course c ON r.course = c.id WHERE c.category IN (10,13,28,18,26) GROUP BY r.course ORDER BY COUNT(*) DESC

Forum Module Reports

All Ungraded Forums w/ Link

(By: Ben Haensel )

Please note the following about this query:

This report is specific to the course in which it's run

SELECT u.firstname AS "First", u.lastname AS "Last", f.name AS "Forum", convert(FROM_UNIXTIME(fp.modified),datetime) as LastSubmission, concat('<a target="_blank" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fp.discussion,'">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id

JOIN prefix_forum AS f ON c.id = f.course JOIN prefix_forum_discussions AS fd ON f.id = fd.forum JOIN prefix_forum_posts AS fp ON fd.id = fp.discussion JOIN prefix_user AS u ON u.id = fp.userid

WHERE cm.instance = f.id AND ue.userid = u.id AND ra.userid = u.id AND ra.roleid = '5' AND c.id = '%%COURSEID%%' AND cm.module = '9' AND f.assessed not like '0' AND fp.id not in (select r.itemid from prefix_rating AS r where r.itemid = fp.id and r.component = 'mod_forum')

order by LastSubmission

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

FORUM use Count per COURSE -- not including NEWS Forum!

SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course ORDER BY total desc

FORUM use Count per COURSE by type -- not including NEWS Forum!

SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course,prefix_forum.type ORDER BY total desc

Forum activity - system wide

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

Activity In Forums

Trying to figure out how much real activity we have in Forums by aggregating: Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers , Number of Students, ratio between unique Student posts and the number of students in the Course... SELECT c.fullname,f.name,f.type ,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions ,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions ,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts ,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts ,(SELECT Count( ra.userid ) AS Students FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid =5 AND ctx.instanceid = c.id ) AS StudentsCount ,(SELECT Count( ra.userid ) AS Teachers FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid =3 AND ctx.instanceid = c.id ) AS 'Teacher Count' ,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid IN (3,5) AND ctx.instanceid = c.id ) AS UserCount , (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage , (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage FROM prefix_forum as f JOIN prefix_course as c ON f.course = c.id WHERE `type` != 'news' ORDER BY StudentPostUsage DESC

All Forum type:NEWS

SELECT f.id, f.name FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_forum AS f ON cm.instance = f.id WHERE m.name = 'forum' AND f.type = 'news'

All new forum NEWS items (discussions) from all my Courses

change "userid = 26" and "id = 26" to a new user id SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date FROM prefix_forum_discussions as fd JOIN prefix_forum as f ON f.id = fd.forum JOIN prefix_course as c ON c.id = f.course JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26) WHERE fd.timemodified > ul.timeaccess

AND fd.forum IN (SELECT f.id
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_forum AS f ON cm.instance = f.id
WHERE m.name = 'forum'
AND f.type = 'news')
 AND c.id IN (SELECT c.id
  FROM prefix_course AS c
  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
  JOIN prefix_user AS u ON u.id = ra.userid
  WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC

News Forum - Discussions COUNT

Which is actually... How much instructions students get from their teachers SELECT c.shortname , concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)

 FROM prefix_role_assignments AS ra
 JOIN prefix_user AS u ON ra.userid = u.id
 JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher

,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum FROM prefix_forum_discussions AS fd INNER JOIN prefix_forum AS f ON f.id = fd.forum INNER JOIN prefix_course AS c ON c.id = f.course WHERE f.type = 'news' AND c.category IN (10,13,28,18,26) GROUP BY fd.forum ORDER BY count(fd.id) DESC

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

Advanced Forum (hsuforum) Module Reports

All Ungraded Advanced Forums (hsuforum) w/ Link

(By: Ben Haensel )

Please note the following about this query:

This report is specific to the course in which it's run

SELECT u.firstname AS "First", u.lastname AS "Last", af.name AS "Forum", convert(FROM_UNIXTIME(afp.modified),datetime) as LastSubmission, concat('<a target="_blank" href="%%WWWROOT%%/mod/hsuforum/discuss.php?d=',afp.discussion,'">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id

JOIN prefix_hsuforum AS af ON c.id = af.course JOIN prefix_hsuforum_discussions AS afd ON af.id = afd.forum JOIN prefix_hsuforum_posts AS afp ON afd.id = afp.discussion JOIN prefix_user AS u ON u.id = afp.userid

WHERE cm.instance = af.id AND ue.userid = u.id AND ra.userid = u.id AND c.id = '%%COURSEID%%' AND cm.module = '24' AND af.gradetype = '2' AND afp.id not in (select r.itemid from prefix_rating AS r where r.itemid = afp.id and r.component = 'mod_hsuforum') AND ra.roleid = '5'

order by LastSubmission

Journal Module Reports

All Ungraded Journals w/ Link

(By: Ben Haensel )

Please note the following about this query:

This report is specific to the course in which it's run

SELECT u.firstname AS "First", u.lastname AS "Last", j.name AS "Journal", convert(FROM_UNIXTIME(je.modified),datetime) as LastSubmission, concat('<a target="_blank" href="%%WWWROOT%%/mod/journal/report.php?id=',cm.id,'">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_journal AS j ON c.id = j.course JOIN prefix_journal_entries AS je ON j.id = je.journal JOIN prefix_user AS u ON u.id = je.userid

WHERE cm.instance = j.id AND ue.userid = u.id AND c.id = '%%COURSEID%%' AND cm.module = '29' AND j.grade not like '0' AND je.rating is null

Order by LastSubmission

Lesson Module Reports

All Ungraded Lessons w/ Link

(By: Ben Haensel )

Please note the following about this query:

This report is specific to the course in which it's run
Requirement: The semicolon restriction must be removed from the Configurable Reports block.

SELECT u.firstname AS "First", u.lastname AS "Last", l.name AS "Lesson", convert(FROM_UNIXTIME(la.timeseen),datetime) as LastSubmission, concat('<a target="_blank" href="%%WWWROOT%%/mod/lesson/essay.php?id=',cm.id,'&mode=grade&attemptid=',la.id,'">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_lesson AS l ON c.id = l.course JOIN prefix_lesson_attempts AS la ON l.id = la.lessonid JOIN prefix_user AS u ON u.id = la.userid

WHERE cm.instance = l.id AND ue.userid = u.id AND c.id = '%%COURSEID%%' AND cm.module = '13' AND l.grade not like '0' AND la.useranswer like '%graded%' AND la.useranswer not like '%graded";i:1%'

order by LastSubmission

Quiz Module Reports

All Ungraded Quizzes w/ Link

(By: Ben Haensel )

Please note the following about this query:

This report is specific to the course in which it's run.
Requirement: The "Manual Grading by Student" plugin must be installed for URL string used which points to grade a specific user's essay responses.

SELECT u.firstname AS "First", u.lastname AS "Last", q.name AS "Quiz", convert(FROM_UNIXTIME(qa.timefinish),datetime) as LastSubmission,

concat('<a target="_blank" href="%%WWWROOT%%/mod/quiz/report.php?id=',cm.id,'&mode=gradingstudents&usageid=',qa.uniqueid,'&slots=',(Select group_concat(qua.slot SEPARATOR '%2C') from prefix_question_attempts as qua WHERE qua.questionusageid = qa.uniqueid AND qua.behaviour = 'manualgraded'),'&grade=needsgrading','">Grade</a>') AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id

JOIN prefix_quiz AS q ON c.id = q.course JOIN prefix_quiz_attempts AS qa ON q.id = qa.quiz JOIN prefix_user AS u ON u.id = qa.userid

WHERE cm.instance = q.id AND ue.userid = u.id AND cm.module = '16' AND c.id = '%%COURSEID%%' AND qa.timefinish > 0 AND qa.sumgrades is NULL

order by LastSubmission

Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes

SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,

               co.fullname AS course_fullname, co.shortname AS course_shortname,
               qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
               u.firstname, u.lastname, u.email,

FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND

               qu.course = co.id AND
               co.id = ct.instanceid AND
               ra.roleid = re.id AND
               re.name = 'Teacher' AND
               ra.contextid = ct.id AND
               ra.userid = u.id

SELECT Count('x') As NumOfStudents

                               FROM prefix_role_assignments a
                               JOIN prefix_user u ON userid = u.id
                               WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)

Number of Quizes per Course

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

List all MultiAnswer (Cloze) Questions

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'

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

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

List Questions in each Quiz

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

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

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.

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

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.

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

Badges Leaderboard

A simple list of usernames and how many badges they have earned overall.

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

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.

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

Cohorts by user

How to get a list of all users and which cohorts they belong to.

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

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

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

Teacher Grading Reports

Grade Everything w/ Links

(By: Ben Haensel )

Please note the following about this query:

This report is designed to grade assign, forum, hsuforum, journal, lesson, and quiz mods.
This report is specific to the USER that runs the report.
This report checks for any ungraded activities in all of the courses a user is enrolled as an editing teacher.
This report takes resubmitted Assign into account.
Requirement: The Assign mod must be updated (see github)to use a URL string without the rownum logic.
Requirement: For Lessons grading output, the semicolon restriction must be removed from the Configurable Reports block.
Requirement: For Quiz grading output, the "Manual Grading by Student" plugin must be installed for URL string used which points to grade a specific users essay responses.

SELECT

c.fullname as Course, u.firstname AS "First", u.lastname AS "Last", case when gi.itemmodule = 'hsuforum' then "adv-forum" else gi.itemmodule end as AssessmentType, gi.itemname AS "AssessmentName",

Case when cm.module = '1' then convert(FROM_UNIXTIME(asb.timemodified),datetime) when cm.module = '16' then convert(FROM_UNIXTIME(qa.timefinish),datetime) when cm.module = '9' then convert(FROM_UNIXTIME(fp.modified),datetime) when cm.module = '24' then convert(FROM_UNIXTIME(afp.modified),datetime) when cm.module = '29' then convert(FROM_UNIXTIME(je.modified),datetime) when cm.module = '13' then convert(FROM_UNIXTIME(la.timeseen),datetime) END as LastSubmission,

Case when cm.module = 1 then (case when asb.timemodified > g.timemodified then 'resubmitted' else asb.status end) else end AS "AssignStatus",

Case when cm.module = '1' then concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id, '&userid=',asb.userid,'&action=grade','">Grade</a>') when cm.module = '16' then concat('<a target="_blank" href="%%WWWROOT%%/mod/quiz/report.php?id=',cm.id,'&mode=gradingstudents&usageid=',qa.uniqueid,'&slots=',(Select group_concat(qua.slot SEPARATOR '%2C') from prefix_question_attempts as qua WHERE qua.questionusageid = qa.uniqueid AND qua.behaviour = 'manualgraded'),'&grade=needsgrading','">Grade</a>') when cm.module = '9' then concat('<a target="_blank" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fp.discussion,'">Grade</a>') when cm.module = '24' then concat('<a target="_blank" href="%%WWWROOT%%/mod/hsuforum/discuss.php?d=',afp.discussion,'">Grade</a>') when cm.module = '29' then concat('<a target="_blank" href="%%WWWROOT%%/mod/journal/report.php?id=',cm.id,'">Grade</a>') when cm.module = '13' then concat('<a target="_blank" href="%%WWWROOT%%/mod/lesson/essay.php?id=',cm.id,'&mode=grade&attemptid=',la.id,'">Grade</a>') END AS "GradeLink"

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON c.id = cm.course JOIN prefix_enrol AS en ON c.id = en.courseid JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id JOIN prefix_grade_items gi ON c.id = gi.courseid JOIN prefix_grade_grades AS g on gi.id = g.itemid JOIN prefix_user AS u ON u.id = ue.userid JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id left join prefix_role_assignments AS rax ON ra.userid = rax.userid and ra.contextid = rax.contextid and ra.id > rax.id

left JOIN prefix_assign_submission AS asb ON gi.iteminstance = asb.assignment and (g.finalgrade is NULL or asb.timemodified > g.timemodified) and asb.userid = u.id

left JOIN prefix_quiz_attempts AS qa ON gi.iteminstance = qa.quiz and qa.userid = u.id and qa.sumgrades is NULL and qa.timefinish > '0'

LEFT JOIN prefix_journal AS j ON c.id = j.course and cm.instance = j.id AND j.grade not like '0' left JOIN prefix_journal_entries AS je ON j.id = je.journal and cm.instance = je.journal and je.userid = ue.userid AND je.rating is null

LEFT JOIN prefix_lesson AS l ON c.id = l.course and cm.instance = l.id AND l.grade not like '0' LEFT JOIN prefix_lesson_attempts AS la ON l.id = la.lessonid and cm.instance = la.lessonid and la.userid = u.id AND la.useranswer like '%graded%' AND la.useranswer not like '%graded";i:1%'

LEFT JOIN prefix_forum AS f ON c.id = f.course and cm.instance = f.id and f.assessed not like '0' LEFT JOIN prefix_forum_discussions AS fd ON f.id = fd.forum and cm.instance = fd.forum and fd.userid = u.id LEFT JOIN prefix_forum_posts AS fp ON fd.id = fp.discussion and fp.userid = u.id

LEFT JOIN prefix_hsuforum AS af ON c.id = af.course and af.gradetype = '2' and cm.instance = af.id LEFT JOIN prefix_hsuforum_discussions AS afd ON af.id = afd.forum and cm.instance = afd.forum and afd.userid = u.id LEFT JOIN prefix_hsuforum_posts AS afp ON afd.id = afp.discussion and afp.userid = u.id

WHERE c.id in (SELECT cz.id FROM prefix_course AS cz JOIN prefix_enrol AS enz ON cz.id = enz.courseid JOIN prefix_user_enrolments AS uez ON uez.enrolid = enz.id JOIN prefix_user AS uz ON uz.id = uez.userid JOIN prefix_context AS ctxz ON cz.id = ctxz.instanceid JOIN prefix_role_assignments AS raz ON raz.contextid = ctxz.id WHERE cz.id = c.id AND raz.roleid = '3' AND raz.userid = uz.id AND raz.userid = uez.userid AND raz.userid = '%%USERID%%')

and rax.id is null AND g.userid = u.id AND ra.userid = u.id AND ra.userid = ue.userid AND gi.courseid = cm.course and gi.iteminstance = cm.instance

and ( (cm.module = '1' and asb.userid = u.id and (g.finalgrade is NULL or asb.timemodified > g.timemodified)) OR (cm.module = '16' and qa.userid = u.id and qa.sumgrades is NULL and qa.timefinish > '0') OR (cm.module = '9' and fp.userid = u.id and cm.instance = fd.forum AND f.assessed not like '0' AND not exists (select r.itemid from prefix_rating AS r where r.itemid = fp.id and r.component = 'mod_forum')) OR (cm.module = '24' and afp.userid = u.id and cm.instance = afd.forum AND af.gradetype = '2' AND not exists (select r.itemid from prefix_rating AS r where r.itemid = afp.id and r.component = 'mod_hsuforum')) OR (cm.module = '29' and je.userid = u.id AND j.grade not like '0' AND je.rating is null) OR (cm.module = '13' and la.userid = u.id AND l.grade not like '0' AND la.useranswer like '%graded%' AND la.useranswer not like '%graded";i:1%') )

ORDER BY u.id, AssessmentType, LastSubmission

Useful sub queries

All teachers in the course

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

@@ Line 1: / Line 1: @@
 {{Sitewide reports}}
 ==User and Role Report==
+===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>
@@ Line 79: / Line 95: @@
 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>
@@ Line 95: / Line 128: @@
 (By: [http://moodle.org/mod/forum/discuss.php?d=153059#p712837 Séverin Terrier] )
 <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>
@@ Line 117: / Line 152: @@
 ===Lists the users who have only logged into the site once===
 <code sql>
-SELECT id, username, firstname, laastname, idnumber
+SELECT id, username, firstname, lastname, idnumber
 FROM prefix_user
 WHERE prefix_user.deleted = 0
@@ Line 138: / Line 173: @@
 ===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>
 SELECT *
@@ Line 162: / Line 197: @@
 <code sql>
 SELECT u.firstname, u.lastname, u.email
-FROM mdl_role_assignments AS ra
+FROM prefix_role_assignments AS ra
-JOIN mdl_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
+JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50
-JOIN mdl_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
+JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%%
-JOIN mdl_user AS u ON u.id = ra.userid
+JOIN prefix_user AS u ON u.id = ra.userid
 </code>
@@ Line 195: / Line 230: @@
 For Moodle 2.2 , by  Isuru Madushanka Weerarathna
 <code sql>
-SELECT uenr.userid As User, IF(enr.courseid=enr.courseid ,'Y','N') As Enrolled,
+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 mdl_enrol As enr, mdl_user_enrolments AS uenr
+FROM prefix_enrol As enr, prefix_user_enrolments AS uenr
-WHERE enr.id = uenr.enrolid AND enr.status = uenr.statu
+WHERE enr.id = uenr.enrolid AND enr.status = uenr.status
 </code>
 === List of users with language===
@@ Line 213: / Line 249: @@
 <code sql>
 UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
+</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)===
 <code sql>
@@ Line 268: / Line 338: @@
 </code>
-==Course Reports==
+===Weekly Instructor Online Participation===
-===Most Active courses===
+Contributed by Elizabeth Dalton
+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.
 <code sql>
-SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
+SELECT
-FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
+c.shortname AS CourseID
-GROUP BY courseId
+, cc.name AS Category
-ORDER BY hits DESC
+, CONCAT(u.firstname ,' ',u.lastname) AS Instructor
-</code>
+, (SELECT COUNT( ra2.userid ) AS Users2 FROM prefix_role_assignments AS ra2
+JOIN prefix_context AS ctx2 ON ra2.contextid = ctx2.id
+WHERE ra2.roleid = 5 AND ctx2.instanceid = c.id) AS Students
+, c.startdate AS Course_Start_Date
+, c.visible AS Visible
+,  COUNT(l.id) AS Edits
-===Active courses, advanced===
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time)) - WEEK(FROM_UNIXTIME(c.startdate))<0,1,0)) AS BeforeTerm
-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
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=0,1,0)) AS Week1
-FROM prefix_role_assignments AS ra
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=1,1,0)) AS Week2
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=2,1,0)) AS Week3
-JOIN prefix_user AS u ON u.id = ra.userid
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=3,1,0)) AS Week4
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+, 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
-,CASE
+, SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))>=12,1,0)) AS AfterTerm
-  WHEN c.fullname LIKE '%תשע' THEN 'תשע'
-  WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
-  WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
-END AS Year
-,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
+, CONCAT('<a target="_new" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS Link
-,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+FROM prefix_user AS u
+JOIN prefix_role_assignments AS ra ON u.id = ra.userid
 JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+JOIN prefix_course AS c ON c.id = ctx.instanceid
+JOIN prefix_course_categories as cc ON c.category = cc.id
-FROM prefix_log l
+LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%"
-INNER JOIN prefix_course c ON l.course = c.id
-GROUP BY c.id
-HAVING Modules > 2
-ORDER BY Year DESC, hits DESC
-</code>
-===Count unique teachers with courses that use at least X module (Moodle19)===
+WHERE ra.roleid =3
-You can remove the outer "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQL query and get the list of the Teachers and Courses.
+AND ctx.instanceid = c.id
-<code sql>
+AND c.shortname LIKE :course
-SELECT COUNT(*)
+AND u.lastname LIKE :last_name
-FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher
-,(SELECT COUNT(*) FROM mdl_course_modules cm WHERE cm.course = c.id) AS Modules
+GROUP BY u.idnumber, c.id
-FROM mdl_course AS c
+HAVING students > 0
-JOIN mdl_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
+ORDER BY c.shortname
-JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
-JOIN mdl_user AS u ON u.id = ra.userid
-JOIN mdl_course_categories AS cc ON cc.id = c.category
-WHERE  ra.roleid = 3
-GROUP BY u.id
-HAVING Modules > 5) AS ActiveTeachers
 </code>
-===RESOURCE count for each COURSE===
+===Module activity (Hits) between dates===
 <code sql>
-SELECT COUNT(l.id) count, l.course, c.fullname coursename
+SELECT module, COUNT( * )
-FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
+FROM prefix_log AS l
-GROUP BY course
+WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN  '2012-10-01 00:00:00' AND  '2013-09-31 00:00:00')
-ORDER BY count DESC
+GROUP BY module
 </code>
-===Common resource types count for each Category (Moodle19)===
+===Module activity (Instances and Hits) for each academic year===
-Including sub-categories in total count.
 <code sql>
-SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
+SELECT name
-,(SELECT COUNT( * )
-FROM prefix_resource AS r
+,(SELECT COUNT(*)
-JOIN prefix_course AS c ON c.id = r.course
+FROM mdl_log AS l
-JOIN prefix_course_categories AS cc ON cc.id = c.category
+WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
-WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%'
+AND l.module = m.name AND l.action = 'add'
-) AS Links
+) AS "Added 2010"
-,(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
+FROM mdl_log AS l
-WHERE roleid = 5 AND module = 'resource' AND category = mcc.id
+WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00')
-) AS Hits
+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"
-FROM prefix_course_categories AS mcc
+,(SELECT COUNT(*)
-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 mdl_log AS l
-JOIN mdl_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
+WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00')
-JOIN mdl_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id
+AND l.module = m.name
-JOIN mdl_course AS c ON c.id = l.course
+) AS "Used 2011"
-JOIN mdl_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(*)
+,(SELECT COUNT(*)
-FROM mdl_url AS u
+FROM mdl_log AS l
-JOIN mdl_course AS c ON c.id = u.course
+WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
-JOIN mdl_course_categories AS cc ON cc.id = c.category
+AND l.module = m.name AND l.action = 'add'
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
+) AS "Added 2012"
-) AS URLs,
-(SELECT COUNT(*)
+,(SELECT COUNT(*)
-FROM mdl_folder AS f
+FROM mdl_log AS l
-JOIN mdl_course AS c ON c.id = f.course
+WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00')
-JOIN mdl_course_categories AS cc ON cc.id = c.category
+AND l.module = m.name
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
+) AS "Used 2012"
-) AS FOLDERs,
-(SELECT COUNT(*)
+FROM mdl_modules AS m
-FROM mdl_page AS p
+</code>
-JOIN mdl_course AS c ON c.id = p.course
-JOIN mdl_course_categories AS cc ON cc.id = c.category
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
-) AS PAGEs,
-(SELECT COUNT(*)
+==Course Reports==
-FROM mdl_book AS b
+===Most Active courses===
-JOIN mdl_course AS c ON c.id = b.course
+<code sql>
-JOIN mdl_course_categories AS cc ON cc.id = c.category
+SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
+FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
-) AS BOOKs,
+GROUP BY courseId
+ORDER BY hits DESC
-(SELECT COUNT(*)
-FROM mdl_label AS l
-JOIN mdl_course AS c ON c.id = l.course
-JOIN mdl_course_categories AS cc ON cc.id = c.category
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
-) AS LABELs,
-(SELECT COUNT(*)
-FROM mdl_tab AS t
-JOIN mdl_course AS c ON c.id = t.course
-JOIN mdl_course_categories AS cc ON cc.id = c.category
-WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
-) AS TABs
-FROM mdl_course_categories AS mcc
-ORDER BY mcc.path
 </code>
-===Detailed Resource COUNT by Teacher in each course===
+===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
-Including (optional) filter by: year, semester and course id.
+,(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
-<code sql>
+,CASE
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
+  WHEN c.fullname LIKE '%תשע' THEN 'תשע'
-, c.id
+  WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
-,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+  WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
-  FROM prefix_role_assignments AS ra
+END AS Year
-  JOIN prefix_user AS u ON ra.userid = u.id
-  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
-  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS 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 = 5 AND ctx.instanceid = c.id) AS Students
-, (CASE
+FROM prefix_log l
-WHEN c.fullname LIKE '%תשעב%' THEN '2012'
+INNER JOIN prefix_course c ON l.course = c.id
-WHEN c.fullname LIKE '%תשעא%' THEN '2011'
+GROUP BY c.id
-END ) as Year
+HAVING Modules > 2
-, (CASE
+ORDER BY Year DESC, hits DESC
-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>
-===Courses that are defined as using GROUPs===
+===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 concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+SELECT COUNT(*)
-,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
+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_groups g WHERE g.courseid = c.id) Groups
+,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules
- FROM `prefix_course` AS c
+FROM prefix_course AS c
-WHERE groupmode > 0
+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>
-===Courses with Groups===
+===RESOURCE count for each COURSE===
-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
+SELECT COUNT(l.id) count, l.course, c.fullname coursename
-FROM prefix_course AS c
+FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
-JOIN prefix_groups AS g ON c.id = g.courseid
+GROUP BY course
-WHERE c.groupmode > 0
+ORDER BY count DESC
 </code>
-===Groups in course with member list===
+===Common resource types count for each Category (Moodle19)===
+Including sub-categories in total count.
-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
+SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category
-FROM prefix_course AS c
+,(SELECT COUNT( * )
-JOIN prefix_groups AS g
+FROM prefix_resource AS r
-JOIN prefix_groups_members AS m ON g.id = m.groupid
+JOIN prefix_course AS c ON c.id = r.course
-JOIN prefix_user AS u ON m.userid = u.id
+JOIN prefix_course_categories AS cc ON cc.id = c.category
-WHERE c.id = #
+WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%'
-</code>
+) AS Links
-===Group Export===
+,(SELECT COUNT( * )
+FROM prefix_resource AS r
-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.
+JOIN prefix_course AS c ON c.id = r.course
+JOIN prefix_course_categories AS cc ON cc.id = c.category
-<code sql>
+WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference NOT LIKE 'http://%'
-SELECT g.name AS groupname, g.description, g.enrolmentkey
+) AS Files
-FROM prefix_groups AS g
-JOIN prefix_course as c ON g.courseid = c.id
+,(SELECT COUNT( * )
-WHERE c.id = #
+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:
-===List all Courses in and below a certain category===
-Use this SQL code to retrieve all courses that exist in or under a set category.
-$s should be the id of the category you want to know about...
 <code sql>
-SELECT prefix_course. * , prefix_course_categories. *
+CREATE VIEW stats_log_context_role_course AS
-FROM prefix_course, prefix_course_categories
+SELECT l.course, c.category, cc.path, l.module, l.action, ra.userid, ra.roleid
-WHERE prefix_course.category = prefix_course_categories.id
+FROM prefix_log AS l
-AND (
+JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50
-prefix_course_categories.path LIKE '/$s/%'
+JOIN prefix_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id
-OR prefix_course_categories.path LIKE '/$s'
+JOIN prefix_course AS c ON c.id = l.course
-)
+JOIN prefix_course_categories AS cc ON cc.id = c.category
 </code>
-===List all Categories in one level below a certain category===
+Same query but for Moodle2+
-Use this PHP code to retrieve a list of all categories below a certain category.
+<code sql>
+SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category,
+mcc.path,
-$s should be the id of the top level category you are interested in.
+(SELECT COUNT(*)
-<code php>
+FROM prefix_url AS u
-<?php
+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,
-require_once('./config.php');
+(SELECT COUNT(*)
+FROM prefix_folder AS f
-$parent_id = $s;
+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,
-$categories= array();
+(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,
-$categories = get_categories($parent_id);
+(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,
-echo '<ol>';
+(SELECT COUNT(*)
-foreach ($categories as $category)
+FROM prefix_label AS l
-        {
+JOIN prefix_course AS c ON c.id = l.course
-        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
+JOIN prefix_course_categories AS cc ON cc.id = c.category
-        }
+WHERE cc.path LIKE CONCAT( '%/', mccid, '%' )
-echo '</ol>';
+) AS LABELs,
-?>
+(SELECT COUNT(*)
-</code>
+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
-===Blog activity per Course (not including VIEW)===
+FROM prefix_course_categories AS mcc
-Filter activity logging to some specific Course Categories!
+ORDER BY mcc.path
-+ link course name to actual course (for quick reference)
-(you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
-<code sql>
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
-,m.name ,count(cm.id) as counter
-,(SELECT Count( ra.userid ) AS Users
-FROM prefix_role_assignments AS ra
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE ra.roleid = 5
-AND ctx.instanceid = c.id
-) AS Students
-, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
-FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id
-WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
-GROUP BY cm.course,cm.module order by counter desc
 </code>
-===All Courses which uploaded a Syllabus file===
+===Detailed Resource COUNT by Teacher in each course===
-+ under specific Category
-+ show first Teacher in that course
+Including (optional) filter by: year, semester and course id.
-+ link Course's fullname to actual course
 <code sql>
-SELECT
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID
-concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+, c.id
-,c.shortname,r.name
+,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
-,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
+  FROM prefix_role_assignments AS ra
-FROM prefix_role_assignments AS ra
+  JOIN prefix_user AS u ON ra.userid = u.id
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
-JOIN prefix_user as u ON u.id = ra.userid
+  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
-FROM prefix_resource as r
+, (CASE
-JOIN prefix_course as c ON r.course = c.id
+WHEN c.fullname LIKE '%תשעב%' THEN '2012'
-WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )
+WHEN c.fullname LIKE '%תשעא%' THEN '2011'
-AND c.category IN (10,18,26,13,28)
+END ) as Year
-</code>
+, (CASE
+WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A'
-===Site-wide completed SCORM activities by Course name===
+WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B'
-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.
+WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C'
-<code sql>
+END ) as Semester
-SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
+,COUNT(c.id) AS Total
-FROM prefix_scorm_scoes_track AS st
+,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs
-JOIN prefix_user AS u ON st.userid=u.id
+,(SELECT count(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs
-JOIN prefix_scorm AS sc ON sc.id=st.scormid
-JOIN prefix_course AS c ON c.id=sc.course
+FROM `prefix_resource` as r
-WHERE st.value='completed'
+JOIN `prefix_course` AS c on c.id = r.course
-ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
+#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>
-==Grade and Course Completion Reports==
+===Courses that are defined as using GROUPs===
-===Site-Wide Grade Report with All Items===
-Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports
 <code sql>
-SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
+SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
-c.fullname AS 'Course',
+,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules
-cc.name AS 'Category',
+,(SELECT count(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups
+ FROM `prefix_course` AS c
+WHERE groupmode > 0
+</code>
-CASE
+===Courses with Groups===
-  WHEN gi.itemtype = 'course'
-  THEN c.fullname + ' Course Total'
-  ELSE gi.itemname
-END AS 'Item Name',
-ROUND(gg.finalgrade,2) AS Grade,
+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.
-DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
+<code sql>
+SELECT c.shortname, g.name, c.groupmode
 FROM prefix_course AS c
-JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+JOIN prefix_groups AS g ON c.id = g.courseid
-JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+WHERE c.groupmode > 0
-JOIN prefix_user AS u ON u.id = ra.userid
+</code>
-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
+===Groups in course with member list===
-ORDER BY lastname
-</code>
+List the groups in a course (replace the # by the course id number) with the members of each group.
-For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD. Replace the line
-<code>
-DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
-</code>
-with
-<code>
-DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECOND) AS Time
-</code>
-===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
 <code sql>
-SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name',
+SELECT c.shortname, g.name AS Groupname, u.username
-cc.name AS 'Category',
+FROM prefix_course AS c
-CASE
+JOIN prefix_groups AS g ON g.courseid = c.id
-  WHEN gi.itemtype = 'course'
+JOIN prefix_groups_members AS m ON g.id = m.groupid
-  THEN c.fullname + ' Course Total'
+JOIN prefix_user AS u ON m.userid = u.id
-  ELSE gi.itemname
+WHERE c.id = #
-END AS 'Item Name',
+</code>
-ROUND(gg.finalgrade,2) AS Grade,
+===Group Export===
-DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
-FROM prefix_course AS c
+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.
-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'
+<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>
-ORDER BY lastname
+===List all Courses in and below a certain category===
-</code>
+Use this SQL code to retrieve all courses that exist in or under a set category.
-For MySQL users:
+$s should be the id of the category you want to know about...
 <code sql>
-SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name',
+SELECT prefix_course. * , prefix_course_categories. *
-c.fullname AS 'Course',
+FROM prefix_course, prefix_course_categories
-cc.name AS 'Category',
+WHERE prefix_course.category = prefix_course_categories.id
-CASE
+AND (
-  WHEN gi.itemtype = 'course'
+prefix_course_categories.path LIKE '/$s/%'
-  THEN CONCAT(c.fullname, ' - Total')
+OR prefix_course_categories.path LIKE '/$s'
-  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>
-===Learner report by Learner with grades===
+===List all Categories in one level below a certain category===
-Which Learners in which course and what are the grades
+Use this PHP code to retrieve a list of all categories below a certain category.
-<code sql>
-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
+$s should be the id of the top level category you are interested in.
+<code php>
+<?php
+require_once('./config.php');
-FROM prefix_course AS c
+$parent_id = $s;
-JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
-JOIN prefix_user AS u ON u.id = ra.userid
-JOIN prefix_grade_grades AS gg ON gg.userid = u.id
-JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
-JOIN prefix_course_categories AS cc ON cc.id = c.category
-WHERE  gi.courseid = c.id and gi.itemname != 'Attendance'
-ORDER BY `Name` ASC
-</code>
-===User Course Completion===
+$categories= array();
-A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.
+$categories = get_categories($parent_id);
-<code sql>
+echo '<ol>';
-SELECT u.username, c.shortname,
+foreach ($categories as $category)
-DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
+        {
-),'%Y-%m-%d')
+        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
-AS completed
+        }
-FROM prefix_course_completions AS p
+echo '</ol>';
-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===
+===Blog activity per Course (not including VIEW)===
+Filter activity logging to some specific Course Categories!
-A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
++ link course name to actual course (for quick reference)
+(you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
 <code sql>
-SELECT u.username AS user,
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
-c.shortname AS course,
+,m.name ,count(cm.id) as counter
-DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
+,(SELECT Count( ra.userid ) AS Users
-CASE
+FROM prefix_role_assignments AS ra
-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"
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-ELSE "All"
+WHERE ra.roleid = 5
-END AS aggregation,
+AND ctx.instanceid = c.id
-CASE
+) AS Students
-WHEN p.criteriatype = 1 THEN "Self"
+, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
-WHEN p.criteriatype = 2 THEN "By Date"
+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
-WHEN p.criteriatype = 3 THEN "Unenrol Status"
+WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
-WHEN p.criteriatype = 4 THEN "Activity"
+GROUP BY cm.course,cm.module order by counter desc
-WHEN p.criteriatype = 5 THEN "Duration"
+</code>
-WHEN p.criteriatype = 6 THEN "Course Grade"
-WHEN p.criteriatype = 7 THEN "Approve by Role"
+===Student's posts content in all course blogs (oublog)===
-WHEN p.criteriatype = 8 THEN "Previous Course"
+<code sql>
-END AS criteriatype,
+SELECT
-CASE
+b.name
-WHEN p.criteriatype = 1 THEN "*"
+,op.title
-WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
+,op.message
-WHEN p.criteriatype = 3 THEN t.unenroled
+,( SELECT CONCAT(u.firstname, ' ',u.lastname) FROM prefix_user AS u WHERE u.id = oi.userid) AS "Username"
-WHEN p.criteriatype = 4 THEN
-CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
+FROM prefix_oublog_posts AS op
-WHEN p.criteriatype = 5 THEN p.enrolperiod
+JOIN prefix_oublog_instances AS oi ON oi.id = op.oubloginstancesid
-WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2))
+JOIN prefix_oublog as b ON b.id = oi.oublogid
-WHEN p.criteriatype = 7 THEN p.role
+JOIN prefix_course AS c ON b.course = c.id
-WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance)
-END AS criteriadetail
+WHERE c.id = %%COURSEID%%
-FROM prefix_course_completion_crit_compl AS t
+</code>
-JOIN prefix_user AS u ON t.userid = u.id
-JOIN prefix_course AS c ON t.course = c.id
+===All Courses which uploaded a Syllabus file===
-JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id
++ under specific Category
++ show first Teacher in that course
-</code>
++ link Course's fullname to actual course
-===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
-SELECT c.shortname AS Course,
+concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
-CASE
+,c.shortname,r.name
-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"
+,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
-ELSE "Any"
+FROM prefix_role_assignments AS ra
-END AS Course_Aggregation,
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-CASE
+JOIN prefix_user as u ON u.id = ra.userid
-WHEN t.criteriatype = 1 THEN "Self completion"
+WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
-WHEN t.criteriatype = 2 THEN "Date done by"
+FROM prefix_resource as r
-WHEN t.criteriatype = 3 THEN "Unenrolement"
+JOIN prefix_course as c ON r.course = c.id
-WHEN t.criteriatype = 4 THEN "Activity completion"
+WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' )
-WHEN t.criteriatype = 5 THEN "Duration in days"
+AND c.category IN (10,18,26,13,28)
-WHEN t.criteriatype = 6 THEN "Final grade"
+</code>
-WHEN t.criteriatype = 7 THEN "Approve by role"
-WHEN t.criteriatype = 8 THEN "Previous course"
+===Site-wide completed SCORM activities by Course name===
-END AS Criteria_type,
+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.
-CASE
+<code sql>
-WHEN t.criteriatype = 1 THEN "On"
+SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
-WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
+FROM prefix_scorm_scoes_track AS st
-WHEN t.criteriatype = 3 THEN "On"
+JOIN prefix_user AS u ON st.userid=u.id
-WHEN t.criteriatype = 4 THEN
+JOIN prefix_scorm AS sc ON sc.id=st.scormid
-CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
+JOIN prefix_course AS c ON c.id=sc.course
-WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
+WHERE st.value='completed'
-WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
+ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
-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>
-==Activity Module Reports==
+==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"
-===How many SCORM activities are used in each Course===
+,(SELECT COUNT(*)
-<code sql>
+FROM prefix_log AS l
-SELECT cm.course,c.fullname ,m.name
+JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
-,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
+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"
-FROM `prefix_course_modules` as cm
-  JOIN prefix_modules as m ON cm.module=m.id
+,(SELECT COUNT(*)
-  JOIN prefix_course as c ON cm.course = c.id
+FROM prefix_log AS l
-WHERE m.name LIKE '%scorm%'
+JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50
-GROUP BY cm.course,cm.module
+JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3
-ORDER BY count(cm.id) desc
+WHERE l.course = cm.course AND l.module = m.name) AS "Teachers HITs"
-</code>
-===Detailed ACTIONs for each MODULE===
+FROM mdl_course_modules AS cm
-<code sql>
+JOIN mdl_modules AS m on m.id = cm.module
-SELECT module,action,count(id) as counter
+WHERE cm.course = '%%COURSEID%%'
-FROM prefix_log
+GROUP BY cm.module
-GROUP BY module,action
-ORDER BY module,counter desc
 </code>
-===Most popular ACTIVITY===
+==Grade and Course Completion Reports==
+===Site-Wide Grade Report with All Items===
+Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports
 <code sql>
-SELECT COUNT(l.id) hits, module
+SELECT u.firstname AS 'First' , u.lastname AS 'Last',
-FROM prefix_log l
+u.firstname + ' ' + u.lastname AS 'Display Name',
-WHERE module != 'login' AND module != 'course' AND module != 'role'
+c.fullname AS 'Course',
-GROUP BY module
+cc.name AS 'Category',
-ORDER BY hits DESC
-</code>
-===System wide use of ACTIVITIES and RESOURCES===
+CASE
-<code sql>
+  WHEN gi.itemtype = 'course'
-SELECT count( cm.id ) AS counter, m.name
+  THEN c.fullname + ' Course Total'
-FROM `prefix_course_modules` AS cm
+  ELSE gi.itemname
-JOIN prefix_modules AS m ON cm.module = m.id
+END AS 'Item Name',
-GROUP BY cm.module
-ORDER BY counter DESC
-</code>
-===LOG file ACTIONS per MODULE per COURSE (IDs)===
+ROUND(gg.finalgrade,2) AS Grade,
-<code sql>
+DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
-select course,module,action,count(action) as summa from prefix_log
-where action <> 'new'
-group by course,action,module
-order by course,module,action
-</code>
-===System Wide usage count of various course Activities===
+FROM prefix_course AS c
-(Tested and works fine in Moodle 2.x)
+JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-Like: Forum, Wiki, Blog, Assignment, Database,
+JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
-#Within specific category
-#Teacher name in course
-<code sql>
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
-,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
-FROM prefix_role_assignments AS ra
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 JOIN prefix_user AS u ON u.id = ra.userid
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+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
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+WHERE  gi.courseid = c.id
-JOIN prefix_modules AS m ON cm.module = m.id
+ORDER BY lastname
-WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
+</code>
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+For MySQL users, you'll need to use the MySQL FROM_UNIXTIME function instead of DATEADD. Replace the line:
-JOIN prefix_modules AS m ON cm.module = m.id
+<code>
-WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
+DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
+</code>
+with:
+<code>
+FROM_UNIXTIME(gg.timemodified) AS Time
+</code>
+And:
+<code>
+u.firstname + ' ' + u.lastname AS 'Display Name',
+</code>
+with:
+<code>
+CONCAT(u.firstname,' ',u.lastname) AS 'Display Name',
+</code>
-,(SELECT count( m.name ) AS count FROM
+===Site-Wide Grade Report with Just Course Totals===
-prefix_course_modules AS cm
+A second site-wide grade report for all students that just shows course totals. Works with ad-hoc reports or Configurable Reports
-JOIN prefix_modules AS m ON cm.module = m.id
+<code sql>
-WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
+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',
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+ROUND(gg.finalgrade,2) AS Grade,
-JOIN prefix_modules AS m ON cm.module = m.id
+DATEADD(ss,gg.timemodified,'1970-01-01') AS Time
-WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+FROM prefix_course AS c
-JOIN prefix_modules AS m ON cm.module = m.id
+JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
+JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+JOIN prefix_user AS u ON u.id = ra.userid
-,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+JOIN prefix_grade_grades AS gg ON gg.userid = u.id
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
-WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+JOIN prefix_course_categories as cc ON cc.id = c.category
+WHERE  gi.courseid = c.id AND gi.itemtype = 'course'
-FROM prefix_course AS c
+ORDER BY lastname
-WHERE c.category IN ( 18)
-ORDER BY Wikis DESC,Blogs DESC, Forums DESC
 </code>
-===Course wiki usage/activity over the last 6 semesters===
+For MySQL users:
 <code sql>
-SELECT "Courses with Wikis"
+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',
-,(SELECT count( m.name ) AS count FROM
+ROUND(gg.finalgrade,2) AS Grade,
-prefix_course_modules AS cm
+FROM_UNIXTIME(gg.timemodified) AS TIME
-JOIN prefix_modules AS m ON cm.module = m.id
-JOIN prefix_course AS c ON c.id = cm.course
+FROM prefix_course AS c
-WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-  and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester A%') AS '2010 <br/> Semester A'
+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>
-,(SELECT count( m.name ) AS count FROM
+===Learner report by Learner with grades===
-prefix_course_modules AS cm
+Which Learners in which course and what are the grades
-JOIN prefix_modules AS m ON cm.module = m.id
+<code sql>
-JOIN prefix_course AS c ON c.id = cm.course
+SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
-WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+CASE WHEN gi.itemtype = 'Course'
- and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester B%') AS '2010 <br/> Semester B'
+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
-,(SELECT count( m.name ) AS count FROM
+FROM prefix_course AS c
-prefix_course_modules AS cm
+JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-JOIN prefix_modules AS m ON cm.module = m.id
+JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
-JOIN prefix_course AS c ON c.id = cm.course
+JOIN prefix_user AS u ON u.id = ra.userid
-WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+JOIN prefix_grade_grades AS gg ON gg.userid = u.id
-  and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעא <br/> סמסטר א'
+JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
+JOIN prefix_course_categories AS cc ON cc.id = c.category
+WHERE  gi.courseid = c.id and gi.itemname != 'Attendance'
+ORDER BY `Name` ASC
+</code>
-,(SELECT count( m.name ) AS count FROM
+===User Course Completion===
-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
+A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.
-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
+<code sql>
-prefix_course_modules AS cm
+SELECT u.username, c.shortname,
-JOIN prefix_modules AS m ON cm.module = m.id
+DATE_FORMAT(FROM_UNIXTIME(p.timecompleted
-JOIN prefix_course AS c ON c.id = cm.course
+),'%Y-%m-%d')
-WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+AS completed
- and c.fullname LIKE CONCAT('%','תשעב','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעב <br/> סמסטר ב'
+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>
-,(SELECT count( m.name ) AS count FROM
+===User Course Completion with Criteria===
-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
+A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
-prefix_course_modules AS cm
-JOIN prefix_modules AS m ON cm.module = m.id
-JOIN prefix_course AS c ON c.id = cm.course
-WHERE cm.course = c.id AND m.name LIKE '%wiki%'
- and c.fullname LIKE CONCAT('%','תשעג','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעג <br/> סמסטר ב'
-</code>
-===Detailed WIKI activity (per wiki per course)===
-Including Number of Students in course (for reference)
 <code sql>
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
+SELECT u.username AS user,
-,(SELECT Count( ra.userid ) AS Users
+c.shortname AS course,
-FROM prefix_role_assignments AS ra
+DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed,
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+CASE
-WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students
+WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a  WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any"
-,m.name
+ELSE "All"
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
+END AS aggregation,
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE
+CASE
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT
+WHEN p.criteriatype = 1 THEN "Self"
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD'
+WHEN p.criteriatype = 2 THEN "By Date"
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT
+WHEN p.criteriatype = 3 THEN "Unenrol Status"
-, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)'
+WHEN p.criteriatype = 4 THEN "Activity"
-FROM `prefix_course_modules` as cm
+WHEN p.criteriatype = 5 THEN "Duration"
-JOIN prefix_modules as m ON cm.module=m.id
+WHEN p.criteriatype = 6 THEN "Course Grade"
-JOIN prefix_course as c ON cm.course = c.id
+WHEN p.criteriatype = 7 THEN "Approve by Role"
-WHERE m.name LIKE '%wiki%'
+WHEN p.criteriatype = 8 THEN "Previous Course"
-GROUP BY cm.course,cm.module
+END AS criteriatype,
-ORDER BY 'All (NO View)' DESC
+CASE
-</code>
+WHEN p.criteriatype = 1 THEN "*"
+WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d')
-===Wiki usage, system wide===
+WHEN p.criteriatype = 3 THEN t.unenroled
-(you can filter the output by selecting some specific course categories : "WHERE c.category IN ( 8,13,15)")
+WHEN p.criteriatype = 4 THEN
+CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>')
-<code sql>
+WHEN p.criteriatype = 5 THEN p.enrolperiod
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+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
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+</code>
-JOIN prefix_modules AS m ON cm.module = m.id
-WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
-,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity<br/>ALL'
+===Courses with Completion Enabled and their settings===
-,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity<br/>ADD'
+List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.
-,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity<br/>EDIT'
+<code sql>
-,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity<br/>ANNOTATE'
+SELECT c.shortname AS Course,
+CASE
-,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity<br/>Comments'
+WHEN (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"
-,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+END AS Course_Aggregation,
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+CASE
-WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+WHEN t.criteriatype = 1 THEN "Self completion"
+WHEN t.criteriatype = 2 THEN "Date done by"
+WHEN t.criteriatype = 3 THEN "Unenrolement"
+WHEN t.criteriatype = 4 THEN "Activity completion"
+WHEN t.criteriatype = 5 THEN "Duration in days"
+WHEN t.criteriatype = 6 THEN "Final grade"
+WHEN t.criteriatype = 7 THEN "Approve by role"
+WHEN t.criteriatype = 8 THEN "Previous course"
+END AS Criteria_type,
+CASE
+WHEN t.criteriatype = 1 THEN "On"
+WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d')
+WHEN t.criteriatype = 3 THEN "On"
+WHEN t.criteriatype = 4 THEN
+CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>')
+WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400)
+WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2)
+WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role)
+WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance)
+END AS Criteria_detail
+FROM prefix_course_completion_criteria as t
+JOIN prefix_course AS c ON t.course = c.id
+WHERE c.enablecompletion = 1
+ORDER BY course
+</code>
+===Course Completion Report with custom dates===
-,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp
+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
-JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid
-WHERE ouw.course = c.id GROUP BY ouw.course  ) as OUWikiPages
+<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>
-,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
+===Scales used in activities===
-JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages
+<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_course AS c
+FROM prefix_grade_items AS gi
-WHERE c.category IN ( 8,13,15)
+JOIN prefix_course AS c ON c.id = gi.courseid
-HAVING Wikis > 0
+JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance
-ORDER BY 'WikiActivity<br/>ALL' DESC
+JOIN prefix_scale AS scale ON scale.id = gi.scaleid
+WHERE gi.scaleid IS NOT NULL
 </code>
-===Aggregated Teacher activity by "WEB2" Modules===
+==Activity Module Reports==
-(Tested and works fine in Moodle 2.x)
-The NV column shows activity without VIEW log activity
+===How many SCORM activities are used in each Course===
 <code sql>
-SELECT ra.userid, u.firstname,u.lastname
+SELECT cm.course,c.fullname ,m.name
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki
+,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum
+FROM `prefix_course_modules` as cm
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV
+  JOIN prefix_modules as m ON cm.module=m.id
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog
+  JOIN prefix_course as c ON cm.course = c.id
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV
+WHERE m.name LIKE '%scorm%'
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment
+GROUP BY cm.course,cm.module
-,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV
+ORDER BY count(cm.id) desc
-FROM prefix_role_assignments AS ra
-JOIN prefix_user AS u ON u.id = ra.userid
-WHERE ra.roleid = 3
-GROUP BY ra.userid
 </code>
-===List all the certificates issued, sort by variables in the custom profile fields===
+===Detailed ACTIONs for each MODULE===
-Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.
+<code sql>
+SELECT module,action,count(id) as counter
+FROM prefix_log
+GROUP BY module,action
+ORDER BY module,counter desc
+</code>
+===Most popular ACTIVITY===
 <code sql>
-SELECT
+SELECT COUNT(l.id) hits, module
-DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y/%m/%d' ) AS Date,
+FROM prefix_log l
-prefix_certificate_issues.classname AS Topic,
+WHERE module != 'login' AND module != 'course' AND module != 'role'
-prefix_certificate.name AS Certificate,
+GROUP BY module
-prefix_certificate_issues.studentname as Name,
+ORDER BY hits DESC
-prefix_user_info_data.data AS Units
-FROM
-prefix_certificate_issues
-INNER JOIN prefix_user_info_data
-on prefix_certificate_issues.userid = prefix_user_info_data.userid
-INNER JOIN prefix_certificate
-on prefix_certificate_issues.certificateid = prefix_certificate.id
-WHERE prefix_user_info_data.data='Unit 1'
-OR prefix_user_info_data.data='Unit 2'
-OR prefix_user_info_data.data='Unit 3'
-ORDER BY Units, Name, Topic ASC
 </code>
-===Counter Blog usage in Courses,system wide===
+===System wide use of ACTIVITIES and RESOURCES===
-What teachers in what courses, uses blogs and how many + student count in that course.
 <code sql>
+SELECT count( cm.id ) AS counter, m.name
+FROM `prefix_course_modules` AS cm
+JOIN prefix_modules AS m ON cm.module = m.id
+GROUP BY cm.module
+ORDER BY counter DESC
+</code>
-SELECT ( @counter := @counter+1) as counter,
+===LOG file ACTIONS per MODULE per COURSE (IDs)===
-concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+<code sql>
+select course,module,action,count(action) as summa from prefix_log
+where action <> 'new'
+group by course,action,module
+order by course,module,action
+</code>
-,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+===System Wide usage count of various course Activities===
-  FROM prefix_role_assignments AS ra
+(Tested and works fine in Moodle 2.x)
-  JOIN prefix_user AS u ON ra.userid = u.id
+Like: Forum, Wiki, Blog, Assignment, Database,
-  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+#Within specific category
-  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+#Teacher name in course
-,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+<code sql>
-JOIN prefix_modules AS m ON cm.module = m.id
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
-WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
-,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+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
+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_course AS c, (SELECT @counter := 0) as s_init
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
-WHERE c.category IN ( 8,13,15)
+JOIN prefix_modules AS m ON cm.module = m.id
-HAVING Blogs > 0
+WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
-ORDER BY Blogs DESC
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
-</code>
+JOIN prefix_modules AS m ON cm.module = m.id
+WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
-=== Elluminate (Blackboard Collaborate) - system wide usage===
+,(SELECT count( m.name ) AS count FROM
-<code sql>
+prefix_course_modules AS cm
-SELECT e.name As Session ,er.recordingsize
+JOIN prefix_modules AS m ON cm.module = m.id
-,c.fullname As Course
+WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
-,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
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
-JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid
+JOIN prefix_modules AS m ON cm.module = m.id
-JOIN prefix_course as c ON c.id = e.course
+WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
-JOIN prefix_user AS u ON u.id = e.creator
-ORDER BY er.recordingsize DESC
-</code>
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+JOIN prefix_modules AS m ON cm.module = m.id
+WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
-=== Choice ===
+,(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
-Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.
+FROM prefix_course AS c
+WHERE c.category IN ( 18)
+ORDER BY Wikis DESC,Blogs DESC, Forums DESC
+</code>
+===Course wiki usage/activity over the last 6 semesters===
 <code sql>
-SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
+SELECT "Courses with Wikis"
-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 Module Reports==
+,(SELECT count( m.name ) AS count FROM
-===All Ungraded Assignments===
+prefix_course_modules AS cm
-Returns all the submitted assignments that still need grading
+JOIN prefix_modules AS m ON cm.module = m.id
-<code sql>
+JOIN prefix_course AS c ON c.id = cm.course
-select
+WHERE cm.course = c.id AND m.name LIKE '%wiki%'
-u.firstname AS "First",
+ and c.fullname LIKE CONCAT('%','2010','%') and c.fullname LIKE '%Semester A%') AS '2010 <br/> Semester A'
-u.lastname AS "Last",
-c.fullname AS "Course",
-a.name AS "Assignment"
-from prefix_assignment_submissions as asb
+,(SELECT count( m.name ) AS count FROM
-join prefix_assignment as a ON a.id = asb.assignment
+prefix_course_modules AS cm
-join prefix_user as u ON u.id = asb.userid
+JOIN prefix_modules AS m ON cm.module = m.id
-join prefix_course as c ON c.id = a.course
+JOIN prefix_course AS c ON c.id = cm.course
-join prefix_course_modules as cm 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'
-where asb.grade < 0 and cm.instance = a.id
+,(SELECT count( m.name ) AS count FROM
-and cm.module = 1
+prefix_course_modules AS cm
+JOIN prefix_modules AS m ON cm.module = m.id
-order by c.fullname, a.name, u.lastname
+JOIN prefix_course AS c ON c.id = cm.course
-</code>
+WHERE cm.course = c.id AND m.name LIKE '%wiki%'
+ and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר א%') AS 'תשעא <br/> סמסטר א'
-===All Ungraded Assignments w/ Link===
+,(SELECT count( m.name ) AS count FROM
-Returns all the submitted assignments that still need grading, along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle.
+prefix_course_modules AS cm
-<code sql>
+JOIN prefix_modules AS m ON cm.module = m.id
-select
+JOIN prefix_course AS c ON c.id = cm.course
-u.firstname AS "First",
+WHERE cm.course = c.id AND m.name LIKE '%wiki%'
-u.lastname AS "Last",
+ and c.fullname LIKE CONCAT('%','תשעא','%') and c.fullname LIKE '%סמסטר ב%') AS 'תשעא <br/> סמסטר ב'
-c.fullname AS "Course",
-a.name AS "Assignment",
-'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
+,(SELECT count( m.name ) AS count FROM
-+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
+prefix_course_modules AS cm
-+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
+JOIN prefix_modules AS m ON cm.module = m.id
-AS "Assignmentlink"
+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/> סמסטר ב'
-from prefix_assignment_submissions as asb
+,(SELECT count( m.name ) AS count FROM
-join prefix_assignment as a ON a.id = asb.assignment
+prefix_course_modules AS cm
-join prefix_user as u ON u.id = asb.userid
+JOIN prefix_modules AS m ON cm.module = m.id
-join prefix_course as c ON c.id = a.course
+JOIN prefix_course AS c ON c.id = cm.course
-join prefix_course_modules as cm 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/> סמסטר א'
-where asb.grade < 0 and cm.instance = a.id and cm.module = 1
+,(SELECT count( m.name ) AS count FROM
+prefix_course_modules AS cm
-order by c.fullname, a.name, u.lastname
+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>
-===Assignments (and Quizzes) waiting to be graded===
+===Detailed WIKI activity (per wiki per course)===
-This report requires a YEAR filter to be added (Available when using the latest block/configurable_reports)
+Including Number of Students in course (for reference)
-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('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
+,(SELECT Count( ra.userid ) AS Users
-,(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 = 5 AND ctx.instanceid = c.id ) AS Students
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+,m.name
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
-,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%annotate%' ) as ANNOTATE
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%comment%' ) as COMMENT
-,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%add%' ) as 'A DD'
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%edit%' ) as EDIT
+, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action NOT LIKE '%view%' ) as 'All (NO View)'
+FROM `prefix_course_modules` as cm
+JOIN prefix_modules as m ON cm.module=m.id
+JOIN prefix_course as c ON cm.course = c.id
+WHERE m.name LIKE '%wiki%'
+GROUP BY cm.course,cm.module
+ORDER BY 'All (NO View)' DESC
+</code>
-,(SELECT COUNT(*)
+===Wiki usage, system wide===
-FROM prefix_course_modules cm
+(you can filter the output by selecting some specific course categories : "WHERE c.category IN ( 8,13,15)")
-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(*)
+<code sql>
-FROM prefix_quiz_attempts AS qa
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
-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(*)
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
-FROM prefix_quiz_attempts AS qa
+JOIN prefix_modules AS m ON cm.module = m.id
-JOIN prefix_quiz AS q ON q.id = qa.quiz
+WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
-WHERE q.course = c.id
-AND qa.timefinish > 0
-GROUP BY q.course) AS 'finished quiz attempts'
-,(SELECT Count( ra.userid ) AS Users
+,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%') AS 'WikiActivity<br/>ALL'
-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(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%add%' ) AS 'WikiActivity<br/>ADD'
-SELECT count(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_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%edit%' ) AS 'WikiActivity<br/>EDIT'
-SELECT count(*)
-FROM prefix_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(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%annotate%' ) AS 'WikiActivity<br/>ANNOTATE'
-,(
+,(SELECT count(*) FROM prefix_log as l WHERE l.course = c.id AND l.module LIKE '%wiki%' and l.action LIKE '%comments%' ) AS 'WikiActivity<br/>Comments'
-SELECT count(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 Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
-SELECT course, count(*) AS iOpenAssignments
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-FROM prefix_assignment AS a
+WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
-WHERE FROM_UNIXTIME(a.timedue) > NOW()
-GROUP BY a.course
+,(SELECT count(*) FROM prefix_ouwiki_pages as ouwp
-) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
+JOIN prefix_ouwiki as ouw ON ouw.id = ouwp.subwikiid
+WHERE ouw.course = c.id GROUP BY ouw.course  ) as OUWikiPages
+,(SELECT count( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
+JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) As NWikiPages
-WHERE 1=1
+FROM prefix_course AS c
-#AND c.fullname LIKE '%תשעג%'
+WHERE c.category IN ( 8,13,15)
-%%FILTER_YEARS:c.fullname%%
+HAVING Wikis > 0
-## You can enable the SEMESTER filter as well,
+ORDER BY 'WikiActivity<br/>ALL' DESC
-## by uncommenting the following line:
-## %%FILTER_SEMESTERS:c.fullname%%
-ORDER BY 'Open <br/>Assignments' DESC
 </code>
-===Who is using "Single File Upload" assignment===
+===Aggregated Teacher activity by "WEB2" Modules===
+(Tested and works fine in Moodle 2.x)
+The NV column shows activity without VIEW log activity
 <code sql>
-SELECT
+SELECT ra.userid, u.firstname,u.lastname
-concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%') AS Wiki
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%wiki%' AND l.action NOT LIKE '%view%') AS Wiki_NV
-,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%') AS Forum
-FROM prefix_role_assignments AS ra
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%forum%' AND l.action NOT LIKE '%view%') AS Forum_NV
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%') AS Blog
-JOIN prefix_user AS u ON u.id = ra.userid
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%blog%' AND l.action NOT LIKE '%view%') AS Blog_NV
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%') AS Assignment
+,(SELECT count(*) FROM prefix_log as l WHERE l.userid = u.id AND l.module LIKE '%assignment%' AND l.action NOT LIKE '%view%') AS Assignment_NV
+FROM prefix_role_assignments AS ra
+JOIN prefix_user AS u ON u.id = ra.userid
+WHERE ra.roleid = 3
+GROUP BY ra.userid
+</code>
-,ass.name as "Assignment Name"
+===List all the certificates issued, sort by variables in the custom profile fields===
+Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.
-FROM
+<code sql>
-prefix_assignment as ass
+SELECT
+DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y-%m-%d' ) AS Date,
+prefix_certificate_issues.classname AS Topic,
+prefix_certificate.name AS Certificate,
+prefix_certificate_issues.studentname as Name,
+prefix_user_info_data.data AS Units
+FROM
+prefix_certificate_issues
-JOIN
+INNER JOIN prefix_user_info_data
-prefix_course as c ON c.id = ass.course
+on prefix_certificate_issues.userid = prefix_user_info_data.userid
+INNER JOIN prefix_certificate
+on prefix_certificate_issues.certificateid = prefix_certificate.id
+WHERE prefix_user_info_data.data='Unit 1'
+OR prefix_user_info_data.data='Unit 2'
+OR prefix_user_info_data.data='Unit 3'
-WHERE `assignmenttype` LIKE 'uploadsingle'
+ORDER BY Units, Name, Topic ASC
 </code>
-==Resource Module Reports==
+===Counter Blog usage in Courses,system wide===
-===List "Recently uploaded files"===
+What teachers in what courses, uses blogs and how many + student count in that course.
-see what users are uploading
 <code sql>
-SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info
-FROM `prefix_log`
-WHERE `action` LIKE 'upload'
-ORDER BY `prefix_log`.`time`  DESC
-</code>
-===List Courses that loaded a specific file: "X"===
+SELECT ( @counter := @counter+1) as counter,
-Did the Teacher (probably) uploaded course's Syllabus ?
+concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
-<code sql>
-SELECT c.id, c.fullname  FROM `prefix_log` as l
+,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
-JOIN prefix_course as c ON c.id = l.course
+  FROM prefix_role_assignments AS ra
-WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
+  JOIN prefix_user AS u ON ra.userid = u.id
+  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+JOIN prefix_modules AS m ON cm.module = m.id
+WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
+,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+FROM prefix_course AS c, (SELECT @counter := 0) as s_init
+WHERE c.category IN ( 8,13,15)
+HAVING Blogs > 0
+ORDER BY Blogs DESC
 </code>
-===All resources that link to some specific external website===
+=== Elluminate (Blackboard Collaborate) - system wide usage===
-+ link to course
-+ who's the teacher
-+ link to external resource
 <code sql>
-SELECT
+SELECT e.name As Session ,er.recordingsize
-concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+,c.fullname As Course
-,c.shortname,r.name
+,u.firstname,u.lastname
-,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+,DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%d-%m-%Y') AS dTimeStart
-FROM prefix_role_assignments AS ra
+,concat('<a target="_new" href="%%WWWROOT%%/moodle/mod/elluminate/loadrecording.php?id=',er.id,'">Show</a>') AS RecordedSession
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-JOIN prefix_user AS u ON u.id = ra.userid
+FROM prefix_elluminate_recordings AS er
-WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid
-,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
+JOIN prefix_course as c ON c.id = e.course
-FROM prefix_resource AS r
+JOIN prefix_user AS u ON u.id = e.creator
-JOIN prefix_course AS c ON r.course = c.id
+ORDER BY er.recordingsize DESC
-WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'
 </code>
-==="Compose Web Page" RESOURCE count===
+=== 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 course,prefix_course.fullname, COUNT(*) AS Total
+SELECT c.shortname AS course, u.username, h.name as question, o.text AS answer
-FROM `prefix_resource`
+FROM prefix_choice AS h
-JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
+JOIN prefix_course AS c ON h.course = c.id
-WHERE type='html'
+JOIN prefix_choice_answers AS a ON h.id = a.choiceid
-GROUP BY course
+JOIN prefix_user AS u ON a.userid = u.id
+JOIN prefix_choice_options AS o ON a.optionid = o.id
 </code>
-===Resource count in courses===
+=== Assignment type usage in courses ===
-+ (First)Teacher name
-+ Where course is inside some specific Categories
 <code sql>
 SELECT
-COUNT(*) AS count
-,r.course
+CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments"
-,c.shortname shortname
-,c.fullname coursename
+,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments
-,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
-  FROM prefix_role_assignments AS ra
+,(SELECT COUNT(*)
-  JOIN prefix_user as u ON ra.userid = u.id
+FROM prefix_assign_plugin_config AS apc
-  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+JOIN prefix_assign AS iassign ON iassign.id = apc.assignment
-  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1'
-FROM prefix_resource r
+#GROUP BY apc.plugin
-JOIN prefix_course c ON r.course = c.id
+) AS "File Assignments"
-WHERE c.category IN (10,13,28,18,26)
-GROUP BY r.course
+,(SELECT COUNT(*)
-ORDER BY COUNT(*) DESC
+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>
-==Forum Module Reports==
+==Assign Module Reports==
-===print all User's post in course Forums===
+===All Ungraded Assign using rownum Ranking w/ Link===
-@@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.)
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+For the Ungraded Assign (mod_assign) query below to work, teaching staff must use the default Assign sort, or logout and log back in after sorting.  Otherwise, grade links will direct to the wrong user.
+*This report takes resubmitted Assign into account
+*This report is specific to the course in which it's run
+*Requirement: User must use the default Assign sort
 <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
+u.firstname AS "First",
-,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
+u.lastname AS "Last",
-,count(*) as Posts
+a.name AS "Assignment",
-,(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
+convert(FROM_UNIXTIME(asb.timemodified),datetime) as LastSubmission,
+case when asb.timemodified > g.timemodified
+then 'resubmitted'
+else asb.status end AS "Status",
+concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?rownum=',
-FROM prefix_forum_posts AS fp
+(select x.rank from
-JOIN prefix_user as u ON u.id = fp.userid
+(SELECT @curRank := @curRank + 1 AS rank, id
-JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
+from (select user2.id
-JOIN prefix_forum AS f ON f.id = fd.forum
+FROM prefix_user AS user2
-JOIN prefix_course as c ON c.id = fd.course
+INNER JOIN prefix_user_enrolments AS ue ON user2.id = ue.userid
-WHERE fd.course = '@@COURSEID@@'
+INNER JOIN prefix_enrol AS en ON ue.enrolid = en.id
-GROUP BY f.id,u.id
+INNER JOIN prefix_course AS course ON en.courseid = course.id
-ORDER BY u.id
+where course.id = '%%COURSEID%%') as G, (select @curRank:= -1) r
+order by id asc) as x
+where x.id = asb.userid),
+'&useridlist=',asb.userid,'&id=',cm.id,'&action=grade','">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON c.id = en.courseid
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_grade_items gi ON c.id = gi.courseid
+JOIN prefix_grade_grades AS g on gi.id = g.itemid
+JOIN prefix_assign AS a ON c.id = a.course
+JOIN prefix_assign_submission AS asb ON asb.assignment = a.id
+JOIN prefix_user AS u ON u.id = asb.userid
+WHERE c.id = '%%COURSEID%%'
+AND ue.userid = u.id
+AND cm.module = 1
+AND cm.instance = a.id
+AND gi.itemmodule = 'assign'
+AND gi.iteminstance = a.id
+AND g.userid = u.id
+AND (g.finalgrade is NULL or asb.timemodified > g.timemodified)
+ORDER BY LastSubmission
 </code>
-===FORUM use Count per COURSE -- not including NEWS Forum!===
+===All Ungraded Assign using modification for Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+For this version of the Ungraded Assign (mod_assign) query below to work, this [https://goo.gl/FXfDGa modification to the ROWNUM logic] must be implemented so that using ROWNUM in the concatenated URL string is not necessary. In BlueSky School's 2.7 Moodle instance, we have not experienced issues with this suggested change.  For more information regarding the rownum logic discussion, visit these Moodle Tracker pages:
+*[https://tracker.moodle.org/browse/MDL-42306 Grading assignments - row number logic]
+*[https://tracker.moodle.org/browse/MDL-44330 Assignment opens up the "wrong" student grading form]
+Please note the following about this query:
+*This report takes resubmitted Assign into account
+*This report is specific to the course in which it's run
+*Requirement: The Assign mod must be updated ([https://goo.gl/FXfDGa see github]) to use a URL string without the rownum logic
 <code sql>
-SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
+SELECT
-INNER JOIN prefix_course
+u.firstname AS "First",
-ON prefix_course.id = prefix_forum.course
+u.lastname AS "Last",
-WHERE NOT(prefix_forum.type = 'news')
+a.name AS "Assignment",
-GROUP BY prefix_forum.course
+convert(FROM_UNIXTIME(asb.timemodified),datetime) as LastSubmission,
-ORDER BY total desc
+case when asb.timemodified > g.timemodified
-</code>
+then 'resubmitted'
+else asb.status end AS "Status",
+concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id,
+'&userid=',asb.userid,'&action=grade','">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON c.id = en.courseid
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_grade_items gi ON c.id = gi.courseid
+JOIN prefix_grade_grades AS g on gi.id = g.itemid
+JOIN prefix_assign AS a ON c.id = a.course
+JOIN prefix_assign_submission AS asb ON asb.assignment = a.id
+JOIN prefix_user AS u ON u.id = asb.userid
+WHERE c.id = '%%COURSEID%%'
+AND ue.userid = u.id
+AND cm.module = 1
+AND cm.instance = a.id
+AND gi.itemmodule = 'assign'
+AND gi.iteminstance = a.id
+AND g.userid = u.id
+AND (g.finalgrade is NULL or asb.timemodified > g.timemodified)
-===FORUM use Count per COURSE by type -- not including NEWS Forum!===
+ORDER BY LastSubmission
-<code sql>
-SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
-INNER JOIN prefix_course
-ON prefix_course.id = prefix_forum.course
-WHERE NOT(prefix_forum.type = 'news')
-GROUP BY prefix_forum.course,prefix_forum.type
-ORDER BY total desc
 </code>
-===Forum activity - system wide===
+==Assignment Module Reports==
+===All Ungraded Assignments===
+Returns all the submitted assignments that still need grading
 <code sql>
-SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
+select
-,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+u.firstname AS "First",
-  FROM prefix_role_assignments AS ra
+u.lastname AS "Last",
-  JOIN prefix_user AS u ON ra.userid = u.id
+c.fullname AS "Course",
-  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+a.name AS "Assignment"
-  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
+from prefix_assignment_submissions as asb
-ORDER BY count( * ) DESC
+join prefix_assignment as a ON a.id = asb.assignment
-</code>
+join prefix_user as u ON u.id = asb.userid
+join prefix_course as c ON c.id = a.course
+join prefix_course_modules as cm ON c.id = cm.course
-===Activity In Forums===
+where asb.grade < 0 and cm.instance = a.id
-Trying to figure out how much real activity we have in Forums by aggregating:
+and cm.module = 1
-Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers , Number of Students, ratio between unique Student posts and the number of students in the Course...
+order by c.fullname, a.name, u.lastname
+</code>
+===All Ungraded Assignments w/ Link===
+Returns all the submitted assignments that still need grading, along with a link that goes directly to the submission to grade it. The links work if you view the report within Moodle.
 <code sql>
-SELECT c.fullname,f.name,f.type
+select
-,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
+u.firstname AS "First",
-,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
+u.lastname AS "Last",
-,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
+c.fullname AS "Course",
-,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
+a.name AS "Assignment",
-,(SELECT Count( ra.userid ) AS Students
-FROM prefix_role_assignments AS ra
+'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
++ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
-WHERE ra.roleid =5
++ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
-AND ctx.instanceid = c.id
+AS "Assignmentlink"
-) AS StudentsCount
-,(SELECT Count( ra.userid ) AS Teachers
-FROM prefix_role_assignments AS ra
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE ra.roleid =3
-AND ctx.instanceid = c.id
-) AS 'Teacher<br/>Count'
-,(SELECT Count( ra.userid ) AS Users
-FROM prefix_role_assignments AS ra
-JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE ra.roleid IN (3,5)
-AND ctx.instanceid = c.id
-) AS UserCount
-, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
-, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
-FROM prefix_forum as f
-JOIN prefix_course as c ON f.course = c.id
-WHERE `type` != 'news'
-ORDER BY StudentPostUsage DESC
-</code>
-===All Forum type:NEWS===
+from prefix_assignment_submissions as asb
-<code sql>
+join prefix_assignment as a ON a.id = asb.assignment
-SELECT f.id, f.name
+join prefix_user as u ON u.id = asb.userid
-FROM prefix_course_modules AS cm
+join prefix_course as c ON c.id = a.course
-JOIN prefix_modules AS m ON cm.module = m.id
+join prefix_course_modules as cm ON c.id = cm.course
-JOIN prefix_forum AS f ON cm.instance = f.id
-WHERE m.name = 'forum'
+where asb.grade < 0 and cm.instance = a.id and cm.module = 1
-AND f.type = 'news'
+order by c.fullname, a.name, u.lastname
 </code>
-===All new forum NEWS items (discussions) from all my Courses===
+===Assignments (and Quizzes) waiting to be graded===
-change "userid = 26" and "id = 26" to a new user id
+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 c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
-FROM prefix_forum_discussions as fd
-JOIN prefix_forum as f ON f.id = fd.forum
+,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
-JOIN prefix_course as c ON c.id = f.course
+FROM prefix_role_assignments AS ra
-JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE fd.timemodified > ul.timeaccess
+JOIN prefix_user AS u ON u.id = ra.userid
- AND fd.forum IN (SELECT f.id
+WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
- FROM prefix_course_modules AS cm
- JOIN prefix_modules AS m ON cm.module = m.id
+,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments
- JOIN prefix_forum AS f ON cm.instance = f.id
- WHERE m.name = 'forum'
- AND f.type = 'news')
-  AND c.id IN (SELECT c.id
-  FROM prefix_course AS c
-  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
-  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
-  JOIN prefix_user AS u ON u.id = ra.userid
-  WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
-</code>
+,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes'
-===News Forum - Discussions COUNT===
+,(SELECT COUNT(*)
-Which is actually... How much instructions students get from their teachers
+FROM prefix_course_modules cm
-<code sql>
+JOIN prefix_modules as m ON m.id = cm.module
-SELECT c.shortname ,
+WHERE m.name LIKE 'quiz' AND cm.course = c.id
-concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+GROUP BY cm.course
-,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+) AS 'nQuizzes'
-  FROM prefix_role_assignments AS ra
-  JOIN prefix_user AS u ON ra.userid = u.id
-  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
-  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
-,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
-FROM prefix_forum_discussions AS fd
-INNER JOIN prefix_forum AS f ON f.id = fd.forum
-INNER JOIN prefix_course AS c ON c.id = f.course
-WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
-GROUP BY fd.forum
-ORDER BY count(fd.id) DESC
-</code>
-==Quiz Module Reports==
+,(SELECT COUNT(*)
-===Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes===
+FROM prefix_quiz_attempts AS qa
-<code sql>
+JOIN prefix_quiz AS q ON q.id = qa.quiz
-SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
+WHERE q.course = c.id
-                co.fullname AS course_fullname, co.shortname AS course_shortname,
+AND qa.timefinish = 0
-                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
+GROUP BY q.course) AS 'unFinished Quiz attempts'
-                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
-WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
-                qu.course = co.id AND
-                co.id = ct.instanceid AND
-                ra.roleid = re.id AND
-                re.name = 'Teacher' AND
-                ra.contextid = ct.id AND
-                ra.userid = u.id
-SELECT Count('x') As NumOfStudents
-                                FROM mdl_role_assignments a
-                                JOIN mdl_user u ON userid = u.id
-                                WHERE roleid = 5 AND contextid = (SELECT id FROM mdl_context WHERE instanceid = 668 AND contextlevel = 50)
-</code>
-===Number of Quizes per Course===
+,(SELECT COUNT(*)
-<code sql>
+FROM prefix_quiz_attempts AS qa
-SELECT count(*)
+JOIN prefix_quiz AS q ON q.id = qa.quiz
-,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+WHERE q.course = c.id
-,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes
+AND qa.timefinish > 0
+GROUP BY q.course) AS 'finished quiz attempts'
-FROM prefix_course_modules cm
+,(SELECT Count( ra.userid ) AS Users
-JOIN prefix_course c ON c.id = cm.course
+FROM prefix_role_assignments AS ra
-JOIN prefix_modules as m ON m.id = cm.module
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-WHERE m.name LIKE 'quiz'
+WHERE ra.roleid = 5
-GROUP BY c.id
+AND ctx.instanceid = c.id
-</code>
+) AS nStudents
-===List all MultiAnswer (Cloze) Questions===
+,(
-<code sql>
+SELECT count(a.id)
-SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
+FROM prefix_assignment AS a
-,question.id question_id, question.questiontext
+JOIN prefix_course_modules AS cm ON a.course = cm.course
-FROM  prefix_question question
+WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id
-JOIN prefix_quiz_question_instances qqi ON question.id = qqi.question
+) nAssignments
-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,
+SELECT count(*)
-So grades could be uploaded into an administrative SIS. Use with Configurable Reports.
+FROM prefix_assignment AS a
-<code sql>
+WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW()
-SELECT COUNT( * )
+GROUP BY a.course
-,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course
+) 'Open <br/>Assignments'
-FROM  prefix_grade_items AS gi
-JOIN prefix_course as c ON c.id = gi.courseid
+, CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)'
-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 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 DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess
+,(
+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'
-,(SELECT r.name
+FROM prefix_course AS c
-FROM  prefix_user_enrolments AS uenrol
+LEFT JOIN (
-JOIN prefix_enrol AS e ON e.id = uenrol.enrolid
+SELECT course, count(*) AS iAssignments
-JOIN prefix_role AS r ON e.id = r.id
+FROM prefix_assignment AS a
-WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName
+GROUP BY a.course
+) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id
-FROM prefix_user_enrolments AS ue
-JOIN prefix_enrol AS e ON e.id = ue.enrolid
+LEFT JOIN (
-JOIN prefix_course AS c ON c.id = e.courseid
+SELECT course, count(*) AS iOpenAssignments
-JOIN prefix_user AS user2 ON user2 .id = ue.userid
+FROM prefix_assignment AS a
-LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id
+WHERE FROM_UNIXTIME(a.timedue) > NOW()
-WHERE c.id=14 and ue.userid NOT IN (SELECT qa.userid FROM prefix_quiz_attempts AS qa
+GROUP BY a.course
-JOIN prefix_quiz AS q ON qa.quiz = q.id
+) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id
-JOIN prefix_course AS c ON q.course = c.id
-WHERE c.id = 14 AND q.name LIKE '%quiz name goes here%')
+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>
-==SCORM Activity Reports==
+===Who is using "Single File Upload" assignment===
-===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
+SELECT
-FROM prefix_scorm_scoes_track AS st
+concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
-JOIN prefix_user AS u ON st.userid=u.id
-JOIN prefix_scorm AS sc ON sc.id=st.scormid
+,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
-JOIN prefix_course AS c ON c.id=sc.course
+FROM prefix_role_assignments AS ra
-WHERE st.value='completed'
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
-ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
+JOIN prefix_user AS u ON u.id = ra.userid
-</code>
+WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+,ass.name as "Assignment Name"
-== Badges==
+FROM
+prefix_assignment as ass
-=== All badges issued, by User ===
+JOIN
+prefix_course as c ON c.id = ass.course
-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, and a direct link to that issued badge page so you can see all the other details for that badge.
+WHERE `assignmenttype` LIKE 'uploadsingle'
+</code>
+==Resource Module Reports==
+===List "Recently uploaded files"===
+see what users are uploading
 <code sql>
-SELECT u.username, b.name AS badgename,
+SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info
-CASE
+FROM `prefix_log`
-WHEN b.courseid IS NOT NULL THEN
+WHERE `action` LIKE 'upload'
-(SELECT c.shortname
+ORDER BY `prefix_log`.`time`  DESC
-    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,
-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.
+===List Courses that loaded a specific file: "X"===
+Did the Teacher (probably) uploaded course's Syllabus ?
+<code sql>
+SELECT c.id, c.fullname  FROM `prefix_log` as l
+JOIN prefix_course as c ON c.id = l.course
+WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
+</code>
-=== All badges available in the system, with Earned count ===
+===All resources that link to some specific external website===
++ link to course
-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.
++ who's the teacher
++ link to external resource
+<code sql>
+SELECT
+concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+,c.shortname,r.name
+,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+JOIN prefix_user AS u ON u.id = ra.userid
+WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
+FROM prefix_resource AS r
+JOIN prefix_course AS c ON r.course = c.id
+WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'
+</code>
+==="Compose Web Page" RESOURCE count===
 <code sql>
-SELECT b.id, b.name, b.description,
+SELECT course,prefix_course.fullname, COUNT(*) AS Total
-CASE
+FROM `prefix_resource`
-WHEN b.type = 1 THEN "System"
+JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
-WHEN b.type = 2 THEN "Course"
+WHERE type='html'
-END AS Context,
+GROUP BY course
-CASE
+</code>
-WHEN b.courseid IS NOT NULL THEN
-(SELECT c.shortname
+===Resource count in courses===
-    FROM prefix_course AS c
++ (First)Teacher name
-    WHERE c.id = b.courseid)
++ Where course is inside some specific Categories
-WHEN b.courseid IS NULL THEN "*"
+<code sql>
-END AS Course,
+SELECT
-CASE
+COUNT(*) AS count
-WHEN b.status = 0 OR b.status = 2 THEN "No"
+,r.course
-WHEN b.status = 1 OR b.status = 3 THEN "Yes"
+,c.shortname shortname
-WHEN b.status = 4 THEN "x"
+,c.fullname coursename
-END AS Available,
+,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
-CASE
+  FROM prefix_role_assignments AS ra
-WHEN b.status = 0 OR b.status = 1 THEN "0"
+  JOIN prefix_user as u ON ra.userid = u.id
-WHEN b.status = 2 OR b.status = 3 OR b.status = 4 THEN
+  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
- (SELECT COUNT(*)
+  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
-  FROM prefix_badge_issued AS d
+FROM prefix_resource r
-  WHERE d.badgeid = b.id
+JOIN prefix_course c ON r.course = c.id
- )
+WHERE c.category IN (10,13,28,18,26)
-END AS Earned
+GROUP BY r.course
-FROM prefix_badge AS b
+ORDER BY COUNT(*) DESC
+</code>
-</code>
+==Forum Module Reports==
+===All Ungraded Forums w/ Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
-=== Badges Leaderboard ===
+Please note the following about this query:
-A simple list of usernames and how many badges they have earned overall.
+*This report is specific to the course in which it's run
 <code sql>
-SELECT u.username, (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.userid = u.id) AS earned
+SELECT
+u.firstname AS "First",
+u.lastname AS "Last",
+f.name AS "Forum",
+convert(FROM_UNIXTIME(fp.modified),datetime) as LastSubmission,
+concat('<a target="_blank" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fp.discussion,'">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON en.courseid = c.id
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+JOIN prefix_role_assignments  AS ra ON ra.contextid = ctx.id
+JOIN prefix_forum AS f ON c.id = f.course
+JOIN prefix_forum_discussions AS fd ON f.id = fd.forum
+JOIN prefix_forum_posts AS fp ON fd.id = fp.discussion
+JOIN prefix_user AS u ON u.id = fp.userid
+WHERE cm.instance = f.id
+AND ue.userid = u.id
+AND ra.userid = u.id
+AND ra.roleid = '5'
+AND c.id = '%%COURSEID%%'
+AND cm.module = '9'
+AND f.assessed not like '0'
+AND fp.id not in (select r.itemid from prefix_rating AS r where r.itemid = fp.id and r.component = 'mod_forum')
+order by LastSubmission
+</code>
+===print all User's post in course Forums===
+@@COURSEID@@ is a variable the is replace by the current CourseID you are running the sql report from. if you are using the latest block/configurable_reports ! (You can always change it to a fixed course or remove it to display all courses.)
+<code sql>
+SELECT
+concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') As Fullname
+,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum
+,count(*) as Posts
+,(SELECT count(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum as iforum ON iforum.id = ifd.forum  WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion
+FROM prefix_forum_posts AS fp
+JOIN prefix_user as u ON u.id = fp.userid
+JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id
+JOIN prefix_forum AS f ON f.id = fd.forum
+JOIN prefix_course as c ON c.id = fd.course
+WHERE fd.course = '@@COURSEID@@'
+GROUP BY f.id,u.id
+ORDER BY u.id
+</code>
+===FORUM use Count per COURSE -- not including NEWS Forum!===
+<code sql>
+SELECT prefix_course.fullname, prefix_forum.course, count(*) as total FROM prefix_forum
+INNER JOIN prefix_course
+ON prefix_course.id = prefix_forum.course
+WHERE NOT(prefix_forum.type = 'news')
+GROUP BY prefix_forum.course
+ORDER BY total desc
+</code>
+===FORUM use Count per COURSE by type -- not including NEWS Forum!===
+<code sql>
+SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, count(*) as total FROM prefix_forum
+INNER JOIN prefix_course
+ON prefix_course.id = prefix_forum.course
+WHERE NOT(prefix_forum.type = 'news')
+GROUP BY prefix_forum.course,prefix_forum.type
+ORDER BY total desc
+</code>
+===Forum activity - system wide===
+<code sql>
+SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID
+,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+  FROM prefix_role_assignments AS ra
+  JOIN prefix_user AS u ON ra.userid = u.id
+  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+,c.fullname as Course
+,f.type
+,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+, fd.forum, f.name,count(*) AS cPostAndDisc
+,(SELECT count(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion
+FROM prefix_forum_posts AS fp
+JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion
+JOIN prefix_forum AS f ON f.id = fd.forum
+JOIN prefix_course AS c ON c.id = f.course
+WHERE f.type != 'news' AND c.fullname LIKE '%2013%'
+## WHERE 1=1
+## %%FILTER_YEARS:c.fullname%%
+## You can enable the SEMESTER filter as well,
+## by uncommenting the following line:
+## %%FILTER_SEMESTERS:c.fullname%%
+GROUP BY fd.forum
+ORDER BY count( * ) DESC
+</code>
+===Activity In Forums===
+Trying to figure out how much real activity we have in Forums by aggregating:
+Users in Course, Number of Posts, Number of Discussions, Unique student post, Unique student discussions, Number of Teachers , Number of Students, ratio between unique Student posts and the number of students in the Course...
+<code sql>
+SELECT c.fullname,f.name,f.type
+,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
+,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
+,(SELECT count(fp.id) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as Posts
+,(SELECT count(distinct fp.userid) FROM prefix_forum_discussions fd JOIN prefix_forum_posts as fp ON fd.id = fp.discussion WHERE f.id = fd.forum) as UniqueUsersPosts
+,(SELECT Count( ra.userid ) AS Students
+FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+WHERE ra.roleid =5
+AND ctx.instanceid = c.id
+) AS StudentsCount
+,(SELECT Count( ra.userid ) AS Teachers
+FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+WHERE ra.roleid =3
+AND ctx.instanceid = c.id
+) AS 'Teacher<br/>Count'
+,(SELECT Count( ra.userid ) AS Users
+FROM prefix_role_assignments AS ra
+JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+WHERE ra.roleid IN (3,5)
+AND ctx.instanceid = c.id
+) AS UserCount
+, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
+, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
+FROM prefix_forum as f
+JOIN prefix_course as c ON f.course = c.id
+WHERE `type` != 'news'
+ORDER BY StudentPostUsage DESC
+</code>
+===All Forum type:NEWS===
+<code sql>
+SELECT f.id, f.name
+FROM prefix_course_modules AS cm
+JOIN prefix_modules AS m ON cm.module = m.id
+JOIN prefix_forum AS f ON cm.instance = f.id
+WHERE m.name = 'forum'
+AND f.type = 'news'
+</code>
+===All new forum NEWS items (discussions) from all my Courses===
+change "userid = 26" and "id = 26" to a new user id
+<code sql>
+SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
+FROM prefix_forum_discussions as fd
+JOIN prefix_forum as f ON f.id = fd.forum
+JOIN prefix_course as c ON c.id = f.course
+JOIN prefix_user_lastaccess as ul ON (c.id = ul.courseid AND ul.userid = 26)
+WHERE fd.timemodified > ul.timeaccess
+ AND fd.forum IN (SELECT f.id
+ FROM prefix_course_modules AS cm
+ JOIN prefix_modules AS m ON cm.module = m.id
+ JOIN prefix_forum AS f ON cm.instance = f.id
+ WHERE m.name = 'forum'
+ AND f.type = 'news')
+  AND c.id IN (SELECT c.id
+  FROM prefix_course AS c
+  JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+  JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+  JOIN prefix_user AS u ON u.id = ra.userid
+  WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
+</code>
+===News Forum - Discussions COUNT===
+Which is actually... How much instructions students get from their teachers
+<code sql>
+SELECT c.shortname ,
+concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+  FROM prefix_role_assignments AS ra
+  JOIN prefix_user AS u ON ra.userid = u.id
+  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
+,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',count(fd.id),'</a>') AS DiscussionsSum
+FROM prefix_forum_discussions AS fd
+INNER JOIN prefix_forum AS f ON f.id = fd.forum
+INNER JOIN prefix_course AS c ON c.id = f.course
+WHERE f.type = 'news' AND c.category IN (10,13,28,18,26)
+GROUP BY fd.forum
+ORDER BY count(fd.id) DESC
+</code>
+===Cantidad de foros que han sido posteados por profesor===
+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>
+==Advanced Forum (hsuforum) Module Reports==
+===All Ungraded Advanced Forums (hsuforum) w/ Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+Please note the following about this query:
+*This report is specific to the course in which it's run
+<code sql>
+SELECT
+u.firstname AS "First",
+u.lastname AS "Last",
+af.name AS "Forum",
+convert(FROM_UNIXTIME(afp.modified),datetime) as LastSubmission,
+concat('<a target="_blank" href="%%WWWROOT%%/mod/hsuforum/discuss.php?d=',afp.discussion,'">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON en.courseid = c.id
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+JOIN prefix_role_assignments  AS ra ON ra.contextid = ctx.id
+JOIN prefix_hsuforum AS af ON c.id = af.course
+JOIN prefix_hsuforum_discussions AS afd ON af.id = afd.forum
+JOIN prefix_hsuforum_posts AS afp ON afd.id = afp.discussion
+JOIN prefix_user AS u ON u.id = afp.userid
+WHERE cm.instance = af.id
+AND ue.userid = u.id
+AND ra.userid = u.id
+AND c.id = '%%COURSEID%%'
+AND cm.module = '24'
+AND af.gradetype = '2'
+AND afp.id not in (select r.itemid from prefix_rating AS r where r.itemid = afp.id and r.component = 'mod_hsuforum')
+AND ra.roleid = '5'
+order by LastSubmission
+</code>
+==Journal Module Reports==
+===All Ungraded Journals w/ Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+Please note the following about this query:
+*This report is specific to the course in which it's run
+<code sql>
+SELECT
+u.firstname AS "First",
+u.lastname AS "Last",
+j.name AS "Journal",
+convert(FROM_UNIXTIME(je.modified),datetime) as LastSubmission,
+concat('<a target="_blank" href="%%WWWROOT%%/mod/journal/report.php?id=',cm.id,'">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON en.courseid = c.id
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_journal AS j ON c.id = j.course
+JOIN prefix_journal_entries AS je ON j.id = je.journal
+JOIN prefix_user AS u ON u.id = je.userid
+WHERE cm.instance = j.id
+AND ue.userid = u.id
+AND c.id = '%%COURSEID%%'
+AND cm.module = '29'
+AND j.grade not like '0'
+AND je.rating is null
+Order by LastSubmission
+</code>
+==Lesson Module Reports==
+===All Ungraded Lessons w/ Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+Please note the following about this query:
+*This report is specific to the course in which it's run
+*Requirement: The semicolon restriction must be removed from the Configurable Reports block.
+<code sql>
+SELECT
+u.firstname AS "First",
+u.lastname AS "Last",
+l.name AS "Lesson",
+convert(FROM_UNIXTIME(la.timeseen),datetime) as LastSubmission,
+concat('<a target="_blank" href="%%WWWROOT%%/mod/lesson/essay.php?id=',cm.id,'&mode=grade&attemptid=',la.id,'">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON en.courseid = c.id
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_lesson AS l ON c.id = l.course
+JOIN prefix_lesson_attempts AS la ON l.id = la.lessonid
+JOIN prefix_user AS u ON u.id = la.userid
+WHERE cm.instance = l.id
+AND ue.userid = u.id
+AND c.id = '%%COURSEID%%'
+AND cm.module = '13'
+AND l.grade not like '0'
+AND la.useranswer like '%graded%'
+AND la.useranswer not like '%graded";i:1%'
+order by LastSubmission
+</code>
+==Quiz Module Reports==
+===All Ungraded Quizzes w/ Link===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+Please note the following about this query:
+*This report is specific to the course in which it's run.
+*Requirement: The "[https://moodle.org/plugins/view/quiz_gradingstudents Manual Grading by Student]" plugin must be installed for URL string used which points to grade a specific user's essay responses.
+<code sql>
+SELECT
+u.firstname AS "First",
+u.lastname AS "Last",
+q.name AS "Quiz",
+convert(FROM_UNIXTIME(qa.timefinish),datetime) as LastSubmission,
+concat('<a target="_blank" href="%%WWWROOT%%/mod/quiz/report.php?id=',cm.id,'&mode=gradingstudents&usageid=',qa.uniqueid,'&slots=',(Select group_concat(qua.slot SEPARATOR '%2C') from prefix_question_attempts as qua
+WHERE qua.questionusageid = qa.uniqueid
+AND qua.behaviour = 'manualgraded'),'&grade=needsgrading','">Grade</a>') AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON en.courseid = c.id
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_quiz AS q ON c.id = q.course
+JOIN prefix_quiz_attempts AS qa ON q.id = qa.quiz
+JOIN prefix_user AS u ON u.id = qa.userid
+WHERE cm.instance = q.id
+AND ue.userid = u.id
+AND cm.module = '16'
+AND c.id = '%%COURSEID%%'
+AND qa.timefinish > 0
+AND qa.sumgrades is NULL
+order by LastSubmission
+</code>
+===Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes===
+<code sql>
+SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
+                co.fullname AS course_fullname, co.shortname AS course_shortname,
+                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
+                u.firstname, u.lastname, u.email,
+FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u
+WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND
+                qu.course = co.id AND
+                co.id = ct.instanceid AND
+                ra.roleid = re.id AND
+                re.name = 'Teacher' AND
+                ra.contextid = ct.id AND
+                ra.userid = u.id
+SELECT Count('x') As NumOfStudents
+                                FROM prefix_role_assignments a
+                                JOIN prefix_user u ON userid = u.id
+                                WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)
+</code>
+===Number of Quizes per Course===
+<code sql>
+SELECT count(*)
+,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">Link</a>') AS Quizes
+FROM prefix_course_modules cm
+JOIN prefix_course c ON c.id = cm.course
+JOIN prefix_modules as m ON m.id = cm.module
+WHERE m.name LIKE 'quiz'
+GROUP BY c.id
+</code>
+===List all MultiAnswer (Cloze) Questions===
+<code sql>
+SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/attempt.php?q=', quiz.id, '">', quiz.name, '</a>') AS Quiz
+,question.id question_id, question.questiontext
+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>
+==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>
+==Teacher Grading Reports==
+===Grade Everything w/ Links===
+(By: [https://moodle.org/user/profile.php?id=1176858 Ben Haensel] )
+Please note the following about this query:
+*This report is designed to grade assign, forum, hsuforum, journal, lesson, and quiz mods.
+*This report is specific to the USER that runs the report.
+*This report checks for any ungraded activities in all of the courses a user is enrolled as an editing teacher.
+*This report takes resubmitted Assign into account.
+*Requirement: The Assign mod must be updated ([https://goo.gl/FXfDGa see github])to use a URL string without the rownum logic.
+*Requirement: For Lessons grading output, the semicolon restriction must be removed from the Configurable Reports block.
+*Requirement: For Quiz grading output, the "[https://moodle.org/plugins/view/quiz_gradingstudents Manual Grading by Student]" plugin must be installed for URL string used which points to grade a specific users essay responses.
+<code sql>
+SELECT
+c.fullname as Course,
+u.firstname AS "First",
+u.lastname AS "Last",
+case when gi.itemmodule = 'hsuforum' then "adv-forum"
+else gi.itemmodule end as AssessmentType,
+gi.itemname AS "AssessmentName",
+Case when cm.module = '1' then convert(FROM_UNIXTIME(asb.timemodified),datetime)
+when cm.module = '16' then convert(FROM_UNIXTIME(qa.timefinish),datetime)
+when cm.module = '9' then convert(FROM_UNIXTIME(fp.modified),datetime)
+when cm.module = '24' then convert(FROM_UNIXTIME(afp.modified),datetime)
+when cm.module = '29' then convert(FROM_UNIXTIME(je.modified),datetime)
+when cm.module = '13' then convert(FROM_UNIXTIME(la.timeseen),datetime)
+END as LastSubmission,
+Case when cm.module = 1 then (case when asb.timemodified > g.timemodified then 'resubmitted' else asb.status end)
+else '' end AS "AssignStatus",
+Case when cm.module = '1' then concat('<a target="_blank" href="%%WWWROOT%%/mod/assign/view.php?id=',cm.id,
+'&userid=',asb.userid,'&action=grade','">Grade</a>')
+when cm.module = '16' then concat('<a target="_blank" href="%%WWWROOT%%/mod/quiz/report.php?id=',cm.id,'&mode=gradingstudents&usageid=',qa.uniqueid,'&slots=',(Select group_concat(qua.slot SEPARATOR '%2C') from prefix_question_attempts as qua
+WHERE qua.questionusageid = qa.uniqueid
+AND qua.behaviour = 'manualgraded'),'&grade=needsgrading','">Grade</a>')
+when cm.module = '9' then concat('<a target="_blank" href="%%WWWROOT%%/mod/forum/discuss.php?d=',fp.discussion,'">Grade</a>')
+when cm.module = '24' then concat('<a target="_blank" href="%%WWWROOT%%/mod/hsuforum/discuss.php?d=',afp.discussion,'">Grade</a>')
+when cm.module = '29' then concat('<a target="_blank" href="%%WWWROOT%%/mod/journal/report.php?id=',cm.id,'">Grade</a>')
+when cm.module = '13' then concat('<a target="_blank" href="%%WWWROOT%%/mod/lesson/essay.php?id=',cm.id,'&mode=grade&attemptid=',la.id,'">Grade</a>')
+END AS "GradeLink"
+FROM prefix_course AS c
+JOIN prefix_course_modules AS cm ON c.id = cm.course
+JOIN prefix_enrol AS en ON c.id = en.courseid
+JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
+JOIN prefix_grade_items gi ON c.id = gi.courseid
+JOIN prefix_grade_grades AS g on gi.id = g.itemid
+JOIN prefix_user AS u ON u.id = ue.userid
+JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+JOIN prefix_role_assignments  AS ra ON ra.contextid = ctx.id
+left join prefix_role_assignments  AS rax ON ra.userid = rax.userid
+and ra.contextid = rax.contextid
+and ra.id > rax.id
+left JOIN prefix_assign_submission AS asb ON gi.iteminstance = asb.assignment
+and (g.finalgrade is NULL or asb.timemodified > g.timemodified)
+and asb.userid = u.id
+left JOIN prefix_quiz_attempts AS qa ON gi.iteminstance = qa.quiz
+and qa.userid = u.id
+and qa.sumgrades is NULL and qa.timefinish > '0'
+LEFT JOIN prefix_journal AS j ON c.id = j.course
+and cm.instance = j.id
+AND j.grade not like '0'
+left JOIN prefix_journal_entries AS je ON j.id = je.journal
+and cm.instance = je.journal
+and je.userid = ue.userid
+AND je.rating is null
+LEFT JOIN prefix_lesson AS l ON c.id = l.course
+and cm.instance = l.id
+AND l.grade not like '0'
+LEFT JOIN prefix_lesson_attempts AS la ON l.id = la.lessonid
+and cm.instance = la.lessonid
+and la.userid = u.id
+AND la.useranswer like '%graded%'
+AND la.useranswer not like '%graded";i:1%'
+LEFT JOIN prefix_forum AS f ON c.id = f.course
+and cm.instance = f.id
+and f.assessed not like '0'
+LEFT JOIN prefix_forum_discussions AS fd ON f.id = fd.forum
+and cm.instance = fd.forum
+and fd.userid = u.id
+LEFT JOIN prefix_forum_posts AS fp ON fd.id = fp.discussion
+and fp.userid = u.id
+LEFT JOIN prefix_hsuforum AS af ON c.id = af.course
+and af.gradetype = '2'
+and cm.instance = af.id
+LEFT JOIN prefix_hsuforum_discussions AS afd ON af.id = afd.forum
+and cm.instance = afd.forum
+and afd.userid = u.id
+LEFT JOIN prefix_hsuforum_posts AS afp ON afd.id = afp.discussion
+and afp.userid = u.id
+WHERE c.id in (SELECT cz.id FROM prefix_course AS cz
+JOIN prefix_enrol AS enz ON cz.id = enz.courseid
+JOIN prefix_user_enrolments AS uez ON uez.enrolid = enz.id
+JOIN prefix_user AS uz ON uz.id = uez.userid
+JOIN prefix_context AS ctxz ON cz.id = ctxz.instanceid
+JOIN prefix_role_assignments AS raz ON raz.contextid = ctxz.id
+WHERE cz.id = c.id
+AND raz.roleid = '3'
+AND raz.userid = uz.id
+AND raz.userid = uez.userid
+AND raz.userid = '%%USERID%%')
+and rax.id is null
+AND g.userid = u.id
+AND ra.userid = u.id
+AND ra.userid = ue.userid
+AND gi.courseid = cm.course
+and gi.iteminstance = cm.instance
+and (
+(cm.module = '1' and asb.userid = u.id and (g.finalgrade is NULL or asb.timemodified > g.timemodified)) OR
+(cm.module = '16' and qa.userid = u.id and qa.sumgrades is NULL and qa.timefinish > '0') OR
+(cm.module = '9' and fp.userid = u.id and cm.instance = fd.forum AND f.assessed not like '0' AND not exists (select r.itemid from prefix_rating AS r where r.itemid = fp.id and r.component = 'mod_forum')) OR
+(cm.module = '24' and afp.userid = u.id and cm.instance = afd.forum AND af.gradetype = '2' AND not exists (select r.itemid from prefix_rating AS r where r.itemid = afp.id and r.component = 'mod_hsuforum')) OR
+(cm.module = '29' and je.userid = u.id AND j.grade not like '0' AND je.rating is null) OR
+(cm.module = '13' and la.userid = u.id AND l.grade not like '0' AND la.useranswer like '%graded%' AND la.useranswer not like '%graded";i:1%')
+)
-<code sql>
+ORDER BY u.id, AssessmentType, LastSubmission
-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===
+== Useful sub queries ==
-How to get a list of all users and which cohorts they belong to.
+=== All teachers in the course ===
 <code sql>
-SELECT u.firstname, u.lastname, h.idnumber, h.name
+,(SELECT GROUP_CONCAT( CONCAT( u.firstname,  " ", u.lastname ) )
-FROM prefix_cohort AS h
+FROM prefix_course ic
-JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
+JOIN prefix_context con ON con.instanceid = ic.id
-JOIN prefix_user AS u ON hm.userid = u.id
+JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50
-ORDER BY u.firstname
+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>
+==See also==
+[https://github.com/jleyva/moodle-configurable_reports_repository Configurable Reports Repository on GitHub]
 [[Category:Contributed code]]

Documentation

ad-hoc contributed reports: Difference between revisions

Latest revision as of 17:06, 30 November 2017

User and Role Report

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

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

Student (user) COUNT in each Course

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

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

Role assignments on categories

Permissions Overides on Categories

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

Lists "loggedin users" from the last 120 days

Lists the users who have only logged into the site once

Students in all courses of some institute

Full User info (for deleted users)

User's courses

List Users with extra info (email) in current course

Special Roles

Courses without Teachers

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

List of users with language

Compare role capability and permissions

Log Activity Reports

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

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

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

How many LOGINs per user and user's Activity

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

Weekly Instructor Online Participation

Module activity (Hits) between dates

Module activity (Instances and Hits) for each academic year

Course Reports

Most Active courses

Active courses, advanced

Count unique teachers with courses that use at least X module (Moodle19)

RESOURCE count for each COURSE

Common resource types count for each Category (Moodle19)

Detailed Resource COUNT by Teacher in each course

Courses that are defined as using GROUPs

Courses with Groups

Groups in course with member list

Group Export

List all Courses in and below a certain category

List all Categories in one level below a certain category

Blog activity per Course (not including VIEW)

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

All Courses which uploaded a Syllabus file

Site-wide completed SCORM activities by Course name

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

Grade and Course Completion Reports

Site-Wide Grade Report with All Items

Site-Wide Grade Report with Just Course Totals

Learner report by Learner with grades

User Course Completion

User Course Completion with Criteria

Courses with Completion Enabled and their settings

Course Completion Report with custom dates

Scales used in activities

Activity Module Reports

How many SCORM activities are used in each Course

Detailed ACTIONs for each MODULE

Most popular ACTIVITY

System wide use of ACTIVITIES and RESOURCES

LOG file ACTIONS per MODULE per COURSE (IDs)

System Wide usage count of various course Activities

Course wiki usage/activity over the last 6 semesters

Detailed WIKI activity (per wiki per course)

Wiki usage, system wide

Aggregated Teacher activity by "WEB2" Modules

List all the certificates issued, sort by variables in the custom profile fields

Counter Blog usage in Courses,system wide

Elluminate (Blackboard Collaborate) - system wide usage

Choice

Assignment type usage in courses

Assign Module Reports

All Ungraded Assign using rownum Ranking w/ Link

All Ungraded Assign using modification for Link

Assignment Module Reports

All Ungraded Assignments