Duration of time by State "Open" and" Work in Progress"

latoyajones
Giga Contributor

I have been tasked with creating metrics for measuring the length of time a ticket is in the "open" state  and "work in progress" state.  Is there an out of the box way to calculate this metric? BTW I am using Washington DC. Thanks in advance for your help. 

5 REPLIES 5

Murthy Ch
Giga Sage

@latoyajones 

If metrics is enabled you can make use of the metric_instance table and create a bar graph something like below:
Assuming, Incident table here:

metrics.png

Group by ID and Aggreation Sum for Duration field give you the result which you want.

Hope this helps

Thanks,
Murthy

Ankur Bawiskar
Tera Patron
Tera Patron

@latoyajones 

yes out of the box there are metrics for incident for this

you can refer those and create something similar for your case and then have reporting on it

AnkurBawiskar_0-1744773907452.png

 

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards,
Ankur
Certified Technical Architect  ||  9x ServiceNow MVP  ||  ServiceNow Community Leader

inavarretem
Tera Expert

@Murthy Ch  @Ankur Bawiskar 

 

Any idea how to create the report based on that metric? I already have the metric that calculates the time spent on each status of the case.

inavarretem_0-1744835365851.png

But I need to build a report that can give me the information.

  • Assignment Group. (this information in on the case)
  • Assigned To (this information in on the case)
  • Time spent on each state (this information is on the metric)

 

 



@inavarretem @latoyajones 

Gotcha! In this case you need to create a database view something like below:

MurthyCh_0-1744843689226.png

where your case_sys_id = mertics_id and include your column information and then create a report on your database view:

MurthyCh_1-1744843779134.png

I just created and it worked perfectly in PDI.

 

 

Thanks,
Murthy