Is it possible to declare and set a variable value for date_hour and date_wday before passing it to a search?

[srjurell]

Is it possible to declare and set a variable value for date_hour and date_wday before search and then pass it to the search? I am doing some statistical manipulations based on 26 weeks worth of data. The query runs fairly fast if I limit the search to specific date_hour and date_wday, but takes a very long time to run without the date_* filters (or filtering after the initial search). I'd like to be able to set the date_hour and date_wday based date at runtime.

The following runs fast:

sourcetype=device  host=1.2.3.4 date_hr=11 date_wday="friday" earliest=-190d@d latest=-8d@d 

What I'd like to do is something like:

|eval hr=strftime(now(),"%H")|eval wday = lower(strftime(now(),"%A"))|search sourcetype=device host=1.2.3.4 date_hour=hr date_wday=wday  earliest=-190d@d latest=-8d@d

I imagine the proper long-term answer is to use summary indexes, but I haven't figured out how to do them yet (yes, I've read the docs), plus I want to be sure the queries work well before setting up the index.

Thanks in advance for your inputs

[dwaddle · Accepted solution]

Subsearch to the rescue...

sourcetype=device host=1.2.3.4  earliest=-190d@d latest=-8d@d 
[ | stats count | eval date_hour=strftime(now(),"%H") | eval date_wday = lower(strftime(now(),"%A")) | fields - count  ] 
| ..

The subsearch will emit date_hour and date_wday into the outer search and you're off to the races.

[srjurell]

Appreciate it!

Thanks again

[srjurell]

dwaddle,
Thank you for the amazingly fast reply! This works like a charm with one change. For some reason I had to add "tonumber" to the date_hour eval and "tostring" to the date_wday eval. Search ends up looking like:

 sourcetype=device host=1.2.3.4 earliest=-190d@d latest=-8d@d
[ | stats count | eval date_hour=tonumber(strftime(now(),"%H"))| eval date_wday = tostring(lower(strftime(now(),"%A"))) | fields - count ]
| ..

Why must the subsearch start with a stats function rather than just eval? I had tried a subsearch before but had started it with the eval functions and that did not work.

srjurell

[dwaddle]

The | stats count is a hack to generate an event. The eval adds fields to existing events, but it cannot generate events on its own. Your subsearch needs to start with some form of event-generating command. It just so happens that | stats count is one of the cheapest ways to generate a single event.