Splunk Search
Highlighted

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

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
Highlighted

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

Explorer

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

%m%d%Y

Becomes

%m/%d/%Y
0 Karma
Highlighted

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

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

Highlighted

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

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
Highlighted

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

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
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.