topic Re: Turn stats search into chart in Splunk Search

Turn stats search into chart

tkwaller — Mon, 17 Oct 2016 14:17:02 GMT

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!

Re: Turn stats search into chart

sundareshr — Mon, 17 Oct 2016 14:27:22 GMT

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

Re: Turn stats search into chart

javiergn — Mon, 17 Oct 2016 14:31:28 GMT

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:

Re: Turn stats search into chart

gcusello — Mon, 17 Oct 2016 14:32:30 GMT

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

Re: Turn stats search into chart

gcusello — Mon, 17 Oct 2016 14:32:30 GMT

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

Re: Turn stats search into chart

tkwaller — Mon, 17 Oct 2016 14:33:45 GMT

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

Thanks a bunch!

Re: Turn stats search into chart

acharlieh — Mon, 17 Oct 2016 14:38:03 GMT

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.

Re: Turn stats search into chart

tkwaller — Tue, 29 Sep 2020 11:26:31 GMT

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.

Re: Turn stats search into chart

somesoni2 — Mon, 17 Oct 2016 14:46:09 GMT

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

Re: Turn stats search into chart

javiergn — Mon, 17 Oct 2016 14:51:06 GMT

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

Re: Turn stats search into chart

acharlieh — Mon, 17 Oct 2016 14:52:03 GMT

or get rid of the unnecessary stats in the middle 🙂

Re: Turn stats search into chart

tkwaller — Mon, 17 Oct 2016 14:55:55 GMT

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

Re: Turn stats search into chart

javiergn — Mon, 17 Oct 2016 15:01:50 GMT

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