Splunk Search

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
Path Finder

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?

0 Karma
1 Solution

ktugwell_splunk
Splunk Employee
Splunk Employee

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

View solution in original post

javiergn
SplunkTrust
SplunkTrust

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.

0 Karma

ktugwell_splunk
Splunk Employee
Splunk Employee

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
Splunk Employee
Splunk Employee

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
Path Finder

works a dream!!! cheers mate

0 Karma

ktugwell_splunk
Splunk Employee
Splunk Employee

Cool! Glad it worked.

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

0 Karma
Get Updates on the Splunk Community!

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 ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...