Sys_user Audit Report

Wade Clairmont · ‎05-31-2016

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!

Chuck Tomasi · ‎05-31-2016

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)

View solution in original post

Chuck Tomasi · ‎01-09-2017

Group membership is tracked through a many-to-many table called sys_user_grmember.

As people are added to groups, new records are placed in this table. As they are removed, records are deleted. This makes reporting a bit of a challenge.

What you can do is create yourself a "mebership log" table and use a business rule to track the changes to the table. As records are added to sys_user_grmember, create an entry in your log table that someone was added to a group. If someone is removed from a group, the same BR can add another entry to track that change as well. Now you can report on your log table of everyone who has been added/deleted.

Kaju · ‎02-18-2019

Hi Chuck Tomasi,

Can we achieve same thing for report on sc_task table ?

Actually I have report on sc_task , want to include "user_name" field from sys_history_line in that report.

Please suggest.

Kaju · ‎02-19-2019

Hi Chuck Tomasi,

Can we achieve same thing for report on sc_task table ?

Actually I have report on sc_task , want to include "user_name" field from sys_history_line in that report.

Please suggest.