How can I use a unixtimestamp as a timerange filter like with earliest & latest in the first pipe?

[HeinzWaescher]

Hi,

my events have a field with epochtime which I want to use in the very first pipe to filter the search
Of course I can do it like

sourcetype=foo field<=1461110400

Is it somehow possible to use this filter as a readable date in the first pipe? Like in earliest

earliest="04/20/2016:00:00:00"

Cheers
Heinz

[javiergn · Accepted solution]

You could use a subsearch for that. For instance:

sourcetype=foo [
   | stats count 
   | fields - count 
   | eval earliest="04/20/2016:00:00:00" 
   | eval earliest=strptime(earliest, "%m/%d/%Y:%H:%M:%S") 
   | table earliest
]

Or if you want to use a different field name other than earliest you can do it this way (note you now need the greater than or less than symbols):

sourcetype=foo field <= [
    | stats count 
    | fields - count 
    | eval query="04/20/2016:00:00:00" 
    | eval query=strptime(query, "%m/%d/%Y:%H:%M:%S") 
    | format "" "" "" "" "" ""
]

[HeinzWaescher]

This works fine, thanks! Perhaps you could answer some additional questions to help me understading this solution?

Normally I try to avoid subsearches wherever I can, because of their limitations.
Can limitations (especially runtime) be a problem for very large datasets here? Can we avoid the problems by adding "head 1" in the subsearch?

 sourcetype=foo field <= [
     **| head 1**
     | stats count 
     | fields - count 
     | eval query="04/20/2016:00:00:00" 
     | eval query=strptime(query, "%m/%d/%Y:%H:%M:%S") 
     | format "" "" "" "" "" ""
 ]

What is the | format good for in this search?
And finally: Why is there no index or sourcetype targeted in the subsearch?

Thanks in advance

[javiergn]

The subsearch is just generating a one time value for you therefore you don't need to search for anything.
It won't have any limitations because of what I said above, you are not searching your data you are just generating a value that you can use as a prefilter.

If you want to avoid using subsearches you could do something like the following, but it won't be applied as early in your query as the solutions above. Try both options and see which one you like the most or which one performs better.

sourcetype=foo
| eval timefilter="04/20/2016:00:00:00" 
| eval timefilter_epoch=strptime(timefilter, "%m/%d/%Y:%H:%M:%S") 
| where field1 <= timefilter_epoch

[HeinzWaescher]

I was aware of the third option, but wanted to avoid searching all events and filter afterwards.
When there are no limitations in this case, I'm fine with a subsearch 🙂 Thanks a lot