how to generate a report of all active users with roles and recent activity

[santa]

I am looking for a way to generate a report of all active users with roles and recent activity - the ideal way to do this would be a report against the sys_user table with the following criteria:

Active is True

Roles is anything

Last login on This year

Unfortunately, Last login field is not being populated in our instance, and while I'm sure we could find a way to populate it to generate this report in the future, I need to find a way to report on this retroactively. We have a lot of accounts that are active with roles but we know are not being used, so looking for a way to list those to start deactivating them.

This would be a big help to help us with account management and save our organization some $ on licensing, so if anyone can provide a solution I would be very grateful and may have to buy lunch next time you're in LA LA land!

[Vance1]

Hi Robert,

I had a similar concern a while back and asked this community. You can read my thread here which details the report configuration needed to see license utilization and determine Actual License Position (ALP). You can then easily export that report to Excel/Google Sheets and apply conditional formatting to the last login time column to visually indicate new versus old logins. We have taken it a step further and used Tableau to create an interactive dashboard to that represents the last login against an internally set threshold for reclamation. Furthermore, I am in the midst of implementing a workflow to automatically reclaim licenses based on last login time.

Something to note:

• Your original post says that the "last login" field isn't populating
• You need to use the "last login time" field
  • Both fields exist but the "last login time" field is the one that will capture when a user interacts with your environment.

I hope this is what you were looking for, if so please be sure to mark it as the correct answer or helpful.

[Adarsh Krishnam]

USE The Following Condition on the SYS_USER_SESSION TABLE

1)Invalidated = NULL 
2)Name is not empty. 
3)Name is distinct or you can do a group by. 

You can find the list of active users who are logged in currently to the service now instance for all the nodes.

Additional Info : 

below script which will provide the count of users logged in currently.
This uses the stats page to get the information.

var diag = new Diagnostics();
while (diag.nextNode()) {
  var diagNode = diag.getNode();
  var ss = diagNode.stats.sessionsummary;
  if (ss) {
    gs.print('Server: ' + diagNode.name + ' logged in users: ' + diagNode.stats.sessionsummary["@logged_in"]);
  }
}