Splunk Search

Why Splunk filter data NOT IN subquery?

marcosjags
Explorer

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

 

Labels (3)
Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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?

0 Karma

marcosjags
Explorer

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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 path=message.extra.dj output=dj | table dj] | stats count by fname
0 Karma

marcosjags
Explorer

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

marcosjags
Explorer

@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"}}

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

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}\/(?<dj>\d+)" | table dj] | stats count by fname
0 Karma

marcosjags
Explorer

@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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma

PickleRick
Ultra Champion

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.

0 Karma

marcosjags
Explorer

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.

0 Karma

PickleRick
Ultra Champion

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.

0 Karma

marcosjags
Explorer

@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.

0 Karma

PickleRick
Ultra Champion

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.

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...