How to dynamically generate Splunk search queries for Splunk alerts based on alert search times?

[FutureSight]

In order for me to create appropriate Splunk alerts for a certain process, I need to be able to dynamically generate the searches used by the alert based on the search time used for the search. I emphasize "search time", because a lot of Splunk's functionality is based on the event time.

When searching from StartSearchDate=YYYY_MM_DD at HH:MM:SS to EndSearchDate=YYYY_MM_DD at HH:MM:SS , I need to search for the following in a log: DataFile_($StartSearchDate - 1 day).dat

The full search query will be:
DataFile_($StartSearchDate - 1 day).dat + "Some static string"

For example:
With the search time range as 01/25/2014 03:00 - 01/25/2014 05:00, the search will be
DataFile_20140124.dat + "Some static string"

I will also need to take care of edge cases such as the first day of a new year and of a new month.

Example: First day of 2014
DataFile_20131231.dat + "Some static string"

Example: First day of December 2014
DataFile_20141130.dat + "Some static string"

Is there a way to do this using Splunk alerts?

Thanks in advance!

[martin_mueller · Accepted solution]

You can build your query in a subsearch to utilize the time range.

  index=foo
[ stats count | addinfo
| eval query = "DataFile_" + strftime(relative_time(info_min_time, "-d"), "%Y%m%d") + ".dat some static string"
| fields query
]
other filters go here | more processing

The subsearch will evaluate to whatever that query field contains. Using the magic field name query avoids the default behaviour of turning the subsearch into a fieldname="value" filter.

[marcoscala]

Great! I'd like to use the same method to crete a macro to create a condition in a search to analyze Twitter data.
I have a form with some inputs fields the user can fill to select filters in a search. I have a "Hashtag" fiel with a default "" value, but if the user doesn't specify a value, I want add the "" condition, not the "entities.hashtags{}.text=*" condition, because I want to extract tweets even if the entities.hashtags{}.text is not present (if the tweet hasn't any hashtag, there's no field at all).

I've tried something like:

index=twitter lang=* [search * | head 1| eval query = if("$field$" == "*","*","entities.hashtags{}.text=$field$") | fields query | format "" "" "" "" "" ""]

which works only if $field$=="*", otherwise if the value in the input field is "myhashtag" the subsearch returns
"entities.hashtags{}.text=myhashtag" which doesn't match (Splunk looks for the STRING "entities.hashtags{}.text=myhashtag"

Basically I should find a way to strip the "" at the beginning and end of the field "query".

Any idea?

Regards,
Marco