Report - Percent of SLA used by assignment group

[Sam Ogden]

Hi All,

I was wondering if there were suggestions on how we could capture the following:

The percentage of the resolve SLA that is used per assignment group.

We need some form of report to identify the percentage of SLA that each assignment group of an incident is using E.g.   If a log is assigned to 5 groups through the cycle of the log and they all spend equal time working on the log each team will have used 20% of the SLA.

However if we have 100 min SLA and the log is assigned to 3 different teams through its life and the first team spends 10 mins, the 2nd team 70 mins and the 3rd team 20 mins then we can see that team 2 used 70% of the SLA.

We want this to try and identify if we get any bottle necks on logs where a large portion of an SLA is being wasted with a log sat on a team before it gets passed on to another team to be resolved.

Any suggestions on a report to cover this, or any other ways we could utilise to try and track this kind of information will be greatly appreciated.

If any further info is required please just ask.

Thanks

Sam

P.S we are using Helsinki Patch 11 and do not have the performance analytics plugin.   If there are solutions though using performance analytics these will still be helpful as it may give us a use case to look into this plugin

[Josh Cooper]

I've done something like this a few times using Performance Analytics, but it's not strictly speaking, necessary.   What I've done is called "Total Time of Possession" and takes advantage of our Metrics functionality.   Performance Analytics lets you chart those things over time, but you can still report directly on the metric_instance table to just see the results, or on the incident_metric database view (acts as a table) to add more incident fields as well.

Out of the box, we have a metric called Assignment group that tracks the duration of the assignment group field.   So every time the field changes, it creates a new metric to show how long the previous value was assigned.   The drawback was just that if the same group is assigned to the same ticket 3 different times, it gets 3 different metrics.

So I created a new metric called Total Time of Possession, and all it does is, when the state goes to "Closed" then it goes through all the Assignment Group metrics and totals them up for each group - so that you end up with one value for the total, instead of 3 different entries that had to be added together.

You're adding the SLA percentage, which is a bit of a twist, but should be doable very similiarly:

Instead of setting the Total Time of Possession to calculate only when it's closed, set it to calculate when it's closed OR whenever the flag on the incident form that tells you SLA expired is set to true.

Then it will sum up the total time at that time.   In the script, in addition to summing the duration for each assignment group, also add them all together, so you get essentially a total duration type stopwatch that stops when the sla flag is tripped.   Now, after calculating your duration for each assignment group, instead of setting the duration, then just use <Assignment Group Total Time of Possession / Total Possession of All Assignment Groups * 100>.   The value should be what percent of the SLA was consumed by the Assignment Group (within a second or two).

Now if you're not doing anything with the sla_due field on the Incident to see if the SLA is met, then you may have to set the metric up on the SLA table instead, and then you'll just want to make sure you add the business rule that allows metrics to calculate - by default it's only set on tables extended from Task.   I believe it's the one called 'metrics events' - just change the table it runs against to the SLA table and insert and stay, and that should fix that piece of it.

[Sam Ogden]

Hi Josh,

Thanks for the above detailed explanation.

Could you share the setup of the metric you created for Total Time of Possession so I have an idea of how this works?

Thanks

Sam

[Rushikesh Mandh · Accepted solution]

This functionality is available out of the box without PA through 'SLA Breakdowns' plugin introduced in the Kingston release :

https://docs.servicenow.com/bundle/london-it-service-management/page/product/service-level-management/concept/sla-breakdown-definition.html 

[Sam Ogden]

I had seen the SLA Breakdowns, but not had chance yet to explore these.

Thanks for the update though.

Sam