Reporting on absence of roles

[Peter Clough]

Good morning. We are trying to build a report to find users who are missing the basic user roles we assign to everyone, however when we test this report, we get back people who, at least so far, have all had all of the roles looked for.

We currently have the following on the filter:

h_sys_accounts_roles is not Basic User Role, Self Service User, Docmanager Portal AND h_sys_accounts attribute is not ICT or HR AND h_sys_accounts account status is not 2/archived

Is anyone able to advise where we've gone wrong with this please?

[Jim]

I use this to check if the script failed and didn't apply the roles that all users should have, you can build on it if you like to look at specific roles

SELECT * FROM h_sys_accounts as a
where h_account_status = '0'
AND NOT Exists (SELECT * FROM h_sys_accounts_roles as b  where a.h_user_id = b.h_user_id)

Basically where the account is not archived (I believe - was a while ago I wrote this) and they are not in the sys account roles table

[Jim]

Correction: I think 0 is a basic user, Done this way because the analysts would have different roles 

[Sam P]

If you're sure EVERYONE needs it, you could just add it here and it will add to those that don't have it: