Report on assignment group changes

[palmen]

I've got a question regarding reports and I can't figure out how to solve this

What they want is the following

A report that show all incidents last 6 months that has been assigned to "Group A" and "Group B".

They want the result to be shown in a list and get an answer to the following questions

Which group opened the incident?

Which group closed the incident?

How many assignment groups has been involved in total?

Amount of incidents between these 2 groups?

We can't use a normal report for this since it only take the current assignment group into consideration.

If I understand it correct we'll have to be looking at metrics here.

There is a metric set up to track the duration per assignment group which I assume we can use. But how do I get the report from it to show all incidents that has passed these two assignment groups?

[JusCuz]

I agree 100% with this statement, at my first company that implemented ServiceNow, we were warned against reporting on the history of anything due to the performance impact that would be incurred.

If you absolutely had to go this route, I would maybe just have a reporting instance up, where you cloned your PROD instance over on a regular basis and did reports like this in the reporting instance.

[Mike Allen]

It's easier and probably better overall to go the ODBC route, anyway.   Just set up a reporting server and use linked servers to pull the data down and report on it offline.

[Mike Allen]

The group changes can be seen in metrics.   Some of the other things they want to see are not as easy, like what group opened the ticket, what group closed the ticket, etc.   History can be quite big, but so can metrics.   The lesson really is make sure your query is optimized, even for reporting.

[palmen]

Thanks for all the input

We have a field for initial workgroup on all our incidents so it's not going to be a problem for us to get this value.

I think we'll have to bring in some help to solve this report since I've never worked with metrics before and how to do the reporting. It doesn't sound like it's an easy report to set up so better have someone look at it who has the skills already, and at the same time teach me how to do it in the future.

[palmen · Accepted solution]

I got some help and we've now managed to get the data needed.

We do this with 2 different reports

Report 1

Table: Metric [metric_instance]

Conditions: Definition is Assignment Group | Created on Last 6 months | Value is one of Group A, Group B

With this report I get all incidents that has been on Group A or Group B.

Export to Excel to match incidents that has been on booth Group A and Group B, you'll get a column with all incidents number.

Report 2

Table: Incident [incident]

Condition: Number is one of [insert all incident numbers from report 1]

This report should show all incidents that has been on Group A and Group B.

We have a custom field named "Initial Group" which store the initial workgroup

Assignement group field is used to see what group closed the incident.

We have a count (reassignment count) on each incident which add +1 every time the assignment group is changed. This doesn't show all the assignment groups involved but gives us an indication on if the incident has passed a lot of different groups for some reason.