ad-hoc contributed reports
These are community contributed reports for the Custom_SQL_queries_report site-wide report plugin (module)
Everyone is welcome to add their own. until we find a better way to exchange reports between us.
Enjoy :-)
Detailed ACTIONs for each MODULE
SELECT module,action,count(id) as counter
FROM prefix_log
GROUP BY module,action
ORDER BY module,counter desc
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
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
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
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
"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
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
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
LIST of all site USERS by COURSE enrolment
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
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
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 `mdl_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
and user count for that same population:
SELECT COUNT(id) as Users FROM `mdl_user`
WHERE DATEDIFF( NOW(),FROM_UNIXTIME(`lastlogin`) ) < 120
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
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 mdl_user as u
JOIN mdl_user_info_data as uid ON uid.userid = u.id
JOIN mdl_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"
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
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
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
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)
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%'
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
User's courses
change "u.id = 2" with a new user id
SELECT u.firstname, u.lastname, c.id, c.fullname
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
WHERE u.id = 2
All Forum type:NEWS
SELECT f.id, f.name
FROM mdl_course_modules AS cm
JOIN mdl_modules AS m ON cm.module = m.id
JOIN mdl_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 mdl_forum_discussions as fd
JOIN mdl_forum as f ON f.id = fd.forum
JOIN mdl_course as c ON c.id = f.course
JOIN mdl_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 mdl_course_modules AS cm
JOIN mdl_modules AS m ON cm.module = m.id
JOIN mdl_forum AS f ON cm.instance = f.id
WHERE m.name = 'forum'
AND f.type = 'news')
AND c.id IN (SELECT c.id
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
WHERE u.id = 26) ORDER BY `fd`.`timemodified` DESC
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
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
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
Users that are not Assigned to any course and have not logged-in during the last 180 days
SELECT concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',u.id,'">',u.firstname ,' ',u.lastname,'</a>') AS Username
,(SELECT Count( ra.userid ) FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON (ra.contextid = ctx.id AND ctx.contextlevel = 50)
WHERE ra.userid = u.id) AS Count
FROM prefix_user AS u
WHERE lastlogin < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND lastlogin != 0 AND lastaccess < UNIX_TIMESTAMP(DATE_SUB(NOW(),INTERVAL 180 DAY))
AND deleted = 0
ORDER BY Count ASC
Courses with more then 3 Resources
SELECT COUNT(r.id) AS counter, r.course,c.shortname shortname, c.fullname coursename
FROM prefix_resource r
JOIN prefix_course c on r.course = c.id
GROUP BY course
HAVING counter > 3
ORDER BY counter DESC