Splunk Search

How do you return Boolean if today matches the dates listed in lookups table?

lucy2019
Explorer

I have mydates.csv file uploaded to Splunk lookups. It looks like this:

Date
1/2/2019
2/5/2019
2/16/2019

I need to add date check function to my search, so it will check if today’s date is listed in mydates.csv file. If it is, then create dayflag=YES. Otherwise, set dayflag=NO.

How can I do this?

0 Karma

woodcock
Esteemed Legend

Like this:

index=abcd name=user1 action=login
| eval day_flag = 
[| makeresults
| eval now=relative_time(now(), "@d")
| search
[|inputlookup mydates.csv
| table Date
| eval now = strptime(Date, "%m/%d/%Y")
| table now
| format]
| stats count AS day_flag
| return $day_flag]

This gives 0 for false and 1 for true

0 Karma

woodcock
Esteemed Legend

Hey, @lucy2019, did you get an answer? Come back and give us an update!

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Something like this should work:

| inputlookup YOURDATA.csv
| eval today = strftime(now(), "%d/%m/%Y")
| eval dayflag = if(today==DATE_FIELD, "yes", "no")

Good luck 🙂

0 Karma

lucy2019
Explorer

@chrisyoungerjds My search is very simple. Just pull the login activities for a user. So it looks like this:

Index=abcd name=user1 action=login

My data would look like this:
timestamp: 2-22-2019 02:02:05 name=user1 action= login info= success src_ip= x.x.x.x

My goal is, if I run this every hour, I need to all login activities for user1 for the hour. And ii also need to check today’s date against lookup table. If it matches any date in the lookup table, then create dayflag and set to ‘y’. Otherwise, set dayflag to ‘n’.

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

OK I understand now. If you have your CSV with (at least) two columns like so:

Date,DateFlag
01/01/2019,y

then you can do a query like this

index=abcd name=user1 action=login 
|eval Date = strftime(now(), “%d/%m/%Y”) 
|lookup mydates.csv Date OUTPUTNEW dateflag
0 Karma

lucy2019
Explorer

@chrisyoungerjds Thanks! I added dateflag as second column in mydates.csv. I also included today’s date in mydates.csv file and ran query:

index=abcd name=user1 action=login
|eval Date = strftime(now(), “%m/%d/%Y”)
|lookup mydates.csv Date OUTPUTNEW  dateflag
|table dateflag Date

I was getting empty string in dateflag. Also, I tried to add:

|stats count

to see event counts. This seems not working .

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Are there any errors displayed when you run my search?

If not, are you sure that the date format in the csv and returned for "Date" are exactly the same?

0 Karma

lucy2019
Explorer

There were no errors. Date field returned the same as it is listed in .csv file.

0 Karma

lucy2019
Explorer

@chrisyoungerjds Thank you! I added your query after my search, I got 0 events returned while my own search should return some events. My search looks like this:

 Index=abcd name=user1 action=login [|inputlookup mydates.csv |eval today=strftime(now(), “%d/%m/%Y”) |eval dayflag=if(today==Date, “y”, “n”)]

Am I missing anything here?

0 Karma

chrisyounger
SplunkTrust
SplunkTrust

Hi @lucy2019

There are a few different ways it could work. Can you supply more details about your existing search? and possibly a tiny bit of sample data. We will be able to help you better that way.

0 Karma

lakshman239
SplunkTrust
SplunkTrust

Index=abcd name=user1 action=login |eval today=strftime(now(), “%d/%m/%Y”) |inputlookup today AS Date mydates.csv

the above will return results if today's date matches the date in the lookup table

0 Karma

lucy2019
Explorer

@lakshman239 Thanks! However, this returned error ‘AS is invalid argument for inputlook.’.

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