How to write a search to only display entries added in the last 24 hours based on a time field from a lookup CSV file?

[ng87]

I have a .csv file as a lookup file that gets updated daily with new records.

It has a number of fields, one being date_added (example field format: 2016-04-17T04:23:40). I am after an easy way to be able to display only the entries added in the last 24 hours (in the ideal world, something like date_added=-24h) .
Reason I am after this is I am creating a Splunk report that will take the new entries added to this CSV and then do a subsearch.

Any ideas how this can be done?

[javiergn]

You could also create a time-based lookup:

http://docs.splunk.com/Documentation/Splunk/6.4.0/Knowledge/Usefieldlookupstoaddinformationtoyoureve...

http://docs.splunk.com/Documentation/Splunk/6.4.0/Knowledge/Configureatime-boundedlookup

Make sure your max and min offset times are correctly configured. For instance, if max offset is 3600 seconds:

# transforms.conf
[mylookup]
filename = mylookup.csv
max_offset_secs = 3600
time_field = timestamp
time_format = %Y-%m-%d %H:%M:%S

And your lookup is as follows:

timestamp, index, value
2016-04-20 10:00:00, _internal, value1
2016-04-19 09:00:00, _internal, value2
2016-04-10 16:00:00, _internal, value3

And assuming the current date is: 2016-04-21 10:00:00.

Then the following query will only return value1:

index=_internal earliest=-24h | lookup mylookup index OUTPUT value | dedup value | table value

Whereas the following will return no results:

index=_internal earliest=-23h | lookup mylookup index OUTPUT value | dedup value | table value

And the following will return values 1 and 2:

index=_internal earliest=-3d | lookup mylookup index OUTPUT value | dedup value | table value

Hope that helps.

[ktugwell_splunk · Accepted solution]

Hey ng87,

Have you tried using relative_time?

And example of how this could be used(I'll eval them first so you can see the usage):

your search | eval start_time=relative_time(now(), "-24h") | where ((date_added > strftime(start_time, "%Y-%m-%dT%H:%M:%S")) AND (date_added < strftime(now(), "%Y-%m-%dT%H:%M:%S")))

Here's a doc about date and times: http://docs.splunk.com/Documentation/Splunk/6.4.0/SearchReference/Commontimeformatvariables

[ktugwell_splunk]

Hey ng87,

Have you tried using relative_time?

And example of how this could be used(I'll eval them first so you can see the usage):

your search | eval start_time=relative_time(now(), "-24h") | where ((date_added > strftime(start_time, "%Y-%m-%dT%H:%M:%S")) AND (date_added < strftime(now(), "%Y-%m-%dT%H:%M:%S")))

Here's a doc about date and times: http://docs.splunk.com/Documentation/Splunk/6.4.0/SearchReference/Commontimeformatvariables

[ng87]

works a dream!!! cheers mate

[ktugwell_splunk]

Cool! Glad it worked.

I've just posted it as the answer, please accept it if you don't mind 🙂