need to fetch removed users list from itil role

[Purushotham Ga2]

HI Experts,

How to create a report showing users with ITIL since 2025, as well as users who had ITIL roles removed.

Please assist me on this. 

[Purushotham Ga2]

Hi @fknell ,

Thanks for the reply

Users Who Had ITIL Role Removed
This report tracks removals, which is crucial for license reclamation.
Table: Deleted Records (sys_audit_delete)
Filters:
Table name | is | sys_user_has_role
Payload | Contains | itil
Deleted | on or after | 2025-01-01 00:00:00

the above query is working but here i need user name suppose to be fetch from list view.

pls assist me on this

[Vaishnavi Lathk]

Hi Purushotham,

You can get this information by using the User Has Role (sys_user_has_role) table along with the Audit (sys_audit) table.

For users who currently have the ITIL role since 2025, you can create a report on User Has Role with conditions like Role = ITIL and Created on after 01 Jan 2025. This will show users who were assigned the ITIL role from 2025 onwards.

For users whose ITIL role was removed, you need to check the Audit table, because when a role is removed, the record is deleted from sys_user_has_role and only the audit/history keeps the track. Create a report on the Audit table with conditions such as Table name = sys_user_has_role, Old value = ITIL, and Created on after 01 Jan 2025. This will show who had the ITIL role and when it was removed.

One important thing to verify is whether auditing is enabled on the sys_user_has_role table. If auditing is not enabled, then past removed roles will not be available. In that case, you may need to check sys_history_line or enable auditing going forward.

A practical approach is to create two reports (ITIL assigned and ITIL removed) and place them on a dashboard so you can track both easily.

Hope this helps. Let me know if you want a background script to fetch both assigned and removed users in a single list.

[Purushotham Ga2]

Hi Vaishnavi,

please share background script for this.

Thank you