Reporting option for Historie states of cases

[NicoleG]

Hello,

we want to implement a reporting for all cases in which we can see a lifecycle of a case. As example how long was the state 'Awaiting Info'. I tried it with the sys_audit table but there a information missing as the case number. And when we try it with the reporting fuction we only see the current state of the ticket not the one it has been.
Is it possible to report the historie information of a case?

Thanks and kind regards

Nicole 

[Chetna_dev]

Yes, it is possible to report on the historical information of a case in ServiceNow, including the duration that a case stayed in each state (e.g., how long a case was in "Awaiting Info"). This type of reporting requires you to track state transitions and use historical data, which is stored in tables like sys_audit, sys_audit_delete, and the task_sla table (for SLA-based data).

1. Use the sys_audit Table:

• The sys_audit table tracks changes to fields, including the state field.
• Join the sys_audit table with the case table (e.g., task) using the documentkey (which corresponds to the case sys_id).
• Filter by fieldname = 'state' to see state transitions.

2. Calculate Time in Each State:

• Use the timestamps in sys_audit (sys_created_on) to calculate how long the case remained in each state by comparing state change events.

3. Create a Database View:

• Create a view joining sys_audit with the case table, pulling in case number, state, and the time of state changes.

4. Create a Report:

• Use the database view to generate a report showing the case number and how long it stayed in each state.

You can also use SLA tracking (task_sla table) for detailed time spent in each state, especially if SLAs are set up for certain stages.

if you found this helpful please accept as solution and mark helpful

best

Chetna

[Antoni Zahariev]

The requirement can be easily achieved by creating relevant SLAs (OLAs) to do the measurements and the same SLA Definitions to be added under the existing OOTB or newly created SLA breakdown definitions.

The SLA breakdowns capture, store, and relay detailed task ownership information and duration for all task SLA records related to a specific task (incident).