Hey @Community Alums
For this requirement, I would avoid relying only on Database Views if you need to audit the complete inherited role hierarchy.
Database Views are not recursive, so they work only when the inheritance depth is fixed and known. In real role structures, sys_user_role_contains can have multiple levels, so a fixed-level DB View may miss roles once the hierarchy goes deeper than the joins you created.
Approach:
Use a Script Include or scheduled job to recursively resolve the role hierarchy, then store the flattened result in a custom reporting table. Build the dashboard on that custom table instead of calculating the full hierarchy live in the report.
High-level approach:
- Get roles assigned directly to the user from sys_user_has_role.
- Get roles assigned through groups using:
- sys_user_grmember
- sys_group_has_role
- For each role, recursively check sys_user_role_contains.
- Continue until no further child roles are found.
- Store the resolved data in a custom audit table.
- Use that table for dashboard/reporting.
Example custom audit table fields:
User
Assignment source: Direct / Group / Inherited
Group
Parent role
Inherited role
Role path
Contains admin keyword
Last calculated date
Important point:
While writing the recursive logic, maintain a visited role list to avoid duplicate processing or possible circular role references.
Example logic:
- Start with Role X
- Check child roles in sys_user_role_contains
- For each child role, check if it also contains other roles
- Continue recursively
- Stop when there are no more child roles or the role was already processed
For reporting, I would suggest not running recursion directly inside the dashboard. Instead, calculate the data through a scheduled job and store it in a custom table. The dashboard can then simply report on the flattened table.
So the practical recommendation would be:
Fixed-level DB View: only suitable if hierarchy depth is small and predictable
Recursive Script Include: suitable for resolving the complete hierarchy
Custom flattened audit table: best option for dashboards and audit reporting
This pattern is usually more reliable for audit dashboards where both accuracy and performance are important.
*********************************************************************************************************
If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.
Regards
Vaishali Singh
Servicenow Developer
Linkedin - https://www.linkedin.com/in/vaishali-singh-2273361bb
