I have mydates.csv file uploaded to Splunk lookups. It looks like this:
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?
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]
@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:
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:
to see event counts. This seems not working .
@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?
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