topic Re: Splunk filter data NOT IN subquery in Splunk Search

Why Splunk filter data NOT IN subquery?

marcosjags — Mon, 25 Apr 2022 15:25:04 GMT

index=xt DONT_MATCH | spath input=log path=message.extra.dj output=dj | spath input=log output=fname path=message.msg.fname| search dj=* NOT [search EXTERNAL_API OR EXTERNAL_STATUS | spath input=log output=url path=url | dedup url | rex field=url "^(\/\w+){6}\/(?<variable>\d+)" | table url variable | stats list(variable) as variable] | stats count by fname

The task here is to show the dj which is there in this event DONT_MATCH and it should not show those dj if it occurs in these 2 events EXTERNAL_API OR EXTERNAL_STATUS. So basically I want to show all the DJ which is there in DONT_MATCH and NOT IN EXTERNAL_API OR EXTERNAL_STATUS

Re: Splunk filter data NOT IN subquery

PickleRick — Mon, 25 Apr 2022 09:58:18 GMT

That's a bit unclear. You search for something called DONT_MATCH, in the subsearch you're tabling two fields only to count one of them and completely discard the other one.

Be a bit more specific what your events look like and what you want to achieve. It would be most desirable to do it without subsearches if possible.

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 10:12:04 GMT

Hi @PickleRick Actually there are 3 events name

1. DONT_MATCH

2. EXTERNAL_API

3. EXTERNAL_STATUS

So I want to get values from the first event only. So I am filtering the DJ which I get in all the 3 events. What I want here is if the DJ is there in First event then ideally it should not be there in the other two and it should show all the values related to that DJ which will eventually help me in creating charts.

Re: Splunk filter data NOT IN subquery

PickleRick — Mon, 25 Apr 2022 10:46:56 GMT

Ahhh, so they are literal constants included in the events, not some placeholders you use to obfuscate parts of your search? 🙂

It's still not clear what is the relation between separate events. What comes to mind first is a simple

index=xt DONT_MATCH NOT EXTERNAL_API NOT EXTERNAL_STATUS | all that spath stuff.

But I'm not sure if that's what you want. I suppose not. There is some identifier (dj?) that occurs in those events and each of those events can have either of those 3 names? And you want only those events for which (for single dj) you have an even with "DONT_MATCH" but don't have another event with the same dj which contains EXTERNAL_AP or EXTERNAL_STATUS?

Be a bit more specific.

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 11:26:53 GMT

@PickleRick These separate events are having dj which is common for all the 3 events.

if I do

index=xt DONT_MATCH NOT EXTERNAL_API NOT EXTERNAL_STATUS | all that spath stuff

Then also it will pull the DJ which at some point of time exists in all the 3. So what I want here is to pull all the DJ which is there in First even i.e DONT_MATCH and Does not exist in other 2. This is the expectation here. To fetch all the DJ from 1st event which does not exist in other 2.

Re: Splunk filter data NOT IN subquery

ITWhisperer — Mon, 25 Apr 2022 11:37:27 GMT

It is not clear how you are identifying events which match DONT_MATCH but don't match events which match EXTERNAL_API or EXTERNAL_STATUS.

Do your events already have a field called variable or url when they match DONT_MATCH?

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 13:11:15 GMT

@ITWhisperer I am trying to filter all the events which matches DONT_MATCH using DJ and then I am trying to filter DJ from EXTERNAL_API or EXTERNAL_STATUS and the one which will not match the DJ from EXTERNAL_API or EXTERNAL_STATUS should be my expected DJ. basically A same DJ can be in all the 3 events. What I want is only those DT which are in event DONT_MATCH and NOT IN EXTERNAL_API or EXTERNAL_STATUS

Re: Splunk filter data NOT IN subquery

ITWhisperer — Mon, 25 Apr 2022 13:51:27 GMT

So, what does url and variable have to do with this?

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 13:58:05 GMT

@ITWhisperer So for these 2 Events EXTERNAL_API OR EXTERNAL_STATUS. I am getting a url something like this /api/sns/exts/djs/310200019110274535/ds/310200019110274536/ and variable is the value which I am extracting from this url which is 310200019110274535 in this case

Re: Splunk filter data NOT IN subquery

ITWhisperer — Mon, 25 Apr 2022 14:05:24 GMT

How does this relate to the events you are getting with the DONT_MATCH search?

You don't appear to have a url or variable field extracted by the first part of the search

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 14:10:48 GMT

@ITWhisperer SO in first part of the search I am getting DJ directly in my splunk log but for the other 2 events I am getting in url.

for DONT_MATCH i am getting it as

{"log":"{"logtime": "2022-04-25T09:53:47.629", "level": "[INFO]","dj": "310200019098846363"}}

Re: Splunk filter data NOT IN subquery

ITWhisperer — Mon, 25 Apr 2022 14:14:53 GMT

OK so for the filtering to work, the field names should probably match - try it this way

Re: Splunk filter data NOT IN subquery

marcosjags — Mon, 25 Apr 2022 14:27:45 GMT

@ITWhisperer I have tried this way. But it is still fetching the data from all the events . it is not excluding the other 2 events

Re: Splunk filter data NOT IN subquery

ITWhisperer — Mon, 25 Apr 2022 14:38:23 GMT

Can you give an example of an event which should not be there together with the event which should have knocked it out?

Re: Splunk filter data NOT IN subquery

PickleRick — Mon, 25 Apr 2022 16:34:22 GMT

You could try using subsearch to find values of dj to exclude but if your set of events to process is relatively large, it can cause problems. Even worse - you will not know there were problems, you would just get wrong results. Also, if you only want some statistical summary, not whole levels, you could get by some clever groupping and stats I believe.