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

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,

1. Within specific category
2. Teacher name in course

SELECT concat('<a target="_new" href="%%WWWROOT%%/course/view.php?id=',c.id,'">',c.fullname,'</a>') AS Course
 
,(SELECT CONCAT(u.firstname,' ', u.lastname) AS Teacher
FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
JOIN prefix_user AS u ON u.id = ra.userid
WHERE ra.roleid = 3 AND ctx.instanceid = c.id LIMIT 1) AS Teacher 
 
,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%wiki%') AS Wikis
,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%blog%') AS Blogs
 
,(SELECT COUNT( m.name ) AS COUNT FROM 
prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%forum%') AS Forums
 
,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%data%') AS Databses
 
,(SELECT COUNT( m.name ) AS COUNT FROM prefix_course_modules AS cm
JOIN prefix_modules AS m ON cm.module = m.id
WHERE cm.course = c.id AND m.name LIKE '%assignment%') AS Assignments
 
,(SELECT COUNT( ra.userid ) AS Users FROM prefix_role_assignments AS ra
JOIN prefix_context AS ctx ON ra.contextid = ctx.id
WHERE ra.roleid = 5 AND ctx.instanceid = c.id) AS Students
 
FROM prefix_course AS c
WHERE c.category IN ( 18)
ORDER BY Wikis DESC,Blogs DESC, Forums DESC

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