How to use a date format as a filter in the base search

[HeinzWaescher]

Hi,

I have events with a timestamp_value=1477043785561
We can filter like this:

index=a sourcetype=logins timestampvalue<=1477008000

Is it possible to use a date format in the base search to filter?

Of course the following does not work, but perhaps something similar is possible so that I don't have to translate every filter into epochtime.

index=a sourcetype=logins timestampvalue<=2016-10-21

Best
Heinz

[inventsekar]

 index=a sourcetype=logins | where ( timestampvalue <= [| makeresults | eval search=strptime("2016-10-21", "%Y-%m-%d") | return search] )

[HeinzWaescher]

How I understand it, this search will first load all events and filter afterwards. That's why I would like to have it in the base search before the first pipe

[inventsekar]

ok, lets try -
| makeresults | eval searchtime=strptime("2016-10-21", "%Y-%m-%d") | search index=a sourcetype=logins timestampvalue <= searchtime

[sundareshr · Accepted solution]

Try this

 index=a sourcetype=logins timestampvalue<=[| makeresults | eval search=strptime("2016-10-21", "%Y-%m-%d") | return search]

[HeinzWaescher]

This does not work because of this error:

Error in 'search' command: Unable to parse the search: Comparator '=' has an invalid term on the left hand side.

I tried different adjustments but without success

[HeinzWaescher]

It seems that there was just missing a charachter

index=a sourcetype=logins timestampvalue<=[| makeresults | eval search=strptime("2016-10-21", "%Y-%m-%d") | return $search]

This works fine, thanks for this approach!
I assume that this does not have negative subsearch performance issues or subsearch limits, because it is only calcualting one single result?

[inventsekar]

one more thought -
if timestampvalue and indextime are same, why not add timestampvalue to latest and try -
index=a sourcetype=logins earliest="10/01/2016:00:00:00" latest="10/21/2016:00:00:00"
by this, we can avoid the subsearch altogether.

[sundareshr]

It only calculates the servertime and returns one value, so should be any impact.