Splunk Search

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

wtaylor149
Explorer

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.

Tags (1)
0 Karma

wtaylor149
Explorer

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
Engager

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

0 Karma

CSmoke
Path Finder

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

this worked for me, thanks!

0 Karma

bshuler_splunk
Splunk Employee
Splunk Employee

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.

0 Karma

sundareshr
Legend

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

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...