Splunk Search

Eval value based on timerange

cpeteman
Contributor

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

lguinn2
Legend

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.

View solution in original post

cpeteman
Contributor

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

0 Karma

lguinn2
Legend

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
Legend

LOL!!!!!!!

cpeteman
Contributor

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

cpeteman
Contributor

Sorry I fell asleep on the keyboard lol

cpeteman
Contributor

bbiiiiiiiiiiiiiiiiiiidtiiiiiiiiiiiiidjiooooooooooooooooooooodiiiiiiiiiiiiiiiiiiiiiiiibb

cpeteman
Contributor

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
Motivator

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

cpeteman
Contributor

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
Motivator

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
Motivator

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
Contributor

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
Contributor

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
Motivator

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
Legend

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
Contributor

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
Motivator

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

aholzer
Motivator

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
Motivator

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
Contributor

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

Get Updates on the Splunk Community!

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

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...