I have a group of log entries with an id field, and a status field. For a given id, over a given amount of time, status can equal failed a number of times, and that's ok, so long as there has been at least one success. What I want is a percentage of distinct id's which have never had a single success over the given period of time.
I had come up with this search:
index="index" source="source" requestType="TYPE" | where status="SUCCEEDED" | stats dc(id) as successCount | append [ search index="index" source="source" requestType="TYPE" | stats dc(id) as totalCount ] | stats values(successCount) as sc values(totalCount) as tc | eval successRate = (sc/tc*100) | fields successRate
However, after digging into results today based on the resulting percentages, I feel as though the results of it might not be accurate.
I can get a stats table with results that I can sort to show me the ids with 0 successes, with this:
index="index" source="source" requestType="TYPE" | eval successId = if((status="SUCCEEDED"),1,0) | eval failId = if((status!="SUCCEEDED"),1,0) | stats sum(successId) as successes, sum(failId) as fails, values(failureMessage) by id
So what I'm looking to do is a search that will give me a % of id's that did not ever have a status of "SUCCEEDED" over the given time period. I can't seem to get the results of the above 'stats' into something correctly that can count id's that have successes == 0 and divide it by distinct id's.
Would something like this run anywhere search work:
| makeresults
| append [| makeresults | eval id=1, status="SUCCEEDED"]
| append [| makeresults | eval id=1, status="FAILED"]
| append [| makeresults | eval id=2, status="FAILED"]
| stats values(status) AS status BY id
| eval only_failed=if(mvcount(status)=1 AND status!="SUCCEEDED", "YES", "NO")
| stats count BY only_failed
And if you want to turn that into a percentage of failed over the total:
| eventstats sum(count) AS total_count
| eval percentage_of_total=round(count/total_count, 2)*100
| search only_failed="YES"
Give this a try
index="index" source="source" requestType="TYPE"
| stats values(status) as status by id
| eval success=if(isnotnull(mvfind(status,"SUCCEEDED")),1,0)
| stats sum(success) as success count as total
| eval "No Success %"=round((success*100/total),2)
Would something like this run anywhere search work:
| makeresults
| append [| makeresults | eval id=1, status="SUCCEEDED"]
| append [| makeresults | eval id=1, status="FAILED"]
| append [| makeresults | eval id=2, status="FAILED"]
| stats values(status) AS status BY id
| eval only_failed=if(mvcount(status)=1 AND status!="SUCCEEDED", "YES", "NO")
| stats count BY only_failed
And if you want to turn that into a percentage of failed over the total:
| eventstats sum(count) AS total_count
| eval percentage_of_total=round(count/total_count, 2)*100
| search only_failed="YES"
Thank you! Works great!