How to search a lookup table or file by the column that has timestamp

[wtaylor149]

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.

[wtaylor149]

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

[pravinvram]

This worked for me , thanks a lot for the simple solution

[CSmoke]

|inputlookup incident_review_lookup | eval _time=time | where _time >= relative_time(now(), "-2h@h")

this worked for me, thanks!

[bshuler_splunk]

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.

[sundareshr]

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