Hola Claudio yo talvez te pueda ayudar con una de tus inquietudes (1)
ESta consulta la creo Cristian Blanquer : https://moodle.org/mod/forum/discuss.php?d=346950&parent=1401289
Nombres de los profesores
select
C.fullname,
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 01 ) as 'Enero',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 02 ) as 'Febrero',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 03 ) as 'Marzo',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 04 ) as 'Abril',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 05 ) as 'Mayo',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 06 ) as 'Junio',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 07 ) as 'Julio',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 08 ) as 'Agosto',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 09 ) as 'Septiembre',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 10 ) as 'Octubre',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 11 ) as 'Noviembre',
(select GROUP_CONCAT(CONCAT(U.firstname, ' ', U.lastname) SEPARATOR ', ') FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 12 ) as 'Diciembre'
FROM mdl_course C
WHERE fullname='NOMBRE_CURSO'
or fullname='NOMBRE_CURSO' ;
Cuenta de profesores
select
C.fullname ,
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 01 ) as 'Enero',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 02 ) as 'Febrero',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 03 ) as 'Marzo',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 04 ) as 'Abril',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 05 ) as 'Mayo',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 06 ) as 'Junio',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 07 ) as 'Julio',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 08 ) as 'Agosto',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 09 ) as 'Septiembre',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 10 ) as 'Octubre',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 11 ) as 'Noviembre',
(select COUNT(U.id) FROM mdl_user U INNER JOIN mdl_role_assignments RA ON RA.userid = U.id INNER JOIN mdl_user_lastaccess UL ON UL.userid = U.id WHERE RA.roleid = 5 AND UL.courseid = C.id AND DATE_FORMAT(FROM_UNIXTIME(UL.timeaccess), '%m') = 12 ) as 'Diciembre'
FROM mdl_course C
-- WHERE C.id = 3
WHERE fullname='NOMBRE_CURSO'
or fullname='NOMBRE_CURSO' ;
DEBES TENER EN CUENTA EL ID DEL ROL Y EL NOMBRE DEL CURSO, TAMBIÉN EL NOMBRE DE LAS TABLAS COMO VOS LAS TIENES