Eval value based on timerange

[cpeteman]

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")

[cpeteman]

not that I'm complaining but why did using2 just up vote everything on this post?

[lguinn2 · Accepted solution]

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.

[lguinn2]

LOL!!!!!!!

[cpeteman]

Just to be clear, I got almost 150 points by dozing off lol.

[cpeteman]

Sorry I fell asleep on the keyboard lol

[cpeteman]

bbiiiiiiiiiiiiiiiiiiidtiiiiiiiiiiiiidjiooooooooooooooooooooodiiiiiiiiiiiiiiiiiiiiiiiibb

[cpeteman]

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.

[aholzer]

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

[cpeteman]

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 ?

[aholzer]

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

[aholzer]

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.

[cpeteman]

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.

[cpeteman]

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?

[aholzer]

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.

[lguinn2]

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...

[cpeteman]

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?

[aholzer]

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

[aholzer]

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.

[aholzer]

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

[cpeteman]

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")"