Splunk Search

How to calculate percentage and display this on a timechart?

mitcanmit
Explorer

I extract a variable called "state" using rex, and it has 3 values: success, aborted, chargeback
Now I want to see the success rate, i.e. number of successes divided by number of all 3 states combined, on a timeline.

My query:

  base search
 | stats count as total count(eval(state="success")) as success
 | eval conversion=success/total
 | timechart span=30m by conversion

If I remove "timechart" line, the search works properly but I want to get a timeline of the conversion. What am I missing here?

1 Solution

somesoni2
SplunkTrust
SplunkTrust

Give this a try

 base search | eval success=if(state="success",1,0)
| timechart span=30m count as total sum(success) as success
| eval conversion=success/total
| table _time conversion

View solution in original post

splunksan
Engager

to calculate percentage directly on timechart command (+ we can apply a by clause)

| eval success=if(http_status<500,1,0)
| timechart span=1m eval(sum(success)*100/count) by vhost limit=0

 

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Give this a try

 base search | eval success=if(state="success",1,0)
| timechart span=30m count as total sum(success) as success
| eval conversion=success/total
| table _time conversion

stephanefotso
Motivator

Hi! i'm not sure that you can use a timechart command at that level with the stats command.You must change the stats command to eventstats, or Instead, use a chart like this.

 | stats count as total count(eval(state="success")) as success by _time
 | eval conversion=success/total
 | chart   values(conversion)  by _time

And, if you still wish to use the span, use it like this:

     | bucket span=30m _time | stats count as total count(eval(state="success")) as success by _time
     | eval conversion=success/total
     | chart   values(conversion)  by _time

or you can use your timechart with eventstats like this

      | eventstats count as total count(eval(state="success")) as success 
         | eval conversion=success/total
         | timechart   span=30m count by conversion
SGF

rsennett_splunk
Splunk Employee
Splunk Employee
|bucket _time span=30m | stats count as total count(eval(state="success")) as success by _time
|eval conversion=success/total
|timechart values(conversion)

I've amended my search here to better help you break things down so you can see what's happening.
Both somesoni2 and I have brought the concept of the 30m span higher so that your totals will represent the total within the span.
As for the data... I would suggest that you run each line and look at the statistics tab. I ran this on some web logs to test using the status_description="OK" which is akin to your "success" as it is the most common occurrence. And what I found is that most often, the success and total fields had the same value... so success/total=1 in that case.

You might want to take a look at this blog http://www.davidveuve.com/tech/timechart-versus-stats/ David Veuve is a Splunker, and he explains how timechart works in a way that I think will turn on the lightbulb for you, and offer you the option of using stats to get more granular...

With Splunk... the answer is always "YES!". It just might require more regex than you're prepared for!

mitcanmit
Explorer

Thanks for the answer but it did not work. I got three weird columns, i.e. _time, 0 and 1.

Any idea?

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