SQL report users and their roles

SQL report users and their roles

by Levente Vass -
Number of replies: 5

I'm trying to make an SQL report that displays all the users and their site-wide role (I suppose every user has a site role no matter whether they logged in or not). I made this so far but this code lists not all the users (I don't understand why not):

SELECT distinct u.email, r.name, u.username, u.firstname, u.lastname, if(DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d') = '1970-01-01', 'Never logged in', DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d')) AS 'First logged in',r.name

FROM prefix_role_assignments AS ra

JOIN prefix_role AS r ON r.id = ra.roleid

JOIN prefix_user AS u ON u.id = ra.userid

where u.deleted = 0

Average of ratings: -
In reply to Levente Vass

Re: SQL report users and their roles

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
You can absolutely have users on the site who have no role assignments at all. They won't be able to do much.

If you actually mean site context (but I don't think you do) role assignments then that should be very few users. You may not be aware that site administrator is NOT a role assignment.

Looks ok but 'u.firstaccess = 0' would have been less typing than the 1970 stuff
Average of ratings: -
In reply to Howard Miller

Tárgy: Re: SQL report users and their roles

by Levente Vass -
Thank your for your answer. I have some test users in my site and I want to mark them when someone downloads a report. E.g. I download all the users of the site but I want to see which one of them is a test user (but I have to mark them somehow). First I wanted to add them to a special cohort but I didn't succeed and now I added them to a special role (test), but this is not the right way because I cannot see all the users of the site. Can you help me somehow?

I have another idea, if a username contains the word 'test' the it is a test user, I tried to make an SQL rerport for this but it always gives error. This is the code:

SELECT u.email, u.username , u.firstname, u.lastname, if(DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d') = '1970-01-01', 'Never logged in', DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d')) AS 'First logged in', 
CASE WHEN u.username like '%test%' THEN Testuser = 'Yes'
else
'No'
END AS Testuser 
FROM prefix_user AS u
where u.deleted = 0


Average of ratings: -
In reply to Levente Vass

Tárgy: Re: SQL report users and their roles

by Levente Vass -
I have managed to create such a report, if anyone needs this:
SELECT u.email, u.firstname, u.lastname, if(DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d') = '1970-01-01', 'Never logged in', DATE_FORMAT(FROM_UNIXTIME(u.firstaccess),'%Y-%m-%d')) AS 'First logged in', u.username,
CASE WHEN u.username like '%test%' THEN 'Yes'
else
'No'
END AS Testuser
FROM prefix_user AS u
where u.deleted = 0 and u.username <> 'guest'
Average of ratings: -
In reply to Levente Vass

Re: Tárgy: Re: SQL report users and their roles

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
First, your query does work.

I think you might have discovered that "no," users do not have a site role. I am a "manager," "teacher," and "student" based on my needs within a course.

From your query, you realized that users can be "deleted." When deleted, they are really not in your Moodle, but Moodle prefers to tag these users as "deleted" instead of completely removing them from the prefix_user table (I agree).

There is always a "guest" user, too, which your query does not want to include.
Average of ratings: Useful (1)
In reply to Levente Vass

Re: SQL report users and their roles

by Randy Thornton -
Picture of Documentation writers
"all the users and their site-wide role (I suppose every user has a site role no matter whether they logged in or not"

Actually, it is more complicated.

Three things to understand are: 1) Users who are not logged in are treated as though the were in the "Guest role" but is it not actually assigned to them properly speaking: the code checks whether a user is logged in yet and treats them accordingly: there is only one Guest just as there is only one anonymous; 2) Actual roles are not assigned until a user logs in; and 3) Not all role assignments are in the role_assignments table.

When a user logs in they are immediately assigned one of two roles: Authenticated user or Site administrator. Neither of these appear in the role_assignments table.

Next, after logging in, if the user has been assigned an explicit system level role, they will get that too: the role_assignments table holds the roles that you assign at the site level in SIte administration > Users > Permissions > Assign system roles. You will know this when the contextid for that assignment is 1, which means the site level. This is how the default role of Manager works, for example.

The role_assignments table also holds assignments for categories, courses, activities, and user to user. It's the contextid that tells you exactly where the assignment is made. You will see many examples of how to use this over in the Ad-hoc contributed reports collection: https://docs.moodle.org/400/en/ad-hoc_contributed_reports

Therefore, as Howard says, basing your query on the role_assignments table will only return those users how have some explicit system level role assigned to them:

SELECT u.username, r.shortname
FROM prefix_role_assignments ra
JOIN prefix_user u ON u.id = ra.userid
JOIN prefix_role r ON r.id = ra. roleid
WHERE ra.contextid = 1

Any user when logging in who is not assigned one of those System level roles or is not a Site admin, will be an Authenticated user only. But that is not stored anywhere in the database: the Moodle code just checks to see if the user is logged in or not, and if so, already knows they are an Authenticated user.
Average of ratings: Useful (4)