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!