Sys_user Audit Report

[Wade Clairmont]

Hey all,

I am attempting to create a report to audit all changes to the sys_user (name, location, status, etc.).   However, my issue lies in the fact that I require some detailed information about the changes to each user that are not included in the sys_user table, but the sys_audit / sys_history_line tables.

Here is my criteria

1) Filter - Updated last week

2) Display - Date/time update occurred

3) Display - Sys_User details

4) Display - Associated audit details (name of field name, old value,new value)

I can report on the sys_audit table however, I have no idea of how to reference the sys_user table to get the user details that I need including the resolved references made in the audit table.

I am sure I cannot be the only one out there that has requirements to report on this, so if anyone can lend a hand, it would surely be appreciated.

Thanks in advance!

[Steve McCarty]

I believe you will have to create a Database View that combines the sys_audit and sys_user tables.   I created the database view below on a developer instance and was then able to create reports based on this view that had fields from both the sys_audit and sys_user tables.   You can get more info on database views here: Database Views - ServiceNow Wiki.

- Steve

[Chuck Tomasi · Accepted solution]

Hi Wade,

Here's what I came up with in lieu of creating a report on sys_history_line (it's not available to report on that table because of potential performance impact.)

Step 1: Go to the dictionary entry for sys_user and check the Audit box on the table (collection) record. Mine was off by default. Without this, you won't see much.

I went to the sys_history_line list of records from the navigation filter and added the Set.Table and Set fields to show the impacted table and field.

You can then create a filter on the sys_user table and records updated after the last 30 days. Something like this:

Which produced (for my tiny sample set)

[Wade Clairmont]

Wow, thank you so much!   I will give this a try and let you know how it goes.

[Chad Wilhelm1]

Hello,

What about if you wanted to track group memberships from sys_user.     For example if I were to add a user to group ABC

Label

Group Membership

Old

is Null

New

ABC

Same goes for removal

Label

Group Membership

Old

ABC

New

is Null

Thanks,

Chad