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?
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
Hey, @lucy2019, did you get an answer? Come back and give us an update!
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 🙂
@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’.
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
@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 .
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?
There were no errors. Date field returned the same as it is listed in .csv file.
@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?
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.
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
@lakshman239 Thanks! However, this returned error ‘AS is invalid argument for inputlook.’.