topic Re: How to graph SLA conformance? in Dashboards & Visualizations

How to graph SLA conformance?

talbotrs — Sat, 12 Nov 2016 16:11:08 GMT

I have a sourcetype that is an extract from a hardware fault call logging system. One of the columns (called ‘Conformance’) has a value that tells you if the call was resolved within the timeframe of a 'service level agreement' (SLA). A value of 1 means the call met the SLA, a value of 0 means the call failed the SLA. However, there is another column called ‘Override’ and if there is any text in that column, then that indicates that the SLA failure was overridden and the call met the SLA. With me so far?!

What I need to do is identify the number of calls that met the SLA:

search NOT Override="*" AND Conformance=1

... then identify the number of calls that failed the SLA, and were then overridden:

search Override="*" AND Conformance=0

... then I need to identify the number of calls that failed the SLA:

search NOT Override="*" AND Conformance=0

Finally, I need to take all the calls that met the SLA first time, add that number to the calls that met the SLA having been ‘overridden’, and then display that as one value in a pie chart, along with the calls that failed the SLA. It will be a pie chart with one very big segment (SLA achieved), and one very small one (SLA failed).

If you can help, I would be very grateful, as I have a deadline to meet in 2 days time!

Re: How to graph SLA conformance?

gokadroid — Sat, 12 Nov 2016 19:58:16 GMT

Hi @talbotrs, please try this below:

your base query to get Conformance and Override events
| eval slaStats=case(((Conformance=1 AND Override!="*") OR (Override="*" AND Conformance=0)), "SLA achieved", (Override!="*" AND Conformance=0), "SLA failed", 1=1, "Other" )
| stats count by slaStats

Now in the Visualization choose pie chart and it should show up like you expected.

Updated the brackets as per comment

Re: How to graph SLA conformance?

kabSplunk — Sat, 12 Nov 2016 20:38:44 GMT

What is 1=1 doing in this?

Re: How to graph SLA conformance?

gokadroid — Sat, 12 Nov 2016 20:39:57 GMT

case default

Re: How to graph SLA conformance?

kabSplunk — Sat, 12 Nov 2016 20:51:26 GMT

Thx for clarifying..

Re: How to graph SLA conformance?

talbotrs — Mon, 14 Nov 2016 12:09:40 GMT

Hi, thank-you so much for your reply ... unfortunately, I could not get this query to work, and I think the reason is that one of the closing brackets is in the wrong place. I tried a number of changes, but none of them worked. However, you did confirm what I thought, which was that the ‘eval’ command is the way to address this particular requirement. I looked at the training material that I got from the ‘Searching and Reporting with Splunk’ training course, and used one of the examples to produce the following query (the ‘NOT Customer’ parts of the query are to exclude 3 customers where the SLA data is not present in the ‘crspcalls’ sourcetype):

(Bizarrely, the asterisk symbol won't display properly here, so I've used the word 'asterisk' instead)

sourcetype=crspcalls NOT Customer="CustomerA" AND NOT Customer="CustomerB" AND NOT Customer="CustomerC"
| stats
count(eval(Conformance="1" AND Override!="asterisk")) as Met,
count(eval(Conformance="0" AND Override="asterisk")) as Overridden,
count(eval(Conformance="0" AND Override!="asterisk")) as Failed

That query ran without producing any errors, but I could see that the results were not accurate at all. So the query results were 3 columns on the ‘Statistics’ tab (Met, Overridden and Failed), but the results were completely inaccurate. If I changed the 2 instances of
AND Override!="asterisk"
to
AND NOT Override="asterisk"
the query produced different results, but again, completely inaccurate results.

I realised that the issue was with the searching the Override field using the asterisk symbol, as if I listed all the possible values that exist in the Override field, as in the following example, the results were accurate.

sourcetype=crspcalls NOT Customer="CustomerA" AND NOT Customer="CustomerB" AND NOT Customer="CustomerC"
| stats
count(eval(Conformance="1" AND NOT Override="NF"
AND NOT Override="HU"
AND NOT Override="NF-CHARGE"
)) as Met,
count(eval(Conformance="0" AND Override="NF"
OR Override="HU"
OR Override="NF-CHARGE"
)) as Overridden,
count(eval(Conformance="0" AND NOT Override="NF"
AND NOT Override="HU"
AND NOT Override="NF-CHARGE"
)) as Failed

I’ve only listed 3 of the possible values that exist in the Override field, but there are in fact 36 possible values. As you can imagine, the query with all 36 possible values listed 3 times is a bit of a monster query!

Can you suggest what the problems is with the asterisk symbol, or can you see where the problem is with the misplaced closing bracket in your original suggestion?

Re: How to graph SLA conformance?

aaraneta_splunk — Mon, 14 Nov 2016 16:29:31 GMT

@talbotrs - When you are posting sample code, you need to wrap your text in a "code sample". That is why the asterisks in your code are not properly rendering. Use the "code sample" button located in the toolbar, to the right of the Blockquote (") button.

Re: How to graph SLA conformance?

gokadroid — Tue, 15 Nov 2016 03:07:51 GMT

Can you please try to see if isnull works for your case, where you change the | eval slaStats=case(((Conformance=1 AND Override!="*") OR (Override="*" AND Conformance=0)), "SLA achieved", (Override!="*" AND Conformance=0), "SLA failed", 1=1, "Other" )

as follows:

| eval slaStats=case(((Conformance=1 AND isnull(Override)) OR (NOT isnull(Override) AND Conformance=0)), "SLA achieved", (isnull(Override) AND Conformance=0), "SLA failed", 1=1, "Other" )

I have updated the brackets in the answer. Apologies for having missed it earlier.