Report on assignment group changes

palmen · ‎09-24-2015

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?

palmen · ‎09-25-2015

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.

View solution in original post

Mike Allen · ‎09-24-2015

A lot of the information you are looking for is going to be in the sys_history_line table. Start there. You're correct about the metric table, too. That would also be a good place that will have information. I don't know that you will be able to package it together nicely in a report in SN, though, without using the ODBC connector to take the data to excel or SSRS or something along those lines.

Eric K3 · ‎09-24-2015

The History table is not an option when I go to create a new report.

Mike Allen · ‎09-24-2015

You have to add it in the sys_properties:

Uncle Rob · ‎09-24-2015

Be careful when doing so. The two times I've seen instances brought down from the inside, it was through reporting on History. Mind you, that was several versions ago, and the user in question had about 50 "OR DOES NOT CONTAIN" conditions, but still... I'd shoot for doing this via metrics.