topic Re: How to calculate a percentage of distinct id's from a group of events which have never had a field matching a certain value? in Splunk Search

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 — Wed, 07 Feb 2018 19:46:42 GMT

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.

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

micahkemp — Wed, 07 Feb 2018 20:02:37 GMT

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"

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

somesoni2 — Wed, 07 Feb 2018 20:34:27 GMT

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)

Re: 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 — Wed, 07 Feb 2018 20:35:13 GMT

Thank you! Works great!