Excelentes reportes específicos hechos por usuarios en consultas querys a la base de datos de Moodle
https://docs.moodle.org/all/es/30/Reportes_espec%C3%ADficos_hechos_por_usuarios
- 1 Reportes de usuario y de rol
- 1.1 Número de distintos alumnos y profesores inscritos por categoría (inclyendo todas sus sub-categorías)
- 1.2 ACCIONES detalladas para cada ROL (PROFESOR, PROFESOR SIN DERECHOS DE EDICIÓN y ESTUDIANTE)
- 1.3 NÚMERO de estudiantes en cada Curso
- 1.4 Listado de Estudiantes matriculados en cursos con breadcrumb de categorías
- 1.5 Número de inscripciones en cada Curso
- 1.6 LISTA de todos los USUARIOS del sitio por inscripción a CURSO (Moodle 2.x)
- 1.7 Usuarios inscritos, que no ingresaron al curso, ni siquiera una vez (Moodle 2)
- 1.8 Asignaciones de rol en categorías
- 1.9 Anulaciones de permisos en Categorías
- 1.10 Lista "Cursos Totalmente Abiertos" (visibles, abiertos a invitados, sin contraseña)
- 1.11 Lista "usuarios ingresados" de los últimos 120 días
- 1.12 Lista los usuarios que solamente han ingresado al sitio una vez
- 1.13 Estudiantes en todos los cursos de algún instituto
- 1.14 Información completa de usuario (para usuarios eliminados)
- 1.15 Cursos del usuario
- 1.16 Lista Usuarios con información extra (email) en el curso actual
- 1.17 Roles Especiales
- 1.18 Cursos sin Profesores
- 1.19 Lista de usuarios que han estado inscritos por más de 4 semanas
- 1.20 Lista de usuarios con Idioma
- 1.21 Lista de usuarios con Autenticación
- 1.22 Comparar capacidades y permisos de rol
- 1.23 Tiempo acumulativo del usuario empleado en el curso
- 1.24 Reporte de Estudiante con Baja Participación
- 1.25 Mensajes a Todos los Usuarios en un Curso Específico
- 2 Reportes de actividad de Bitácoras
- 2.1 Count all Active Users by ROLE in a course category (including all of its sub-categories)
- 2.2 Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)
- 2.3 Actividad Total de Roles:"Profesor" y "Profesor sin derechos de edición" por Fechas y Horas
- 2.4 Cuantos INGRESOS por usuario y Actividad del usuario
- 2.5 Actividad total por curso, por usuario individual en las últimas 24 hrs
- 2.6 Participación semanal en línea de Instructor
- 2.7 Module activity (Hits) between dates
- 2.8 Module activity (Instances and Hits) for each academic year
- 2.9 Unique user sessions per day and month + graph
- 3 Reportes de Curso
- 3.1 Cursos más Activos
- 3.2 Cursos activos, avanzado
- 3.3 Count unique teachers with courses that use at least X module (Moodle19)
- 3.4 RESOURCE count for each COURSE
- 3.5 Common resource types count for each Category (Moodle19)
- 3.6 Detailed Resource COUNT by Teacher in each course
- 3.7 Courses that are defined as using GROUPs
- 3.8 Cursos con Grupos
- 3.9 Groups in course with member list
- 3.10 Group Export
- 3.11 List all Courses in and below a certain category
- 3.12 List all Categories in one level below a certain category
- 3.13 Blog activity per Course (not including VIEW)
- 3.14 Student's posts content in all course blogs (oublog)
- 3.15 All Courses which uploaded a Syllabus file
- 3.16 Site-wide completed SCORM activities by Course name
- 4 Instancias de Módulo + ACIERTOS a Módulo (Module HITs) por rol de profesor y estudiante en curso
- 5 Reportes de Calificación y Finalización de Curso
- 5.1 Site-Wide Grade Report with All Items
- 5.2 Site-Wide Grade Report with Just Course Totals
- 5.3 Learner report by Learner with grades
- 5.4 User Course Completion
- 5.5 User Course Completion with Criteria
- 5.6 Courses with Completion Enabled and their settings
- 5.7 Course Completion Report with custom dates
- 5.8 Scales used in activities
- 6 Reportes de Actividad de Módulos
- 6.1 How many SCORM activities are used in each Course
- 6.2 Detailed ACTIONs for each MODULE
- 6.3 Actividad más popular
- 6.4 System wide use of ACTIVITIES and RESOURCES
- 6.5 LOG file ACTIONS per MODULE per COURSE (IDs)
- 6.6 System Wide usage count of various course Activities
- 6.7 Course wiki usage/activity over the last 6 semesters
- 6.8 Detailed WIKI activity (per wiki per course)
- 6.9 Wiki usage, system wide
- 6.10 Aggregated Teacher activity by "WEB2" Modules
- 6.11 List all the certificates issued, sort by variables in the custom profile fields
- 6.12 Counter Blog usage in Courses,system wide
- 6.13 Elluminate (Blackboard Collaborate) - system wide usage
- 6.14 Choice
- 6.15 Assignment type usage in courses
- 7 Reportes del Módulo de Tareas
- 8 Reportes de Módulo Recurso
- 9 Reportes del Módulo Foro
- 9.1 print all User's post in course Forums
- 9.2 FORUM use Count per COURSE -- not including NEWS Forum!
- 9.3 FORUM use Count per COURSE by type -- not including NEWS Forum!
- 9.4 Forum activity - system wide
- 9.5 Actividad en Foros
- 9.6 All Forum type:NEWS
- 9.7 All new forum NEWS items (discussions) from all my Courses
- 9.8 News Forum - Discussions COUNT
- 9.9 Cantidad de foros que han sido posteados por profesor
- 10 Reportes del Módulo Examen
- 10.1 Generar una lista de de instructores y sus direcciones de correo para aquellos cursos que tengan "preguntas de tipo Ensayo" en sus exámenes
- 10.2 Número de exámenes por curso
- 10.3 Lista de preguntas de Multirespuesta (Cloze)
- 10.4 Listar cursos con calificacines MANUALES
- 10.5 Listar los usuarios que no tomaron el Examen
- 10.6 Listar Preguntas en cada Examen
- 11 Reportes de Actividad SCORM
- 12 Insignias
- 13 Reportes del Administrador
- 14 Sub consultas (sub queries) útiles
- 15 Vea también
Reportes de usuario y de rol
Número de distintos alumnos y profesores inscritos por categoría (inclyendo todas sus sub-categorías)
SELECT COUNT(DISTINCT lra.userid) AS learners, COUNT(DISTINCT tra.userid) AS teachers FROM prefix_course AS c #, mdl_course_categories AS cats LEFT JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS lra ON lra.contextid = ctx.id JOIN prefix_role_assignments AS tra ON tra.contextid = ctx.id JOIN prefix_course_categories AS cats ON c.category = cats.id WHERE c.category = cats.id AND ( cats.path LIKE '%/CATEGORYID/%' #Replace CATEGORYID WITH the category id you want TO COUNT (eg: 80) OR cats.path LIKE '%/CATEGORYID' ) AND lra.roleid=5 AND tra.roleid=3
ACCIONES detalladas para cada ROL (PROFESOR, PROFESOR SIN DERECHOS DE EDICIÓN y ESTUDIANTE)
SELECT r.name, l.action, COUNT( l.userid ) AS counter FROM prefix_log AS l JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50 JOIN prefix_role_assignments AS ra ON l.userid = ra.userid AND ra.contextid = context.id JOIN prefix_role AS r ON ra.roleid = r.id WHERE ra.roleid IN ( 3, 4, 5 ) GROUP BY roleid, l.action
NÚMERO de estudiantes en cada Curso
Incluyendo (opcional) filtrar por: año (si se incluye en el nombre_completo del curso).
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',course.id,'">',course.fullname,'</a>') AS Course ,concat('<a target="_new" href="%%WWWROOT%%/user/index.php?contextid=',context.id,'">Show users</a>') AS Users , COUNT(course.id) AS Students FROM prefix_role_assignments AS asg JOIN prefix_context AS context ON asg.contextid = context.id AND context.contextlevel = 50 JOIN prefix_user AS USER ON USER.id = asg.userid JOIN prefix_course AS course ON context.instanceid = course.id WHERE asg.roleid = 5 # AND course.fullname LIKE '%2013%' GROUP BY course.id ORDER BY COUNT(course.id) DESC
Listado de Estudiantes matriculados en cursos con breadcrumb de categorías
Muestra el listado de estudiantes matriculados en cursos de un año específico con breadcrumb de categorías en donde se encuentra cada curso.
SELECT Alumnos.ID, Alumnos.Usuario, Alumnos.Nombre, Alumnos.Apellido, Alumnos.email, Alumnos.Rol, Alumnos.NombreLargoCurso, Alumnos.NombreCortoCurso, Alumnos.FechaInicioCurso, Categorias.breadcrumb FROM (SELECT DISTINCT u.id AS ID, u.username AS Usuario, UPPER(u.firstname) AS Nombre, UPPER(u.lastname) AS Apellido, u.email AS email, course_cat.path AS Ruta, c.fullname AS NombreLargoCurso, c.shortname AS NombreCortoCurso, rol.shortname AS Rol, DATE_FORMAT(FROM_UNIXTIME(c.startdate),'%d-%m-%Y') AS FechaInicioCurso FROM {USER} AS u INNER JOIN {user_enrolments} AS e ON e.userid = u.id INNER JOIN {enrol} AS en ON en.id = e.enrolid INNER JOIN {course} AS c ON c.id = en.courseid INNER JOIN {course_categories} AS course_cat ON course_cat.id = c.category INNER JOIN prefix_role_assignments ra ON ra.userid = u.id INNER JOIN prefix_context ct ON ct.id = ra.contextid INNER JOIN prefix_role rol ON rol.id = ra.roleid WHERE u.deleted = 0 AND u.suspended = 0 AND YEAR(FROM_UNIXTIME(c.timemodified)) = 2020 AND YEAR(FROM_UNIXTIME(c.startdate)) = 2020 AND c.visible = 1 AND ra.roleid =5 AND #Rol estudiante ct.instanceid = c.id %%FILTER_COURSES:c.id%%) AS Alumnos INNER JOIN (SELECT t1.id, t1.name, t1.parent, t1.depth, t1.path AS Ruta1, GROUP_CONCAT(t2.name ORDER BY LOCATE(CONCAT('/', t2.id, '/'), CONCAT(t1.path, '/')) SEPARATOR ' - ') breadcrumb FROM prefix_course_categories t1, prefix_course_categories t2 WHERE LOCATE(CONCAT('/', t2.id, '/'), CONCAT(t1.path, '/')) GROUP BY t1.id, t1.name, t1.parent, t1.depth, t1.path) AS Categorias ON Alumnos.Ruta = Categorias.Ruta1
Número de inscripciones en cada Curso
Shows the total number of enroled users of all roles in each course. Sorted by course name.
SELECT c.fullname, COUNT(ue.id) AS Enroled FROM prefix_course AS c JOIN prefix_enrol AS en ON en.courseid = c.id JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id GROUP BY c.id ORDER BY c.fullname
LISTA de todos los USUARIOS del sitio por inscripción a CURSO (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
Usuarios inscritos, que no ingresaron al curso, ni siquiera una vez (Moodle 2)
Designed forMoodle 2 table structure and uses special plugin filter : %%FILTER_SEARCHTEXT:table.field%%
SELECT user2.id AS ID, ul.timeaccess, user2.firstname AS Firstname, user2.lastname AS Lastname, user2.email AS Email, user2.city AS City, user2.idnumber AS IDNumber, user2.phone1 AS Phone, user2.institution AS Institution, IF (user2.lastaccess = 0,'never', DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess ,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess ,(SELECT r.name FROM prefix_user_enrolments AS uenrol JOIN prefix_enrol AS e ON e.id = uenrol.enrolid JOIN prefix_role AS r ON e.id = r.id WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName FROM prefix_user_enrolments AS ue JOIN prefix_enrol AS e ON e.id = ue.enrolid JOIN prefix_course AS c ON c.id = e.courseid JOIN prefix_user AS user2 ON user2 .id = ue.userid LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id WHERE c.id=16 AND ul.timeaccess IS NULL %%FILTER_SEARCHTEXT:user2.firstname%%
Asignaciones de rol en categorías
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.id,'</a>') AS id, concat('<a target="_new" href="%%WWWROOT%%/course/category.php?id=',cc.id,'">',cc.name,'</a>') AS category, cc.depth, cc.path, r.name AS ROLE, concat('<a target="_new" href="%%WWWROOT%%/user/view.php?id=',usr.id,'">',usr.lastname,'</a>') AS name, usr.firstname, usr.username, usr.email FROM prefix_course_categories cc INNER JOIN prefix_context cx ON cc.id = cx.instanceid AND cx.contextlevel = '40' INNER JOIN prefix_role_assignments ra ON cx.id = ra.contextid INNER JOIN prefix_role r ON ra.roleid = r.id INNER JOIN prefix_user usr ON ra.userid = usr.id ORDER BY cc.depth, cc.path, usr.lastname, usr.firstname, r.name, cc.name
Anulaciones de permisos en Categorías
(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
Lista "Cursos Totalmente Abiertos" (visibles, abiertos a invitados, sin contraseña)
(By: Séverin Terrier )
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS id, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS 'Course', concat('<a target="_new" href="%%WWWROOT%%/enrol/instances.php?id=',c.id,'">Méthodes inscription</a>') AS 'Enrollment plugins', e.sortorder FROM prefix_enrol AS e, prefix_course AS c WHERE e.enrol='guest' AND e.status=0 AND e.password='' AND c.id=e.courseid AND c.visible=1
Lista "usuarios ingresados" de los últimos 120 días
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
Lista los usuarios que solamente han ingresado al sitio una vez
SELECT id, username, firstname, lastname, idnumber FROM prefix_user WHERE prefix_user.deleted = 0 AND prefix_user.lastlogin = 0 AND prefix_user.lastaccess > 0
Estudiantes en todos los cursos de algún instituto
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'
Información completa de usuario (para usuarios eliminados)
Including extra custom profile fields (from prefix_user_info_data)
SELECT * FROM prefix_user AS u JOIN prefix_user_info_data AS uid ON uid.userid = u.id JOIN prefix_user_info_field AS uif ON (uid.fieldid = uif.id AND uif.shortname = 'class') WHERE `deleted` = "1" AND `institution`="your school name" AND `department` = "your department" AND `data` = "class level and number"
Cursos del usuario
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
Lista Usuarios con información extra (email) en el curso actual
blocks/configurable_reports replaces %%COURSEID%% with course id.
SELECT u.firstname, u.lastname, u.email FROM prefix_role_assignments AS ra JOIN prefix_context AS context ON context.id = ra.contextid AND context.contextlevel = 50 JOIN prefix_course AS c ON c.id = context.instanceid AND c.id = %%COURSEID%% JOIN prefix_user AS u ON u.id = ra.userid
Roles Especiales
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
Cursos sin Profesores
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
Lista de usuarios que han estado inscritos por más de 4 semanas
For Moodle 2.2 , by Isuru Madushanka Weerarathna
SELECT uenr.userid AS USER, IF(enr.courseid=uenr.courseid ,'Y','N') AS Enrolled, IF(DATEDIFF(NOW(), FROM_UNIXTIME(uenr.timecreated))>=28,'Y','N') AS EnrolledMoreThan4Weeks FROM prefix_enrol AS enr, prefix_user_enrolments AS uenr WHERE enr.id = uenr.enrolid AND enr.status = uenr.status
Lista de usuarios con Idioma
An issue with systems that do not have their default language set up properly is the need to do a mass change for all users to a localization. A common case is changing default English to American English.
This will show you the language setting for all users:
SELECT username, lang FROM prefix_user
This code will change the setting from 'en' to 'en_us' for all users:
UPDATE prefix_user SET lang = 'en_us' WHERE lang = 'en'
Lista de usuarios con Autenticación
Sometimes you need to do mass changes of authentication methods. A common case is changing default manual to LDAP.
This will show you the Authentication setting for all users:
SELECT username, auth FROM prefix_user
This code will change the setting from 'manual' to 'ldap' for all users except for the first two accounts which are Guest and Admin. (WARNING: it is bad practice to change you admin account from manual to an external method as failure of that external method will lock you out of Moodle as admin.)
UPDATE prefix_user SET auth = 'ldap' WHERE auth = 'manual' AND id > 2
Comparar capacidades y permisos de rol
SELECT DISTINCT mrc.capability ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '1' AND rc.contextid = '1') AS Manager ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '2' AND rc.contextid = '1') AS CourseCreator ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '3' AND rc.contextid = '1') AS Teacher ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '4' AND rc.contextid = '1') AS AssistantTeacher ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '5' AND rc.contextid = '1') AS Student ,(SELECT rc.permission FROM `mdl_role_capabilities` AS rc WHERE rc.capability = mrc.capability AND rc.roleid = '6' AND rc.contextid = '1') AS Guest FROM `mdl_role_capabilities` AS mrc
Tiempo acumulativo del usuario empleado en el curso
A sum up of the time delta between logstore_standard_log user's records, considering the a 2 hour session limit.
Uses: current user's id %%USERID%% and current course's id %%COURSEID%%
And also using a date filter (which can be ignored)
The extra "User" field is used as a dummy field for the Line chart Series field, in which I use X=id, Series=Type, Y=delta.
SELECT l.id, l.timecreated, DATE_FORMAT(FROM_UNIXTIME(l.timecreated),'%d-%m-%Y') AS dTime, @prevtime := (SELECT MAX(timecreated) FROM mdl_logstore_standard_log WHERE userid = %%USERID%% AND id < l.id ORDER BY id ASC LIMIT 1) AS prev_time, IF (l.timecreated - @prevtime < 7200, @delta := @delta + (l.timecreated-@prevtime),0) AS sumtime, l.timecreated-@prevtime AS delta, "User" AS TYPE FROM prefix_logstore_standard_log AS l, (SELECT @delta := 0) AS s_init # CHANGE UserID WHERE l.userid = %%USERID%% AND l.courseid = %%COURSEID%% %%FILTER_STARTTIME:l.timecreated:>%% %%FILTER_ENDTIME:l.timecreated:<%%
Reporte de Estudiante con Baja Participación
Contributed by Elizabeth Dalton, Granite State College / Moodle HQ
This report returns a list of students who are enrolled in courses filtered by a short-name text marker (in this case "OL-") in the specified category, but have very low participation in the course during the specified time period (fewer than 2 "Edits" to Activity Modules, indicating few active contributions to the course). The number of "Edits" is provided for each student for the time period specified.
An "Edit" is defined as course activity other than viewing content. Click the "Logs" link to review the student activity. The Logs offer the option to review "View" activity as well as "Edit" activity.
Only "visible" courses are included in this report. The report may be downloaded as an Excel spreadsheet.
Don't forget to set up Filters: "Start / End date filter" and "Filter categories" on the Filters tab in Configurable reports.
SELECT u.lastname AS LAST, u.firstname AS FIRST, u.idnumber AS IDnumber, u.email AS email, c.shortname AS CourseID, COUNT(l.id) AS Edits, CONCAT('<a target="_new" href="https://learn.granite.edu/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=-view&logformat=showashtml','">','Logs','</a>') AS Link FROM prefix_user AS u JOIN prefix_role_assignments AS ra ON u.id = ra.userid JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_course AS c ON c.id = ctx.instanceid LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%" %%FILTER_STARTTIME:l.TIME:>%% %%FILTER_ENDTIME:l.TIME:<%% WHERE ra.roleid =5 AND ctx.instanceid = c.id AND c.visible=1 # This prefix FILTER allows the exclusion OF non-online courses at the original institution. ALTER this TO fit your institution, OR remove it. AND c.shortname LIKE '%OL-%' %%FILTER_CATEGORIES:c.category%% GROUP BY u.idnumber HAVING Edits < 2
Mensajes a Todos los Usuarios en un Curso Específico
Contributed by: Randy Thornton
This query shows the personal messages between users in a specific course, given the course id number. Properly speaking, personal messages pertain only to users and are not part of courses, but by filtering enrollments for roles in a course, you can show this.
This report as is shows only the messages between Teachers and Students, as the WHERE statement contains and AND ((...))) section that restrict this report to ONLY messages between Teachers (role id = 3) and Students (role id =5). Remove that part of the statement if you wish to see _all_ messages between all users, e.g. teachers to teachers, student to student.
Also, if you have created custom roles, you can replace the default id numbers with custom ones to further enhance the report.
SELECT u.username AS 'From', CONCAT(u.firstname ,' ',u.lastname) AS 'From Name', u2.username AS 'To', CONCAT(u2.firstname ,' ',u2.lastname) AS 'To Name', DATE_FORMAT(FROM_UNIXTIME(me.timecreated), '%Y-%m-%d %H:%i') AS 'When', me.subject AS 'Subject', me.smallmessage AS 'Message' FROM prefix_message me JOIN prefix_role_assignments AS ra ON ra.userid = me.useridfrom AND ra.roleid IN (3,4,5) JOIN prefix_role_assignments AS ra2 ON ra2.userid = me.useridto AND ra2.roleid IN (3,4,5) JOIN prefix_context AS ctx ON ra.contextid = ctx.id AND ra2.contextid = ctx.id JOIN prefix_course AS c ON c.id = ctx.instanceid JOIN prefix_user u ON u.id = me.useridfrom JOIN prefix_user u2 ON u2.id = me.useridto WHERE c.id=## AND ((ra.roleid = 3 AND ra2.roleid = 5) OR (ra.roleid = 5 AND ra2.roleid = 3)) ORDER BY me.useridfrom, me.useridto, me.timecreated
Reportes de actividad de Bitácoras
Count all Active Users by ROLE in a course category (including all of its sub-categories)
SELECT COUNT(DISTINCT l.userid) AS active FROM mdl_course AS c JOIN mdl_context AS ctx ON ctx.instanceid=c.id JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id JOIN mdl_user_lastaccess AS l ON ra.userid = l.userid JOIN mdl_course_categories AS cats ON c.category = cats.id WHERE c.category=cats.id AND ( cats.path LIKE '%/80/%' OR cats.path LIKE '%/80' ) AND ra.roleid=3 AND ctx.contextlevel=50 #ra.roleid= TEACHER 3, NON-EDITING TEACHER 4, STUDENT 5 AND l.timeaccess > (unix_timestamp() - ((60*60*24)*NO_OF_DAYS)) #NO_OF_DAYS CHANGE TO NUMBER
Detailed "VIEW" ACTION for each ROLE (TEACHER,NONE-EDITING TEACHER and STUDENT)
SELECT l.action, COUNT( l.userid ) AS counter , r.name FROM `prefix_log` AS l JOIN `prefix_role_assignments` AS ra ON l.userid = ra.userid JOIN `prefix_role` AS r ON ra.roleid = r.id WHERE (ra.roleid IN (3,4,5)) AND (l.action LIKE '%view%' ) GROUP BY roleid,l.action ORDER BY r.name,counter DESC
Actividad Total de Roles:"Profesor" y "Profesor sin derechos de edición" por Fechas y Horas
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
Cuantos INGRESOS por usuario y Actividad del usuario
+ 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
Actividad total por curso, por usuario individual en las últimas 24 hrs
SELECT COUNT(DISTINCT userid) AS countUsers , COUNT(course) AS countVisits , concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course FROM prefix_log AS l JOIN prefix_course AS c ON c.id = l.course WHERE course > 0 AND FROM_UNIXTIME(TIME) >= DATE_SUB(NOW(), INTERVAL 1 DAY) AND c.fullname LIKE '%תשעג%' GROUP BY course ORDER BY countVisits DESC
Participación semanal en línea de Instructor
Contributed by Elizabeth Dalton
Displays participation of instructors in all courses per week of a term, including pre-term and post-term edits. An edit is defined as a change to the course, such as a discussion post, the grading of an assignment, or the uploading of file attachments, as well as alterations to course content.
- To specify a subject and/or course number, use % as a wildcard, e.g. ARTS% or ARTS501%
- To match part of a last name, use %, e.g. Smi% will match "Smith", "Smile", etc.
At our institution, we include filters on the course name or category to constrain by terms. These are very specific to how course names and categories are constructed at our institution, so I've removed those elements from this code. Also, our terms are 12 weeks long. You would want to insert additional "SUM" lines for longer terms, or remove lines for shorter terms.
SELECT c.shortname AS CourseID , cc.name AS Category , CONCAT(u.firstname ,' ',u.lastname) AS Instructor , (SELECT COUNT( ra2.userid ) AS Users2 FROM prefix_role_assignments AS ra2 JOIN prefix_context AS ctx2 ON ra2.contextid = ctx2.id WHERE ra2.roleid = 5 AND ctx2.instanceid = c.id) AS Students , c.startdate AS Course_Start_Date , c.visible AS Visible , COUNT(l.id) AS Edits , SUM(IF(WEEK(FROM_UNIXTIME(l.time)) - WEEK(FROM_UNIXTIME(c.startdate))<0,1,0)) AS BeforeTerm , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=0,1,0)) AS Week1 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=1,1,0)) AS Week2 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=2,1,0)) AS Week3 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=3,1,0)) AS Week4 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=4,1,0)) AS Week5 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=5,1,0)) AS Week6 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=6,1,0)) AS Week7 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=7,1,0)) AS Week8 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=8,1,0)) AS Week9 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=9,1,0)) AS Week10 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=10,1,0)) AS Week11 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))=11,1,0)) AS Week12 , SUM(IF(WEEK(FROM_UNIXTIME(l.time))-WEEK(FROM_UNIXTIME(c.startdate))>=12,1,0)) AS AfterTerm , CONCAT('<a target="_new" href="%%WWWROOT%%/report/log/index.php',CHAR(63),'chooselog=1&showusers=1&showcourses=0&id=',c.id,'&user=',u.id,'&date=0&modid=&modaction=&logformat=showashtml','">','Logs','</a>') AS Link FROM prefix_user AS u JOIN prefix_role_assignments AS ra ON u.id = ra.userid JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_course AS c ON c.id = ctx.instanceid JOIN prefix_course_categories AS cc ON c.category = cc.id LEFT JOIN prefix_log AS l ON l.userid = u.id AND l.course = c.id AND l.action NOT LIKE "view%" WHERE ra.roleid =3 AND ctx.instanceid = c.id AND c.shortname LIKE :course AND u.lastname LIKE :last_name GROUP BY u.idnumber, c.id HAVING students > 0 ORDER BY c.shortname
Nota: Pendiente de Traducir. ¡Anímese a traducir esta página!. ( y otras páginas pendientes)
Module activity (Hits) between dates
SELECT module, COUNT( * ) FROM prefix_log AS l WHERE (FROM_UNIXTIME( l.`time` ) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00') GROUP BY module
Module activity (Instances and Hits) for each academic year
SELECT name ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00') AND l.module = m.name AND l.action = 'add' ) AS "Added 2010" ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2010-10-01 00:00:00' AND '2011-09-31 00:00:00') AND l.module = m.name ) AS "Used 2010" ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00') AND l.module = m.name AND l.action = 'add' ) AS "Added 2011" ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2011-10-01 00:00:00' AND '2012-09-31 00:00:00') AND l.module = m.name ) AS "Used 2011" ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00') AND l.module = m.name AND l.action = 'add' ) AS "Added 2012" ,(SELECT COUNT(*) FROM mdl_log AS l WHERE (FROM_UNIXTIME(l.`time`) BETWEEN '2012-10-01 00:00:00' AND '2013-09-31 00:00:00') AND l.module = m.name ) AS "Used 2012" FROM mdl_modules AS m
Unique user sessions per day and month + graph
The "graph" column is used when displaying a graph (which needs at least three columns to pick from)
SELECT COUNT(DISTINCT ip) AS "Unique Users" ,DATE_FORMAT(FROM_UNIXTIME(timecreated), "%m / %d") AS "Month / Day" ,"Graph" FROM `mdl_logstore_standard_log` GROUP BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated)) ORDER BY MONTH(FROM_UNIXTIME(timecreated)), DAY(FROM_UNIXTIME(timecreated))
Reportes de Curso
Cursos más Activos
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
Cursos activos, avanzado
Including: Teacher's name, link to the course, All types of log activities, special YEAR generated field, Activities and Resource count, enrolled Student count
SELECT COUNT(l.id) hits, concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher ,CASE WHEN c.fullname LIKE '%תשע' THEN 'תשע' WHEN c.fullname LIKE '%תשעא' THEN 'תשעא' WHEN c.fullname LIKE '%תשעב' THEN 'תשעב' END AS YEAR ,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = l.course) Modules ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students FROM prefix_log l INNER JOIN prefix_course c ON l.course = c.id GROUP BY c.id HAVING Modules > 2 ORDER BY YEAR DESC, hits DESC
Count unique teachers with courses that use at least X module (Moodle19)
You can remove the outer "SELECT COUNT(*) FROM (...) AS ActiveTeachers" SQL query and get the list of the Teachers and Courses.
SELECT COUNT(*) FROM (SELECT c.id AS CourseID, c.fullname AS Course, ra.roleid AS RoleID, CONCAT(u.firstname, ' ', u.lastname) AS Teacher ,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) AS Modules FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50 JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE ra.roleid = 3 GROUP BY u.id HAVING Modules > 5) AS ActiveTeachers
RESOURCE count for each COURSE
SELECT COUNT(l.id) COUNT, l.course, c.fullname coursename FROM prefix_resource l INNER JOIN prefix_course c ON l.course = c.id GROUP BY course ORDER BY COUNT DESC
Common resource types count for each Category (Moodle19)
Including sub-categories in total count.
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category ,(SELECT COUNT( * ) FROM prefix_resource AS r JOIN prefix_course AS c ON c.id = r.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference LIKE 'http://%' ) AS Links ,(SELECT COUNT( * ) FROM prefix_resource AS r JOIN prefix_course AS c ON c.id = r.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'file' AND r.reference NOT LIKE 'http://%' ) AS Files ,(SELECT COUNT( * ) FROM prefix_resource AS r JOIN prefix_course AS c ON c.id = r.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'directory' ) AS Folders ,(SELECT COUNT( * ) FROM prefix_resource AS r JOIN prefix_course AS c ON c.id = r.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%', mccid, '%' ) AND r.TYPE = 'html' ) AS Pages ,(SELECT COUNT(*) FROM stats_log_context_role_course WHERE roleid = 5 AND module = 'resource' AND category = mcc.id ) AS Hits FROM prefix_course_categories AS mcc ORDER BY mcc.path
Where "stats_log_context_role_course" (in the above SQL query) is a VIEW generated by:
CREATE VIEW stats_log_context_role_course AS SELECT l.course, c.category, cc.path, l.module, l.action, ra.userid, ra.roleid FROM prefix_log AS l JOIN prefix_context AS context ON context.instanceid = l.course AND context.contextlevel = 50 JOIN prefix_role_assignments AS ra ON ra.userid = l.userid AND ra.contextid = context.id JOIN prefix_course AS c ON c.id = l.course JOIN prefix_course_categories AS cc ON cc.id = c.category
Same query but for Moodle2+
SELECT mcc.id AS mccid, CONCAT( LPAD( '', mcc.depth, '.' ) , mcc.name ) AS Category, mcc.path, (SELECT COUNT(*) FROM prefix_url AS u JOIN prefix_course AS c ON c.id = u.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS URLs, (SELECT COUNT(*) FROM prefix_folder AS f JOIN prefix_course AS c ON c.id = f.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS FOLDERs, (SELECT COUNT(*) FROM prefix_page AS p JOIN prefix_course AS c ON c.id = p.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS PAGEs, (SELECT COUNT(*) FROM prefix_book AS b JOIN prefix_course AS c ON c.id = b.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS BOOKs, (SELECT COUNT(*) FROM prefix_label AS l JOIN prefix_course AS c ON c.id = l.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS LABELs, (SELECT COUNT(*) FROM prefix_tab AS t JOIN prefix_course AS c ON c.id = t.course JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE cc.path LIKE CONCAT( '%/', mccid, '%' ) ) AS TABs FROM prefix_course_categories AS mcc ORDER BY mcc.path
Detailed Resource COUNT by Teacher in each course
Including (optional) filter by: year, semester and course id.
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS CourseID , c.id ,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname) FROM prefix_role_assignments AS ra JOIN prefix_user AS u ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher , (CASE WHEN c.fullname LIKE '%תשעב%' THEN '2012' WHEN c.fullname LIKE '%תשעא%' THEN '2011' END ) AS YEAR , (CASE WHEN c.fullname LIKE '%סמסטר א%' THEN 'Semester A' WHEN c.fullname LIKE '%סמסטר ב%' THEN 'Semester B' WHEN c.fullname LIKE '%סמסטר ק%' THEN 'Semester C' END ) AS Semester ,COUNT(c.id) AS Total ,(SELECT COUNT(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 20) AS TABs ,(SELECT COUNT(*) FROM prefix_course_modules AS cm WHERE cm.course = c.id AND cm.module= 33) AS BOOKs FROM `prefix_resource` AS r JOIN `prefix_course` AS c ON c.id = r.course #WHERE TYPE= 'file' AND reference NOT LIKE 'http://%' #WHERE 1=1 #%%FILTER_YEARS:c.fullname%% #AND c.fullname LIKE '%2013%' GROUP BY course ORDER BY COUNT(c.id) DESC
Courses that are defined as using GROUPs
SELECT concat('<a target="_new" href="%%WWWROOT%%/group/index.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,(SELECT COUNT(*) FROM prefix_course_modules cm WHERE cm.course = c.id) Modules ,(SELECT COUNT(*) FROM prefix_groups g WHERE g.courseid = c.id) Groups FROM `prefix_course` AS c WHERE groupmode > 0
Cursos con Grupos
List of all courses with Groups in them (groupmode > 0). You can also use groupmode=1 to list just Separate type groups or groupmode=2 to list Visible type groups.
SELECT c.shortname, g.name, c.groupmode FROM prefix_course AS c JOIN prefix_groups AS g ON c.id = g.courseid WHERE c.groupmode > 0
Groups in course with member list
List the groups in a course (replace the # by the course id number) with the members of each group.
SELECT c.shortname, g.name AS Groupname, u.username FROM prefix_course AS c JOIN prefix_groups AS g ON g.courseid = c.id JOIN prefix_groups_members AS m ON g.id = m.groupid JOIN prefix_user AS u ON m.userid = u.id WHERE c.id = #
Group Export
There's a Importar_grupos function, but no export. Use this to give you a report with the proper column order and headings to export to a csv file you can then import into another course to replicate the groups. This is a simple version with just the main fields: groupname, description, enrolment key.
SELECT g.name AS groupname, g.description, g.enrolmentkey FROM prefix_groups AS g JOIN prefix_course AS c ON g.courseid = c.id WHERE c.id = #
List all Courses in and below a certain category
Use this SQL code to retrieve all courses that exist in or under a set category.
$s should be the id of the category you want to know about...
SELECT prefix_course. * , prefix_course_categories. * FROM prefix_course, prefix_course_categories WHERE prefix_course.category = prefix_course_categories.id AND ( prefix_course_categories.path LIKE '/$s/%' OR prefix_course_categories.path LIKE '/$s' )
List all Categories in one level below a certain category
Use this PHP code to retrieve a list of all categories below a certain category.
$s should be the id of the top level category you are interested in.
<?php require_once('./config.php'); $parent_id = $s; $categories= array(); $categories = get_categories($parent_id); echo '<ol>'; foreach ($categories as $category) { echo '<li><a href="'.$CFG->wwwroot.'/course/category.php?id='.$category->id.'">'.$category->name.'</a></li>'; } echo '</ol>'; ?>
Blog activity per Course (not including VIEW)
Filter activity logging to some specific Course Categories! + link course name to actual course (for quick reference) (you can change %blog% to %wiki% to filter down all wiki activity or any other module you wish)
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',cm.course,'">',c.fullname,'</a>') AS CourseID ,m.name ,COUNT(cm.id) AS counter ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS Students , ( SELECT COUNT(id) FROM prefix_log WHERE `module` LIKE '%blog%' AND course = c.id AND action NOT LIKE '%view%' ) AS BlogActivity FROM `prefix_course_modules` AS cm JOIN prefix_modules AS m ON cm.module=m.id JOIN prefix_course AS c ON cm.course = c.id WHERE m.name LIKE '%blog%' AND c.category IN ( 8,13,15) GROUP BY cm.course,cm.module ORDER BY counter DESC
Student's posts content in all course blogs (oublog)
SELECT b.name ,op.title ,op.message ,( SELECT CONCAT(u.firstname, ' ',u.lastname) FROM prefix_user AS u WHERE u.id = oi.userid) AS "Username" FROM prefix_oublog_posts AS op JOIN prefix_oublog_instances AS oi ON oi.id = op.oubloginstancesid JOIN prefix_oublog AS b ON b.id = oi.oublogid JOIN prefix_course AS c ON b.course = c.id WHERE c.id = %%COURSEID%%
All Courses which uploaded a Syllabus file
+ under specific Category + show first Teacher in that course + link Course's fullname to actual course
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,c.shortname,r.name ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher FROM prefix_resource AS r JOIN prefix_course AS c ON r.course = c.id WHERE ( r.name LIKE '%סילבוס%' OR r.name LIKE '%סילאבוס%' OR r.name LIKE '%syllabus%' OR r.name LIKE '%תכנית הקורס%' ) AND c.category IN (10,18,26,13,28)
Site-wide completed SCORM activities by Course name
This report will list all completed attempts for all SCORM activities. It is ordered first by Course name, then student's last name, then student's first name, then attempt number. Please note: the FROM_UNIXTIME command is for MySQL.
SELECT u.firstname FIRST,u.lastname LAST,c.fullname Course, st.attempt Attempt,st.value STATUS,FROM_UNIXTIME(st.timemodified,"%m-%d-%Y") DATE FROM prefix_scorm_scoes_track AS st JOIN prefix_user AS u ON st.userid=u.id JOIN prefix_scorm AS sc ON sc.id=st.scormid JOIN prefix_course AS c ON c.id=sc.course WHERE st.value='completed' ORDER BY c.fullname, u.lastname,u.firstname, st.attempt
Instancias de Módulo + ACIERTOS a Módulo (Module HITs) por rol de profesor y estudiante en curso
SELECT m.name AS "Module name" , COUNT(*) AS "Module count" ,(SELECT COUNT(*) FROM prefix_log AS l WHERE l.course = cm.course AND l.module = m.name ) AS "Hits" ,(SELECT COUNT(*) FROM prefix_log AS l JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50 JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 5 WHERE l.course = cm.course AND l.module = m.name) AS "Students HITs" ,(SELECT COUNT(*) FROM prefix_log AS l JOIN prefix_context AS con ON con.instanceid= l.course AND con.contextlevel=50 JOIN prefix_role_assignments AS ra ON ra.contextid= con.id AND ra.userid= l.userid AND ra.roleid = 3 WHERE l.course = cm.course AND l.module = m.name) AS "Teachers HITs" FROM mdl_course_modules AS cm JOIN mdl_modules AS m ON m.id = cm.module WHERE cm.course = '%%COURSEID%%' GROUP BY cm.module
Reportes de Calificación y Finalización de Curso
Site-Wide Grade Report with All Items
Shows grades for all course items along with course totals for each student. Works with ad-hoc reports or Configurable Reports
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', c.fullname AS 'Course', cc.name AS 'Category', CASE WHEN gi.itemtype = 'course' THEN c.fullname + ' Course Total' ELSE gi.itemname END AS 'Item Name', ROUND(gg.finalgrade,2) AS Grade, DATEADD(ss,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. Replace the line
DATEADD(ss,gi.timemodified,'1970-01-01') AS Time
with
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. Works with ad-hoc reports or Configurable Reports
SELECT u.firstname AS 'First' , u.lastname AS 'Last', u.firstname + ' ' + u.lastname AS 'Display Name', cc.name AS 'Category', CASE WHEN gi.itemtype = 'course' THEN c.fullname + ' Course Total' ELSE gi.itemname END AS 'Item Name', ROUND(gg.finalgrade,2) AS Grade, DATEADD(ss,gg.timemodified,'1970-01-01') AS TIME FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE gi.courseid = c.id AND gi.itemtype = 'course' ORDER BY lastname
For MySQL users:
SELECT u.firstname AS 'First' , u.lastname AS 'Last', CONCAT(u.firstname , ' ' , u.lastname) AS 'Display Name', c.fullname AS 'Course', cc.name AS 'Category', CASE WHEN gi.itemtype = 'course' THEN CONCAT(c.fullname, ' - Total') ELSE gi.itemname END AS 'Item Name', ROUND(gg.finalgrade,2) AS Grade, FROM_UNIXTIME(gg.timemodified) AS TIME FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE gi.courseid = c.id ORDER BY lastname
Learner report by Learner with grades
Which Learners in which course and what are the grades
SELECT u.firstname AS 'Name' , u.lastname AS 'Surname', c.fullname AS 'Course', cc.name AS 'Category', CASE WHEN gi.itemtype = 'Course' THEN c.fullname + ' Course Total' ELSE gi.itemname END AS 'Item Name', ROUND(gg.finalgrade,2) AS Score,ROUND(gg.rawgrademax,2) AS MAX, ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) AS Percentage, IF (ROUND(gg.finalgrade / gg.rawgrademax * 100 ,2) > 79,'Yes' , 'No') AS Pass FROM prefix_course AS c JOIN prefix_context AS ctx ON c.id = ctx.instanceid JOIN prefix_role_assignments AS ra ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid JOIN prefix_grade_grades AS gg ON gg.userid = u.id JOIN prefix_grade_items AS gi ON gi.id = gg.itemid JOIN prefix_course_categories AS cc ON cc.id = c.category WHERE gi.courseid = c.id AND gi.itemname != 'Attendance' ORDER BY `Name` ASC
User Course Completion
A very simple report with list of course completion status by username. Completions are noted by date, blank otherwise.
SELECT u.username, c.shortname, DATE_FORMAT(FROM_UNIXTIME(p.timecompleted ),'%Y-%m-%d') AS completed FROM prefix_course_completions AS p JOIN prefix_course AS c ON p.course = c.id JOIN prefix_user AS u ON p.userid = u.id WHERE c.enablecompletion = 1 ORDER BY u.username
User Course Completion with Criteria
A report with course completions by username, with Aggregation method, Criteria types, and Criteria detail where available.
SELECT u.username AS USER, c.shortname AS course, DATE_FORMAT(FROM_UNIXTIME(t.timecompleted),'%Y-%m-%d') AS completed, CASE WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a WHERE (a.course = c.id AND a.criteriatype IS NULL) = 1) THEN "Any" ELSE "All" END AS aggregation, CASE WHEN p.criteriatype = 1 THEN "Self" WHEN p.criteriatype = 2 THEN "By Date" WHEN p.criteriatype = 3 THEN "Unenrol Status" WHEN p.criteriatype = 4 THEN "Activity" WHEN p.criteriatype = 5 THEN "Duration" WHEN p.criteriatype = 6 THEN "Course Grade" WHEN p.criteriatype = 7 THEN "Approve by Role" WHEN p.criteriatype = 8 THEN "Previous Course" END AS criteriatype, CASE WHEN p.criteriatype = 1 THEN "*" WHEN p.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(p.timeend),'%Y-%m-%d') WHEN p.criteriatype = 3 THEN t.unenroled WHEN p.criteriatype = 4 THEN CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',p.module,'/view.php?id=',p.moduleinstance,'">',p.module,'</a>') WHEN p.criteriatype = 5 THEN p.enrolperiod WHEN p.criteriatype = 6 THEN CONCAT('Needed: ',ROUND(p.gradepass,2),' Achieved: ',ROUND(t.gradefinal,2)) WHEN p.criteriatype = 7 THEN p.role WHEN p.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = p.courseinstance) END AS criteriadetail FROM prefix_course_completion_crit_compl AS t JOIN prefix_user AS u ON t.userid = u.id JOIN prefix_course AS c ON t.course = c.id JOIN prefix_course_completion_criteria AS p ON t.criteriaid = p.id
Courses with Completion Enabled and their settings
List of all courses with completion enabled and their Aggregation setting, Criteria types, and Criteria details.
SELECT c.shortname AS Course, CASE WHEN (SELECT a.method FROM prefix_course_completion_aggr_methd AS a WHERE (a.course = t.course AND a.criteriatype IS NULL)) = 2 THEN "All" ELSE "Any" END AS Course_Aggregation, CASE WHEN t.criteriatype = 1 THEN "Self completion" WHEN t.criteriatype = 2 THEN "Date done by" WHEN t.criteriatype = 3 THEN "Unenrolement" WHEN t.criteriatype = 4 THEN "Activity completion" WHEN t.criteriatype = 5 THEN "Duration in days" WHEN t.criteriatype = 6 THEN "Final grade" WHEN t.criteriatype = 7 THEN "Approve by role" WHEN t.criteriatype = 8 THEN "Previous course" END AS Criteria_type, CASE WHEN t.criteriatype = 1 THEN "On" WHEN t.criteriatype = 2 THEN DATE_FORMAT(FROM_UNIXTIME(t.timeend),'%Y-%m-%d') WHEN t.criteriatype = 3 THEN "On" WHEN t.criteriatype = 4 THEN CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',t.module,'/view.php?id=',t.moduleinstance,'">',t.module,'</a>') WHEN t.criteriatype = 5 THEN ROUND(t.enrolperiod/86400) WHEN t.criteriatype = 6 THEN ROUND(t.gradepass,2) WHEN t.criteriatype = 7 THEN (SELECT r.shortname FROM prefix_role AS r WHERE r.id = t.role) WHEN t.criteriatype = 8 THEN (SELECT pc.shortname FROM prefix_course AS pc WHERE pc.id = t.courseinstance) END AS Criteria_detail FROM prefix_course_completion_criteria AS t JOIN prefix_course AS c ON t.course = c.id WHERE c.enablecompletion = 1 ORDER BY course
Course Completion Report with custom dates
List of users who completed multiple or single course/s from a start date to end date chosen by the user. The output gives username, name, course name, completion date and score
SELECT u.username AS 'User Name', CONCAT(u.firstname , ' ' , u.lastname) AS 'Name', c.shortname AS 'Course Name', DATE_FORMAT(FROM_UNIXTIME(p.timecompleted),'%W %e %M, %Y') AS 'Completed Date', ROUND(c4.gradefinal,2) AS 'Score' FROM prefix_course_completions AS p JOIN prefix_course AS c ON p.course = c.id JOIN prefix_user AS u ON p.userid = u.id JOIN prefix_course_completion_crit_compl AS c4 ON u.id = c4.userid WHERE c.enablecompletion = 1 AND (p.timecompleted IS NOT NULL OR p.timecompleted !='') AND (p.timecompleted>= :start_date AND p.timecompleted<=:end_date) GROUP BY u.username ORDER BY c.shortname
Scales used in activities
SELECT scale.name ,CONCAT('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,CONCAT('<a target="_new" href="%%WWWROOT%%/mod/',gi.itemmodule,'/view.php?id=',cm.id,'">',gi.itemname,'</a>') AS "Module View" ,CONCAT('<a target="_new" href="%%WWWROOT%%/course/modedit.php?up','date=',cm.id,'">',gi.itemname,'</a>') AS "Module Settings" FROM prefix_grade_items AS gi JOIN prefix_course AS c ON c.id = gi.courseid JOIN prefix_course_modules AS cm ON cm.course = gi.courseid AND cm.instance = gi.iteminstance JOIN prefix_scale AS scale ON scale.id = gi.scaleid WHERE gi.scaleid IS NOT NULL
Reportes de Actividad de Módulos
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
Actividad más popular
SELECT COUNT(l.id) hits, module FROM prefix_log l WHERE module != 'login' AND module != 'course' AND module != 'role' GROUP BY module ORDER BY hits DESC
System wide use of ACTIVITIES and RESOURCES
SELECT COUNT( cm.id ) AS counter, m.name FROM `prefix_course_modules` AS cm JOIN prefix_modules AS m ON cm.module = m.id GROUP BY cm.module ORDER BY counter DESC
LOG file ACTIONS per MODULE per COURSE (IDs)
SELECT course,module,action,COUNT(action) AS summa FROM prefix_log WHERE action <> 'new' GROUP BY course,action,module ORDER BY course,module,action
System Wide usage count of various course Activities
(Tested and works fine in Moodle 2.x) Like: Forum, Wiki, Blog, Assignment, Database,
- Within specific category
- Teacher name in course
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students FROM prefix_course AS c WHERE c.category IN ( 18) ORDER BY Wikis DESC,Blogs DESC, Forums DESC
Course wiki usage/activity over the last 6 semesters
SELECT "Courses with Wikis" ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','2010','%') AND c.fullname LIKE '%Semester A%') AS '2010 <br/> Semester A' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','2010','%') AND c.fullname LIKE '%Semester B%') AS '2010 <br/> Semester B' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעא','%') AND c.fullname LIKE '%סמסטר א%') AS 'תשעא <br/> סמסטר א' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעא','%') AND c.fullname LIKE '%סמסטר ב%') AS 'תשעא <br/> סמסטר ב' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעב','%') AND c.fullname LIKE '%סמסטר א%') AS 'תשעב <br/> סמסטר א' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעב','%') AND c.fullname LIKE '%סמסטר ב%') AS 'תשעב <br/> סמסטר ב' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעג','%') AND c.fullname LIKE '%סמסטר א%') AS 'תשעג <br/> סמסטר א' ,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id JOIN prefix_course AS c ON c.id = cm.course WHERE cm.course = c.id AND m.name LIKE '%wiki%' AND c.fullname LIKE CONCAT('%','תשעג','%') AND c.fullname LIKE '%סמסטר ב%') AS 'תשעג <br/> סמסטר ב'
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
Elluminate (Blackboard Collaborate) - system wide usage
SELECT e.name AS SESSION ,er.recordingsize ,c.fullname AS Course ,u.firstname,u.lastname ,DATE_FORMAT(FROM_UNIXTIME(e.timestart),'%d-%m-%Y') AS dTimeStart ,concat('<a target="_new" href="%%WWWROOT%%/moodle/mod/elluminate/loadrecording.php?id=',er.id,'">Show</a>') AS RecordedSession FROM prefix_elluminate_recordings AS er JOIN prefix_elluminate AS e ON e.meetingid = er.meetingid JOIN prefix_course AS c ON c.id = e.course JOIN prefix_user AS u ON u.id = e.creator ORDER BY er.recordingsize DESC
Choice
Results of the Choice activity. For all courses, shows course shortname, username, the Choice text, and the answer chosen by the user.
SELECT c.shortname AS course, u.username, h.name AS question, o.text AS answer FROM prefix_choice AS h JOIN prefix_course AS c ON h.course = c.id JOIN prefix_choice_answers AS a ON h.id = a.choiceid JOIN prefix_user AS u ON a.userid = u.id JOIN prefix_choice_options AS o ON a.optionid = o.id
Assignment type usage in courses
SELECT CONCAT('<a target="_new" href="%%WWWROOT%%/mod/assign/index.php?id=',c.id,'">',c.fullname,'</a>') AS "List assignments" ,(SELECT COUNT(*) FROM prefix_assign WHERE c.id = course) AS Assignments ,(SELECT COUNT(*) FROM prefix_assign_plugin_config AS apc JOIN prefix_assign AS iassign ON iassign.id = apc.assignment WHERE iassign.course = c.id AND apc.plugin = 'file' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1' #GROUP BY apc.plugin ) AS "File Assignments" ,(SELECT COUNT(*) FROM prefix_assign_plugin_config AS apc JOIN prefix_assign AS iassign ON iassign.id = apc.assignment WHERE iassign.course = c.id AND apc.plugin = 'onlinetext' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1' ) AS "Online Assignments" ,(SELECT COUNT(*) FROM prefix_assign_plugin_config AS apc JOIN prefix_assign AS iassign ON iassign.id = apc.assignment WHERE iassign.course = c.id AND apc.plugin = 'pdf' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1' ) AS "PDF Assignments" ,(SELECT COUNT(*) FROM prefix_assign_plugin_config AS apc JOIN prefix_assign AS iassign ON iassign.id = apc.assignment WHERE iassign.course = c.id AND apc.plugin = 'offline' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1' ) AS "Offline Assignments" ,(SELECT COUNT(*) FROM prefix_assign_plugin_config AS apc JOIN prefix_assign AS iassign ON iassign.id = apc.assignment WHERE iassign.course = c.id AND apc.plugin = 'comments' AND apc.subtype = 'assignsubmission' AND apc.name = 'enabled' AND apc.value = '1' ) AS "Assignments Comments" FROM prefix_assign AS assign JOIN prefix_course AS c ON c.id = assign.course GROUP BY c.id
Reportes del Módulo de Tareas
Todas las tareas sin calificar
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
Todas las tareas sin calificar con Enlace
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
Tareas (y Exámenes pendientes de calificar
This report requires a YEAR filter to be added (Available when using the latest block/configurable_reports)
Which you can always remove, to make this query work on earlier versions.
The report includes:
- number of quizzes
- unFinished Quiz attempts
- Finished Quiz attempts
- number of students
- number of Assignments
- number of submitted answers by students
- number of unchecked assignments (waiting for the Teacher) in a Course.
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course ,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id JOIN prefix_user AS u ON u.id = ra.userid WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher ,concat('<a target="_new" href="%%WWWROOT%%/mod/assignment/index.php?id=',c.id,'">מטלות</a>') AS Assignments ,concat('<a target="_new" href="%%WWWROOT%%/mod/quiz/index.php?id=',c.id,'">בחנים</a>') AS 'Quizzes' ,(SELECT COUNT(*) FROM prefix_course_modules cm JOIN prefix_modules AS m ON m.id = cm.module WHERE m.name LIKE 'quiz' AND cm.course = c.id GROUP BY cm.course ) AS 'nQuizzes' ,(SELECT COUNT(*) FROM prefix_quiz_attempts AS qa JOIN prefix_quiz AS q ON q.id = qa.quiz WHERE q.course = c.id AND qa.timefinish = 0 GROUP BY q.course) AS 'unFinished Quiz attempts' ,(SELECT COUNT(*) FROM prefix_quiz_attempts AS qa JOIN prefix_quiz AS q ON q.id = qa.quiz WHERE q.course = c.id AND qa.timefinish > 0 GROUP BY q.course) AS 'finished quiz attempts' ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id ) AS nStudents ,( SELECT COUNT(a.id) FROM prefix_assignment AS a JOIN prefix_course_modules AS cm ON a.course = cm.course WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id ) nAssignments ,( SELECT COUNT(*) FROM prefix_assignment AS a WHERE a.course = c.id AND FROM_UNIXTIME(a.timedue) > NOW() GROUP BY a.course ) 'Open <br/>Assignments' , CONCAT(ROUND( (100 / iAssignments ) * iOpenAssignments ) ,'%') 'unFinished <br/>Assignments <br/>(percent)' ,( SELECT COUNT(asb.id) FROM prefix_assignment_submissions AS asb JOIN prefix_assignment AS a ON a.id = asb.assignment JOIN prefix_course_modules AS cm ON a.course = cm.course WHERE asb.grade < 0 AND cm.instance = a.id AND cm.module = 1 AND a.course = c.id ) 'unChecked <br/>Submissions' ,( SELECT COUNT(asb.id) FROM prefix_assignment_submissions AS asb JOIN prefix_assignment AS a ON a.id = asb.assignment JOIN prefix_course_modules AS cm ON a.course = cm.course WHERE cm.instance = a.id AND cm.module = 1 AND a.course = c.id ) 'Submitted <br/>Assignments' FROM prefix_course AS c LEFT JOIN ( SELECT course, COUNT(*) AS iAssignments FROM prefix_assignment AS a GROUP BY a.course ) AS tblAssignmentsCount ON tblAssignmentsCount.course = c.id LEFT JOIN ( SELECT course, COUNT(*) AS iOpenAssignments FROM prefix_assignment AS a WHERE FROM_UNIXTIME(a.timedue) > NOW() GROUP BY a.course ) AS tblOpenAssignmentsCount ON tblOpenAssignmentsCount.course = c.id WHERE 1=1 #AND c.fullname LIKE '%תשעג%' %%FILTER_YEARS:c.fullname%% ## You can enable the SEMESTER FILTER AS well, ## BY uncommenting the following line: ## %%FILTER_SEMESTERS:c.fullname%% ORDER BY 'Open <br/>Assignments' DESC
Quien está usando la tarea con "Subida única de archivo"
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'
Reportes de Módulo Recurso
Lista de "Archivos subidos recientemente"
vea lo que los usuarios están subiendo
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
Número de Recursos en cursos
+ (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
Reportes del Módulo Foro
print all User's post in course Forums
@@COURSEID@@ is a variable the is replace by the current CourseID you are running the sql report from. if you are using the latest block/configurable_reports ! (You can always change it to a fixed course or remove it to display all courses.)
SELECT concat('<a target="_new" href="%%WWWROOT%%/mod/forum/user.php?course=',c.id,'&id=',u.id,'&mode=posts">',CONCAT(u.firstname,' ', u.lastname),'</a>') AS Fullname ,concat('<a target="_new" href="%%WWWROOT%%/mod/forum/view.php?f=',fd.forum,'">',f.name,'</a>') AS Forum ,COUNT(*) AS Posts ,(SELECT COUNT(*) FROM prefix_forum_discussions AS ifd JOIN prefix_forum AS iforum ON iforum.id = ifd.forum WHERE ifd.userid = fp.userid AND iforum.id = f.id) AS cAllDiscussion FROM prefix_forum_posts AS fp JOIN prefix_user AS u ON u.id = fp.userid JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id JOIN prefix_forum AS f ON f.id = fd.forum JOIN prefix_course AS c ON c.id = fd.course WHERE fd.course = '@@COURSEID@@' GROUP BY f.id,u.id ORDER BY u.id
FORUM use Count per COURSE -- not including NEWS Forum!
SELECT prefix_course.fullname, prefix_forum.course, COUNT(*) AS total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course ORDER BY total DESC
FORUM use Count per COURSE by type -- not including NEWS Forum!
SELECT prefix_course.fullname, prefix_forum.course, prefix_forum.type, COUNT(*) AS total FROM prefix_forum INNER JOIN prefix_course ON prefix_course.id = prefix_forum.course WHERE NOT(prefix_forum.type = 'news') GROUP BY prefix_forum.course,prefix_forum.type ORDER BY total DESC
Forum activity - system wide
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.id,'</a>') AS CourseID ,( SELECT DISTINCT CONCAT(u.firstname,' ',u.lastname) FROM prefix_role_assignments AS ra JOIN prefix_user AS u ON ra.userid = u.id JOIN prefix_context AS ctx ON ctx.id = ra.contextid WHERE ra.roleid = 3 AND ctx.instanceid = c.id AND ctx.contextlevel = 50 LIMIT 1) AS Teacher ,c.fullname AS Course ,f.type ,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students , fd.forum, f.name,COUNT(*) AS cPostAndDisc ,(SELECT COUNT(*) FROM prefix_forum_discussions AS ifd WHERE ifd.forum = f.id) AS cDiscussion FROM prefix_forum_posts AS fp JOIN prefix_forum_discussions AS fd ON fd.id = fp.discussion JOIN prefix_forum AS f ON f.id = fd.forum JOIN prefix_course AS c ON c.id = f.course WHERE f.type != 'news' AND c.fullname LIKE '%2013%' ## WHERE 1=1 ## %%FILTER_YEARS:c.fullname%% ## You can enable the SEMESTER FILTER AS well, ## BY uncommenting the following line: ## %%FILTER_SEMESTERS:c.fullname%% GROUP BY fd.forum ORDER BY COUNT( * ) DESC
Actividad en Foros
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
Cantidad de foros que han sido posteados por profesor
Queriamos saber cuales son las acciones del profesor dentro de los foros de cada curso, por ello se hizo este informe.
SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.shortname,'</a>') AS curso, CONCAT(u.firstname ,' ',u.lastname) AS Facilitador, (SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm JOIN prefix_modules AS m ON cm.module = m.id WHERE cm.course = c.id AND m.name LIKE '%forum%') AS foros, COUNT(*) AS Posts FROM prefix_forum_posts AS fp JOIN prefix_forum_discussions AS fd ON fp.discussion = fd.id JOIN prefix_forum AS f ON f.id = fd.forum JOIN prefix_course AS c ON c.id = fd.course JOIN prefix_user AS u ON u.id = fp.userid WHERE fp.userid = ( SELECT DISTINCT prefix_user.id FROM prefix_user JOIN prefix_role_assignments AS ra ON ra.userid = prefix_user.id WHERE ra.roleid = 3 AND userid = fp.userid LIMIT 1 ) AND c.shortname LIKE '%2014-2-1%' GROUP BY c.id, u.id
Reportes del Módulo Examen
Generar una lista de de instructores y sus direcciones de correo para aquellos cursos que tengan "preguntas de tipo Ensayo" en sus exámenes
SELECT qu.id AS quiz_id, qu.course AS course_id, qu.questions, co.fullname AS course_fullname, co.shortname AS course_shortname, qu.name AS quiz_name, FROM_UNIXTIME(qu.timeopen) AS quiz_timeopen, FROM_UNIXTIME(qu.timeclose) AS quiz_timeclose, u.firstname, u.lastname, u.email, FROM prefix_quiz qu, prefix_course co, prefix_role re, prefix_context ct, prefix_role_assignments ra, prefix_user u WHERE FROM_UNIXTIME(timeopen) > '2008-05-14' AND qu.course = co.id AND co.id = ct.instanceid AND ra.roleid = re.id AND re.name = 'Teacher' AND ra.contextid = ct.id AND ra.userid = u.id SELECT COUNT('x') AS NumOfStudents FROM prefix_role_assignments a JOIN prefix_user u ON userid = u.id WHERE roleid = 5 AND contextid = (SELECT id FROM prefix_context WHERE instanceid = 668 AND contextlevel = 50)
Número de exámenes por curso
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
Lista de preguntas de Multirespuesta (Cloze)
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'
Listar cursos con calificacines MANUALES
Which is basically and indication to teachers using Moodle to hold offline grades inside Moodle's Gradebook, So grades could be uploaded into an administrative SIS. Use with Configurable Reports.
SELECT COUNT( * ) ,concat('<a target="_new" href="%%WWWROOT%%/grade/edit/tree/index.php?showadvanced=1&id=',c.id,'">',c.fullname,'</a>') AS Course FROM prefix_grade_items AS gi JOIN prefix_course AS c ON c.id = gi.courseid WHERE `itemtype` = 'manual' GROUP BY courseid
Listar los usuarios que no tomaron el Examen
Do not forget to change "c.id = 14" and q.name LIKE '%quiz name goes here%'
SELECT user2.id AS ID, ul.timeaccess, user2.firstname AS Firstname, user2.lastname AS Lastname, user2.email AS Email, user2.username AS IDNumber, user2.institution AS Institution, IF (user2.lastaccess = 0,'never', DATE_FORMAT(FROM_UNIXTIME(user2.lastaccess),'%Y-%m-%d')) AS dLastAccess ,(SELECT DATE_FORMAT(FROM_UNIXTIME(timeaccess),'%Y-%m-%d') FROM prefix_user_lastaccess WHERE userid=user2.id AND courseid=c.id) AS CourseLastAccess ,(SELECT r.name FROM prefix_user_enrolments AS uenrol JOIN prefix_enrol AS e ON e.id = uenrol.enrolid JOIN prefix_role AS r ON e.id = r.id WHERE uenrol.userid=user2.id AND e.courseid = c.id) AS RoleName FROM prefix_user_enrolments AS ue JOIN prefix_enrol AS e ON e.id = ue.enrolid JOIN prefix_course AS c ON c.id = e.courseid JOIN prefix_user AS user2 ON user2 .id = ue.userid LEFT JOIN prefix_user_lastaccess AS ul ON ul.userid = user2.id WHERE c.id=14 AND ue.userid NOT IN (SELECT qa.userid FROM prefix_quiz_attempts AS qa JOIN prefix_quiz AS q ON qa.quiz = q.id JOIN prefix_course AS c ON q.course = c.id WHERE c.id = 14 AND q.name LIKE '%quiz name goes here%')
Listar Preguntas en cada Examen
SELECT quiz.id,quiz.name, q.id, q.name FROM mdl_quiz AS quiz JOIN mdl_question AS q ON FIND_IN_SET(q.id, quiz.questions) WHERE quiz.course = %%COURSEID%% ORDER BY quiz.id ASC
Reportes de Actividad SCORM
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
Insignias
Todas las insignias emitidas, por Usuario
This report will show you all the badges on a site that have been issued, both site and all courses, by the username of each user issued a badge. Includes the type of criteria passed (activity, course completion, manual), date issued, date expires, and a direct link to that issued badge page so you can see all the other details for that badge.
SELECT u.username, b.name AS badgename, CASE WHEN b.courseid IS NOT NULL THEN (SELECT c.shortname FROM prefix_course AS c WHERE c.id = b.courseid) WHEN b.courseid IS NULL THEN "*" END AS Context, CASE WHEN t.criteriatype = 1 AND t.method = 1 THEN "Activity Completion (All)" WHEN t.criteriatype = 1 AND t.method = 2 THEN "Activity Completion (Any)" WHEN t.criteriatype = 2 AND t.method = 2 THEN "Manual Award" WHEN t.criteriatype = 4 AND t.method = 1 THEN "Course Completion (All)" WHEN t.criteriatype = 4 AND t.method = 2 THEN "Course Completion (Any)" ELSE CONCAT ('Other: ', t.criteriatype) END AS Criteriatype, DATE_FORMAT( FROM_UNIXTIME( d.dateissued ) , '%Y-%m-%d' ) AS dateissued, DATE_FORMAT( FROM_UNIXTIME( d.dateexpire ), '%Y-%m-%d' ) AS dateexpires, CONCAT ('<a target="_new" href="%%WWWROOT%%/badges/badge.php?hash=',d.uniquehash,'">link</a>') AS Details FROM prefix_badge_issued AS d JOIN prefix_badge AS b ON d.badgeid = b.id JOIN prefix_user AS u ON d.userid = u.id JOIN prefix_badge_criteria AS t ON b.id = t.badgeid WHERE t.criteriatype <> 0 ORDER BY u.username
Please note: the FROM_UNIXTIME command is for MySQL.
Todas las Insignias disponibles en el sistema, con número de Ganadas
Report of all badges in the system, with badge name and description, context, course shortname if a course badge, whether it is active and available, and a count of how many users have been issued that badge.
SELECT b.id, b.name, b.description, CASE WHEN b.type = 1 THEN "System" WHEN b.type = 2 THEN "Course" END AS Context, CASE WHEN b.courseid IS NOT NULL THEN (SELECT c.shortname FROM prefix_course AS c WHERE c.id = b.courseid) WHEN b.courseid IS NULL THEN "*" END AS Course, CASE WHEN b.status = 0 OR b.status = 2 THEN "No" WHEN b.status = 1 OR b.status = 3 THEN "Yes" WHEN b.status = 4 THEN "x" END AS Available, CASE WHEN b.status = 0 OR b.status = 1 THEN "0" WHEN b.status = 2 OR b.status = 3 OR b.status = 4 THEN (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.badgeid = b.id ) END AS Earned FROM prefix_badge AS b
Badges Leaderboard
A simple list of usernames and how many badges they have earned overall.
SELECT u.username, (SELECT COUNT(*) FROM prefix_badge_issued AS d WHERE d.userid = u.id) AS earned FROM prefix_user AS u ORDER BY earned DESC, u.username ASC
Reportes del Administrador
Cambios en la Configuración en formato amistoso para Exportar
The Administrative report Config changes is very useful but it would be nice to have it in a format that could be easily exported in one listing. Here is code to do that.
SELECT DATE_FORMAT( FROM_UNIXTIME( g.timemodified ) , '%Y-%m-%d' ) AS DATE, u.username AS USER, g.name AS setting, CASE WHEN g.plugin IS NULL THEN "core" ELSE g.plugin END AS plugin, g.value AS new_value, g.oldvalue AS original_value FROM prefix_config_log AS g JOIN prefix_user AS u ON g.userid = u.id ORDER BY DATE DESC
Cohortes por usuario
How to get a list of all users and which cohorts they belong to.
SELECT u.firstname, u.lastname, h.idnumber, h.name FROM prefix_cohort AS h JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid JOIN prefix_user AS u ON hm.userid = u.id ORDER BY u.firstname
Cursos Creados y Cursos Activos por Año
Active courses is counting course that have at least one Hit, And "Active_MoreThan100Hits" counts courses that have at least 100 Hits
SELECT YEAR( FROM_UNIXTIME( `timecreated` ) ) AS YEAR, COUNT( * ) AS Counter , (SELECT COUNT( DISTINCT course ) FROM prefix_log AS l WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) ) ) AS "Active" ,(SELECT COUNT(*) FROM ( SELECT COUNT( * ),TIME FROM prefix_log AS l GROUP BY course HAVING COUNT(*) > 100) AS courses_log WHERE YEAR( FROM_UNIXTIME( courses_log.`time` ) ) = YEAR( FROM_UNIXTIME( `timecreated` ) ) ) AS "Active_MoreThan100Hits" FROM `prefix_course` GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
Usuarios Creados y Usuarios Activos por Año
Active users is counting users that have at least one Hit, And "Active_MoreThan500Hits" counts users that have at least 500 Hits
SELECT YEAR( FROM_UNIXTIME( `firstaccess` ) ) AS YEAR, COUNT( * ) AS Counter , (SELECT COUNT( DISTINCT userid ) FROM prefix_log AS l WHERE YEAR( FROM_UNIXTIME( l.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) ) ) AS "Active" ,(SELECT COUNT(*) FROM ( SELECT COUNT( * ),TIME FROM prefix_log AS l GROUP BY userid HAVING COUNT(*) > 500) AS users_log WHERE YEAR( FROM_UNIXTIME( users_log.`time` ) ) = YEAR( FROM_UNIXTIME( `firstaccess` ) ) ) AS "Active_MoreThan500Hits" FROM `prefix_user` GROUP BY YEAR( FROM_UNIXTIME( `timecreated` ) )
Sub consultas (sub queries) útiles
Todos los profesores en el curso
,(SELECT GROUP_CONCAT( CONCAT( u.firstname, " ", u.lastname ) ) FROM prefix_course ic JOIN prefix_context con ON con.instanceid = ic.id JOIN prefix_role_assignments ra ON con.id = ra.contextid AND con.contextlevel = 50 JOIN prefix_role r ON ra.roleid = r.id JOIN prefix_user u ON u.id = ra.userid WHERE r.id = 3 AND ic.id = c.id GROUP BY ic.id ) AS TeacherNames
No hay comentarios:
Publicar un comentario