Difference between revisions of "ad-hoc contributed reports"

Jump to: navigation, search

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

(Attempt to categorise reports so it's easier to find them! No changes just headings added :))
Line 1: Line 1:
{{Sitewide reports}}
+
==User and Role Report==
These are community contributed reports for the [[Custom SQL queries report]] site-wide report plugin (module) <br/>
 
Everyone is welcome to add their own. until we find a better way to exchange reports between us.<br/>
 
Enjoy :-)<br/>
 
 
 
----
 
===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>
 
 
 
===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 43: Line 9:
 
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 217: Line 85:
 
SELECT COUNT(id) as Users  FROM `prefix_user`  
 
SELECT COUNT(id) as Users  FROM `prefix_user`  
 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
 
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
</code>
 
 
===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>
 
</code>
  
Line 259: Line 110:
 
</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>
 
<code sql>
SELECT c.fullname,f.name,f.type
+
SELECT u.firstname, u.lastname, c.id, c.fullname
,(SELECT count(id) FROM prefix_forum_discussions as fd WHERE f.id = fd.forum) as Discussions
+
FROM prefix_course AS c
,(SELECT count(distinct fd.userid) FROM prefix_forum_discussions as fd WHERE fd.forum = f.id) as UniqueUsersDiscussions
+
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
,(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
+
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
,(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
+
JOIN prefix_user AS u ON u.id = ra.userid
,(SELECT Count( ra.userid ) AS Students
+
WHERE u.id = 2
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>
 
</code>
  
===Blog activity per Course (not including VIEW)===
+
===Special Roles===
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)
 
 
<code sql>
 
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
+
SELECT ra.roleid,r.name
,m.name ,count(cm.id) as counter
+
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,(SELECT Count( ra.userid ) AS Users
+
,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_context AS ctx ON ra.contextid = ctx.id
+
JOIN prefix_role AS r ON r.id = ra.roleid
WHERE ra.roleid = 5
+
JOIN prefix_user AS u ON u.id = ra.userid
AND ctx.instanceid = c.id
+
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
) AS Students
+
JOIN prefix_course AS c ON ctx.instanceid = c.id
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
+
WHERE ra.roleid > 6
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id  
 
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 
GROUP BY cm.course,cm.module order by counter desc
 
 
</code>
 
</code>
  
===Detailed WIKI activity (per wiki per course)===
+
===Courses without Teachers===
Including Number of Students in course (for reference)
+
Actually, shows the number of Teachers in a course.
 
<code sql>
 
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID 
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,(SELECT Count( ra.userid ) AS Users
+
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
FROM prefix_role_assignments AS ra
 
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
,m.name
+
FROM prefix_course AS c
, ( SELECT count(id) FROM prefix_log WHERE cmid = cm.id AND action LIKE '%updat%' ) as 'UPDAT E'
+
ORDER BY Teachers ASC
, ( 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>
  
===All Courses which uploaded a Syllabus file===
+
==Log Acivity Reports==
+ under specific Category
+
===Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)===
+ show first Teacher in that course
 
+ link Course's fullname to actual course
 
 
<code sql>
 
<code sql>
SELECT
+
SELECT l.action, count( l.userid ) as counter , r.name
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
FROM `prefix_log` as l
,c.shortname,r.name
+
JOIN `prefix_role_assignments` AS ra on l.userid = ra.userid
,(SELECT CONCAT(u.firstname,' ', u.lastname) as Teacher
+
JOIN `prefix_role` AS r ON ra.roleid = r.id
FROM prefix_role_assignments AS ra
+
WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' )
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
GROUP BY roleid,l.action
JOIN prefix_user as u ON u.id = ra.userid
+
order by r.name,counter desc
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)
 
 
</code>
 
</code>
  
===All resources that link to some specific external website===
+
===Total Activity of Roles:"Teacher" and "None-Editing Teacher" by Dates and by Hours===
+ link to course
+
The output columns of this report table can be used as base for a Pivot-Table
+ who's the teacher
+
which will show the amount of '''activity''' per '''hour''' per '''days''' in 3D graph view.
+ link to external resource
+
 
 
<code sql>
 
<code sql>
SELECT
+
SELECT DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%Y/%m/%d' ) AS grptimed ,
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
DATE_FORMAT( FROM_UNIXTIME( l.time ) , '%k' ) AS grptimeh  , count( l.userid ) AS counter
,c.shortname,r.name
+
FROM `prefix_log` AS l
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
+
JOIN prefix_user AS u ON u.id = l.userid
FROM prefix_role_assignments AS ra
+
JOIN prefix_role_assignments AS ra ON l.userid = ra.userid
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
+
JOIN prefix_role AS r ON r.id = ra.roleid
JOIN prefix_user AS u ON u.id = ra.userid
+
WHERE ra.roleid IN (3,4)
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
+
GROUP BY grptimed,grptimeh
,concat('<a target="_new" href="%%WWWROOT%%/mod/resource/view.php?id=',r.id,'">',r.name,'</a>') AS Resource
+
ORDER BY grptimed,grptimeh
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>
  
Line 385: Line 184:
 
</code>
 
</code>
  
===User's courses===
+
==Course Reports==
change "u.id = 2" with a new user id
+
===Most Active courses===
 
<code sql>
 
<code sql>
SELECT u.firstname, u.lastname, c.id, c.fullname
+
SELECT COUNT(l.id) hits, l.course courseId, c.fullname coursename
FROM prefix_course AS c
+
FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
GROUP BY courseId
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
+
ORDER BY hits DESC
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE u.id = 2
 
 
</code>
 
</code>
  
===All Forum type:NEWS===
+
===RESOURCE count for each COURSE===
 
<code sql>
 
<code sql>
SELECT f.id, f.name
+
SELECT COUNT(l.id) count, l.course, c.fullname coursename
FROM prefix_course_modules AS cm
+
FROM prefix_resource l INNER JOIN prefix_course c on l.course = c.id
JOIN prefix_modules AS m ON cm.module = m.id
+
GROUP BY course
JOIN prefix_forum AS f ON cm.instance = f.id
+
ORDER BY count DESC
WHERE m.name = 'forum'
 
AND f.type = 'news'
 
 
</code>
 
</code>
  
===All new forum NEWS items (discussions) from all my Courses===
+
===Courses that are defined as using GROUPs===
change "userid = 26" and "id = 26" to a new user id
 
 
<code sql>
 
<code sql>
SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
FROM prefix_forum_discussions as fd
+
FROM `prefix_course` AS c
JOIN prefix_forum as f ON f.id = fd.forum
+
WHERE groupmode > 0
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>
 
</code>
  
==Resource count in courses==
+
===List all Courses in and below a certain category===
+ (First)Teacher name
+
Use this SQL code to retrieve all courses that exist in or under a set category.
+ 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>
 
  
==News Forum - Discussions COUNT==
+
$s should be the id of the category you want to know about...
Which is actually... How much instructions students get from their teachers
 
 
<code sql>
 
<code sql>
SELECT c.shortname ,
+
SELECT prefix_course. * , prefix_course_categories. *
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
+
FROM prefix_course, prefix_course_categories
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
+
WHERE prefix_course.category = prefix_course_categories.id
  FROM prefix_role_assignments AS ra
+
AND (
  JOIN prefix_user AS u ON ra.userid = u.id
+
prefix_course_categories.path LIKE '/$s/%'
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
+
OR prefix_course_categories.path LIKE '/$s'
  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>
 
</code>
  
==Special Roles==
+
===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)
 
<code sql>
 
<code sql>
SELECT ra.roleid,r.name
+
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
,concat('<a target="_new" href="%%WWWROOT%%/course/user.php?id=1&user=',ra.userid,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
+
,m.name ,count(cm.id) as counter
,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
 
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
+
WHERE ra.roleid = 5
JOIN prefix_context AS ctx ON (ctx.id = ra.contextid AND ctx.contextlevel = 50)
+
AND ctx.instanceid = c.id
JOIN prefix_course AS c ON ctx.instanceid = c.id
+
) AS Students
WHERE ra.roleid > 6
+
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
 +
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id  
 +
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
 +
GROUP BY cm.course,cm.module order by counter desc
 
</code>
 
</code>
  
==Courses without Teachers==
+
===All Courses which uploaded a Syllabus file===
Actually, shows the number of Teachers in a course.
+
+ under specific Category
 +
+ show first Teacher in that course
 +
+ link Course's fullname to actual course
 
<code sql>
 
<code sql>
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
SELECT
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
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_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 3 AND ctx.instanceid = c.id) AS Teachers
+
JOIN prefix_user as u ON u.id = ra.userid
FROM prefix_course AS c
+
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) as Teacher
ORDER BY Teachers ASC
+
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)
 
