Hi,
I am using Moodle 3.6
In custom report I can get logged in users by this query:
SELECT id, username, firstname, lastname,FROM_UNIXTIME( lastaccess ) AS lastaccess
FROM prefix_user
WHERE DATEDIFF( NOW(),FROM_UNIXTIME( lastaccess ) ) < 1100
Now, I need to add user's course and email address in this query.
How can I add ?
Please help...
Do help please
Have you looked at some of the contributed reports?
https://docs.moodle.org/36/en/ad-hoc_contributed_reports#Students_in_all_courses_of_some_institute
Hi Rick,
Thanks for reply.
Above query is from the same link. I need to add course in addition.
I don't have time right not to write the full query, but you need to combine your above query with the contributed report query.
This one give you students in all courses:
SELECT c.id, c.fullname, u.firstname, u.lastname, u.deleted
FROM mdl_course AS c
JOIN mdl_context AS ctx ON c.id = ctx.instanceid
JOIN mdl_role_assignments AS ra ON ra.contextid = ctx.id
JOIN mdl_user AS u ON u.id = ra.userid
WHERE ra.roleid =5
AND ctx.instanceid = c.id
Thanks for reply Rick...
Your query says "No explicit prefix"
My guess is that you might have to change my table prefix, "mdl_" to whatever your Moodle is using. This prefix gets established when one installs Moodle.