How to get the first ever assignment group for a report?

Moedeb · ‎08-08-2019

So I have a need to report on all incident tickets logged originally to the IT Service Desk, simple as if an incident was logged and the first assignment group was the IT Service Desk + 1 to the total count.

Now not all incidents are logged by the IT Service Desk, to the IT Service Desk assignment queue first up - they might have been logged through the portal and been assigned the the IT Service Desk, or logged directly by the IT Service Desk, both cases should have the IT Service Desk as the first assignment group.

I have a filtered report that uses the incident_metric table to look at if an incident was ever assigned to the IT Service Desk, but it doesn't tell me if it was originally assigned to them or not?

The filter is: (this looks at incidents not updated in the last 2 weeks and were ever assigned to the IT Service Desk)

Want just all incidents where first assignment group was IT Service Desk, no matter what the current state is, or where it is currently assigned.

Thanks in advance for the help.

amaradiswamy · ‎08-08-2019

Hi,

For this, you may follow any one of the below

1. Create a script calculation type metric on incident table and assignment group field and write script to create a metric instance.

if (current.sys_mod_count == 0) {
 
  if (current.assignment_group == 'SYS_ID_OF_Service_DESK')
      value = true;
     
  createMetric();
}

function createMetric(value) {
  var mi = new MetricInstance(definition, current);
  if (mi.metricExists()) 
    return;

  var gr = mi.getNewRecord();
  gr.field_value = 'IT Support';
  gr.field = null;
  gr.calculation_complete = true;
  gr.insert();
}

2. Create a field on incident table with name as "First Assigned Group" and create a before insert BR to update this field

View solution in original post

Moedeb · ‎02-02-2021

So what I ended up doing to get this to work for me was the following:

Create a new reference field on the incident table (I did it via Form Design), I added it and moved it off the form, so it gets its value, but isn't seen on the form.
Then I created a business rule that simply says - if the first assignment field is empty then make it the same value as the current assignment field, if it isn't empty then don't update it.
Then I set my reports to look for the first assignment group field of the value I want.

Hope this helps

amaradiswamy · ‎02-02-2021

Try below, I tested in my dev instance looks like it is working

if (current.sys_mod_count == 0) {

if (current.assignment_group == 'sys_id_of_group') {
value = false;
} else {
value = true;
}
createMetric(value);
}

function createMetric(value) {
if(!value)
{
var mi = new MetricInstance(definition, current);
if (mi.metricExists())
return;

var gr = mi.getNewRecord();
gr.field_value = 'IT Support';
gr.field = null;
gr.calculation_complete = true;
gr.insert();
}
}

Luke Van Epen · ‎09-04-2022

For anyone looking at this from the future, the HistoryWalker API lets us do this more generically now, I wrote an article on how to do this for any field without the need for custom tables:

https://community.servicenow.com/community?id=community_article&sys_id=1d532cf3dbe5d118bb4a474d13961...