Record History re: change of ticket State and duration on each state

[ctraspo340]

Hi,

How can I generate a report based on Record History label from multiple tickets?

My task is to provide an analysis on how much time does each ticket stays under Pending, Open, Work in progress states.

We are using the HR Case Management table for our tickets.  I don't see any fields where State history is available.

Best,

Ctraspo340

[miftikhar20]

Hi @ctraspo340 ,
Use a Metric Definition on the state field of the HR Case Management table (sn_hr_core_case) so ServiceNow tracks duration per state automatically.

1. Go to Metrics > Definitions
   

    

2. Create a metric with:

   • Table: sn_hr_core_case

   • Field: state

   • Type: Field value duration

     

3. After cases change states, metric_instance records will capture start/end times and durations.

4. Build a report on the metric_instance table filtering by your metric to calculate time spent in each state.

Useful links:
• Metric Definitions documentation:
https://www.servicenow.com/docs/r/yokohama/platform-administration/c_MetricDefinitionSupport.html
• ServiceNow KB article on reporting with metrics:
https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0793124
• Community Post:
Report to calculate incident's each state duration along with onhold reason. 
• Helpful video explanation:
https://www.youtube.com/watch?v=NHL1ckMKiEg

If my response helped, please mark it as the accepted solution so others can benefit as well.