SOLVED

KQL - Devices NOT in Computer Groups

%3CLINGO-SUB%20id%3D%22lingo-sub-3284456%22%20slang%3D%22en-US%22%3EKQL%20-%20Devices%20NOT%20in%20Computer%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3284456%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI'm%20trying%20to%20make%20an%20KQL%20Query%20for%20all%20computers%20that%20are%20NOT%20in%203%20certain%20groups.%20I%20tried%20this%20but%20without%20success.%20There%20are%20always%20all%20computers%20because%20they%20are%20at%20least%20in%20the%20%22Domain%20Computers%22%20Group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-bash%22%3E%3CCODE%3EComputerGroup%0A%20%20%20%20%7C%20where%20(GroupSource%20%3D%3D%20%22ActiveDirectory%22)%0A%20%20%20%20%7C%20where%20not(Group%20startswith%20%22Groupname%22)%0A%20%20%20%20%7C%20distinct%20Computer%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EMaybe%20somebody%20has%20a%20hint%20for%20me%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%3C%2FP%3E%3CP%3EAlex%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3302427%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20-%20Devices%20NOT%20in%20Computer%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3302427%22%20slang%3D%22en-US%22%3EI%20dont%20have%20this%20data%2C%20but%20did%20you%20try%20something%20like%3A%3CBR%20%2F%3E%3CBR%20%2F%3EComputerGroup%3CBR%20%2F%3E%7C%20where%20GroupSource%20!in%20('ActiveDirectory'%2C'Grp2'%2C'Grp3')%3CBR%20%2F%3E%7C%20distinct%20Computer%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3331745%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20-%20Devices%20NOT%20in%20Computer%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3331745%22%20slang%3D%22en-US%22%3EHi%20Clive%2C%3CBR%20%2F%3E%3CBR%20%2F%3Ethanks%20for%20your%20response.%20I%20already%20tried%20this%20but%20it%20doesn't%20work%20%3A(%3C%2Fimg%3E%3CBR%20%2F%3EWhen%20I%20filter%20per%20%22ActiveDirectory%22%20I%20don't%20get%20any%20result%20and%20when%20I%20filter%20per%20Group%20it%20returns%20always%20all%20computers.%3CBR%20%2F%3E%3CBR%20%2F%3EBest%3CBR%20%2F%3EAlex%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3342176%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20-%20Devices%20NOT%20in%20Computer%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3342176%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1321290%22%20target%3D%22_blank%22%3E%40Speed1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20is%20better%3F%26nbsp%3B%20I%20build%20a%20list%20of%20Computers%20in%20the%203%20groups%2C%20then%20check%20which%20computers%20are%20not%20in%20that%20list%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3Elet%20allComputersinGroups%20%3D%20ComputerGroup%0A%7C%20where%20Group%20in%20('Domain%20Controllers'%2C'Exchange%20Servers'%2C'fakeGroupName')%0A%7C%20summarize%20count()%20by%20Computer%2C%20Group%3B%0AComputerGroup%0A%7C%20where%20%20Computer%20!in%20(allComputersinGroups)%0A%7C%20summarize%20dcount(Computer)%2Cmake_set(Computer)%20%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3342877%22%20slang%3D%22en-US%22%3ERe%3A%20KQL%20-%20Devices%20NOT%20in%20Computer%20Groups%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3342877%22%20slang%3D%22en-US%22%3EThis%20worked%2C%20thank%20you!%3C%2FLINGO-BODY%3E
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!