Getting Data In

Search with where to filter based on wildcard variable

splunkreal
Motivator

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%")

* If this helps, please upvote or accept solution 🙂 *
0 Karma

dstoev
Explorer

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 fieldswhich 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:

splunk_query_year_month.png

Any ideas?

0 Karma

splunkreal
Motivator

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")."%"

* If this helps, please upvote or accept solution 🙂 *

mayurr98
Super Champion

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!

0 Karma

splunkreal
Motivator

No, 2018-01-21 logs still there 😞

* If this helps, please upvote or accept solution 🙂 *
0 Karma

mayurr98
Super Champion

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.

0 Karma

splunkreal
Motivator

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

* If this helps, please upvote or accept solution 🙂 *
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...