Splunk Search

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

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

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

Esteemed Legend

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

0 Karma

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

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

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

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

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

Explorer

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

0 Karma

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

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

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

Explorer

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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!