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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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
SplunkTrust
SplunkTrust

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!

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