KQL - Devices NOT in Computer Groups

[Speed1]

Hi,

I'm trying to make an KQL Query for all computers that are NOT in 3 certain groups. I tried this but without success. There are always all computers because they are at least in the "Domain Computers" Group.

ComputerGroup
    | where (GroupSource == "ActiveDirectory")
    | where not(Group startswith "Groupname")
    | distinct Computer

Maybe somebody has a hint for me?

Best

Alex

[#2]

I dont have this data, but did you try something like:

ComputerGroup
| where GroupSource !in ('ActiveDirectory','Grp2','Grp3')
| distinct Computer

[#3]

Hi Clive,

thanks for your response. I already tried this but it doesn't work :(
When I filter per "ActiveDirectory" I don't get any result and when I filter per Group it returns always all computers.

Best
Alex

[#4 · Accepted solution]

@Speed1 

Maybe this is better?  I build a list of Computers in the 3 groups, then check which computers are not in that list

let allComputersinGroups = ComputerGroup
| where Group in ('Domain Controllers','Exchange Servers','fakeGroupName')
| summarize count() by Computer, Group;
ComputerGroup
| where  Computer !in (allComputersinGroups)
| summarize dcount(Computer),make_set(Computer) 

[#5]

This worked, thank you!