Splunk Search

How do you perform a search if today is not the date listed in the lookup .csv file

lucy2019
Explorer

I have lookup file my_dates.csv like this:

mydate, something
1/1/2019, sth1
2/12/2019,sth2
2/20/2019,sth
3/13/2019,sth3

I need to perform a search for if today's date is not the one in my_dates.csv. My search looks like this:

 |bin _time span=1d | search NOT [|inputlook my_dates.csv | eval _time=strptime(mydate, "%m%d%Y") | table _time]

However, this seems to not work. For example, today's date is 2/20/2019. I still got some results back if I run the above search.

0 Karma
1 Solution

woodcock
Esteemed Legend

I am by no means validating your particular design here, but the way to make it function is like this:

... | bin _time span=1d | search NOT [|inputlook my_dates.csv | eval time=strptime(mydate, "%m%d%Y") | table time | format | rex field=search mode=sed "s/time/_time/g"]

View solution in original post

woodcock
Esteemed Legend

I am by no means validating your particular design here, but the way to make it function is like this:

... | bin _time span=1d | search NOT [|inputlook my_dates.csv | eval time=strptime(mydate, "%m%d%Y") | table time | format | rex field=search mode=sed "s/time/_time/g"]

lucy2019
Explorer

@woodcock This is awesome! It works perfectly as I need. Can you explain what exactly 'rex field=search mode=sed "s/time/_time/g"' does here? Many thanks!

0 Karma

woodcock
Esteemed Legend

Splunk treats fields that start with underscore differently than other fields in that most commands ignore them as though they were invisible, and _time is even more special which has additional peculiarities all its own. Whenever you are debugging a failed subsearch, tack format on to the end of it in a regular search to validate what the subsearch will generate. In this case, it revealed what I expected: _time was (properly) being treated as invisible so your subsearch had nothing in it. So I used time instead, which did generate the expected logic in the search field, but with the wrong field name. I used sed to rename time back to _time.

0 Karma

lucy2019
Explorer

I had typo in my search, it should be:
index='abcd' string_seach |bin _time span=1d | search NOT [|inputlookup my_dates.csv | eval _time=strptime(mydate, "%m%d%Y") | table _time]

0 Karma

jkat54
SplunkTrust
SplunkTrust

Need to add the slashes “/“ in your _time strptime eval.

%m%d%Y

Becomes

%m/%d/%Y
0 Karma
*NEW* Splunk Love Promo!
Snag a $25 Visa Gift Card for Giving Your Review!

It's another Splunk Love Special! For a limited time, you can review one of our select Splunk products through Gartner Peer Insights and receive a $25 Visa gift card!

Review:





Or Learn More in Our Blog >>