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

View solution in original post

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
Revered Legend

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
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.