Need assistant with report on Incident reassignment counts

[surajchacherkar]

Hello Community,

I want to create a report on the Incident table that shows how many times each individual incident was reassigned between certain types of assignment groups.

My requirement:

1. We have multiple assignment groups that start with XYZ (our internal support groups).

2. We also have multiple assignment groups that start with ABC (these are our client groups).

For each incident, I want to calculate:

XYZ → XYZ: How many times the incident was reassigned from one XYZ group to another XYZ group.

XYZ → ABC: How many times the incident was reassigned from any XYZ group to any ABC group.

So basically, for every incident, I want to count:

Internal reassignments (XYZ → XYZ)

Client handovers (XYZ → ABC)

Where I am looking at:

I know this data is available in the Audit History (sys_audit) table for the assignment_group field.

Each audit record contains Old Value (previous assignment group) and New Value (new assignment group).

What I am expecting:

A report (or query) that looks something like this:

Incident       XYZ→ XYZ       XYZ → ABC

INC00123            4                           2

INC00124            0                           1

INC00125            7                           0

In the above example:

INC00123 was reassigned 4 times between XYZ groups and 2 times from XYZ to ABC.

INC00124 was never reassigned XYZ →XYZ but once XYZ →ABC.

INC00125 was reassigned 7 times XYZ →XYZ but never to ABC.

Question:

What is the best way to achieve this in ServiceNow?

Should I create a Database View joining incident and sys_audit, and then build a report on it? ((I already tried to create DB view but its not working, and not sure the approach)

Or is there any better way (like scripting, scheduled job, BR)?

Any guidance or sample approach would be very helpful.

Thanks in advance! 

[Community Alums]

@surajchacherkar , 

You can try below approaches:

Option 1 – Scheduled Job / Summary Table
Run a job on sys_audit (assignment group changes), resolve old/new group names, count the XYZ→XYZ / XYZ→ABC moves, and dump that into a small summary table per incident. Then just report on that table. Lighter + faster since you’re not hitting sys_audit live. (Also data from sys_audit is cleaned after each month maybe so better approach if you need long term reporting)

Option 2 – Performance Analytics
If you’ve got PA, set up an indicator source on sys_audit, add breakdowns by group pattern, and throw that into PA widgets for trends/hand-offs.

Thanks & Regards,
Muhammad Iftikhar
If my response helped, please mark it helpful & accept the solution so others can benefit as well.

[Ankur Bawiskar]

@surajchacherkar 

If you want to go with Performance Analytics then check this

Measuring Incident Reassignments to the Service Desk (or any other group) in Performance Analytics 

If you want to go with reporting and want to use old value, new value then check these links and create report  and then add it to dashboard

Track Assignment Group Changes with Metric Definition 

How to Create a Historical Report Based on Assignment Group Changes on the Incident Table 

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Ankur Bawiskar]

@surajchacherkar 

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Mark Manders]

Reporting on anything related to the sys_audit table is very bad practice. Creating a DB view containing it, is even worse.

Go with metrics on assignment group changes and do your calculation based on that. Depending on the reason behind the requirement, you could add custom reassignment counters to the table to have it automated (total is OOB, and you can add your custom requirements in custom fields).