Splunk Search

How to use each value in a column to determine count of "success", "failure", or "error", then calculate the success percentage?

jshellman
Engager

I have a search, something like this:

search stuff
    | rex "extract cat"
    | rex "extract field2"
    | rex "extract field3"
    | eval theValue=coalesce(field2, field3)
    | stats count by cat, theValue
    | table count, cat, theValue

So, the output is something like this:

count  | cat   | theValue
55     | BER   | A
 2     | BER   | 302
 1     | BER   | 201
14     | CCG   | 502
 3     | CCG   | null
88     | CCG   | 100

Now, if theValue is A or 100 (or one of a small constant set of values), then it represents a "success" case, if theValue is null it's an error, and any other theValue is an expected "failure" case. So, for each cat value, I want to calculate a percentage of success/failure and show the error count if it exists separately.

So, I want to change the above to: (expressions in {} show the calculation and I want to show the result of that)

cat | success       | errors
BER | {55/(55+2+1)} | 
CCG | {88/(88+14)}  | 3

How might I accomplish this?

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

Updated null value handling

search stuff
| rex "extract cat"
| rex "extract field2"
| rex "extract field3"
| eval theValue=coalesce(field2, field3)
| stats count by cat, theValue
| eval status=case(theValue="A" OR theValue="100" OR thevalue="AnyOtherCondition","success", theValue="null","errors",1=1,"failure")
| chart sum(count) over cat by status
| fillnull value=0 success failure errors
| eval success=round(success/(success+failure+errors),2)
| table cat success error

View solution in original post

somesoni2
SplunkTrust
SplunkTrust

Try like this

Updated null value handling

search stuff
| rex "extract cat"
| rex "extract field2"
| rex "extract field3"
| eval theValue=coalesce(field2, field3)
| stats count by cat, theValue
| eval status=case(theValue="A" OR theValue="100" OR thevalue="AnyOtherCondition","success", theValue="null","errors",1=1,"failure")
| chart sum(count) over cat by status
| fillnull value=0 success failure errors
| eval success=round(success/(success+failure+errors),2)
| table cat success error

jshellman
Engager

Thanks! That almost did it. Because failures and errors don't always occur, I needed to add 3 lines like

| eval errors=if(isnotnull(errors), errors, 0)

or else the success wouldn't show up. Maybe there's an easier way to do that, but it worked.

0 Karma

somesoni2
SplunkTrust
SplunkTrust

This would work just fine. For reference, these are ways you can handle assigning default value, in the order of my personal preference.

...| fillnull value="DefaultValue" fieldname1 fieldname2...

...| eval fieldname1=coalesce(fieldname1,"DefaultValue") ...

...| eval fieldname1=if(isnotnull(fieldname1),fieldname1,"DefaultValue").....
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...