Is there any way to collect historic data captured in the record with performance analytics?

[Winston]

Good afternoon, 

I'm trying to create a performance analytic report that shows how many assets were checked in by non asset steward users for the past 3 years. I'm able to get the report to show the assets that are currently checked in by non asset steward users, but is there a way to collect the historical data from the record audit table or something like that? Or does it just have to collect that data going forward only? 

[SwarnadeepNandy · Accepted solution]

Hello @Winston,

One way to collect the historical data is to use the Record History feature of ServiceNow. This feature allows you to view the changes made to a record over time, including the fields that were updated, the old and new values, and the user who made the change. You can access this feature by clicking the Show record history icon on the form header of any recor....

To use this feature for your report, you can create a scripted indicator source that queries the record history table (sys_history_line) and filters the records by the update_by field (the user who made the change) and the update_time field (the date and time of the change). You can also filter by the field field (the name of the field that was changed) and the new_value field (the new value of the field) to get only the records that were checked in. For example, you can use a script like this: 

// Define a GlideRecord object for the record history table
var gr = new GlideRecord('sys_history_line');
// Add a query to filter by the update_by field
gr.addQuery('update_by', '!=', 'asset steward'); // Replace 'asset steward' with the actual sys_id of the asset steward user
// Add a query to filter by the update_time field
gr.addQuery('update_time', '>=', gs.beginningOfLast3Years()); // Get only records from the past 3 years
// Add a query to filter by the field and new_value fields
gr.addQuery('field', '=', 'checked_in'); // Replace 'checked_in' with the actual name of the field that indicates check-in status
gr.addQuery('new_value', '=', 'true'); // Replace 'true' with the actual value that indicates checked-in status
// Execute the query
gr.query();
// Return the GlideRecord object as the indicator source
gr;

You can then use this scripted indicator source to create an indicator that counts the number of records returned by the script. You can also use breakdowns to group or filter the indicator by different dimensions, such as user, asset, or date .

Another way to collect the historical data is to use Data Collector. This feature allows you to collect data from any table or data source and store it in a separate table for reporting purposes. You can configure a data collector to run on a schedule or on demand, and specify what fields and filters to use for collecting data. You can also define retention policies to delete old data or archive it to another table.

To use this feature for your report, you can create a data collector that collects data from the asset table (alm_asset) and filters it by the checked_in_by field (the user who checked in the asset) and the sys_updated_on field (the date and time when the asset was updated). You can also select other fields that you want to include in your report, such as asset_tag, model, or cost_center. You can then configure the data collector to run daily, weekly, or monthly, depending on how often you want to update your report. You can also set a retention policy to keep only 3 years of data or archive older data to another ta....

You can then use this data collector table as a source for your report. You can create charts, tables, or lists that show how many assets were checked in by non asset steward users over time. You can also use breakdowns or filters to group or filter the data by different dimensions, such as user, asset, or date.

Hope this helps.

Kind Regards,

Swarnadeep Nandy