Splunk Search

How to find failure rate for each separate event type?

MScottFoley
Path Finder

I am trying to find the failure rate for individual events.  Each event has a result which is classified as a success or failure.  For this simple run-anywhere example I would like the output to be: 

Event
              failed_percent
open               .50
close               .66666
lift                    .25

|makeresults|eval Event="open", State="success"
|append[|makeresults|eval Event="open", State="locked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="lift", State="too heavy"]
|append[|makeresults|eval Event="lift", State="success"]
|append[|makeresults|eval Event="lift", State="success"]

| eval Success=mvfilter(match(State,"success"))
| eval Failed=mvfilter(match(State,"locked") OR match(State,"blocked") OR match(State,"too heavy"))

| streamstats count(Success) as success_count,count(Failed) as failed_count
| eval failed_percent=(failed_count)/(success_count+failed_count)
| table Event,success_count,failed_count, failed_percent

 
This lists each of the 7 events separately and the counts always add to the total, not by event.
 
I have tried many different ways to achieve this with no success.  I started with the simple search below and ended up with the search above.  I am not sure how to do an eval(count) on the items in Result.  This is obviously not correct SPL, but I tried | eval failure=sum (|where Result="failed").  Plus it would do nothing to group by Event type.  

| eval Result=case (like(State,"success"),"success",
like(State,"locked"),"failed",
like(State,"blocked"),"failed",
like(State,"too slow"),"failed",
like(State,"too heavy"),"failed",
1=1,"success")
| stats count by Result


I'm not even sure if this is possible.  I could do it with a separate search for each event type, but I want a single table in the end.  I also thought of doing a lot of joins with different searches, but that seems crazy.

Thanks you your help!

Using 
Splunk 8.1.6

Labels (2)
0 Karma
1 Solution

VatsalJagani
SplunkTrust
SplunkTrust

@MScottFoley - Try this query:

|makeresults|eval Event="open", State="success"
|append[|makeresults|eval Event="open", State="locked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="lift", State="too heavy"]
|append[|makeresults|eval Event="lift", State="success"]
|append[|makeresults|eval Event="lift", State="success"]

| eval Status=if(State=="success", "success", "failure")
| stats count(eval(Status=="success")) as Success_Count, count(eval(Status=="failure")) as Failure_Count by Event
| eval Failure_Percentage = round(Failure_Count/(Success_Count+Failure_Count)*100, 2)

VatsalJagani_0-1657003512311.png

 

I hope this helps!!!

 

View solution in original post

0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

@MScottFoley - Try this query:

|makeresults|eval Event="open", State="success"
|append[|makeresults|eval Event="open", State="locked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="lift", State="too heavy"]
|append[|makeresults|eval Event="lift", State="success"]
|append[|makeresults|eval Event="lift", State="success"]

| eval Status=if(State=="success", "success", "failure")
| stats count(eval(Status=="success")) as Success_Count, count(eval(Status=="failure")) as Failure_Count by Event
| eval Failure_Percentage = round(Failure_Count/(Success_Count+Failure_Count)*100, 2)

VatsalJagani_0-1657003512311.png

 

I hope this helps!!!

 

0 Karma

MScottFoley
Path Finder

This worked.  In my tries I was not putting this in the correct place.  

as Failure_Count by Event

 

Tags (1)
0 Karma

VatsalJagani
SplunkTrust
SplunkTrust

@MScottFoley - Great to hear that!!!

If that resolves your answer kindly click on the "Accept as Solution" button underneath the helpful answer so users in the future also will be able to get help from it.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

I am not sure why you are using streamstats - try this

|makeresults|eval Event="open", State="success"
|append[|makeresults|eval Event="open", State="locked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="close", State="blocked"]
|append[|makeresults|eval Event="lift", State="too heavy"]
|append[|makeresults|eval Event="lift", State="success"]
|append[|makeresults|eval Event="lift", State="success"]

| eval Success=mvfilter(match(State,"success"))
| eval Failed=mvfilter(match(State,"locked") OR match(State,"blocked") OR match(State,"too heavy"))

| stats count(Success) as success_count,count(Failed) as failed_count by Event
| eval failed_percent=(failed_count)/(success_count+failed_count)
| table Event,success_count,failed_count, failed_percent
0 Karma
Get Updates on the Splunk Community!

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...

Splunkbase | Splunk Dashboard Examples App for SimpleXML End of Life

The Splunk Dashboard Examples App for SimpleXML will reach end of support on Dec 19, 2024, after which no new ...

Understanding Generative AI Techniques and Their Application in Cybersecurity

Watch On-Demand Artificial intelligence is the talk of the town nowadays, with industries of all kinds ...