Custom Charts and Metrics

[kaan]

Hi everyone,

I am trying to automate some metrics calculations on the system.

Based on an incident's priority this metric will indicate the following:

-         Critical IRs

o     Percentage of Critical IRs that were moved to Pending Resolution within 30 minutes of creation

o     Percentage of Critical IRs that were moved to Pending Resolution within 1 hour of creation

-         High IRs

o     Percentage of High IRs that were moved to Pending Resolution within 2 hours of creation

o     Percentage of High IRs that were moved to Pending Resolution within 4 hours of creation

I am thinking of 3 different ways to do this: (I have bolded questions)

1. Create a Database View with sys_audit and incident. Get the time for when it was moved Pending Resolution from sys_audit. Get the time for when incident was created, from incident table. Create a report from this Database View.

On this report, How can I calculate the percentage and output it without using Custom Charts?

2. Create a new Metric Definition for priority. Create the retroactive metrics (for already existing incidents) with data from sys_audit table with a background script. Create a database view with these metric instances and incident table. Create a report from this Database View.

Should I create a Custom Chart from this Database View or Can I do this with simple report types (List, Pivot table, etc)?

3. Create a Custom Chart and get all data from sys_audit, incident, and Metric Instances with script.

Questions in general:

Which way would be more efficient or simple to create this report?

Is there any other ways to create this report?

If I create a new field on Incident form that calculates if the incident meets the conditions below ("If it moved Pending Resolution within 30 mins of creation", etc) and from there Can I just create a simple report?

I would appreciate any help. I am kind of stuck and my bosses expect me to do this asap.

[tltoulson · Accepted solution]

Hi Kaan,

I saw your comment on my youtube video, thanks reaching out!   I am always happy to do what I can to help out.   With any reporting, I highly advise to use native out of box reporting whenever possible.   Only resort to custom reports if you have to.   Here are a few options I recommend, depending on your goal.

Simplest but Least Flexible

- Add a few checkboxes for each of your report criteria: Pending Resolution within 30 min | 1hr | 2hr | 4hr

- Add a business rule on update, when state changes to Pending Resolution.   Run a calculation to check how long it has been since creation and flick the appropriate check box.

- Create a simple report (Pie for example) and group by whichever checkbox field you want to report on.

Note, that this approach can easily answer the exact questions you have above but it would be hard to answer any other questions (ie. what is the longest and shortest time from creation to pending resolution, what is the distribution, etc)

Possibly the best all around solution - My preferred Anyway

- Add a single duration field to capture the time from Creation to Pending Resolution

- Add a business rule on update, when state changes to Pending Resolution.   Calculate the duration and insert it into the field you created.

- Now you should be able to group by your new field using most chart types in Reports or you can filter based on overall duration.   Very flexible in what questions you can ask

The weird part here is that you may need to work with Report Ranges in order to get the report you want.   The problem is that you can really only set up a single report range for a given field meaning you would have 30 min | 1hr | 2hr | 4hr for report ranges regardless of the Criticality.     But because you have captured the raw data, you can easily use many other approaches to get exactly what you want.

Best for Measuring Success - May Cause Panic

SLA Definitions could also give you what you want here.   I personally steer away from these because of the cognitive overhead of Database Views and using separate tables, they just make me feel stupid anytime I try working with them.   I find the process much more frustrating compared to creating a field on a table and adding a business rule.   The advantage you get here though is that SLAs are designed to measure a duration against a target objective.   The "breached" flags make reporting really easy.   Simply group by the result and you will see all of the ones that breached and all the ones that succeeded counted up for you.   Basically, the SLA includes both the duration calculation of the second option and the flagging of the first option.

The main problem with this one is some folks may panic at the mention of SLA's.   Measure the time and create a report, no problem.   Add a little red "breached" flag and everyone loses their mind.   This option is a little more political and thats the only reason I usually end up not going this route.

I hope this has helped, please let me know if you need any additional help on this.

Kind regards,

Travis Toulson

[kaan]

Travis,

Thank you very much for this! We haven't determined the final approach yet but it has helped me present all the options to my team. Thanks again, you rock!

[gyedwab]

Building on Travis' solutions, you can use the Metric Definition to track the "Create to Pending Resolution"; use the "Create to Resolve Duration" metric that comes out of the box as a guide.

If you want to then do reporting based on ranges of duration, Explore Analytics to do binning -- without requiring an administrator to define report pages a-priori. So for example, if you use the pop-up to group the durations like so:

You'll get the following report:

Create to Pending Resolution

It can be published to a ServiceNow dashboard, displays live data, and you can drill through to the underlying data.

[manish123]

Hi Travis,

Could you please let me know whether this is possible to get a report/dashboard which shows the percentage of cases without SLA?