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
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.
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.
Appreciate it!
Thanks again
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
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.