Metric definition to capture changes to both Assignment group and Assigned to simultaneously

[Alex Macdonel]

I have a requirement where we need to capture how long a ticket is unassigned so we can analyze which groups are understaffed. Sounds simple enough, create a metric definition looking at assigned to and capture its duration.

First problem: on most ticket creations, the assigned to field is empty, so no metric entry is created on ticket creation. -> this was solved by an answer in Re: Creation of Metric with Script Calculation ? - now all tickets with no assigned to

But... this didn't really work for me, the metric only captures changes to the Assigned to field; I know how long a ticket was unassigned for but I have no idea which group had it... I also have a metric definition looking at Assignment group duration, but this metric has the same problem. Could I create a database view to combine both definitions at the same time? Maybe but too cumbersome and I want a simple report, not a complex one.

My solution was to create a new field on the task table with a business rule that populates it with the value of both the assignment group and assigned to, if the assigned to is empty, I have an entry like this [assignment group]:<empty>. With this new field, I can create a a metric definition and the reporting is easy.

My question is, can this be done with a singled script on a metric definition without creating the task field and business rule?

[Michael Fry1]

You can add fields to the Metric table, then use a business rule to create your metrics with assignment group & assigned to (in the new field). Here's a thread on how to create the business rule: Re: Is there a way to report on audits in a way that is efficient (or a way to report on user action...

[Alex Macdonel]

Interesting script but it's somewhat flawed for what I am looking. The calculation takes the last time the ticket was updated as the start value, in my case the requirement is to look for changes to the assigned to / assignment group; if I take a really old ticket, add a work note or a comment, save it and then reassign it, the metric entry will show a value of seconds and not of days. A very good starting point and good if I wanted to see touches to tickets!

[Alex Macdonel · Accepted solution]

I created a new field and, with a business rule, I captured on this field the assignment group + assigned to, if the assigned is empty I use <empty>. Now I can report on which group and who had the ticket at any given time. Not the prettiest but it works for us.