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

ad-hoc contributed reports: Difference between revisions

From MoodleDocs
(sitewide reports template)
(→‎Log Acivity Reports: added Weekly Instructor Participation per Term report)
 
(29 intermediate revisions by 8 users not shown)
Line 1: Line 1:
{{Sitewide reports}}
{{Sitewide reports}}
These are community contributed reports for the [[Custom SQL queries report]] site-wide report plugin (module) <br/>
==User and Role Report==
Everyone is welcome to add their own. until we find a better way to exchange reports between us.<br/>
Enjoy :-)<br/>
 
----
 
===Detailed ACTIONs for each MODULE===
<code sql>
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
</code>
 
===Detailed ACTIONs for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
===Detailed ACTIONs for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
<code sql>
<code sql>
Line 23: Line 10:
GROUP BY roleid,l.action
GROUP BY roleid,l.action
ORDER BY counter desc
ORDER BY counter desc
</code>
===Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
<code sql>
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
</code>
===Most Active courses===
<code sql>
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
</code>
===RESOURCE count for each COURSE===
<code sql>
SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
GROUP BY course
ORDER BY count DESC
</code>
===Most popular ACTIVITY===
<code sql>
SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC
</code>
===System wide use of ACTIVITIES and RESOURCES===
<code sql>
SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC
</code>
===LOG file ACTIONS per MODULE per COURSE (IDs)===
<code sql>
select course,module,action,count(action) as summa from prefix_log
where action <> 'new'
group by course,action,module
order by course,module,action
</code>
==="Compose Web Page" RESOURCE count===
<code sql>
SELECT course,prefix_course.fullname, COUNT(*) AS Total
FROM `prefix_resource`
JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
WHERE type='html'
GROUP BY course
</code>
===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.
<code sql>
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
</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>
</code>


Line 199: Line 88:
</code>
</code>


===List "Recently uploaded files"===
===Lists the users who have only logged into the site once===
see what users are uploading
<code sql>
<code sql>
SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info 
SELECT id, username, firstname, laastname, idnumber
FROM `prefix_log`
FROM prefix_user
WHERE `action` LIKE 'upload'
WHERE prefix_user.deleted = 0
ORDER BY `prefix_log`.`time`  DESC
AND prefix_user.lastlogin = 0
</code>
AND prefix_user.lastaccess > 0
 
===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>
</code>


Line 239: Line 120:
</code>
</code>


===Activity In Forums===
===User's courses===
Trying to figure out how much real activity we have in Forums by aggregating:
change "u.id = 2" with a new user id
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 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
</code>
 
