How do I count unique days where an incident occurred?

[jasonhagen]

I need to collect a count of unique days using the opened_at time-stamp from the incident table.   The concept is to count the number of days an incident of a specific priority occurred in order to determine the percentage of "perfect" days there are in a week or month; the count of incidents occurring is not needed or relevant to the metric.   I assume this would need to be done using a script but I'm not strong in scripting.   Here is an example of what I'm asking for:

Sun         Mon         Tues         Wed         Thurs         Fri         Sat

1                   0                           0                   3                   0                   0               0

Using the above, I see that 2 of 7 days saw an incident created of the target priority (lets say Critical for demonstration purposes).   The desired output is 2.   This would then be pulled into a Formula Indicator and subtracted then divided by 7 for a week to get:   (7-2)/7 = 71.43%.  

If possible, I'd also like to be able to collect this data daily to determine the count of available days in the current week.   For example, if it is Tues then the output would be 3 after the Job runs.   Using the above demonstration matrix, that would be calculated as (3-1)/3 = 66.67%

Has anyone done anything like this in Service Now PA?

[Michael Fry1]

Maybe you can use Trend Data, with report to get your counts. Check out video at 31:00 where they talk about Trends. SolveNow Episode 1 | Demystification of Reporting in ServiceNow and Performance Analytics - YouTube

[Arnoud Kooi]

A few pointers:

Created a buckett group weekdays with a bucket for each day

Name	Start	End
1	0	1
2	1	2
3	2	3
4	3	4
5	4	5
6	5	6
7	6	7

Create a Breakdown source Weekdays using the new bucket group

Create a Breakdown using Breakdown source Weekdays

Create a PA Script with Fact table Incident and Field Opened

Content:

var getWeekday = function(opened){

var dt = new GlideDateTime(opened);

return dt.getDayOfWeek();};

getWeekday(current.sys_created_on);

Create a breakdown relation to the Incident table, use the created script

Add the Weekdays breakdown to a daily indicator, for example (a copy of) Number of new Incidents

Set the Aggregate to count distinct and the field to Priority

Create a Formula indicator with breakdown Weekdays and Priority

Set the formula to:

( (7- [[Number of new incidents / By week SUM +]]) / 7 )*100

Add the automated indicator to a job and do a historic collection.

View the scorecard of the formula indicator

Breakdown on priority and check the scores:

Good luck

[pieter_goris]

Hi Jason,

you could also try setting up an automated indicator that uses a script. The script is like an 'If … Then … Else' and could look like:

var result;

if (current.priority == 1)

  result = 0;

else

  result = 100;

result;

Next define an automated indicator like "Perfect Period without Critical Incident"

-based on the indicator source "Incidents.New" that is fit for daily collection (i.e. has a collection frequency of daily)

-with unit % (so you won't have the "... SUM" time series as they will not work for this indicator)

-using the script you just created

Now the resulting daily score will be either 100% or 0%, which does not result in a very interesting chart. So consider to set the 'Default time series' on the 'Other' tab to e.g. "7d running AVG":

It is only a default, end users can still select the "Daily" time series.

The result looks like:

When the score is 100%, it was a perfect 7-day running period, meaning: on that day there has not been 1 single incident with a "1 - Critical" priority in the last 7 days.

When the score is 0%, then in the last 7 days there have been one or more incidents with a "1 - Critical" priority.

So for different time series, it looks like this, with the first one showing the scores as collected on a daily basis:

Hope this helps.

Cheers, Pieter