Requêtes utiles

Les cours sur lesquels est inscrit un utilisateur avec son rôle dans le cours

SELECT c.id, c.shortname, c.fullname, u.id, u.username, u.firstname, u.lastname, r.shortname as 'role'
From mdl_user as u
join mdl_user_enrolments ue on ue.userid=u.id  AND u.id=18434
join mdl_enrol en on ue.enrolid=en.id
join mdl_role_assignments ra on u.id=ra.userid
join mdl_role r on ra.roleid=r.id 
join mdl_context cx on cx.id = ra.contextid and cx.contextlevel = 50
JOIN mdl_course c ON c.id = cx.instanceid AND en.courseid = c.id;
 AND u.id=18434;

Liste utilisateurs avec rôle

SELECT u.id,u.firstname,u.lastname,u.email,u.suspended,u.deleted,u.auth,FROM_UNIXTIME(u.firstaccess),FROM_UNIXTIME(u.lastaccess),FROM_UNIXTIME(u.lastlogin),r.name
FROM mdl_user u
INNER JOIN mdl_role_assignments ra ON ra.userid = u.id
INNER JOIN mdl_context ct ON ct.id = ra.contextid
INNER JOIN mdl_role r ON r.id = ra.roleid
GROUP BY u.id ORDER BY u.lastlogin ASC
INTO OUTFILE '/tmp/ead_user.csv' 
FIELDS ENCLOSED BY '"' 
TERMINATED BY ';' 
ESCAPED BY '"' 
LINES TERMINATED BY '\r\n';
;

Les traces d'un utilisateur entre 2 dates

select eventname,target,courseid,FROM_UNIXTIME(timecreated) from mdl_logstore_standard_log where timecreated>1675688400 AND timecreated<1675702800 AND userid=78293;

Nbr de participants dans un ou plusieurs cours

SELECT c.id, c.shortname, c.fullname, u.id, u.username, u.firstname, u.lastname, r.shortname as 'role', FROM_UNIXTIME(ue.timecreated)
From   mdl_user as u
join   mdl_user_enrolments ue on ue.userid=u.id
join   mdl_enrol en on ue.enrolid=en.id AND (ue.timecreated>1688162400 AND ue.timecreated<1693951150)
join   mdl_role_assignments ra on u.id=ra.userid
join   mdl_role r on ra.roleid=r.id and r.shortname ='student'
join   mdl_context cx on cx.id = ra.contextid and cx.contextlevel = 50
JOIN   mdl_course c ON c.id = cx.instanceid AND en.courseid and c.id>3606
GROUP BY c.id,u.id;