How to find failure rate for each separate event type?

[MScottFoley]

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

[VatsalJagani · Accepted solution]

@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)

I hope this helps!!!

[MScottFoley]

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

as Failure_Count by Event

[VatsalJagani]

@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.

[ITWhisperer]

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