Splunk Search

What is the correct search syntax for my join NOT IN subsearch?

senthil_cbe
New Member

Hi

I am trying something like this :

select t1.field1
from
table1 t1
where
t1.id not in (select t2.id from table1 t2 where t2.id = t1.id and t2.field3 > 5)

I am trying to write this as :

index=table1 JOIN id NOT [search index=table1 field3 > 5] | TABLE field1d

Is this correct syntax?

Tags (4)
0 Karma
1 Solution

somesoni2
Revered Legend

Try this

index=table1 NOT [ search  index=table1 field3>5 | stats count by id |table id] | table id

OR

index=table1 | eval flag=if (field3>5,1,0) | stats sum(flag) as flag by id | where flag=0 |table id

View solution in original post

somesoni2
Revered Legend

Try this

index=table1 NOT [ search  index=table1 field3>5 | stats count by id |table id] | table id

OR

index=table1 | eval flag=if (field3>5,1,0) | stats sum(flag) as flag by id | where flag=0 |table id

senthil_cbe
New Member

I dont think it will work since I am doing NOT IN with subsearch resultset

select id from table1 where api=D and id not in (select id from table where err = 12)

0 Karma

davebrooking
Contributor

Try something like

index=table1 | eventstats count(err) as allerrs by id | where api="D" | join id [search index=table1 err!=12 | stats count as not12s by id] | where allerrs=not12s
0 Karma

somesoni2
Revered Legend

Try this

index=table1 api=D [search  index = table1 err=12 | stats count by id |table id] | table id
0 Karma

senthil_cbe
New Member

Quick clarification :

suppose I have a following records

Table 1

id err api
1 00 D
1 11 C
1 15 B
2 10 C
3 00 D
3 12 F

Find out id's who didn't encounter err=12 but have api=D
select id from table1 where api=D and id not in (select id from table where err = 12)

My O/P will be
id = 1

How can I achieve this?

0 Karma
Get Updates on the Splunk Community!

Prove Your Splunk Prowess at .conf25—No Prereqs Required!

Your Next Big Security Credential: No Prerequisites Needed We know you’ve got the skills, and now, earning the ...

Splunk Observability Cloud's AI Assistant in Action Series: Observability as Code

This is the sixth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how to ...

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...