Report - Get Total time spent for Incident/CR that group by Ticket Number, Assignment Group, Status

[kimpeng]

Hello expert,

We want to review the total time spent on incidents/change request  that supported by specific assignment group (RG1) for certain statuses, by using ServiceNow Report. For example.
1. Incident - Review the total time for incident that stuck in status "On Hold", "In Process" and "Resolved".
2. Change Request - Review the total time for CR that stuck in status "Assess' and "Implement".

Our requirement:

Basically we wish to GROUP the incident/Change Request with same Ticket number, Assignment Group, State.

We don't use PA, and we wish to use ServiceNow Report to achieve this objective. 

Let me explain with a scenario to better explain myself. For example:

The incident INCxxxxxx has gone thru this status: New --> In Process (1) --> On Hold (1) --> In Process (2) --> Resolved (1) --> On Hold (2)--> In Process (3) --> Resolved (2) --> Closed.

For scenario above, what we want to see in the Report is to show only 3 records/rows for our support team (RG1) for above incident INCxxxxxx that stuck in respective status "In Process",  "On Hold" and "Resolved".

TICKET_NUM     ASSIGNMENT_GROUP     STATE                  TOTAL_TIME

INCxxxxxxxx       RG1                                      IN PROCESS      sum the time [In Process (1)]  + [In Process (2)] + [In Process (3)]    

INCxxxxxxxx       RG1                                      ON HOLD          sum the time [On Hold (1)]  + [On Hold (2)]

INCxxxxxxxx       RG1                                      RESOLVE            sum the time [Resolve (1)]  + [Resolve (2)]                                 

We have checked the records in  incident_metric, However, it is not possible to group the details (ticket number/assignment group/status) into one record when the state changes back to the one that was already visited by the ticket. 

incident_metric shows the following, this is NOT we want. 

TICKET_NUM     ASSIGNMENT_GROUP     STATE                  TOTAL_TIME

INCxxxxxxxx       RG1                                      IN PROCESS (1)       xx minutes

INCxxxxxxxx       RG1                                      ON HOLD (1)        xx hours

INCxxxxxxxx       RG1                                      IN PROCESS (2)       xx minutes

INCxxxxxxxx       RG1                                      ON HOLD (2)         xx minute

INCxxxxxxxx       RG1                                      IN PROCESS (3)       xx minutes

INCxxxxxxxx       RG1                                      RESOLVE (1)            xx minutes

INCxxxxxxxx       RG1                                      RESOLVE (2)           xx minutes

Can you please guide us on how to achieve this requirement? 

Can this be achieved using the metrics? Or we need to write script to do this?

Thank you.

[kimpeng]

Hello expert - may I know is it technical feasible to implement the user requirement above?

It's fine if there is technical limitation in ServiceNow to do it. Appreciate someone can help to confirm it. 

Thanks a bunch.