topic Re: Eval value based on timerange in Splunk Search

Eval value based on timerange

cpeteman — Mon, 08 Jul 2013 20:29:27 GMT

Ok I'm rewriting this question as it has become much simpler than before. All I need to do is have a way the get the length of the current time range I am searching over (as a variable I hope) so that I can use it in eval. What I have right now is:

search term |bucket _time span=1m | stats count by _time,punct | eval occurred=if(count!=0,1,0)| stats sum(count) AS sum,sum(occurred) AS num_of_mins_occurred,mean(count) AS mean,stdev(count) AS standard_deviation by punct |eval hourly=if((num_of_mins_occurred/4)=1,"True","False")

Re: Eval value based on timerange

aholzer — Mon, 28 Sep 2020 14:16:42 GMT

To calculate the number dynamically you may want to calculate the latest(_time) and the earliest(_time) and do a diff on them. You can do this by running an eventstats after your bucketing (to round to the minute).

This will give you the value in hours in the field named "diff":

search_terms | bucket _time span=1m | eventstats latest(_time) as last, earliest(_time) as first | eval diff=(last-first)/60/60

I should mention that if you use "last 4 hours" you will likely get decimal places in your calculation. You can just add a function to truncate or round the decimals if you want.

Re: Eval value based on timerange

cpeteman — Mon, 28 Sep 2020 14:16:45 GMT

I'm afraid that this seems to only be giving me falses
when I try the following "search_term |bucket _time span=1m | stats count by _time,punct | eval occurred=if(count!=0,1,0)| stats sum(count) AS sum_events,sum(occurred) AS num_of_mins_occurred,mean(count) AS mean,stdev(count) AS standard_deviation by punct | eventstats latest(_time) as last, earliest(_time) as first | eval hourly=if((num_of_mins_occurred*60*60/(last-first))==1,"True","False")"

Re: Eval value based on timerange

aholzer — Mon, 28 Sep 2020 14:17:06 GMT

Like I pointed out, if you are using a relative timerange, like "last 4 hours" or "last 24 hours", 99% of the time you'll get a value that includes seconds in it.

What this means for you, is that even if you convert your "num_of_mins_occurred" to seconds, by dividing twice by 60, you will get a decimal answer. It will be something like (4 /60 /60) / (4.01 /60 /60), which is not in fact == 1.

You should run a round or truncate on the answer to the diff before you try to use it in an eval.

Ran out of chars, see below

Re: Eval value based on timerange

aholzer — Mon, 28 Sep 2020 14:17:10 GMT

Like:
search_terms | bucket _time span=1m | eventstats latest(_time) as last, earliest(_time) as first | eval diff=round((last-first)/60/60 , 0)

This will round up to the nearest interger, avoiding the problem I mentioned above.

Also, I just noticed that you ran your eventstats after you ran a stats that does not contain _time as a field. This would also cause problems since the would be no _time field for the eventstats to work with. You'll need to use the eventstats prior to your second stats.

Re: Eval value based on timerange

cpeteman — Mon, 28 Sep 2020 14:17:12 GMT

I fixed the order of eventstats and used to the rounding. I really think what you've have been suggesting should work, Thanks for the help so far. Still not working though, any ideas?

"search_terms |bucket _time span=1m | stats count by _time,punct | eval occurred=if(count!=0,1,0)| eventstats latest(_time) AS last, earliest(_time) AS first |stats sum(count) AS sum,sum(occurred) AS num_of_mins_occurred,mean(count) AS mean,stdev(count) AS standard_deviation by punct | eval diff=round((last-first)/60/60, 0) | eval hourly=if((num_of_mins_occurred/diff)==1,"True","False")"

Re: Eval value based on timerange

aholzer — Tue, 09 Jul 2013 17:59:59 GMT

So there's a couple of things that could be going on.
1) When you run a stats (or any agg command) you lose fields that aren't part of that aggregation. Running an "eval diff=last-first..." after you did a stats that doesn't have last nor first, you'll get blank results for that eval. We'll need last and first in the stats somehow, maybe avg()?
2) I think we'll have to use truncate rather than rounding. Why? If you run "Last 4 hours" it basically does "earliest=-4h@h". The @h snaps it to the beginning of that hour. If you run it at 13:50, you'll get earliest=9:00 til now, for a 4h50m length

Re: Eval value based on timerange

aholzer — Mon, 28 Sep 2020 14:17:18 GMT

