ad-hoc contributed reports
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 1.9.x)-(adding course categories)
SELECT
user.username AS Username,
user.firstname AS Firstname,
user.lastname AS Lastname,
user.email AS Email,
course.fullname AS Course,
(SELECT name from <prefix>_course_categories WHERE id = course.category) AS FirstCategory,
(SELECT name FROM <prefix>_course_categories WHERE id = (SELECT parent from <prefix>_course_categories WHERE id = course.category)) AS SecondCategory,
(SELECT name FROM <prefix>_course_categories WHERE id = (SELECT parent from <prefix>_course_categories WHERE id = (SELECT parent from <prefix>_course_categories WHERE id = course.category))) AS ThirdCategory,
(SELECT name FROM <prefix>_role WHERE id=asg.roleid) AS Role
FROM
<prefix>_user AS user,
<prefix>_course AS course,
<prefix>_user_info_data,
<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
ORDER BY user.username;
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
List of users who have been enrolled for more than 4 weeks
For Moodle 2.2 , by Isuru Madushanka Weerarathna
SELECT uenr.userid As UserID, IF(enr.courseid=enr.courseid ,'Y','N') As Enrolled,
IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') As EnrolledMoreThan4Weeks
FROM prefix_enrol As enr, prefix_user_enrolments AS uenr
WHERE enr.id = uenr.enrolid AND enr.status = uenr.status
Progress Report: A site-wide report of students, their courses, % of completion, and grade thus far
For Moodle 2.2, by Kimber Warden
SELECT u.firstname AS 'First Name'
,
u.lastname AS 'Last Name'
,
c.fullname AS 'Course'
,
FROM_UNIXTIME(ue.timecreated, '%m/%d/%Y') AS 'Enrolled'
,
IFNULL((SELECT DATE_FORMAT(MIN(FROM_UNIXTIME(log.time)),'%m/%d/%Y')
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id), 'Never') AS 'First Access'
,
(SELECT IF(ue.status=0, ' ', 'Withdrawn')) AS 'Withdrawn'
,
IFNULL((SELECT DATE_FORMAT(FROM_UNIXTIME(la.timeaccess), '%m/%d/%Y')
FROM prefix_user_lastaccess la
WHERE la.userid=u.id
AND la.courseid=c.id),'Never') AS 'Last Access',
/*A count of the number of distinct days a student has entered a course*/
IFNULL((SELECT COUNT(DISTINCT FROM_UNIXTIME(log.time, '%m/%d/%Y'))
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id
AND log.action='view'
AND log.module='course'
GROUP BY u.id
),'0') AS '# Days Accessed',
IFNULL((SELECT COUNT(gg.finalgrade)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid=gi.id
WHERE gi.courseid=c.id
AND gg.userid=u.id
AND gi.itemtype='mod'
GROUP BY u.id,c.id),'0') AS 'Activities Completed'
,
IFNULL((SELECT COUNT(gi.itemname)
FROM prefix_grade_items AS gi
WHERE gi.courseid = c.id
AND gi.itemtype='mod'), '0') AS 'Activities Assigned'
,
/*If Activities completed = activities assigned, show date of last log entry. Otherwise, show percentage complete. If Activities Assigned = 0, show 'n/a'.--*/
(SELECT IF(`Activities Assigned`!='0', (SELECT IF((`Activities Completed`)=(`Activities Assigned`),
/*--Last log entry--*/
(SELECT CONCAT('100% completed ',FROM_UNIXTIME(MAX(log.time),'%m/%d/%Y'))
FROM prefix_log log
WHERE log.course=c.id
AND log.userid=u.id),
/*--Percent completed--*/
(SELECT CONCAT(IFNULL(ROUND((`Activities Completed`)/(`Activities Assigned`)*100,0), '0'),'% complete')))), 'n/a')) AS '% of Course Completed'
,
IFNULL(CONCAT(ROUND((SELECT (IFNULL((SELECT SUM(gg.finalgrade)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gi.id=gg.itemid
WHERE gg.itemid=gi.id
AND gi.courseid=c.id
AND gi.itemtype='mod'
AND gg.userid=u.id
GROUP BY u.id,c.id),0)/(SELECT SUM(gi.grademax)
FROM prefix_grade_items AS gi
JOIN prefix_grade_grades AS gg ON gi.id=gg.itemid
WHERE gg.itemid=gi.id
AND gi.courseid=c.id
AND gi.itemtype='mod'
AND gg.userid=u.id
AND gg.finalgrade IS NOT NULL
GROUP BY u.id,c.id))*100),0),'%'),'n/a')
AS 'Quality of Work to Date',
(SELECT IF(`Activities Assigned`!='0',CONCAT(IFNULL(ROUND(((SELECT gg.finalgrade/gi.grademax
FROM prefix_grade_items AS gi
JOIN prefix_grade_grades AS gg ON gg.itemid=gi.id
WHERE gi.courseid=c.id
AND gg.userid=u.id
AND gi.itemtype='course'
GROUP BY 'gi.courseid')*100),0),'0'),'%'),'n/a')) AS 'Final Score (incl xtra credit)'
FROM prefix_user u
JOIN prefix_user_enrolments ue on ue.userid=u.id
JOIN prefix_enrol e ON e.id=ue.enrolid
JOIN prefix_course c on c.id = e.courseid
JOIN prefix_context AS ctx ON ctx.instanceid = c.id
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
JOIN prefix_role AS r ON r.id = e.roleid
WHERE ra.userid=u.id
AND ctx.instanceid=c.id
/*AND ue.status='0'*/ ### "0" for active, "1" for suspended. Leave commented out to include both.
AND ra.roleid='5' ### "5" = student
AND c.visible='1' ### "1" for course visible, "0" for hidden
GROUP BY u.id, c.id
ORDER BY u.lastname, u.firstname, c.fullname
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
Summary of per-student activity in the last 30 days
Notes:
- This query has only been tested on MySQL
- Change "select 30 as days" in the query for different time periods
select
u.id as userid,
u.username,
from_unixtime(l.time) as rvisit,
c.id as rcourseid,
c.fullname as rcourse,
agg.days as days,
agg.numdates,
agg.numcourses,
agg.numlogs
from
prefix_log l inner join prefix_user u
on l.userid = u.id
inner join prefix_course c
on l.course = c.id
inner join (
select
days,
userid,
max(time) as maxtime,
count(distinct date(from_unixtime(time))) as "numdates",
count(distinct course) as numcourses,
count(*) as numlogs
from
prefix_log l inner join prefix_course c
on l.course = c.id
inner join (
select 30 as days
) var
where
l.time > (unix_timestamp() - ((60*60*24)*days))
and c.format != "site"
group by userid) agg
on l.userid = agg.userid
where
l.time = agg.maxtime
and c.format != "site"
group by userid
order by l.time desc
I use this in conjunction with the following template:
username
(<a href="/moodle/report/log/user.php?course=1&mode=all&id=userid" target="_blank">Detailed Activity Log</a>)
Summary of courses’ activity in a father category that began on a date
(Resumen de actividad en cursos de una categoría padre que iniciaron en una fecha)
Note: These data are used to calculate the percentages of student participation in course activities using a worksheet.
Nota: Estos datos sirven para calcular los porcentajes de participación de los estudiantes en las actividades del curso utilizando una hoja de cálculo.
SELECT fullname Curso
,(SELECT CONCAT(u.firstname,' ', u.lastname)
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 IN (3)
AND ctx.instanceid = c.id LIMIT 1
) AS Profesor
,(SELECT cc.name
FROM prefix_course_categories AS cc
WHERE cc.id = c.category
) AS Periodo
, 'Unico' AS Grupo
,(
(SELECT COUNT(gg.id)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid = gi.id
JOIN prefix_assignment AS asg ON gi.iteminstance = asg.id
AND gi.courseid = asg.course
JOIN prefix_course_modules AS cm ON asg.id = cm.instance
AND cm.course = asg.course
WHERE gi.courseid = c.id
AND EXISTS (SELECT *
FROM prefix_log AS l
WHERE l.course = c.id
AND l.cmid = cm.id
AND l.userid = gg.userid
AND l.time = gg.timecreated)
)
+
(SELECT COUNT(gg.id)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid = gi.id
JOIN prefix_assignment AS asg ON gi.iteminstance = asg.id
AND gi.courseid = asg.course
JOIN prefix_course_modules AS cm ON asg.id = cm.instance
AND cm.course = asg.course
WHERE gi.courseid = c.id
AND NOT EXISTS (SELECT *
FROM prefix_log AS l
WHERE l.course = c.id
AND l.cmid = cm.id
AND l.userid = gg.userid
AND l.time = gg.timecreated)
AND gg.rawgrade > 0)
) AS 'Tareas
entregadas'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (l.module = 'forum' AND (l.action = 'add discussion'
OR l.action = 'add post'))
) AS 'Publicaciones
en foros'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (l.module = 'quiz' AND (l.action = 'attempt' OR
l.action = 'review'))
) AS 'Cuestionarios
contestados'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (l.module = 'wiki' AND l.action = 'edit')
) AS 'Participaciones
en wikis'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (l.module = 'glossary' AND l.action IN
('add entry'))
) AS 'Definiciones
en glosarios'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (
(l.module IN ('assignment','blog','file','page','resource','url')
AND l.action = 'view') OR
(l.module = 'forum' AND l.action IN
('view discussion', 'view forum')) OR
(l.module = 'quiz' AND l.action IN
('view summary','preview','view')) OR
(l.module = 'wiki' AND l.action IN
('diff','history','view')) OR
(l.module = 'glossary' AND l.action ='view entry')
)
) AS Vistas
,(SELECT Count( ra.userid )
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS Alumnos
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
JOIN prefix_assignment AS a ON cm.course = a.course
AND a.id = cm.instance
WHERE cm.course = c.id
AND m.name LIKE '%assignment%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Tareas
del curso'
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%forum%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Foros
del curso'
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%quiz%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Cuestionario
del curso'
,(SELECT count( m.name )
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%wiki%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Wikis
del curso'
,(SELECT count( m.name )
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%glossary%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Glosarios
del curso'
,(SELECT
(SELECT COUNT(r.id)
FROM prefix_resource AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1)
+
(SELECT COUNT(r.id)
FROM prefix_url AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1)
+
(SELECT COUNT(r.id)
FROM prefix_folder AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1)
+
(SELECT COUNT(r.id) AS count
FROM prefix_page AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND r.name NOT LIKE 'Descrip%'
AND cs.section > 0
AND cs.visible = 1)
) AS Recursos
FROM prefix_course AS c
WHERE c.visible = 1
AND DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%Y/%m/%d') >= '2012/09/10'
AND c.category IN (
SELECT cc.id FROM prefix_course_categories cc
WHERE parent = 7)
AND EXISTS (SELECT *
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)
AND NOT EXISTS (SELECT *
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_groups_members AS gm
ON gm.userid = u.id
JOIN prefix_groups AS g ON g.id = gm.groupid
WHERE ra.roleid IN (3,4) AND ctx.instanceid = c.id
AND g.courseid = c.id)
UNION
SELECT fullname Curso
,(SELECT CONCAT(u.firstname,' ', u.lastname)
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_groups_members AS gm ON gm.userid = u.id
WHERE ra.roleid IN (3,4) AND ctx.instanceid = c.id AND gm.groupid = g.id) AS Profesor
,(SELECT cc.name
FROM prefix_course_categories AS cc
WHERE cc.id = c.category
) AS Periodo
,g.name Grupo
,(
(SELECT COUNT(gg.id)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid = gi.id
JOIN prefix_assignment AS asg ON gi.iteminstance = asg.id
AND gi.courseid = asg.course
JOIN prefix_course_modules AS cm ON asg.id = cm.instance
AND cm.course = asg.course
JOIN prefix_groups_members AS gm ON gm.userid = gg.userid
WHERE gi.courseid = c.id
AND gm.groupid = g.id
AND EXISTS (SELECT *
FROM prefix_log AS l
WHERE l.course = c.id
AND l.cmid = cm.id
AND l.userid = gg.userid
AND l.time = gg.timecreated)
)
+
(SELECT COUNT(gg.id)
FROM prefix_grade_grades AS gg
JOIN prefix_grade_items AS gi ON gg.itemid = gi.id
JOIN prefix_assignment AS asg ON gi.iteminstance = asg.id
AND gi.courseid = asg.course
JOIN prefix_course_modules AS cm ON asg.id = cm.instance
AND cm.course = asg.course
JOIN prefix_groups_members AS gm ON gm.userid = gg.userid
WHERE gi.courseid = c.id
AND gm.groupid = g.id
AND NOT EXISTS (SELECT *
FROM prefix_log AS l
WHERE l.course = c.id
AND l.cmid = cm.id
AND l.userid = gg.userid
AND l.time = gg.timecreated)
AND gg.rawgrade > 0)
) AS 'Tareas
entrgadas'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_groups_members AS gm ON gm.userid = u.id
WHERE l.course = c.id
AND ra.roleid = 5
AND gm.groupid = g.id
AND (l.module = 'forum' AND (l.action = 'add discussion'
OR l.action = 'add post'))
) AS 'Publicaciones
en foros'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_groups_members AS gm ON gm.userid = u.id
WHERE l.course = c.id
AND ra.roleid = 5
AND gm.groupid = g.id
AND (l.module = 'quiz' AND (l.action = 'attempt' OR
l.action = 'review'))
) AS 'Cuestionarios
contestados'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_groups_members AS gm ON gm.userid = u.id
WHERE l.course = c.id
AND ra.roleid = 5
AND gm.groupid = g.id
AND (l.module = 'wiki' AND l.action = 'edit')
) AS 'Participaciones
en wikis'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
WHERE l.course = c.id
AND ra.roleid = 5
AND (l.module = 'glossary' AND l.action IN
('add entry'))
) AS 'Definiciones
en glosarios'
,(SELECT count(l.userid)
FROM prefix_log AS l
JOIN prefix_context AS ctx ON l.course = ctx.instanceid
JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id
AND ra.userid = l.userid
JOIN prefix_user AS u ON u.id = ra.userid
JOIN prefix_groups_members AS gm ON gm.userid = u.id
WHERE l.course = c.id
AND ra.roleid = 5
AND gm.groupid = g.id
AND (
(l.module IN ('assignment','blog','file','page','resource','url')
AND l.action = 'view') OR
(l.module = 'forum' AND l.action IN
('view discussion', 'view forum')) OR
(l.module = 'quiz' AND l.action IN
('view summary','preview','view')) OR
(l.module = 'wiki' AND l.action IN ('diff','history','view'))
)) AS Vistas
,(SELECT Count( ra.userid ) AS Users
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_groups_members AS gm ON gm.userid = u.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
AND gm.groupid = g.id
) AS Alumnos
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
JOIN prefix_assignment AS a ON cm.course = a.course
AND a.id = cm.instance
WHERE cm.course = c.id
AND m.name LIKE '%assignment%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Tareas
del curso'
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%forum%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Foros
del curso'
,(SELECT count(m.name)
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%quiz%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Cuestionarios
del curso'
,(SELECT count( m.name )
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%wiki%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Wikis
del curso'
,(SELECT count( m.name )
FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE cm.course = c.id
AND m.name LIKE '%glossary%'
AND cm.visible = 1
AND cs.section > 0
AND cs.visible = 1
) AS 'Glosarios
del curso'
,(SELECT
(SELECT COUNT(r.id)
FROM prefix_resource AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1
)
+
(SELECT COUNT(r.id)
FROM prefix_url AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1
)
+
(SELECT COUNT(r.id)
FROM prefix_folder AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND cs.section > 0
AND cs.visible = 1
)
+
(SELECT COUNT(r.id) AS count
FROM prefix_page AS r
JOIN prefix_course_modules AS cm
JOIN prefix_course_sections AS cs ON cm.section = cs.id
AND cm.course = cs.course
WHERE r.course = cm.course
AND r.id = cm.instance
AND cm.visible = 1
AND r.course = c.id
AND r.name NOT LIKE 'Descrip%'
AND cs.section > 0
AND cs.visible = 1
)
) AS Recursos
FROM prefix_course AS c
JOIN prefix_groups AS g ON g.courseid = c.id
WHERE c.visible = 1
AND DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%Y/%m/%d') >= '2012/09/10'
AND c.category IN (
SELECT cc.id FROM prefix_course_categories cc
WHERE parent = 7)
AND EXISTS (SELECT *
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)
AND EXISTS (SELECT *
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_groups_members AS gm
ON gm.userid = u.id
WHERE ra.roleid IN (3,4) AND ctx.instanceid = c.id
AND gm.groupid = g.id)
ORDER BY Curso, Grupo
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 '- <a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a>
';
}
echo '
';
?>
Blog activity per Course (not including VIEW)
Filter activity logging to some specific Course Categories!
+ link course name to actual course (for quick reference)
(you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') as CourseID
,m.name ,count(cm.id) as counter
,(SELECT Count( ra.userid ) AS Users
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5
AND ctx.instanceid = c.id
) AS Students
, ( SELECT count(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) as BlogActivity
FROM `prefix_course_modules` as cm JOIN prefix_modules as m ON cm.module=m.id JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15)
GROUP BY cm.course,cm.module order by counter desc
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 SECOND) AS Time
Site-Wide Grade Report with Just Course Totals
A second site-wide grade report for all students that just shows course totals.
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 SECOND) 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
Per-course comparison of average grades: last 30 days vs overall
Notes:
- This query has only been tested on MySQL
- change "ifnull(recent.days,30) as rdays" and "select 30 as days" in the query to use a different time period
select
ifnull(recent.days,30) as rdays,
ifnull(recent.numitems,0) as rnumitems,
ifnull(recent.numusers,0) as rnumusers,
ifnull(recent.avggrade,0) as ravggrade,
overall.*
from
(select
var.days as days,
c.id as courseid,
count(distinct i.id) as numitems,
count(distinct g.userid) as numusers,
round(avg(g.rawgrade),2) as avggrade
from
prefix_grade_grades g inner join prefix_grade_items i
on g.itemid = i.id
inner join prefix_course c
on i.courseid = c.id
inner join prefix_course_categories t
on c.category = t.id
inner join
( select
30 as days ) var
where
g.rawgrade is not null and
c.visible = 1 and
t.visible=1 and
g.timemodified > (unix_timestamp() - ((60*60*24)*var.days))
group by
c.id) recent
right join
(select
c.id as courseid,
c.fullname as coursename,
count(distinct i.id) as numitems,
count(distinct g.userid) as numusers,
round(avg(g.rawgrade),2) as avggrade
from
prefix_grade_grades g inner join prefix_grade_items i
on g.itemid = i.id
inner join prefix_course c
on i.courseid = c.id
inner join prefix_course_categories t
on c.category = t.id
where
g.rawgrade is not null and
c.visible = 1 and
t.visible=1
group by
c.id) overall
on recent.courseid = overall.courseid
I use this in conjunction with the following template:
<a href="/moodle/course/view.php?id=courseid" target="_blank">Course</a> /
<a href="/moodle/grade/report/user/index.php?id=courseid" target="_blank">Gradebook</a>
)
Activity Module Reports
How many SCORM activities are used in each Course
SELECT cm.course,c.fullname ,m.name
,concat('<a target="_new" href="%%WWWROOT%%/mod/scorm/index.php?id=',c.id,'">',count(cm.id),'</a>') AS Counter
FROM `prefix_course_modules` as cm
JOIN prefix_modules as m ON cm.module=m.id
JOIN prefix_course as c ON cm.course = c.id
WHERE m.name LIKE '%scorm%'
GROUP BY cm.course,cm.module
ORDER BY count(cm.id) desc
Detailed ACTIONs for each MODULE
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
Most popular ACTIVITY
SELECT COUNT(l.id) hits, module
FROM prefix_log l
WHERE module != 'login' AND module != 'course' AND module != 'role'
GROUP BY module
ORDER BY hits DESC
System wide use of ACTIVITIES and RESOURCES
SELECT count( cm.id ) AS counter, m.name
FROM `prefix_course_modules` AS cm
JOIN prefix_modules AS m ON cm.module = m.id
GROUP BY cm.module
ORDER BY counter DESC
LOG file ACTIONS per MODULE per COURSE (IDs)
select course,module,action,count(action) as summa from prefix_log
where action <> 'new'
group by course,action,module
order by course,module,action
System Wide usage count of various course Activities
(Tested and works fine in Moodle 2.x) Like: Forum, Wiki, Blog, Assignment, Database,
- Within specific category
- Teacher name in course
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') as Course
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
,(SELECT count( m.name ) AS count FROM
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
,(SELECT count( m.name ) AS count FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
,(SELECT Count( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
FROM prefix_course AS c
WHERE c.category IN ( 18)
ORDER BY Wikis DESC,Blogs DESC, Forums DESC
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%%
- You can enable the SEMESTER filter as well,
- by uncommenting the following line:
- %%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
