topic Re: Search with where to filter based on wildcard variable in Getting Data In

Search with where to filter based on wildcard variable

splunkreal — Tue, 29 Sep 2020 17:45:31 GMT

Could you let me know why the results are not filtered (I hidden sensible data) with | where NOT like (source, "%stimeyesterday%")

![alt text][1]

| tstats latest(_time) as latest,earliest(_time) as earliest WHERE (index=* AND ...) by host source sourcetype | eval lastevent=strftime(latest, "%Y-%m-%d %H:%M") | eval firstevent=strftime(earliest, "%Y-%m-%d %H:%M")
... | eval timeYesterday=round(relative_time(now(), "-1d@d"))| eval stimeyesterday=strftime(timeYesterday, "%Y-%m-%d") | eval timeRelative=round(relative_time(now(), "@d")) | where latest < timeRelative | eval resultat=if(latest < timeRelative,"KO","OK") | eval stimerel=strftime(timeRelative, "%Y-%m-%d %H:%M") | sort host | fields - latest earliest timeRelative timeYesterdayss | where NOT like(source,"%stimeyesterday%")

Re: Search with where to filter based on wildcard variable

mayurr98 — Fri, 19 Jan 2018 17:45:54 GMT

hey @realsplunk, I do not know what you are trying to achieve you want to filter out source which has stimeyesterday keyword? then

instead of | where NOT like(source,"%stimeyesterday%") use | search NOT source=*stimeyesterday*

let me know if this helps!

Re: Search with where to filter based on wildcard variable

splunkreal — Mon, 22 Jan 2018 09:39:03 GMT

No, 2018-01-21 logs still there 😞

Re: Search with where to filter based on wildcard variable

mayurr98 — Mon, 22 Jan 2018 09:44:44 GMT

which is the field that contains date? if you have a value in source field then only you can apply this query.
This works vertically not horizontally. pls, explain what table you got and what do you want to exclude.

Re: Search with where to filter based on wildcard variable

splunkreal — Mon, 22 Jan 2018 11:00:14 GMT

see attachment here : https://answers.splunk.com/storage/attachments/226762-search.png thanks

Re: Search with where to filter based on wildcard variable

splunkreal — Mon, 22 Jan 2018 11:28:54 GMT

Solved by support :

| where NOT like(source,stimeyesterday)
So, in your query, you can use something like, to add the %% around the filter string beforehand:
| eval stimeyesterday="%".strftime(timeYesterday, "%Y-%m-%d")."%"

Re: Search with where to filter based on wildcard variable

dstoev — Thu, 02 Dec 2021 15:07:16 GMT

Hey @splunkreal , thank you for sharing this, it's quite useful!
I have similar eval command:

... | eval year_month=strftime(_time,"%Y-%m")."%" | where like(access_key_1_last_used_date, year_month) OR like(access_key_2_last_used_date, year_month)

which is setting year_month to the following format YYYY-MM, e.g. 2021-12 and then search it in access_key_{1,2}_last_used_date fields , which have the following format 2021-11-20T00:35:38+00:00

So this is working just fine (thanks to the wildcard "%"), but if I ONLY change now() to _time it is not working. If I simplify the search to:

...| eval year_month=strftime(now(),"%Y-%m")."%" | table year_month

Both results in the same:

Any ideas?