I'm trying to pull events from a lookup file that has in one column a timestamp. There will be instances where I'll need to go back 2 days or 90 days or any amount of time in between. Any help would be appreciated.
I actually found another post that addressed the issue and got it to work. So simple, I made this much more complicated than it had to be. Thanks again for your quick response.
here is the search - example:
|inputlookup incident_review_lookup | eval _time=time | where _time >= relative_time(now(), "-2h@h")
link that helped:
https://answers.splunk.com/answers/378460/es-incident-review-lookup.html
This worked for me , thanks a lot for the simple solution
|inputlookup incident_review_lookup | eval _time=time | where _time >= relative_time(now(), "-2h@h")
this worked for me, thanks!
Generally, you can use
| inputlookup foo
to get your data from your lookup into the pipeline.
I will be using
| makeresults | eval data="10/01/2016 13:00:00,tomato,x-large,square,red,,%10/01/2016 13:00:00,apple,x-large,roundish,yellow,,%10/01/2016 13:00:00,grapes,x-large,rectangle,green,,%10/01/2016 11:00:00,tomato,large,square,red,,%10/01/2016 11:00:00,apple,large,roundish,yellow,,%10/01/2016 11:00:00,grapes,large,rectangle,green,,%09/15/2016 11:00:00,tomato,med,square,red,,%09/15/2016 11:00:00,apple,med,roundish,yellow,,%09/15/2016 11:00:00,grapes,med,rectangle,green,,%09/01/2016 11:00:00,tomato,small,square,red,,%09/01/2016 11:00:00,apple,small,roundish,yellow,,%09/01/2016 11:00:00,grapes,small,rectangle,green,," | rex field=data max_match=0 "(?<line>[^%]+)" | mvexpand line | table line | rex field=line "(?<date>[^,]+),(?<fruit>[^,]+),(?<size>[^,]+),(?<shape>[^,]+),(?<color>[^,]+)" | table date fruit size shape color
This has the benefit of explicity showing you the data I am dealing with.
Next I need to conver the date field to a date/time field and then show you how to query against it.
| makeresults | eval data="10/01/2016 13:00:00,tomato,x-large,square,red,,%10/01/2016 13:00:00,apple,x-large,roundish,yellow,,%10/01/2016 13:00:00,grapes,x-large,rectangle,green,,%10/01/2016 11:00:00,tomato,large,square,red,,%10/01/2016 11:00:00,apple,large,roundish,yellow,,%10/01/2016 11:00:00,grapes,large,rectangle,green,,%09/15/2016 11:00:00,tomato,med,square,red,,%09/15/2016 11:00:00,apple,med,roundish,yellow,,%09/15/2016 11:00:00,grapes,med,rectangle,green,,%09/01/2016 11:00:00,tomato,small,square,red,,%09/01/2016 11:00:00,apple,small,roundish,yellow,,%09/01/2016 11:00:00,grapes,small,rectangle,green,," | rex field=data max_match=0 "(?<line>[^%]+)" | mvexpand line | table line | rex field=line "(?<date>[^,]+),(?<fruit>[^,]+),(?<size>[^,]+),(?<shape>[^,]+),(?<color>[^,]+)" | table date fruit size shape color | where strptime(date,"%m/%d/%Y %H:%M:%S") > relative_time(now(), "-30d@d")
Notice here we use strptime to convert to a date/time and then used relative_time to get a time that was 30 days ago.
These functions are documented here: http://docs.splunk.com/Documentation/Splunk/6.5.0/SearchReference/CommonEvalFunctions#Date_and_Time_...
So, for your use case it will be something like:
| inputlookup foo | where strptime(date,"%m/%d/%Y %H:%M:%S") > relative_time(now(), "-2d@d")
or
| inputlookup foo | where strptime(date,"%m/%d/%Y %H:%M:%S") > relative_time(now(), "-90d@d")
Depending on your date field name and the format of your date field.
If you just want to retrieve events from the lookup file, try this
| inputlookup filename.csv | eval t=strptime(timestampfield, "appropriate_time_format_variables") | where t>relative_time(now(), "-1d@d")
http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Commontimeformatvariables
If you want to match with data from a index using timestamp, you will need to setup you lookup as a "time-based" lookup.
https://docs.splunk.com/Documentation/Splunk/6.5.0/Knowledge/Configureatime-boundedlookup