How to calculate a percentage of distinct id's from a group of events which have never had a field matching a certain value?

[dg_fuze]

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.

[somesoni2]

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) 

[micahkemp · Accepted solution]

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"

[dg_fuze]

Thank you! Works great!