Need to filter out the records updated by particular user

[suuriya]

Hi,

I need a favor,  how we can filter out the records updated by X user and the update done by this user is only on state field and on a Y date.

Like if I use the filter option updated by is X and updated on is Y date then all records updated by that user on particular date is filtered I don't want that I need only records where the state field is updated by X user on Y date How we can filter out these records.

Thanks in advance

[Tony Chatfield1]

Hi, SNC provides the ability to filter based on the current content of a record, but there is no direct\easy way to filter data based on historical data population - if you are not recording the updates\changes somewhere.
I would first check metric_instance table in case you already have a metric tracking these changes.
If not and this is a regular requirement, then I would recommend you look creating metrics
Metrics (servicenow.com)

or add a reference field to your data table to track when this change was made.

Otherwise as a 1 off occurrence you could run a query against sys_audit table (if the table is audited) to find the entries that were updated for the tablename, fieldname, sys_created_by, oldvalue or newvalue;
but running regular queries against core system tables like sys_audit should not be encouraged as it may cause degraded performance of your instance.

[suuriya]

Hi @Tony Chatfield1 ,

Thanks for your reply....is there any way we can get the records by using background script or something like that...

[Tony Chatfield1 · Accepted solution]

Hi, if there are not thousands of records and you have auditing enabled for the task table involved;

I would run a query on sys_audit table for your task type = table name, the updated field (IE state) = field name, created on and created by fields, this will allow you to extract a list of the changes made by the identified user and old values verses new values, the impacted task sys_id's are then identifiable via document key which you can extract via csv/xls then use to identify impacted records in a query on your task table.
Note: Any query run against core system tables needs to be considered for impact, and I would not use core system tables for regular reporting, but as a 1 off query you should be ok to use this as a solution.