</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 525: Line 293:
 
</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 555: Line 323:
 
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==
 +
 
 +
===Detailed ACTIONs for each MODULE===
 
<code sql>
 
<code sql>
select
+
SELECT module,action,count(id) as counter
u.firstname AS "First",
+
FROM prefix_log
u.lastname AS "Last",
+
GROUP BY module,action
c.fullname AS "Course",
+
ORDER BY module,counter desc
a.name AS "Assignment"
+
</code>
  
from prefix_assignment_submissions as asb
+
===Most popular ACTIVITY===
join prefix_assignment as a ON a.id = asb.assignment
+
<code sql>
join prefix_user as u ON u.id = asb.userid
+
SELECT COUNT(l.id) hits, module
join prefix_course as c ON c.id = a.course
+
FROM prefix_log l
join prefix_course_modules as cm ON c.id = cm.course
+
WHERE module != 'login' AND module != 'course' AND module != 'role'
 +
GROUP BY module
 +
ORDER BY hits DESC
 +
</code>
  
where asb.grade < 0 and cm.instance = a.id
+
===System wide use of ACTIVITIES and RESOURCES===
and cm.module = 1
+
<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>
  
order by c.fullname, a.name, u.lastname
+
===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>
 
</code>
  
==All Ungraded Assignments w/ Link==
+
===System Wide usage count of various course Activities===
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.
+
(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
  
'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
+
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
+
JOIN prefix_modules AS m ON cm.module = m.id
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
+
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
AS "Assignmentlink"
 
  
 +
,(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_assignment_submissions as asb
+
FROM prefix_course AS c
join prefix_assignment as a ON a.id = asb.assignment
+
WHERE c.category IN ( 18)
join prefix_user as u ON u.id = asb.userid
+
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
join prefix_course as c ON c.id = a.course
+
</code>
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 639: Line 488:
 
</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 658: Line 507:
 
</code>
 
</code>
  
==Courses that are defined as using GROUPs==
+
===List all the certificates issued, sort by variables in the custom profile fields===
<code sql>
 
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
FROM `prefix_course` AS c
 
WHERE groupmode > 0
 
</code>
 
 
 
==System Wide usage count of various course Activities==
 
(Tested and works fine in Moodle 2.x)
 
Like: Forum, Wiki, Blog, Assignment, Database,
 
#Within specific category
 
#Teacher name in course
 
 
 
<code sql>
 
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 
 
 
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
 
FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
JOIN prefix_user AS u ON u.id = ra.userid
 
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
 
 
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 
 
 
,(SELECT count( m.name ) AS count FROM
 
prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
 
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
 
 
 
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 
JOIN prefix_modules AS m ON cm.module = m.id
 
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
 
 
 
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
 
 
FROM prefix_course AS c
 
WHERE c.category IN ( 18)
 
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
 
</code>
 
 
 
==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.
 
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.
  
Line 735: Line 533:
 
ORDER BY Units, Name, Topic ASC
 
ORDER BY Units, Name, Topic ASC
 
</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...
+
===Counter Blog usage in Courses,system wide===
 +
What teachers in what courses, uses blogs and how many + student count in that course.
 +
<code sql>
 +
 
 +
SELECT ( @counter := @counter+1) as counter,
 +
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
 +
 
 +
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 +
  FROM prefix_role_assignments AS ra
 +
  JOIN prefix_user AS u ON ra.userid = u.id
 +
  JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 +
  WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher
 +
 
 +
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 +
 
 +
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
 +
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
 +
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 +
 
 +
FROM prefix_course AS c, (SELECT @counter := 0) as s_init
 +
WHERE c.category IN ( 8,13,15)
 +
HAVING Blogs > 0
 +
ORDER BY Blogs DESC
 +
</code>
 +
 
 +
==Assignment Module Reports==
 +
===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 prefix_course. * , prefix_course_categories. *
+
select
FROM prefix_course, prefix_course_categories
+
u.firstname AS "First",
WHERE prefix_course.category = prefix_course_categories.id
+
u.lastname AS "Last",
AND (
+
c.fullname AS "Course",
prefix_course_categories.path LIKE '/$s/%'
+
a.name AS "Assignment",
OR prefix_course_categories.path LIKE '/$s'
+
 
)
+
'<a href="http://education.varonis.com/mod/assignment/submissions.php' + char(63) +
 +
+ 'id=' + cast(cm.id as varchar) + '&userid=' + cast(u.id as varchar)
 +
+ '&mode=single&filter=0&offset=2">' + a.name + '</a>'
 +
AS "Assignmentlink"
 +
 
 +
 
 +
from prefix_assignment_submissions as asb
 +
join prefix_assignment as a ON a.id = asb.assignment
 +
join prefix_user as u ON u.id = asb.userid
 +
join prefix_course as c ON c.id = a.course
 +
join prefix_course_modules as cm ON c.id = cm.course
 +
 
 +
where asb.grade < 0 and cm.instance = a.id and cm.module = 1
 +
 
 +
order by c.fullname, a.name, u.lastname
 
</code>
 
</code>
  
==Who is using "Single File Upload" assignment==
+
===Who is using "Single File Upload" assignment===
 
<code sql>
 
<code sql>
 
SELECT  
 
SELECT  
Line 771: Line 631:
 
</code>
 
</code>
  
==Counter Blog usage in Courses,system wide==
+
==Resource Module Reports==
What teachers in what courses, uses blogs and how many + student count in that course.
+
===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>
 
<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>
  
SELECT ( @counter := @counter+1) as counter,
+
==="Compose Web Page" RESOURCE count===
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
+
<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)
 
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
 
   FROM prefix_role_assignments AS ra
 
   FROM prefix_role_assignments AS ra
   JOIN prefix_user AS u ON ra.userid = u.id
+
   JOIN prefix_user as u ON ra.userid = u.id
 
   JOIN prefix_context AS ctx ON ctx.id = ra.contextid
 
   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
+
   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>
  
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
+
===FORUM use Count per COURSE by type -- not including NEWS Forum!===
JOIN prefix_modules AS m ON cm.module = m.id
+
<code sql>
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
+
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>
  
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
+
===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
 
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
+
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>
  
FROM prefix_course AS c, (SELECT @counter := 0) as s_init
+
===All Forum type:NEWS===
WHERE c.category IN ( 8,13,15)
+
<code sql>
HAVING Blogs > 0
+
SELECT f.id, f.name
ORDER BY Blogs DESC
+
FROM prefix_course_modules AS cm
 +
JOIN prefix_modules AS m ON cm.module = m.id
 +
JOIN prefix_forum AS f ON cm.instance = f.id
 +
WHERE m.name = 'forum'
 +
AND f.type = 'news'
 
</code>
 
</code>
  
==Learner report by Learner with grades==
+
===All new forum NEWS items (discussions) from all my Courses===
Which Learners in which course and what are the grades
+
change "userid = 26" and "id = 26" to a new user id
 
<code sql>
 
<code sql>
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category',
+
SELECT c.shortname,f.name,fd.name,FROM_UNIXTIME(fd.timemodified ,"%d %M %Y ") as Date
CASE WHEN gi.itemtype = 'Course'     
+
FROM prefix_forum_discussions as fd
THEN c.fullname + ' Course Total' 
+
JOIN prefix_forum as f ON f.id = fd.forum
ELSE gi.itemname
+
JOIN prefix_course as c ON c.id = f.course
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,
+
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>
  
if (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') as Pass
 
  
FROM prefix_course AS c  
+
===News Forum - Discussions COUNT===
JOIN prefix_context AS ctx ON c.id = ctx.instanceid
+
Which is actually... How much instructions students get from their teachers
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id  
+
<code sql>
JOIN prefix_user AS u ON u.id = ra.userid
+
SELECT c.shortname ,
JOIN prefix_grade_grades AS gg ON gg.userid = u.id  
+
concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
JOIN prefix_grade_items AS gi ON gi.id = gg.itemid
+
,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname)
JOIN prefix_course_categories AS cc ON cc.id = c.category
+
  FROM prefix_role_assignments AS ra
WHERE gi.courseid = c.id and gi.itemname != 'Attendance'
+
  JOIN prefix_user AS u ON ra.userid = u.id
ORDER BY `Name` ASC
+
  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>
 
</code>
  
[[Category:Contributed 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>

Revision as of 03:56, 8 March 2012

Contents

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

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

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

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

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

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)

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

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


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<br/>ALL'
 
,(SELECT COUNT(*) FROM prefix_log AS l WHERE l.course = c.id AND l.module LIKE '%wiki%' AND l.action LIKE '%add%' ) AS 'WikiActivity<br/>ADD'
 
,(SELECT COUNT(*) FROM prefix_log AS l WHERE l.course = c.id AND l.module LIKE '%wiki%' AND l.action LIKE '%edit%' ) AS 'WikiActivity<br/>EDIT'
 
,(SELECT COUNT(*) FROM prefix_log AS l WHERE l.course = c.id AND l.module LIKE '%wiki%' AND l.action LIKE '%annotate%' ) AS 'WikiActivity<br/>ANNOTATE'
 
,(SELECT COUNT(*) FROM prefix_log AS l WHERE l.course = c.id AND l.module LIKE '%wiki%' AND l.action LIKE '%comments%' ) AS 'WikiActivity<br/>Comments'
 
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
,(SELECT COUNT(*) FROM prefix_ouwiki_pages AS ouwp
JOIN prefix_ouwiki AS ouw ON ouw.id = ouwp.subwikiid
WHERE ouw.course = c.id GROUP BY ouw.course  ) AS OUWikiPages
 
,(SELECT COUNT( DISTINCT nwp.pagename ) FROM prefix_wiki_pages AS nwp
JOIN prefix_wiki AS nw ON nw.id = nwp.dfwiki WHERE nw.course = c.id ) AS NWikiPages
 
FROM prefix_course AS c
WHERE c.category IN ( 8,13,15)
HAVING Wikis > 0
ORDER BY 'WikiActivity<br/>ALL' DESC

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

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

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)