Getting Data In

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

HeinzWaescher
Motivator

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

0 Karma
1 Solution

javiergn
SplunkTrust
SplunkTrust

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 "" "" "" "" "" ""
]

View solution in original post

javiergn
SplunkTrust
SplunkTrust

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
Motivator

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

0 Karma

javiergn
SplunkTrust
SplunkTrust

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
Motivator

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...