Is there a way to report on users who have had the ITIL license removed in a time period?

[Bill_Ymr_61]

I have been asked to report on users that have had the ITIL license added recently, and also on users that have had the ITIL license removed recently.

I am able to report on the added users but having trouble finding a way to report on the removed users.  Any ideas?

[Robbie]

Hi @Bill_Ymr_61,

This can be found in the 'Deleted Records' table. (sys_audit_delete with some applied filters). Navigate to this by typing 'Deleted Records' in the navigation menu under 'System Definition'

Search for or enter 'sys_user_grmember' in the table name to filter the records by the role table as shown below.

Adding this filter will show all records where a role has been removed for a user.

If you want to search by a particular date or user name, you'll have to search within the payload field.

To help others (or for me to help you more directly), please mark this response correct by clicking on Accept as Solution and/or Kudos.

Thanks, Robbie

[johnfeist]

Hi Bill_Ymr_61,

I ran into a related need some years back.  In theory, you could go through all the logs looking for those data, but that will be a massive pain to get right and could take a long time to run.  Our initial reason for tracking had more to do with people not using their license.  Since the ITIL license is not cheap, my management instituted a thirty day use it or lose it policy.  Part of that was an increasingly more demanding reinstatment process.

In order to make all that work, I created a table that tracks when a user gets or loses their license.  That makes it easy to see how many times we've had to reinstate a user, or in your case who got/lost a license in a time frame.

In order to accomplish that, I created a couple of BRs on sys_user_grmember (after insert and after delete) that update the tracking table.  One other thing that I did shortly after was to add a checkbox on the user record, On Leave.  That is for cases where we know that a user won't be using their license (e.g. maternity leave).  With that checked what happens is that another table gets populated with the groups to which the on leave user belongs.  With that in place they have their license removed.  when they come back and the On Leave box is unchecked, the process is reversed.  Coming back from on leave is not counted as a reinstatement.