SOLVED

KQL - Devices NOT in Computer Groups

New Contributor

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

 

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

ComputerGroup
| where GroupSource !in ('ActiveDirectory','Grp2','Grp3')
| distinct Computer
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
best response confirmed by Speed1 (New Contributor)
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) 

  

This worked, thank you!