Historical report on incident priority

[Gaurav Vaze]

hello all,
I have a requirement to create a report that will show the last 3 months' incidents for which priority has been changed
here is what I have tried
creating a report on incident_metric by creating a definition on the priority field
however, this gives only new incidents after the creation of metric definition

guidance will be appreciated if we can find the historical changes up to 3 months for priority
thank you 

[AshishKM]

Hi @Gaurav Vaze , 

That's correct, metric definition logic will work on all active incidents where the priority is changing. 

For all closed ( active = false ), this metric logic will not work, you have to get the details from audit table ( not recommended to run report )

-Thanks,
AshishKM

[Ratnakar7]

Hi @Gaurav Vaze ,

To create a historical report on incident priority changes over the last 3 months, you'll need to approach this from a different angle since ServiceNow's reporting capabilities may not directly support tracking historical changes in a field like priority.

One approach is to utilize ServiceNow's audit log functionality to track changes to the priority field. Here's a general outline of how you could achieve this:

1. Enable auditing for the priority field: Ensure that auditing is enabled for the priority field on the Incident table. This will track changes made to the priority field over time.

2. Create a script to extract historical data: Write a script to query the audit log table (sys_audit) for changes to the priority field on incident records over the last 3 months.

3. Process and format the data: Retrieve the relevant audit records and process them to extract the necessary information, such as the incident number, old priority, new priority, and timestamp of the change.

4. Generate the report: Use the processed data to generate the report format you need, such as a table or chart showing incidents with priority changes over the last 3 months.

Here's a sample code snippet to give you an idea of how to query the audit log table for priority changes:

// Query the sys_audit table for priority changes on incident records over the last 3 months
var grAudit = new GlideRecord('sys_audit');
grAudit.addQuery('tablename', 'incident'); // Audit records related to incidents
grAudit.addQuery('fieldname', 'priority'); // Audit records for the priority field
grAudit.addQuery('documentkey', 'STARTSWITH', 'INC'); // Only audit records for incidents
grAudit.addQuery('sys_created_on', '>=', gs.monthsAgoStart(3)); // Changes in the last 3 months
grAudit.orderByDesc('sys_created_on'); // Order by creation date in descending order
grAudit.query();

// Process and format the audit records
while (grAudit.next()) {
    // Extract relevant information from the audit record
    var incidentNumber = grAudit.documentkey.getDisplayValue();
    var oldPriority = grAudit.oldvalue.getDisplayValue();
    var newPriority = grAudit.newvalue.getDisplayValue();
    var changeTimestamp = grAudit.sys_created_on.getDisplayValue();
    
    // Process the data or add it to a report
    // Example: gs.info('Incident: ' + incidentNumber + ', Old Priority: ' + oldPriority + ', New Priority: ' + newPriority + ', Change Timestamp: ' + changeTimestamp);
}

Thanks,

Ratnakar

[Gaurav Vaze]

I appreciate the response
the code that you mentioned need to be in Si right?
also how am I supposed to call that in report any idea?
like what should I return from the script? sys_id?
i can call the function in the report where sys id is one of the new SI name. Function name()

will that work?

[Gaurav Vaze · Accepted solution]

got a workaround script

var threeMonthsAgo = gs.daysAgoStart(90); // Calculate the date 90 days ago
    var changedIncidents = [];

    // Query the sys_audit table for changes in the priority field of incidents
    var audit = new GlideRecord('sys_audit');
    audit.addQuery('tablename', 'incident'); // Focus on the incident table
    audit.addQuery('fieldname', 'priority'); // Focus on changes to the priority field
    audit.addQuery('sys_created_on', '>=', threeMonthsAgo); // Limit to changes within the last three months
    audit.query();

    while (audit.next()) {
        // Collect unique Sys IDs of incidents with changed priorities
        var docKey = audit.documentkey.toString();
        if (changedIncidents.indexOf(docKey) === -1) {
            changedIncidents.push(docKey); // Store the Sys ID
        }
    }

   return changedIncidents;

use this in SI and call the SI in report created on incident table  as  
sys_id     -->     is one of the   -->         javascript&colon; new <scriptinludename>().<functionname>()