Advanced Query identifying users without AD account - Technical Rule - Detective

[ram81]

I need to create a detective rule that identifies users without a specific endpoint. I am using the query below but it seems to return all users. I would like to only identify users without a specific endpoint. 

---------------------------------------

a.statuskey=1 and a.id in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname NOT LIKE 'EndPointName')

[rushikeshvartak]

a.statuskey=1 and a.id not in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname NOT LIKE 'EndPointName')

[ram81]

Thanks but this is the same query I provided. 

[rushikeshvartak]

i have changed not in 

a.statuskey=1 and a.id not in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname NOT LIKE 'EndPointName')

[ram81]

Thanks, But this change did not return any results. 

[rushikeshvartak]

I have validated and it works 

a.statuskey=1 and a.id in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname ='Costar')

[rushikeshvartak · Accepted solution]

Validation

a.statuskey=1 and a.id not in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname = 'Costar')

[ram81]

Thanks!

Updated the syntax below and this worked. 

a.statuskey=1 and a.id not in (select distinct us.userkey from User_accounts us, Accounts cc, Endpoints ep where us.accountkey=cc.id and cc.endpointkey =ep.id and ep.endpointname LIKE 'EndPointName')