topic Re: How do you return Boolean if today matches the dates listed in lookups table? in Splunk Search

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

lucy2019 — Fri, 22 Feb 2019 22:20:33 GMT

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?

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

chrisyounger — Fri, 22 Feb 2019 22:24:23 GMT

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 🙂

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

lucy2019 — Fri, 22 Feb 2019 22:54:44 GMT

@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?

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

lakshman239 — Fri, 22 Feb 2019 23:26:38 GMT

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

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

chrisyounger — Fri, 22 Feb 2019 23:37:58 GMT

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.

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

lucy2019 — Sat, 23 Feb 2019 01:49:30 GMT

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

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

lucy2019 — Sat, 23 Feb 2019 02:07:59 GMT

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

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

chrisyounger — Sat, 23 Feb 2019 03:19:18 GMT

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

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

lucy2019 — Sun, 24 Feb 2019 07:32:21 GMT

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

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

chrisyounger — Sun, 24 Feb 2019 07:38:08 GMT

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?

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

lucy2019 — Sun, 24 Feb 2019 08:00:51 GMT

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

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

woodcock — Thu, 28 Feb 2019 06:37:45 GMT

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

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

woodcock — Wed, 06 Mar 2019 04:14:17 GMT

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