question Re: Report on assignment group changes in Developer forum

Report on assignment group changes

palmen — Thu, 24 Sep 2015 11:03:45 GMT

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?

Re: Report on assignment group changes

Mike Allen — Thu, 24 Sep 2015 11:29:14 GMT

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.

Re: Report on assignment group changes

Eric K3 — Thu, 24 Sep 2015 12:36:51 GMT

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

Re: Report on assignment group changes

Mike Allen — Thu, 24 Sep 2015 13:01:05 GMT

You have to add it in the sys_properties:

Re: Report on assignment group changes

Uncle Rob — Thu, 24 Sep 2015 13:25:51 GMT

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.

Re: Report on assignment group changes

JusCuz — Thu, 24 Sep 2015 13:30:17 GMT

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.

Re: Report on assignment group changes

Mike Allen — Thu, 24 Sep 2015 13:36:38 GMT

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.

Re: Report on assignment group changes

Mike Allen — Thu, 24 Sep 2015 13:39:34 GMT

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.

Re: Report on assignment group changes

palmen — Thu, 24 Sep 2015 14:38:34 GMT

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.

Re: Report on assignment group changes

palmen — Fri, 25 Sep 2015 12:59:19 GMT

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.

Re: Report on assignment group changes

shristy2 — Thu, 18 Jan 2018 12:54:14 GMT

Can you share the screenshots how can we achieve initial group on incident?

Re: Report on assignment group changes

palmen — Mon, 29 Jan 2018 14:43:19 GMT

I don't have access to this instance anymore so can't make any screenshots.

What you need to do is to create a custom field and name it Initial Group, it should be a reference to the group table.

When an incident is inserted you should have a business rule running copying the assignment group to the Initial Group field.

Make the Initial Group field read only or hide it on form so noone can change this field (only business rule should be able to do this).