Just double checked, we want to use floor(), since there is no truncate function.

Also, instead of having a separate eventstats, we could include the "latest(_time) AS last, earliest(_time) AS first" as part of your last stats command. This way saving one command step.

Re: Eval value based on timerange

lguinn2 — Tue, 09 Jul 2013 20:26:43 GMT

There is a Splunk command addinfo that adds some fields to your search results. See addinfo for more info 🙂
To calculate the time range of a search:

yoursearchhere
| addinfo
| eval searchRange = info_max_time - info_min_time
| eval searchRangeOutput=tostring(searchRange,"duration")

Note that searchRange will be in seconds. Also, the solution in the comments will compute the time range of the resulting events. This answer will compute the time range of the search itself, regardless of what events are returned.

Re: Eval value based on timerange

aholzer — Tue, 09 Jul 2013 20:29:54 GMT

That's awesome. I didn't know about addinfo.

Re: Eval value based on timerange

cpeteman — Mon, 28 Sep 2020 14:17:28 GMT

This almost almost works, but for a 4 hour time span it gives 4.0100... and when I use round(info_max_time - info_min_time, 0) it works fine but a search over the last 24 hours returns 25 hours and a week returns 169 hours not 168. Is there a clean fix for this?

Re: Eval value based on timerange

lguinn2 — Wed, 10 Jul 2013 02:45:54 GMT

What if you exact()? As in

| eval searchRange = round( exact(info_max_time) - exact(info_min_time), 0)

I would also add this to the search, especially for the day and week, to see what is going on

| eval searchStart=strftime(info_min_time,"%x %X") | eval searchEnd =strftime(info_max_time,"%x %X")

I wonder if there is something weird about the times...

Re: Eval value based on timerange

aholzer — Wed, 10 Jul 2013 13:59:25 GMT

The reason that happens is as follows:

When you run "Last 4 hours" it basically does "earliest=-4h@h". The @h snaps it to the beginning of that hour. If you run it at 13:50, you'll get earliest=9:00 til now, for a 4h50m length. If you round this, you'll get 5h as the answer.

This applies to any relative option from the timerange picker ("Last X ").

In the eval you use to convert your first and last times to hours you should run a floor on them to truncate the decimal places, rather than round which is what is giving you the extra hour.

Re: Eval value based on timerange

cpeteman — Thu, 11 Jul 2013 16:31:56 GMT

It looks like the problem is similar to what you are aholzer is saying. Fro the last four hours it searches from say 12:03:00 to 4:03:38 if I start the search at 4:03:38. Is there a way to make the default so that it starts so many hours ago based onf the seconds as well? Or is this an issue caused by my bucketing of time?

Re: Eval value based on timerange

cpeteman — Thu, 11 Jul 2013 16:41:56 GMT

I know I could make this happen in my search, but it would be better of it was not something I had to do every time I wanted a new search.

Re: Eval value based on timerange

aholzer — Thu, 11 Jul 2013 18:17:45 GMT

Well you can change the definitions of the "Last 4 hours" option (and any other timerange option) to not snap.

Go to: Manager » User interface » Time ranges

To make one of the timerange option stop snapping you just have to remove everything after (and including) the '@'.

For example "Last 4 hours" will look like -4h@h by default, you can change it to -4h, and it will do the EXACT 4 hours ago.

Re: Eval value based on timerange

aholzer — Thu, 11 Jul 2013 18:18:43 GMT

You can also simply define your earliest and latest values in your base search.

Example:
index= sourcetype= earliest=-4h latest=now

Doing it this way should override anything that was selected in the timerange picker

Re: Eval value based on timerange

cpeteman — Thu, 11 Jul 2013 20:22:07 GMT

Thanks for the first solution I'll have to check to see, but from the results I am getting would it not seem that it is probably set for -4h@m ?

Re: Eval value based on timerange

aholzer — Thu, 11 Jul 2013 20:27:24 GMT

You're right, I checked to confirm. I think I had the -4h@h from an older version of splunk.

Re: Eval value based on timerange

cpeteman — Mon, 28 Sep 2020 14:19:41 GMT

Okay. The solution I was able to come up with give the -4h@m is simple and relatively clean but not quite as flexible as I'd like. I just take the extra seconds and subtract it when calculating search Range:

"...|addinfo | eval searchRange = round( info_max_time - info_max_time%60 - info_min_time, 0) | eval..."

Thanks for the help you two, let me know if you think of a better way to do this.