Need to Get a report to calculate a incident spending time in each assignment group

[Nilanjan1]

Dear Experts, 

Is there any use case that could help us to calculate time that is spent across all the assignment group to which an incident is assigned. We tried using Database Views but that is only calculating the entire time slot. Please help ! it is urgent.

[Uncle Rob · Accepted solution]

Look for "Metrics" on your navigator bar.  Unfortunately the term is used over and over and over, so look specifically for THIS one.  

From there, click Definitions

OOB you'll find a definition called "Assignment Group" on the Incident table.  These metric definitions create a metric_instance record any time the specified field value changes.  It logs the start, end, duration, and user on each change.  

This is what the Metric Instance table looks like.  I've filtered for the Definition.  You'll see each instance references a definition, an ID (the sys_id of the task... its not a proper reference unfortunately).  It also tracks the value, start, end, duration, and whether the calculation is complete (which means another change "ended" that instance).

But how do we build a report on that?   That's where some OOB database views come in handy.  You can build a report on a table called "Incident Metrics" which is a dbview linking Incidents, Metric Definitions, and Metric Instances.   Here's a report I built looking ONLY for metric_instances with a definition of Assignment Group and an incident number of a specific one I use for testing.

[Uncle Rob · Accepted solution]

Look for "Metrics" on your navigator bar.  Unfortunately the term is used over and over and over, so look specifically for THIS one.  

From there, click Definitions

OOB you'll find a definition called "Assignment Group" on the Incident table.  These metric definitions create a metric_instance record any time the specified field value changes.  It logs the start, end, duration, and user on each change.  

This is what the Metric Instance table looks like.  I've filtered for the Definition.  You'll see each instance references a definition, an ID (the sys_id of the task... its not a proper reference unfortunately).  It also tracks the value, start, end, duration, and whether the calculation is complete (which means another change "ended" that instance).

But how do we build a report on that?   That's where some OOB database views come in handy.  You can build a report on a table called "Incident Metrics" which is a dbview linking Incidents, Metric Definitions, and Metric Instances.   Here's a report I built looking ONLY for metric_instances with a definition of Assignment Group and an incident number of a specific one I use for testing.

[Nilanjan1]

Thank you so much @Uncle Rob t really appreciate your help and support. 

[Uncle Rob · Accepted solution]

My pleasure.  Metric Definitions is incredibly old but super-duper useful.

[Nilanjan1]

Yes, but how I missed on this, I am still trying to get my head around it... I appreciate your help which actually helped me to look through the documentation also helped me to understand what I should not miss.