Forum Discussion
Speed1
Apr 13, 2022Copper Contributor
KQL - Devices NOT in Computer Groups
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
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)
- Clive_WatsonBronze ContributorI dont have this data, but did you try something like:
ComputerGroup
| where GroupSource !in ('ActiveDirectory','Grp2','Grp3')
| distinct Computer- Speed1Copper ContributorHi 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- Clive_WatsonBronze Contributor
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)