Configurable reports block (plugin)

Here is one I use to get student enrollments.  You are missing the context table in your query.  You should be able to take mine, adjust to what you need (add a column for role) and remove the specific role reference at the end.  Mine is specific to a single category but you can remove that line.

SELECT DISTINCT c.shortname, usr.firstname, usr.lastname, usr.city, groups.name

FROM mdl_course AS c

INNER JOIN mdl_context AS cx ON c.id = cx.instanceid

AND cx.contextlevel = '50'

AND c.category = '4'

INNER JOIN mdl_role_assignments AS ra ON cx.id = ra.contextid

INNER JOIN mdl_role AS r ON ra.roleid = r.id

INNER JOIN mdl_user AS usr ON ra.userid = usr.id

INNER JOIN mdl_groups AS groups ON c.id = groups.courseid

INNER JOIN mdl_groups_members AS members ON groups.id = members.groupid

WHERE members.userid = usr.id

AND r.name = "Student"

ORDER BY groups.name