Hi I was wondering if anyone knew of a way to run a report to list all users set up on our Moodle system (2.5) with their associated email address and other selected data?
I am also keen to understand how to report by cohort
Thanks in advance
Use Site admin > Users > Accounts > Browse list of users, and also Bulk user actions, if you need to download a report to spreadsheet. Cohort is one of the fields you can filter by. You can search cohorts in Site admin > Users > Accounts > Cohorts.
Thank you that's great! Sussed the all users - unfortunately we don't have a cohort field so back to the drawing board on this that bit
If you use the configurable reports plugin, you could probably get a sql report to give you what you need. I have one that will pull the cohort names/ids/description, the user first/last name and ID (not the moodle id, but idnumber). It's pretty easy to add and remove fields, so it could probably do what you want.
I put such a query in the ad hoc reports area some time ago : http://docs.moodle.org/26/en/ad-hoc_contributed_reports#Cohorts_by_user
Melanie - if you have a fancier/nicer one, please feel free to add it over there
Thank you Randy and others for your contributions in building ad hoc reporting SQL queries!
I have never built an SQL query before but I was hoping to piece together a couple that I have found to be particularly useful. I'd like to report on users who belong to cohorts AND report the number of times they've logged in and accessed activities.
Any help would be greatly appreciated!
Here's Randy's cohort users piece:
SELECT u.firstname, u.lastname, h.idnumber, h.name
FROM prefix_cohort AS h
JOIN prefix_cohort_members AS hm ON h.id = hm.cohortid
JOIN prefix_user AS u ON hm.userid = u.id
ORDER BY hm.cohortid
And here is the logins/activities per user piece:
SELECT concat('<a target="_new" href="%%USERID%%', u.id, '&mode=alllogs">', u.firstname ,' ', u.lastname,'</a>') AS Username
,count(*) AS logins
,(SELECT count(*) FROM prefix_log WHERE userid = l.userid GROUP BY userid) AS Activity
FROM prefix_log AS l JOIN prefix_user AS u ON l.userid = u.id
WHERE `action` LIKE '%login%' GROUP BY userid
ORDER BY Activity DESC