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?
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
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
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)
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
Try this
index=table1 api=D [search index = table1 err=12 | stats count by id |table id] | table id
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?