Hello everyone,
I am trying to create a custom URL resource log report. I have created a custom SQL report, however, If I tried to add the name of the URL, the records get duplicated. This is the query I am using:
SELECT DISTINCT
u.username AS "Username",
CONCAT(u.firstname, ' ', u.lastname) AS "Student Name",
FROM_UNIXTIME(l.timecreated,'%d %b %Y') AS "Day",
FROM_UNIXTIME(l.timecreated,'%H:%i') AS "Time",
c.fullname AS "Course name",
#ur.name AS "URL Name",
g.name AS "Group Name",
l.action AS "Action"
FROM mdl_logstore_standard_log AS l
JOIN mdl_user AS u ON u.id = l.userid
JOIN mdl_course AS c ON c.id=l.courseid
JOIN mdl_groups AS g ON c.id = g.courseid
JOIN mdl_groups_members AS gm ON u.id = gm.userid AND g.id = gm.groupid
LEFT JOIN mdl_context AS ctx ON l.contextid = ctx.id AND l.contextinstanceid = ctx.id
#join mdl_url as ur on c.id = ur.course
WHERE l.action ="viewed" AND c.id IN (5) AND l.component = "mod_url" AND l.crud ="r"
Output:
If I add join mdl_url as ur on c.id = ur.course and ur.name AS "URL Name" I am getting the following output
Can someone please suggest a solution?
Moodle version: 3.7.5+