Documentation Moodle ActiveProLearn

Rendering with filters by course and by year, and output data by month, distinct users and course visits

SELECT

 CONCAT(FROM_UNIXTIME(l.timecreated, '%Y-%m'), '-', c.id) as pkey,
 FROM_UNIXTIME(l.timecreated, '%Y-%m') as month,
 FROM_UNIXTIME(l.timecreated, '%Y') as year,
 c.fullname as cn,
 COUNT(DISTINCT l.userid) as dusr,
 COUNT(*) as usr

FROM

{course} c,
{course_categories} cc1,
{course_categories} cc2,
{logstore_standard_log} l

WHERE

c.category = cc1.id AND
cc1.parent = cc2.id AND
l.courseid = c.id AND
l.action = 'viewed'
<%%FILTERS%%>

GROUP BY

FROM_UNIXTIME(l.timecreated, '%Y-%m')

Tzo4OiJzdGRDbGFzcyI6MTAzOntzOjU6InRpdGxlIjtzOjM2OiJGcsOpcXVlbnRhdGlvbiB0ZW1wb3JlbGxlIGRlcyBjb3VycyAiO3M6MTM6ImluYmxvY2tsYXlvdXQiO3M6MToiMCI7czo2OiJ0YXJnZXQiO3M6NjoibW9vZGxlIjtzOjg6InNob3dkYXRhIjtzOjE6IjEiO3M6OToic2hvd2dyYXBoIjtzOjE6IjEiO3M6MTE6InNob3dudW1zdW1zIjtzOjE6IjEiO3M6OToic2hvd3F1ZXJ5IjtzOjE6IjAiO3M6MTc6InNob3dmaWx0ZXJxdWVyaWVzIjtzOjE6IjAiO3M6NToicXVlcnkiO3M6NTM5OiJTRUxFQ1QNCiAgIENPTkNBVChGUk9NX1VOSVhUSU1FKGwudGltZWNyZWF0ZWQsICclWS0lbScpLCAnLScsIGMuaWQpIGFzIHBrZXksDQogICBGUk9NX1VOSVhUSU1FKGwudGltZWNyZWF0ZWQsICclWS0lbScpIGFzIG1vbnRoLA0KICAgRlJPTV9VTklYVElNRShsLnRpbWVjcmVhdGVkLCAnJVknKSBhcyB5ZWFyLA0KICAgYy5mdWxsbmFtZSBhcyBjbiwNCiAgIENPVU5UKERJU1RJTkNUIGwudXNlcmlkKSBhcyBkdXNyLA0KICAgQ09VTlQoKikgYXMgdXNyDQpGUk9NDQogIHtjb3Vyc2V9IGMsDQogIHtjb3Vyc2VfY2F0ZWdvcmllc30gY2MxLA0KICB7Y291cnNlX2NhdGVnb3JpZXN9IGNjMiwNCiAge2xvZ3N0b3JlX3N0YW5kYXJkX2xvZ30gbA0KV0hFUkUNCiAgYy5jYXRlZ29yeSA9IGNjMS5pZCBBTkQNCiAgY2MxLnBhcmVudCA9IGNjMi5pZCBBTkQNCiAgbC5jb3Vyc2VpZCA9IGMuaWQgQU5EDQogIGwuYWN0aW9uID0gJ3ZpZXdlZCcNCiAgPCUlRklMVEVSUyUlPg0KR1JPVVAgQlkNCiAgRlJPTV9VTklYVElNRShsLnRpbWVjcmVhdGVkLCAnJVktJW0nKSI7czoxMjoic3FscGFyYW12YXIxIjtzOjA6IiI7czoxNDoic3FscGFyYW1sYWJlbDEiO3M6MDoiIjtzOjEzOiJzcWxwYXJhbXR5cGUxIjtzOjY6ImNob2ljZSI7czoxNToic3FscGFyYW12YWx1ZXMxIjtzOjA6IiI7czoxMjoic3FscGFyYW12YXIyIjtzOjA6IiI7czoxNDoic3FscGFyYW1sYWJlbDIiO3M6MDoiIjtzOjEzOiJzcWxwYXJhbXR5cGUyIjtzOjY6ImNob2ljZSI7czoxNToic3FscGFyYW12YWx1ZXMyIjtzOjA6IiI7czoxMjoic3FscGFyYW12YXIzIjtzOjA6IiI7czoxNDoic3FscGFyYW1sYWJlbDMiO3M6MDoiIjtzOjEzOiJzcWxwYXJhbXR5cGUzIjtzOjY6ImNob2ljZSI7czoxNToic3FscGFyYW12YWx1ZXMzIjtzOjA6IiI7czoxMjoic3FscGFyYW12YXI0IjtzOjA6IiI7czoxNDoic3FscGFyYW1sYWJlbDQiO3M6MDoiIjtzOjEzOiJzcWxwYXJhbXR5cGU0IjtzOjY6ImNob2ljZSI7czoxNToic3FscGFyYW12YWx1ZXM0IjtzOjA6IiI7czoxMjoib3V0cHV0ZmllbGRzIjtzOjE0OiJtb250aDtkdXNyO3VzciI7czoxMzoib3V0cHV0Zm9ybWF0cyI7czo4OiIlczslZDslZCI7czo4OiJwYWdlc2l6ZSI7czowOiIiO3M6MTU6InVzZWxvY2FsY2FjaGluZyI7czoxOiIwIjtzOjEwOiJjYWNoaW5ndHRsIjtzOjA6IiI7czoxMjoiY2xlYW5kaXNwbGF5IjtzOjE6IjEiO3M6MjI6ImNsZWFuZGlzcGxheXVwdG9jb2x1bW4iO3M6MDoiIjtzOjg6InNvcnRhYmxlIjtzOjE6IjEiO3M6MTU6InNwbGl0c3Vtc29uc29ydCI7czowOiIiO3M6MTE6ImZpZWxkbGFiZWxzIjtzOjQ2OiJNb2lzO1V0aWxpc2F0ZXVycyBkaXN0aW5jdHM7VmlzaXRlcyAoZHUgY291cnMpIjtzOjk6InRhYmxldHlwZSI7czo2OiJsaW5lYXIiO3M6NzoiZmlsdGVycyI7czo1OToiYy5mdWxsbmFtZSBhcyBjbjtGUk9NX1VOSVhUSU1FKGwudGltZWNyZWF0ZWQsICclWScpIGFzIHllYXIiO3M6MTI6ImZpbHRlcmxhYmVscyI7czoxMzoiQ291cnNlO0FubsOpZSI7czoxNDoiZmlsdGVyZGVmYXVsdHMiO3M6MTA6IkZJUlNUO0xBU1QiO3M6MTM6ImZpbHRlcm9wdGlvbnMiO3M6NToic3g7bXgiO3M6MTI6InZlcnRpY2Fsa2V5cyI7czowOiIiO3M6ODoiaG9yaXprZXkiO3M6MDoiIjtzOjE1OiJ2ZXJ0aWNhbGZvcm1hdHMiO3M6MDoiIjtzOjExOiJob3JpemZvcm1hdCI7czowOiIiO3M6Nzoic3BsaXRvbiI7czowOiIiO3M6OToiaG9yaXpzdW1zIjtzOjE6IjAiO3M6ODoidmVydHN1bXMiO3M6MToiMCI7czoxNDoidmVydGljYWxsYWJlbHMiO3M6MDoiIjtzOjEwOiJob3JpemxhYmVsIjtzOjA6IiI7czoxMToicGFyZW50c2VyaWUiO3M6MDoiIjtzOjEwOiJ0cmVlb3V0cHV0IjtzOjA6IiI7czoxNzoidHJlZW91dHB1dGZvcm1hdHMiO3M6MDoiIjtzOjEwOiJ4YXhpc2ZpZWxkIjtzOjU6Im1vbnRoIjtzOjEwOiJ4YXhpc2xhYmVsIjtzOjQ6Ik1vaXMiO3M6NzoieXNlcmllcyI7czo4OiJkdXNyO3VzciI7czoxNDoieXNlcmllc2Zvcm1hdHMiO3M6MjoiJWQiO3M6NDoieW1pbiI7czoxOiIwIjtzOjQ6InltYXgiO3M6MzoiNjAwIjtzOjExOiJ0aWNrc3BhY2luZyI7czoxOiI1IjtzOjEwOiJ5YXhpc3NjYWxlIjtzOjY6ImxpbmVhciI7czoxMjoic2VyaWVzbGFiZWxzIjtzOjMwOiJVdGlsaXNhdGV1cnMgZGlzdGluY3RzO1Zpc2l0ZXMiO3M6MTA6InlheGlzbGFiZWwiO3M6MTI6IlV0aWxpc2F0ZXVycyI7czoxNDoieWF4aXN0aWNrYW5nbGUiO3M6MzoiLTQ1IjtzOjk6ImdyYXBodHlwZSI7czozOiJiYXIiO3M6MTA6ImdyYXBod2lkdGgiO3M6MzoiOTUwIjtzOjExOiJncmFwaGhlaWdodCI7czozOiI0NTAiO3M6MTA6InNob3dsZWdlbmQiO3M6MToiMCI7czo3OiJtYXB0eXBlIjtzOjc6IlJPQURNQVAiO3M6NDoiem9vbSI7czoxOiI2IjtzOjEwOiJkYXRhdGl0bGVzIjtzOjA6IiI7czoxMzoiZGF0YWxvY2F0aW9ucyI7czowOiIiO3M6OToiZGF0YXR5cGVzIjtzOjA6IiI7czozOiJsYXQiO3M6MToiMCI7czozOiJsbmciO3M6MToiMCI7czoxMzoic2hvd2xvd2VyYmFuZCI7czoxOiIxIjtzOjEzOiJ1cHBlcmJhbmR1bml0IjtzOjU6Ik1PTlRIIjtzOjEzOiJsb3dlcmJhbmR1bml0IjtzOjQ6IllFQVIiO3M6MTg6InRpbWVsaW5lZXZlbnR0aXRsZSI7czowOiIiO3M6MTg6InRpbWVsaW5lZXZlbnRzdGFydCI7czowOiIiO3M6MTY6InRpbWVsaW5lZXZlbnRlbmQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWV2ZW50bGluayI7czowOiIiO3M6MTc6InRpbWVsaW5lZXZlbnRkZXNjIjtzOjA6IiI7czoxNDoidGltZWxpbmVjb2xvcnMiO3M6MDoiIjtzOjE4OiJ0aW1lbGluZWNvbG9yZmllbGQiO3M6MDoiIjtzOjE3OiJ0aW1lbGluZWNvbG9ya2V5cyI7czowOiIiO3M6NzoibnVtc3VtcyI7czowOiIiO3M6MTM6Im51bXN1bWZvcm1hdHMiO3M6MDoiIjtzOjEyOiJudW1zdW1sYWJlbHMiO3M6MDoiIjtzOjg6ImNyb25tb2RlIjtzOjk6Im5vcmVmcmVzaCI7czo4OiJjcm9uaG91ciI7czowOiIiO3M6NzoiY3Jvbm1pbiI7czowOiIiO3M6MTM6ImNyb25mcmVxdWVuY3kiO3M6NToiZGFpbHkiO3M6ODoibWFrZWZpbGUiO3M6MToiMCI7czoxMDoiZmlsZW91dHB1dCI7czowOiIiO3M6MTc6ImZpbGVvdXRwdXRmb3JtYXRzIjtzOjA6IiI7czoxMDoiZmlsZWZvcm1hdCI7czozOiJDU1YiO3M6MTU6ImZpbGVzcWxvdXR0YWJsZSI7czowOiIiO3M6MTI6ImZpbGVsb2NhdGlvbiI7czowOiIiO3M6MjE6ImZpbGVwYXRoYWRtaW5vdmVycmlkZSI7czowOiIiO3M6ODoic2F2ZXZpZXciO3M6MjM6IkVucmVnaXN0cmVyIGV0IGFmZmljaGVyIjtzOjk6InNxbHBhcmFtcyI7TjtzOjk6ImlzcnVubmluZyI7aTowO3M6ODoibGFzdGNyb24iO2k6MDt9

• Valéry Frémaux (valery@activeprolearn.com)- Development and documentation
• Florence Labord (florence@activeprolearn.com) - documentation

Back to generic query catalogue index - Component index - Back to plugins index - Back to the catalogue index