Splunk Search

Turn stats search into chart

tkwaller
Builder

Trying to find a way to put the results of this search into a chart. I know the issue is that there are 2 fields Im trying to chart together but the results are useless unless they are tied together:
index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | stats count by gateway result

gateway result count
BRAINTREE Failure 92
VERISIGN Failure 666
VERISIGN Success 7240

What I would LIKE to do is pie chart the successes and failures by gateway.
Any ideas on how to accomplish this? Its a pretty simple search so there not much to narrow down.

Thanks for the help!

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

If I understand correctly what you are asking for, you want everything in one chart correct?
If that's the case simply try this:

index=java host=*pay* api_domain=payment resultCode 
| eval result = if(resultCode = "0", "Success", "Failure") 
| stats count by gateway result
| eval gateway_result = gateway."-".result
| chart sum(count) as count by gateway_result

Output should be as follows based on the sample you gave above:

alt text

View solution in original post

acharlieh
Influencer

So there are a couple of options, assuming this is for a dashboard and your gateways you are charting are fixed, you could use your existing search as a base search, and then have each panel be success/failure by gateway:

<search id="base">
  <query>index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | stats count by gateway result</query>
  <earliest>$time.earliest$</earliest>
  <latest>$time.latest$</latest>
</search>
...
<chart>
  <title>VERISIGN activity</title>
   <search base="base">
      <query>where gateway="VERISIGN"| fields - gateway</query>
    </search>
    ...
</chart>
...

The upside of this is one search, and a view for each gateway, the downside is it assumes fairly static set of gateways and you'd have to change each time. Alternatively you could have a chart of successes and a chart of failures, with gateways being the members, but this might not be the visualization you're wanting.

Another option may be to have a parent chart that shows overall volume by gateway, and a custom drilldown to see the success/failure in a separate chart... (or see volume by success/failure and drill down to gateways)... the downside here being you wind up with a click away from the single glass of pane.

I'm not as familiar with Sideview Utils as I should be, but I also vaguely remember there might be a module to dynamically generate charts like this.

Finally the simplest option would be to just tie together the results and show all on one piechart:

index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure"), gateway_result=gateway.":".result | stats count by gateway_result

OR keep your search as a base and add

eval gateway_result = gateway.":".result | fields - gateway result

as postprocess search... depends on the other panels you want to drive from the base.

gcusello
SplunkTrust
SplunkTrust

You have two columns so it't difficult to use a pie!
you could create an histogram with, gateway on x-axis and results stacked.

index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | chart count over gateway by result

Bye.
Giuseppe

0 Karma

gcusello
SplunkTrust
SplunkTrust

You have two columns so it't difficult to use a pie!
you could create an histogram with, gateway on x-axis and results stacked.

index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | chart count over gateway by result

Bye.
Giuseppe

0 Karma

javiergn
SplunkTrust
SplunkTrust

If I understand correctly what you are asking for, you want everything in one chart correct?
If that's the case simply try this:

index=java host=*pay* api_domain=payment resultCode 
| eval result = if(resultCode = "0", "Success", "Failure") 
| stats count by gateway result
| eval gateway_result = gateway."-".result
| chart sum(count) as count by gateway_result

Output should be as follows based on the sample you gave above:

alt text

tkwaller
Builder

Beautiful, I KNEW it was something simple I was not thinking about. Just didn't eval the results together.

Thanks a bunch!

0 Karma

tkwaller
Builder

Well MOSTLY. When this is run it returns
a count of 1 for each gateway_result because | chart count by gateway_result is only counting 1 record of each for some reason.

0 Karma

tkwaller
Builder

Ahhh had some side help, removed the stats count by gateway result and got it working
thanks!

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Replace last | chart count by gateway_result with | chart sum(count) as count by gateway_result

0 Karma

acharlieh
Influencer

or get rid of the unnecessary stats in the middle 🙂

0 Karma

javiergn
SplunkTrust
SplunkTrust

True. I wasn't even using a stats count in my lab as I was testing this from a CSV with inputcsv:

gateway,result,count
BRAINTREE,Failure,92
VERISIGN,Failure,666
VERISIGN,Success,7240
0 Karma

javiergn
SplunkTrust
SplunkTrust

Fixed my answer there as I made a mistake when copying and pasting from my lab instance

0 Karma

sundareshr
Legend

How about something like this

index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | stats count by gateway result | eval gateway=gateway." (".result.")" 

OR if it doesn't have to be a pie-chart, you can do this and use column/bar chart

index=java host=*pay* api_domain=payment resultCode | eval result = if(resultCode = "0", "Success", "Failure") | chart count over gateway by result
0 Karma
Get Updates on the Splunk Community!

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, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...