Forum Discussion

Speed1's avatar
Speed1
Copper Contributor
Apr 13, 2022

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

 

  • 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) 

      

  • Clive_Watson's avatar
    Clive_Watson
    Bronze Contributor
    I dont have this data, but did you try something like:

    ComputerGroup
    | where GroupSource !in ('ActiveDirectory','Grp2','Grp3')
    | distinct Computer
    • Speed1's avatar
      Speed1
      Copper Contributor
      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
      • Clive_Watson's avatar
        Clive_Watson
        Bronze Contributor

        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) 

          

Resources