Displays the list of users who have never logged in, by cohort, with as output data the categories and subcategories of the courses concerned, the shortnames and the courses, and the cohort names. For users: last name, first name, email, username, role and a custom profile field on their function in Moodle)
SELECT
CONCAT(c.id,'-',u.id) as pkey, c.shortname as cs, c.fullname as cf, c.id as cid, cc1.name as cc1, cc2.name as cc2, ch.name as ch, u.firstname as fn, u.lastname as ln, u.email as em, u.username as un, GROUP_CONCAT(DISTINCT r.shortname ORDER BY r.sortorder DESC SEPARATOR ', ') as roles, uid.data as profil
FROM
{user} u
JOIN
{cohort_members} cm ON cm.userid = u.id
JOIN
{cohort} ch ON cm.cohortid = ch.id
JOIN
{course} c
JOIN
{course_categories} cc1 ON c.category = cc1.id
JOIN
{course_categories} cc2 ON cc1.parent = cc2.id
JOIN
{enrol} e ON e.courseid = c.id AND e.status = 0
JOIN
{context} ctx ON c.id = ctx.instanceid AND ctx.contextlevel = 50
JOIN
{role_assignments} ra ON ctx.id = ra.contextid AND ra.userid = u.id
JOIN
{role} r ON ra.roleid = r.id
LEFT JOIN
{user_lastaccess} ul ON ul.userid = u.id AND c.id = ul.courseid
LEFT JOIN
{user_info_data} uid ON u.id = uid.userid AND uid.fieldid = 2
WHERE
(ul.timeaccess = 0 OR ul.timeaccess IS NULL) <%%FILTERS%%>
GROUP BY
CONCAT(c.id,'-',u.id)
ORDER BY
cc2,cc1,cf,ch
Tzo4OiJzdGRDbGFzcyI6MTA0OntzOjU6InRpdGxlIjtzOjQyOiJWb2x1bcOpdHJpZSBkZXMgbm9uIGNvbm5lY3TDqXMgcGFyIGNvaG9ydGUiO3M6MTM6ImluYmxvY2tsYXlvdXQiO3M6MToiMCI7czo2OiJ0YXJnZXQiO3M6NjoibW9vZGxlIjtzOjg6InNob3dkYXRhIjtzOjE6IjEiO3M6OToic2hvd2dyYXBoIjtzOjE6IjAiO3M6MTE6InNob3dudW1zdW1zIjtzOjE6IjEiO3M6OToic2hvd3F1ZXJ5IjtzOjE6IjAiO3M6MTc6InNob3dmaWx0ZXJxdWVyaWVzIjtzOjE6IjAiO3M6NToicXVlcnkiO3M6ODc5OiJTRUxFQ1QNCiAgIENPTkNBVChjLmlkLCctJyxjaC5pZCkgYXMgcGtleSwNCiAgIGMuc2hvcnRuYW1lIGFzIGNzLA0KICAgYy5pZCBhcyBjaWQsDQogICBjLmZ1bGxuYW1lIGFzIGNmLA0KICAgY2MxLm5hbWUgYXMgY2MxLA0KICAgY2MyLm5hbWUgYXMgY2MyLA0KICAgY2gubmFtZSBhcyBjaCwNCiAgIENPVU5UKCopIGFzIHVzLA0KICAgU1VNKENBU0UgV0hFTiB1bC50aW1lYWNjZXNzIElTIE5VTEwgT1IgdWwudGltZWFjY2VzcyA9IDAgVEhFTiAxIEVMU0UgMCBFTkQpIGFzIG5jdSwNCiAgIFNVTShDQVNFIFdIRU4gdWwudGltZWFjY2VzcyBJUyBOVUxMIE9SIHVsLnRpbWVhY2Nlc3MgPSAwIFRIRU4gMSBFTFNFIDAgRU5EKSAvIENPVU5UKCopICAqIDEwMCBhcyBwcmN0DQpGUk9NDQogICB7dXNlcn0gdQ0KSk9JTg0KICAge2NvaG9ydF9tZW1iZXJzfSBjbSBPTiBjbS51c2VyaWQgPSB1LmlkDQpKT0lODQogICB7Y29ob3J0fSBjaCBPTiBjbS5jb2hvcnRpZCA9IGNoLmlkDQpKT0lODQogICB7ZW5yb2x9IGUgT04gZS5jdXN0b21pbnQxID0gY2guaWQNCkpPSU4NCiAgIHtjb3Vyc2V9IGMgT04gZS5jb3Vyc2VpZCA9IGMuaWQNCkpPSU4NCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MxIE9OIGNjMS5pZCA9IGMuY2F0ZWdvcnkNCkpPSU4NCiAgIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MyIE9OIGNjMS5wYXJlbnQgPSBjYzIuaWQNCkxFRlQgSk9JTg0KICAge3VzZXJfbGFzdGFjY2Vzc30gdWwgT04gdWwudXNlcmlkID0gdS5pZCBBTkQgdWwuY291cnNlaWQgPSBjLmlkDQpXSEVSRQ0KICAgZS5lbnJvbCA9ICdjb2hvcnQnIEFORA0KICAgZS5zdGF0dXMgPSAwDQogICA8JSVGSUxURVJTJSU+DQpHUk9VUCBCWQ0KICAgIENPTkNBVChjLmlkLCctJyxjaC5pZCkiO3M6MTU6InF1ZXJ5cm90YXRlY29scyI7czowOiIiO3M6MTY6InF1ZXJ5cm90YXRlcGl2b3QiO3M6MDoiIjtzOjE4OiJxdWVyeXJvdGF0ZW5ld2tleXMiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjEiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMSI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTEiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczEiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjIiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMiI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTIiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczIiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjMiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsMyI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTMiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczMiO3M6MDoiIjtzOjEyOiJzcWxwYXJhbXZhcjQiO3M6MDoiIjtzOjE0OiJzcWxwYXJhbWxhYmVsNCI7czowOiIiO3M6MTM6InNxbHBhcmFtdHlwZTQiO3M6NjoiY2hvaWNlIjtzOjE1OiJzcWxwYXJhbXZhbHVlczQiO3M6MDoiIjtzOjEyOiJvdXRwdXRmaWVsZHMiO3M6Mjg6ImNjMjtjYzE7Y3M7Y2Y7Y2g7bmN1O3VzO3ByY3QiO3M6MTM6Im91dHB1dGZvcm1hdHMiO3M6NjY6IiVzOyVzOyVzOzxhIGhyZWY9Ii9jb3Vyc2Uvdmlldy5waHA/aWQ9JXtjaWR9Ij4lczwvYT47JXM7JWQ7JWQ7JS4xZiI7czo4OiJwYWdlc2l6ZSI7czowOiIiO3M6MTU6InVzZWxvY2FsY2FjaGluZyI7czoxOiIwIjtzOjEwOiJjYWNoaW5ndHRsIjtzOjA6IiI7czoxMjoiY2xlYW5kaXNwbGF5IjtzOjE6IjEiO3M6MjI6ImNsZWFuZGlzcGxheXVwdG9jb2x1bW4iO3M6MDoiIjtzOjg6InNvcnRhYmxlIjtzOjE6IjEiO3M6MTU6InNwbGl0c3Vtc29uc29ydCI7czowOiIiO3M6MTE6ImZpZWxkbGFiZWxzIjtzOjc4OiJDYXTDqWdvcmllO1NvdXMtY2F0w6lnb3JpZTtDb2RlO0NvdXJzO0NvaG9ydGU7Tm9uIGNvbm5lY3TDqXM7VG90YWw7UG91cmNlbnRhZ2UiO3M6OToidGFibGV0eXBlIjtzOjY6ImxpbmVhciI7czo3OiJmaWx0ZXJzIjtzOjE2OiJjLmZ1bGxuYW1lIGFzIGNmIjtzOjEyOiJmaWx0ZXJsYWJlbHMiO3M6NToiQ291cnMiO3M6MTQ6ImZpbHRlcmRlZmF1bHRzIjtzOjU6IkZJUlNUIjtzOjEzOiJmaWx0ZXJvcHRpb25zIjtzOjI6InN4IjtzOjEyOiJ2ZXJ0aWNhbGtleXMiO3M6MDoiIjtzOjg6Imhvcml6a2V5IjtzOjA6IiI7czoxNToidmVydGljYWxmb3JtYXRzIjtzOjA6IiI7czoxMToiaG9yaXpmb3JtYXQiO3M6MDoiIjtzOjc6InNwbGl0b24iO3M6MDoiIjtzOjk6Imhvcml6c3VtcyI7czoxOiIwIjtzOjg6InZlcnRzdW1zIjtzOjE6IjAiO3M6MTQ6InZlcnRpY2FsbGFiZWxzIjtzOjA6IiI7czoxMDoiaG9yaXpsYWJlbCI7czowOiIiO3M6MTE6InBhcmVudHNlcmllIjtzOjA6IiI7czoxMDoidHJlZW91dHB1dCI7czowOiIiO3M6MTc6InRyZWVvdXRwdXRmb3JtYXRzIjtzOjA6IiI7czoxMDoieGF4aXNmaWVsZCI7czowOiIiO3M6MTA6InhheGlzbGFiZWwiO3M6MDoiIjtzOjc6InlzZXJpZXMiO3M6MDoiIjtzOjE0OiJ5c2VyaWVzZm9ybWF0cyI7czowOiIiO3M6NDoieW1pbiI7czowOiIiO3M6NDoieW1heCI7czowOiIiO3M6MTE6InRpY2tzcGFjaW5nIjtzOjA6IiI7czoxMjoic2VyaWVzbGFiZWxzIjtzOjA6IiI7czoxMDoieWF4aXNsYWJlbCI7czowOiIiO3M6OToiZ3JhcGh0eXBlIjtzOjQ6ImxpbmUiO3M6MTA6ImdyYXBod2lkdGgiO3M6MzoiNDUwIjtzOjExOiJncmFwaGhlaWdodCI7czozOiIyNTAiO3M6MTA6InNob3dsZWdlbmQiO3M6MToiMCI7czo3OiJtYXB0eXBlIjtzOjc6IlJPQURNQVAiO3M6NDoiem9vbSI7czoxOiI2IjtzOjEwOiJkYXRhdGl0bGVzIjtzOjA6IiI7czoxMzoiZGF0YWxvY2F0aW9ucyI7czowOiIiO3M6OToiZGF0YXR5cGVzIjtzOjA6IiI7czozOiJsYXQiO3M6MToiMCI7czozOiJsbmciO3M6MToiMCI7czoxMzoic2hvd2xvd2VyYmFuZCI7czoxOiIxIjtzOjEzOiJ1cHBlcmJhbmR1bml0IjtzOjU6Ik1PTlRIIjtzOjEzOiJsb3dlcmJhbmR1bml0IjtzOjQ6IllFQVIiO3M6MTg6InRpbWVsaW5lZXZlbnR0aXRsZSI7czowOiIiO3M6MTg6InRpbWVsaW5lZXZlbnRzdGFydCI7czowOiIiO3M6MTY6InRpbWVsaW5lZXZlbnRlbmQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWV2ZW50bGluayI7czowOiIiO3M6MTc6InRpbWVsaW5lZXZlbnRkZXNjIjtzOjA6IiI7czoxNDoidGltZWxpbmVjb2xvcnMiO3M6MDoiIjtzOjE4OiJ0aW1lbGluZWNvbG9yZmllbGQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWNvbG9ya2V5cyI7czowOiIiO3M6NzoibnVtc3VtcyI7czo2OiJuY3U7dXMiO3M6MTM6Im51bXN1bWZvcm1hdHMiO3M6NToiJWQ7JWQiO3M6MTI6Im51bXN1bWxhYmVscyI7czozOToiVG90YWwgdXRpbGlzYXRldXJzO1RvdGFsIG5vbiBjb25uZWN0w6lzIjtzOjg6ImNyb25tb2RlIjtzOjk6Im5vcmVmcmVzaCI7czo4OiJjcm9uaG91ciI7czowOiIiO3M6NzoiY3Jvbm1pbiI7czowOiIiO3M6MTM6ImNyb25mcmVxdWVuY3kiO3M6NToiZGFpbHkiO3M6ODoibWFrZWZpbGUiO3M6MToiMCI7czoxMDoiZmlsZW91dHB1dCI7czowOiIiO3M6MTc6ImZpbGVvdXRwdXRmb3JtYXRzIjtzOjA6IiI7czoxMDoiZmlsZWZvcm1hdCI7czozOiJDU1YiO3M6MTU6ImZpbGVzcWxvdXR0YWJsZSI7czowOiIiO3M6MTI6ImZpbGVsb2NhdGlvbiI7czowOiIiO3M6MjE6ImZpbGVwYXRoYWRtaW5vdmVycmlkZSI7czowOiIiO3M6ODoic2F2ZXZpZXciO3M6MjM6IkVucmVnaXN0cmVyIGV0IGFmZmljaGVyIjtzOjk6InNxbHBhcmFtcyI7TjtzOjk6ImlzcnVubmluZyI7aTowO3M6ODoibGFzdGNyb24iO2k6MDt9
Back to generic query catalogue index - Component index - Back to plugins index - Back to the catalogue index