AD computers where security group membership != 1

[dhoward]

Hello!

We have several AD groups, of which all Windows machines on the domain should be a member of one of them. Ideally, I'd like a report that lists all Windows machines that are in a number of those groups not equal to 1.

Let's say that the group names are:

• Group one
  
• Group two
  
• Group three
  
• Group four
  
• Group five
  

If a Windows machine is in none or multiple of these groups, then it should appear in the report. Otherwise, it's no issue and shouldn't appear.

How do I tackle this?

[dhoward]

Thank you! Works great.

[Andy_Sismey]

Hi,

So this should get you started , so what I have done is create a query of AD Groups you require and if the count of these groups is 0 or greater than 1 you have a result, well thats the plan !

I have just added 2 groups but you can just add the extra's

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Count(Query3.Name) As [Number Of Groups]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select tblADGroups.Name,
        tblADComputers.AssetID
      From tblADGroups
        Right Join tblADMembership On tblADGroups.ADObjectID =
          tblADMembership.ParentAdObjectID
        Right Join tblADComputers On tblADComputers.ADObjectID =
          tblADMembership.ChildAdObjectID
      Where (tblADGroups.Name = 'Group1' Or tblADGroups.Name =
          'Group2')
      Group By tblADGroups.Name,
        tblADComputers.AssetID) Query3 On Query3.AssetID = tblAssets.AssetID
Where tsysAssetTypes.AssetTypename Like '%win%' And tblAssetCustom.State = 1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried
Having (Count(Query3.Name) < 1) Or
  (Count(Query3.Name) > 1)