Splunk Search
Highlighted

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

New Member

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.

0 Karma
Highlighted

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?

Champion

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"

View solution in original post

0 Karma
Highlighted

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?

New Member

Thank you! Works great!

0 Karma
Highlighted

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?

SplunkTrust
SplunkTrust

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) 
0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.