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?
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
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
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
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
|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...
Thanks for the answer but it did not work. I got three weird columns, i.e. _time, 0 and 1.
Any idea?