===Special Roles===
<code sql>
<code sql>
SELECT c.fullname,f.name,f.type
SELECT ra.roleid,r.name
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
,concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,(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
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
</code>
===Courses without Teachers===
Actually, shows the number of Teachers in a course.
<code sql>
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
</code>
===List of users who have been enrolled for more than 4 weeks===
For Moodle 2.2 , by  Isuru Madushanka Weerarathna
<code sql>
SELECT uenr.userid As User, IF(enr.courseid=enr.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
WHERE enr.id = uenr.enrolid AND enr.status = uenr.statu
</code>
===Non-Participant Students===
This report returns a list of students who are enrolled in courses in the specified category, but have '''no''' participation in the course during the specified time period. The number of "Logins" is provided for each student for the time period specified.
A "Login" is defined as course activity including viewing content. Click the "Logs" link to review the student activity. The Logs offer the option to review "View" activity as well as "Edit" activity.
Only "visible" courses are included in this report.
<code sql>
SELECT
u.lastname AS Last
, u.firstname AS First
, u.idnumber AS IDnumber
, u.email AS email
, c.shortname AS CourseID
,  count(l.id) AS Logins
, 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_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_course AS c ON c.id = ctx.instanceid
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id  AND FROM_UNIXTIME(l.time) > :start_time AND FROM_UNIXTIME(l.time) < :end_time
WHERE ra.roleid =5
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND ctx.instanceid = c.id
) AS StudentsCount
AND c.visible=1
,(SELECT Count( ra.userid ) AS Teachers
 
FROM prefix_role_assignments AS ra
GROUP BY u.idnumber
 
HAVING Logins < 2
</code>
 
===Low-Participation Students===
This report returns a list of students who are enrolled in courses, but have very low participation in the course during the specified time period, defined as fewer than 2 "Edits." The number of "Edits" is provided for each student for the time period specified.
 
An "Edit" is defined as course activity other than viewing content. Click the "Logs" link to review the student activity. The Logs offer the option to review "View" activity as well as "Edit" activity.
 
Only "visible" courses are included in this report.
 
Note: This report can take up to 5 minutes to run. Please be patient.
 
<code sql>
SELECT
u.lastname AS Last
, u.firstname AS First
, u.idnumber AS IDnumber
, u.email AS email
, c.shortname AS CourseID
,  count(l.id) AS Edits
, 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
 
LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id  AND l.action NOT LIKE "view%" AND FROM_UNIXTIME(l.time) > :start_time AND FROM_UNIXTIME(l.time) < :end_time
 
WHERE ra.roleid =5
AND ctx.instanceid = c.id
AND c.visible=1
 
GROUP BY u.idnumber
 
HAVING Edits < 2
</code>
 
==Log Activity Reports==
===Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
<code sql>
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
</code>
 
===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.
 
<code sql>
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
</code>
 
===How many LOGINs per user and user's Activity===
+ link username to a user activity graph report
<code sql>
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
</code>
 
===Weekly Instructor Participation per Term===
''Note: at our institution, term code is contained within the shortname of the course. Our terms are 12 weeks long. Adjust as needed for your institution.''
 
Displays participation of instructors in all courses in a given term  by week, including pre-term and post-term edits. An '''edit''' is defined as a change to the course, such as a discussion post, the 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.
    You may also use % in the Term code to see all courses in a given Academic Year (e.g. "%13") or during a specific Term (e.g. "FL%")
 
Note: This query can take several minutes to run. Please be patient.
 
<code sql>
SELECT
c.shortname AS CourseID
, cc.name AS Category
, CONCAT('<a target="_new" href="%%WWWROOT%%/course/user.php',CHAR(63),'id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Instructor
,  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_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
WHERE ra.roleid =3
AND ctx.instanceid = c.id
AND ctx.instanceid = c.id
) AS 'Teacher<br/>Count'
AND c.shortname LIKE '%OL-%'
,(SELECT Count( ra.userid ) AS Users
AND RIGHT(c.shortname,4) LIKE :term
AND c.shortname LIKE :course
AND u.lastname LIKE :last_name
 
GROUP BY u.idnumber, c.id
ORDER BY RIGHT(c.shortname,2), c.shortname
</code>
 
==Course Reports==
===Most Active courses===
<code sql>
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
</code>
 
===Active courses, advanced===
Including: Teacher's name, link to the course, All types of log activities, special YEAR generated field, Activities and Resource count, enrolled Student count
<code sql>
SELECT COUNT(l.id) hits, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid IN (3,5)
JOIN prefix_user AS u ON u.id = ra.userid
AND ctx.instanceid = c.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
) AS UserCount
 
, (SELECT (UniqueUsersDiscussions / StudentsCount )) as StudentDissUsage
,CASE
, (SELECT (UniqueUsersPosts /StudentsCount)) as StudentPostUsage
  WHEN c.fullname LIKE '%תשע' THEN 'תשע'
FROM prefix_forum as f
  WHEN c.fullname LIKE '%תשעא' THEN 'תשעא'
JOIN prefix_course as c ON f.course = c.id
  WHEN c.fullname LIKE '%תשעב' THEN 'תשעב'
WHERE `type` != 'news'
END AS Year
ORDER BY StudentPostUsage DESC
 
,(SELECT count(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules
 
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
FROM prefix_log l
INNER JOIN prefix_course c ON l.course = c.id
GROUP BY c.id
HAVING Modules > 2
ORDER BY Year DESC, hits DESC
</code>
 
===RESOURCE count for each COURSE===
<code sql>
SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
GROUP BY course
ORDER BY count DESC
</code>
 
===Courses that are defined as using GROUPs===
<code sql>
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
</code>
 
===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. *
FROM prefix_course, prefix_course_categories
WHERE prefix_course.category = prefix_course_categories.id
AND (
prefix_course_categories.path LIKE '/$s/%'
OR prefix_course_categories.path LIKE '/$s'
)
</code>
===List all Categories in one level below a certain category===
Use this PHP code to retrieve a list of all categories below a certain category.
 
$s should be the id of the top level category you are interested in.
<code php>
<?php
 
require_once('./config.php');
 
$parent_id = $s;
 
$categories= array();
 
$categories = get_categories($parent_id);
 
echo '<ol>';
foreach ($categories as $category)
        {
        echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>';
        }
echo '</ol>';
 
?>
</code>
</code>


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


Line 334: Line 455:
AND c.category IN (10,18,26,13,28)
AND c.category IN (10,18,26,13,28)
</code>
</code>
 
===All resources that link to some specific external website===
===Site-wide completed SCORM activities by Course name===
+ link to course
This report will list all completed attempts for all SCORM activities. It is ordered first by Course name, then student's last name, then student's first name, then attempt number. Please note: the FROM_UNIXTIME command is for MySQL.
+ who's the teacher
+ link to external resource
<code sql>
<code sql>
SELECT
SELECT u.firstname First,u.lastname Last,c.fullname Course, st.attempt Attempt,st.value Status,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") Date
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_scorm_scoes_track AS st
,c.shortname,r.name
JOIN prefix_user AS u ON st.userid=u.id
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
JOIN prefix_scorm AS sc ON sc.id=st.scormid
FROM prefix_role_assignments AS ra
JOIN prefix_course AS c ON c.id=sc.course
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE st.value='completed'  
JOIN prefix_user AS u ON u.id = ra.userid
ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
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>
</code>


===How many LOGINs per user and user's Activity===
===Outcomes per Course===
+ link username to a user activity graph report
This report accepts a course shortname and returns a list of the graded activities in the course, listing which outcomes (if any) have been identified for each activity.
 
<code sql>
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') as Username
SELECT  
,count(*) as logins
c.shortname AS Course
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) as Activity
,gi.itemmodule AS 'Activity Type'
FROM prefix_log as l JOIN prefix_user as u ON l.userid = u.id
 
WHERE `action` LIKE '%login%' group by userid
,(SELECT gi.itemname FROM prefix_grade_items AS gi WHERE gi.itemnumber = 0 AND gi.courseid = cm.course AND gi.iteminstance = cm.instance) AS 'Item Name'
ORDER BY Activity DESC
 
, (SELECT GROUP_CONCAT(go.shortname SEPARATOR ' ') FROM prefix_grade_items AS gi JOIN prefix_grade_outcomes AS go ON go.id = gi.outcomeid WHERE gi.courseid = cm.course AND gi.iteminstance = cm.instance AND gi.itemnumber >= 1000) AS 'Outcomes'
 
FROM prefix_course AS c
JOIN prefix_course_modules AS cm ON cm.course = c.id
 
JOIN prefix_grade_items as gi ON gi.courseid = cm.course AND gi.iteminstance = cm.instance
 
WHERE c.shortname LIKE :shortname
GROUP BY gi.iteminstance
ORDER BY c.shortname
 
</code>
</code>


===User's courses===
===Course Readiness Report===
change "u.id = 2" with a new user id
''Note: This report uses a method of encoding the term, instruction method, and other details in the course shortname and idnumber fields-- you may need to adapt this section of the code for your own institution.''
 
This report accepts a term code, e.g. FL13, and provides a list of all courses currently scheduled for that term. The following information is provided per course:
 
    The full name of the course, with a link to the course
    The instructional method of the course (OL, HY, BL)
    The course category, indicating undergraduate, SOE, or Graduate programs (e.g. "2013-Fall-UG","2013-Fall-Grad", "2013-Fall-SoE", or "%" for all categories)
    The teacher presently assigned to teach the course (first teacher listed only)
    The number of edits the primary teacher has made, where an edit is any change to the course, not including viewing existing content
    A count of the number of resources and activities per course, e.g. Forum, Assignment, Quiz, Pages, Links, and Attached Files
    The date of last syllabus update, provided the syllabus is attached as a file and the filename includes the string "syllabus" (also provided as "days ago")*
 
This report may be downloaded as a Comma Separated Values file and viewed in Excel.
 
* Note: A date value of "1969-12-31 19:00:00" indicates that no syllabus file was found. The date "2012-11-05 15:04:15" is the date of the template creation, indicating that the "syllabus" file is a blank placeholder.
 
<code sql>
<code sql>
SELECT u.firstname, u.lastname, c.id, c.fullname
SELECT c.fullname AS Course
FROM prefix_course AS c
 
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">link</a>') AS Link
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
 
,RIGHT(c.idnumber,2) AS Type
 
, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section
 
,cc.name AS Category
,(SELECT CONCAT(u.lastname,', ', u.firstname) 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
JOIN prefix_user AS u ON u.id = ra.userid
WHERE u.id = 2
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
</code>


===All Forum type:NEWS===
,(SELECT uid.data
<code sql>
FROM prefix_role_assignments AS ra
SELECT f.id, f.name
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
FROM prefix_course_modules AS cm
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_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 = 'ID')WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS ID
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%quiz%') AS Quizzes
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_forum AS f ON cm.instance = f.id
WHERE cm.course = c.id AND m.name LIKE '%assign%') AS Assignments
WHERE m.name = 'forum'
 
AND f.type = 'news'
,(SELECT COUNT(prefix_page.id)
</code>
FROM prefix_page
JOIN prefix_course ON prefix_course.id = prefix_page.course WHERE c.id = prefix_page.course) AS 'Pages'
 
,(SELECT COUNT(prefix_resource.id)
FROM prefix_resource
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files


===All new forum NEWS items (discussions) from all my Courses===
,(SELECT COUNT(prefix_url.id)
change "userid = 26" and "id = 26" to a new user id
FROM prefix_url
<code sql>
JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links
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>


==Resource count in courses==
,(SELECT MAX(prefix_resource.timemodified)
+ (First)Teacher name
FROM prefix_resource
+ Where course is inside some specific Categories
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%' ) AS SyllabusDate
<code sql>
SELECT
COUNT(*) AS count
,r.course
,c.shortname shortname
,c.fullname coursename
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
  FROM prefix_role_assignments AS ra
  JOIN prefix_user as u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
FROM prefix_resource r
JOIN prefix_course c ON r.course = c.id
WHERE c.category IN (10,13,28,18,26)
GROUP BY r.course
ORDER BY COUNT(*) DESC
</code>


==News Forum - Discussions COUNT==
,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified)))
Which is actually... How much instructions students get from their teachers
FROM prefix_resource
<code sql>
JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo
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>


==Special Roles==
,(SELECT count(l.id)
<code sql>
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
FROM prefix_role_assignments AS ra
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
JOIN prefix_log AS l ON l.userid = u.id
JOIN prefix_course AS c ON ctx.instanceid = c.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND l.course = c.id LIMIT 1) AS Edits
WHERE ra.roleid > 6
</code>


==Courses without Teachers==
Actually, shows the number of Teachers in a course.
<code sql>
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
FROM prefix_course AS c
ORDER BY Teachers ASC
JOIN prefix_course_categories AS cc ON cc.id = c.category
WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY') AND RIGHT(c.shortname,4) = :term
AND cc.name LIKE :category
 
ORDER BY c.fullname
</code>
</code>


==Site-Wide Grade Report with All Items==
==Grade Reports==
===Site-Wide Grade Report with All Items===
Shows grades for all course items along with course totals for each student.
Shows grades for all course items along with course totals for each student.
<code sql>
<code sql>
Line 505: Line 612:
</code>
</code>


==Site-Wide Grade Report with Just Course Totals==
===Site-Wide Grade Report with Just Course Totals===
A second site-wide grade report for all students that just shows course totals.
A second site-wide grade report for all students that just shows course totals.
<code sql>
<code sql>
Line 535: Line 642:
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time
DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time
</code>
</code>
==All Ungraded Assignments==
 
Returns all the submitted assignments that still need grading
===Learner report by Learner with grades===
Which Learners in which course and what are the grades
<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
 
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
</code>
 
 
==Activity Module Reports==
 
===How many SCORM activities are used in each Course===
<code sql>
SELECT cm.course,c.fullname ,m.name
,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
FROM `prefix_course_modules` as cm
  JOIN prefix_modules as m ON cm.module=m.id
  JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%scorm%'
GROUP BY cm.course,cm.module
ORDER BY count(cm.id) desc
</code>
 
===Detailed ACTIONs for each MODULE===
<code sql>
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
</code>
 
===Most popular ACTIVITY===
<code sql>
SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC
</code>
 
===System wide use of ACTIVITIES and RESOURCES===
<code sql>
SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC
</code>
 
===LOG file ACTIONS per MODULE per COURSE (IDs)===
<code sql>
select course,module,action,count(action) as summa from prefix_log
where action <> 'new'
group by course,action,module
order by course,module,action
</code>
 
===System Wide usage count of various course Activities===
(Tested and works fine in Moodle 2.x)
Like: Forum, Wiki, Blog, Assignment, Database,
#Within specific category
#Teacher name in course
 
<code sql>
<code sql>
select
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
u.firstname AS "First",
 
u.lastname AS "Last",
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
c.fullname AS "Course",
FROM prefix_role_assignments AS ra
a.name AS "Assignment"
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


from prefix_assignment_submissions as asb
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
join prefix_assignment as a ON a.id = asb.assignment
JOIN prefix_modules AS m ON cm.module = m.id
join prefix_user as u ON u.id = asb.userid
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
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
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
and cm.module = 1
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students


order by c.fullname, a.name, u.lastname
FROM prefix_course AS c
WHERE c.category IN ( 18)
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
</code>
</code>


==All Ungraded Assignments w/ Link==
===Use of selected ACTIVITIES and RESOURCES per COURSE by CATEGORY===
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.
This version uses SUM(IF()) instead of subqueries:
 
<code sql>
<code sql>
select
SELECT
u.firstname AS "First",
 
u.lastname AS "Last",
CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.fullname,'</a>') AS Course
c.fullname AS "Course",
, CONCAT(u.lastname,', ', u.firstname) AS Teacher
a.name AS "Assignment",
, COUNT(DISTINCT IF(cm.visible = 1,cm.module,0) ) AS 'Visible Modules'
, SUM(IF(m.name LIKE '%forum%',1,0)) AS Forums
, SUM(IF(m.name LIKE '%quiz%',1,0)) AS Quizzes
, SUM(IF(m.name LIKE '%assignment%',1,0)) AS Assignments
, SUM(IF(m.name LIKE '%page%',1,0)) AS Pages
, SUM(IF(m.name LIKE '%resource%',1,0)) AS Files
, SUM(IF(m.name LIKE '%url%',1,0)) AS Links
 
FROM prefix_course AS c
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_course_modules AS cm ON cm.course = c.id
JOIN prefix_modules AS m ON cm.module = m.id


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


%%FILTER_CATEGORIES:c.category%%


from prefix_assignment_submissions as asb
GROUP BY c.fullname
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


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


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


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


Line 619: Line 850:
</code>
</code>


==Aggregated Teacher activity by "WEB2" Modules==
===Aggregated Teacher activity by "WEB2" Modules===
(Tested and works fine in Moodle 2.x)
(Tested and works fine in Moodle 2.x)
The NV column shows activity without VIEW log activity
The NV column shows activity without VIEW log activity
Line 638: Line 869:
</code>
</code>


==Courses that are defined as using GROUPs==
===List all the certificates issued, sort by variables in the custom profile fields===
Note: The SQL queries look intimidating at first, but isn't really that difficult to learn. I've seen in the forums that users wanted to do 'site-wide' groups in 1.9x. This is sort of the idea. It pulls all the certificates issued to all users sorted by the custom profile fields, which in my case is the Units or Depts (i.e. my site wide groups). Why certificates? I've explored with both grades and quizzes, the course admins are not really interested in the actual grades but whether the learner received a certificate (i.e. passed the course with x, y, z activities). It also saves me from creating groups and assigning them into the right groups. Even assigning in bulk is not efficient, since I have upward of 25 groups per course and constantly new learners enrolling in courses. The limitation is something to do with the server? as it only pull 5000 rows of data. If anyone figured out how to change this, please let me know. In the meantime, the work around is to pull only a few units/depts at a time to limit the number of rows. This is fine at the moment, since each course admin are only responsible for certain units/depts.
 
<code sql>
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
SELECT
FROM `prefix_course` AS c
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y/%m/%d' ) AS Date,
WHERE groupmode > 0
prefix_certificate_issues.classname AS Topic,
</code>
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


==System Wide usage count of various course Activities==
INNER JOIN prefix_certificate
(Tested and works fine in Moodle 2.x)
on prefix_certificate_issues.certificateid = prefix_certificate.id
Like: Forum, Wiki, Blog, Assignment, Database,
#Within specific category
#Teacher name in course


<code sql>
WHERE prefix_user_info_data.data='Unit 1'
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
OR prefix_user_info_data.data='Unit 2'
OR prefix_user_info_data.data='Unit 3'


,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
ORDER BY Units, Name, Topic ASC
FROM prefix_role_assignments AS ra
</code>
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher


,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
===Counter Blog usage in Courses,system wide===
JOIN prefix_modules AS m ON cm.module = m.id
What teachers in what courses, uses blogs and how many + student count in that course.
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
<code sql>
,(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
SELECT ( @counter := @counter+1) as counter,
prefix_course_modules AS cm
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
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
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
JOIN prefix_modules AS m ON cm.module = m.id
  FROM prefix_role_assignments AS ra
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
  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
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
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 Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
Line 684: Line 917:
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students


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


==List all the certificates issued, sort by variables in the custom profile fields==
==Assignment Module Reports==
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.
===All Ungraded Assignments===
Returns all the submitted assignments that still need grading
<code sql>
select
u.firstname AS "First",
u.lastname AS "Last",
c.fullname AS "Course",
a.name AS "Assignment"
 
from prefix_assignment_submissions as asb
join prefix_assignment as a ON a.id = asb.assignment
join prefix_user as u ON u.id = asb.userid
join prefix_course as c ON c.id = a.course
join prefix_course_modules as cm ON c.id = cm.course
 
where asb.grade < 0 and cm.instance = a.id
and cm.module = 1
 
order by c.fullname, a.name, u.lastname
</code>


===All Ungraded Assignments w/ Link===
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>
<code sql>
SELECT
select
DATE_FORMAT( FROM_UNIXTIME(prefix_certificate_issues.timecreated), '%Y/%m/%d' ) AS Date,
u.firstname AS "First",
prefix_certificate_issues.classname AS Topic,
u.lastname AS "Last",
prefix_certificate.name AS Certificate,
c.fullname AS "Course",
prefix_certificate_issues.studentname as Name,
a.name AS "Assignment",
prefix_user_info_data.data AS Units


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


INNER JOIN prefix_user_info_data
on prefix_certificate_issues.userid = prefix_user_info_data.userid


INNER JOIN prefix_certificate
from prefix_assignment_submissions as asb
on prefix_certificate_issues.certificateid = prefix_certificate.id
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 prefix_user_info_data.data='Unit 1'
where asb.grade < 0 and cm.instance = a.id and cm.module = 1
OR prefix_user_info_data.data='Unit 2'
OR prefix_user_info_data.data='Unit 3'


ORDER BY Units, Name, Topic ASC
order by c.fullname, a.name, u.lastname
</code>
</code>
==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...
===Assignments 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
*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>
<code sql>
SELECT prefix_course. * , prefix_course_categories. *
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_course, prefix_course_categories
 
WHERE prefix_course.category = prefix_course_categories.id
,(SELECT Count( ra.userid ) AS Users
AND (
FROM prefix_role_assignments AS ra
prefix_course_categories.path LIKE '/$s/%'
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
OR prefix_course_categories.path LIKE '/$s'
WHERE ra.roleid = 5
)
AND ctx.instanceid = c.id
) AS Students
 
,(
SELECT count(qz.id)
FROM prefix_quiz AS qz
JOIN prefix_course_modules AS cm ON qz.course = cm.course
WHERE cm.instance = qz.id AND cm.module = 12 AND qz.course = c.id
) nQuizzes
 
,(
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(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
) unCheckedAssignments
 
,(
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
) SubmittedAssignments
 
FROM prefix_course AS c
WHERE 1=1 
%%FILTER_YEARS:c.fullname%%
## You can enable the SEMESTER filter as well,
## by uncommenting the following line:
## %%FILTER_SEMESTERS:c.fullname%%
</code>
</code>


==Who is using "Single File Upload" assignment==
===Who is using "Single File Upload" assignment===
<code sql>
<code sql>
SELECT  
SELECT  
Line 750: Line 1,052:
WHERE `assignmenttype` LIKE 'uploadsingle'
WHERE `assignmenttype` LIKE 'uploadsingle'
</code>
</code>
==Resource Module Reports==
===List "Recently uploaded files"===
see what users are uploading
<code sql>
SELECT FROM_UNIXTIME(time,'%Y %M %D %h:%i:%s') as time ,ip,userid,url,info 
FROM `prefix_log`
WHERE `action` LIKE 'upload'
ORDER BY `prefix_log`.`time`  DESC
</code>
===List Courses that loaded a specific file: "X"===
Did the Teacher (probably) uploaded course's Syllabus ?
<code sql>
SELECT c.id, c.fullname  FROM `prefix_log` as l
JOIN prefix_course as c ON c.id = l.course
WHERE `action` LIKE '%upload%' AND ( info LIKE '%Syllabus%' OR info LIKE '%Sylabus%' ) GROUP BY c.id
</code>
===All resources that link to some specific external website===
+ link to course
+ who's the teacher
+ link to external resource
<code sql>
SELECT
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
,c.shortname,r.name
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
FROM prefix_resource AS r
JOIN prefix_course AS c ON r.course = c.id
WHERE r.reference LIKE 'http://info.oranim.ac.il/home%'
</code>
==="Compose Web Page" RESOURCE count===
<code sql>
SELECT course,prefix_course.fullname, COUNT(*) AS Total
FROM `prefix_resource`
JOIN `prefix_course` ON prefix_course.id = prefix_resource.course
WHERE type='html'
GROUP BY course
</code>
===Resource count in courses===
+ (First)Teacher name
+ Where course is inside some specific Categories
<code sql>
SELECT
COUNT(*) AS count
,r.course
,c.shortname shortname
,c.fullname coursename
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
  FROM prefix_role_assignments AS ra
  JOIN prefix_user as u ON ra.userid = u.id
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
  WHERE ra.roleid = 3 AND ctx.instanceid = r.course AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
FROM prefix_resource r
JOIN prefix_course c ON r.course = c.id
WHERE c.category IN (10,13,28,18,26)
GROUP BY r.course
ORDER BY COUNT(*) DESC
</code>
==Forum Module Reports==
===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>
===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>
==Quiz Module Reports==
===Generate a list of instructors and their email addresses for those courses that has "essay questions" in their quizzes===
<code sql>
SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions,
                co.fullname AS course_fullname, co.shortname AS course_shortname,
                qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose,
                u.firstname, u.lastname, u.email,
FROM 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===
<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 Grade-Book,
So grades could be uploaded into an administrative SIS.
<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>
==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>
[[Category:Contributed code]]

Latest revision as of 18:10, 28 August 2013

User and Role Report

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

SELECT r.name,l.action, count( l.userid ) as counter FROM `prefix_log` as l JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid JOIN prefix_role AS r ON ra.roleid = r.id WHERE ra.roleid IN (3,4,5) GROUP BY roleid,l.action ORDER BY counter desc

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

Reports a site global list of all users enroled in each course SELECT user.firstname AS Firstname, user.lastname AS Lastname, user.email AS Email, user.city AS City, course.fullname AS Course ,(SELECT name FROM prefix_role WHERE id=asg.roleid) FROM prefix_user AS user, prefix_course AS course, prefix_role_assignments AS asg INNER JOIN prefix_context AS context ON asg.contextid=context.id WHERE context.contextlevel = 50 AND user.id=asg.userid AND context.instanceid=course.id

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

Permissions Overides on Categories

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

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

(By: Séverin Terrier ) SELECT id , category , fullname , shortname , enrollable FROM `prefix_course` WHERE `guest` =1 AND `password` = "" AND `visible` =1

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, laastname, 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 mdl_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

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=enr.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 WHERE enr.id = uenr.enrolid AND enr.status = uenr.statu

Non-Participant Students

This report returns a list of students who are enrolled in courses in the specified category, but have no participation in the course during the specified time period. The number of "Logins" is provided for each student for the time period specified.

A "Login" is defined as course activity including viewing content. Click the "Logs" link to review the student activity. The Logs offer the option to review "View" activity as well as "Edit" activity.

Only "visible" courses are included in this report.

SELECT u.lastname AS Last , u.firstname AS First , u.idnumber AS IDnumber , u.email AS email , c.shortname AS CourseID , count(l.id) AS Logins , 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

LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND FROM_UNIXTIME(l.time) > :start_time AND FROM_UNIXTIME(l.time) < :end_time

WHERE ra.roleid =5 AND ctx.instanceid = c.id AND c.visible=1

GROUP BY u.idnumber

HAVING Logins < 2

Low-Participation Students

This report returns a list of students who are enrolled in courses, but have very low participation in the course during the specified time period, defined as fewer than 2 "Edits." The number of "Edits" is provided for each student for the time period specified.

An "Edit" is defined as course activity other than viewing content. Click the "Logs" link to review the student activity. The Logs offer the option to review "View" activity as well as "Edit" activity.

Only "visible" courses are included in this report.

Note: This report can take up to 5 minutes to run. Please be patient.

SELECT u.lastname AS Last , u.firstname AS First , u.idnumber AS IDnumber , u.email AS email , c.shortname AS CourseID , count(l.id) AS Edits , 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

LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%" AND FROM_UNIXTIME(l.time) > :start_time AND FROM_UNIXTIME(l.time) < :end_time

WHERE ra.roleid =5 AND ctx.instanceid = c.id AND c.visible=1

GROUP BY u.idnumber

HAVING Edits < 2

Log Activity Reports

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

Weekly Instructor Participation per Term

Note: at our institution, term code is contained within the shortname of the course. Our terms are 12 weeks long. Adjust as needed for your institution.

Displays participation of instructors in all courses in a given term by week, including pre-term and post-term edits. An edit is defined as a change to the course, such as a discussion post, the 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.
   You may also use % in the Term code to see all courses in a given Academic Year (e.g. "%13") or during a specific Term (e.g. "FL%")

Note: This query can take several minutes to run. Please be patient.

SELECT c.shortname AS CourseID , cc.name AS Category , CONCAT('<a target="_new" href="%%WWWROOT%%/course/user.php',CHAR(63),'id=1&user=',u.id,'&mode=alllogs">',u.firstname ,' ',u.lastname,'</a>') AS Instructor , 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 '%OL-%' AND RIGHT(c.shortname,4) LIKE :term AND c.shortname LIKE :course AND u.lastname LIKE :last_name

GROUP BY u.idnumber, c.id ORDER BY RIGHT(c.shortname,2), c.shortname

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

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

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

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 '
  1. <a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a>
  2. '; } 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

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

Outcomes per Course

This report accepts a course shortname and returns a list of the graded activities in the course, listing which outcomes (if any) have been identified for each activity.

SELECT c.shortname AS Course ,gi.itemmodule AS 'Activity Type'

,(SELECT gi.itemname FROM prefix_grade_items AS gi WHERE gi.itemnumber = 0 AND gi.courseid = cm.course AND gi.iteminstance = cm.instance) AS 'Item Name'

, (SELECT GROUP_CONCAT(go.shortname SEPARATOR ' ') FROM prefix_grade_items AS gi JOIN prefix_grade_outcomes AS go ON go.id = gi.outcomeid WHERE gi.courseid = cm.course AND gi.iteminstance = cm.instance AND gi.itemnumber >= 1000) AS 'Outcomes'

FROM prefix_course AS c JOIN prefix_course_modules AS cm ON cm.course = c.id

JOIN prefix_grade_items as gi ON gi.courseid = cm.course AND gi.iteminstance = cm.instance

WHERE c.shortname LIKE :shortname GROUP BY gi.iteminstance ORDER BY c.shortname

Course Readiness Report

Note: This report uses a method of encoding the term, instruction method, and other details in the course shortname and idnumber fields-- you may need to adapt this section of the code for your own institution.

This report accepts a term code, e.g. FL13, and provides a list of all courses currently scheduled for that term. The following information is provided per course:

   The full name of the course, with a link to the course
   The instructional method of the course (OL, HY, BL)
   The course category, indicating undergraduate, SOE, or Graduate programs (e.g. "2013-Fall-UG","2013-Fall-Grad", "2013-Fall-SoE", or "%" for all categories)
   The teacher presently assigned to teach the course (first teacher listed only)
   The number of edits the primary teacher has made, where an edit is any change to the course, not including viewing existing content
   A count of the number of resources and activities per course, e.g. Forum, Assignment, Quiz, Pages, Links, and Attached Files
   The date of last syllabus update, provided the syllabus is attached as a file and the filename includes the string "syllabus" (also provided as "days ago")*

This report may be downloaded as a Comma Separated Values file and viewed in Excel.

  • Note: A date value of "1969-12-31 19:00:00" indicates that no syllabus file was found. The date "2012-11-05 15:04:15" is the date of the template creation, indicating that the "syllabus" file is a blank placeholder.

SELECT c.fullname AS Course

,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">link</a>') AS Link

,RIGHT(c.idnumber,2) AS Type

, substring_index(substr(c.shortname FROM locate('.',c.shortname)+1),'-',1) AS Section

,cc.name AS Category

,(SELECT CONCAT(u.lastname,', ', u.firstname) 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 uid.data 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 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 = 'ID')WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS ID

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

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

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

,(SELECT COUNT(prefix_page.id) FROM prefix_page JOIN prefix_course ON prefix_course.id = prefix_page.course WHERE c.id = prefix_page.course) AS 'Pages'

,(SELECT COUNT(prefix_resource.id) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course) AS Files

,(SELECT COUNT(prefix_url.id) FROM prefix_url JOIN prefix_course ON prefix_course.id = prefix_url.course WHERE c.id = prefix_url.course) AS Links

,(SELECT MAX(prefix_resource.timemodified) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%' ) AS SyllabusDate

,(SELECT TO_DAYS(NOW())-TO_DAYS(FROM_UNIXTIME(MAX(prefix_resource.timemodified))) FROM prefix_resource JOIN prefix_course ON prefix_course.id = prefix_resource.course WHERE c.id = prefix_resource.course AND prefix_resource.name LIKE '%syllabus%') AS DaysAgo

,(SELECT count(l.id) 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 JOIN prefix_log AS l ON l.userid = u.id WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND l.course = c.id LIMIT 1) AS Edits

FROM prefix_course AS c JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE RIGHT(c.idnumber,2) IN ('OL', 'BL', 'HY') AND RIGHT(c.shortname,4) = :term AND cc.name LIKE :category

ORDER BY c.fullname

Grade Reports

Site-Wide Grade Report with All Items

Shows grades for all course items along with course totals for each student. SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', c.fullname AS 'Course', cc.name AS 'Category',

CASE

 WHEN gi.itemtype = 'course' 
  THEN c.fullname + ' Course Total'
 ELSE gi.itemname

END AS 'Item Name',

ROUND(gg.finalgrade,2) AS Grade, DATEADD(ss,gi.timemodified,'1970-01-01') AS Time

FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE gi.courseid = c.id ORDER BY lastname For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD: DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time

Site-Wide Grade Report with Just Course Totals

A second site-wide grade report for all students that just shows course totals. 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,gi.timemodified,'1970-01-01') AS Time

FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories as cc ON cc.id = c.category

WHERE gi.courseid = c.id AND gi.itemtype = 'course'

ORDER BY lastname For MySQL users, you'll need to use the MySQL DATE_ADD function instead of DATEADD: DATE_ADD('1970-01-01', INTERVAL gi.timemodified SECONDS) AS Time

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


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,

  1. Within specific category
  2. 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

Use of selected ACTIVITIES and RESOURCES per COURSE by CATEGORY

This version uses SUM(IF()) instead of subqueries:

SELECT

CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php',CHAR(63),'id=',c.id,'">',c.fullname,'</a>') AS Course , CONCAT(u.lastname,', ', u.firstname) AS Teacher , COUNT(DISTINCT IF(cm.visible = 1,cm.module,0) ) AS 'Visible Modules' , SUM(IF(m.name LIKE '%forum%',1,0)) AS Forums , SUM(IF(m.name LIKE '%quiz%',1,0)) AS Quizzes , SUM(IF(m.name LIKE '%assignment%',1,0)) AS Assignments , SUM(IF(m.name LIKE '%page%',1,0)) AS Pages , SUM(IF(m.name LIKE '%resource%',1,0)) AS Files , SUM(IF(m.name LIKE '%url%',1,0)) AS Links

FROM prefix_course AS c JOIN prefix_context AS ctx ON ctx.instanceid = c.id JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_course_modules AS cm ON cm.course = c.id JOIN prefix_modules AS m ON cm.module = m.id

WHERE ra.roleid = 3

%%FILTER_CATEGORIES:c.category%%

GROUP BY c.fullname


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

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 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
  • 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 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(qz.id) FROM prefix_quiz AS qz JOIN prefix_course_modules AS cm ON qz.course = cm.course WHERE cm.instance = qz.id AND cm.module = 12 AND qz.course = c.id ) nQuizzes

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

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

FROM prefix_course AS c WHERE 1=1 %%FILTER_YEARS:c.fullname%%

    1. You can enable the SEMESTER filter as well,
    2. by uncommenting the following line:
    3. %%FILTER_SEMESTERS:c.fullname%%

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

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

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

Quiz Module Reports

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

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 Grade-Book, So grades could be uploaded into an administrative SIS. 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

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