topic Splunk where not query returning incorrect results in Splunk Search

Splunk where not query returning incorrect results

msrama5 — Wed, 30 Sep 2020 04:27:31 GMT

Hello, I have the following where not query returning rows that exists in sub search, following is the query
environment=test earliest=-48h latest=-24h index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cardid="*"
WHERE NOT [ search earliest=-48h index=log-cdx-prod source=kubernetes sourcetype=_json "cardRegistered" "cardId" | rename cardNumber as cardid | fields cardid | format] | table cardid

query says take cardid list from first query and return where cardid is not found in second sub search query, I am getting results where cardid is present in second query which is incorrect, condition is where not, any ideas what is going on here ?

Re: Splunk where not query returning incorrect results

manjunathmeti — Wed, 04 Mar 2020 05:30:06 GMT

Remove WHERE from query and put sub-search in parentheses after NOT.

environment=test earliest=-48h latest=-24h index=iis_openapi /internal/loyalty/v1/ cs_uri_stem="registrations" cardid="*" NOT ([ search earliest=-48h index=log-cdx-prod source=kubernetes sourcetype=_json "cardRegistered" "cardId" | rename cardNumber as cardid | fields cardid | format]) | table cardid

Re: Splunk where not query returning incorrect results

msrama5 — Wed, 04 Mar 2020 19:44:24 GMT

Adding bracket is returning the same results , what I need is card id present in first query should not return based on the second sub search query, why would this query fail and return cardid present in first query ?

Re: Splunk where not query returning incorrect results

manjunathmeti — Wed, 04 Mar 2020 23:32:54 GMT

Remove cardid=“*” in first search and check.