cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dhoward
Engaged Sweeper III
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?
2 REPLIES 2
dhoward
Engaged Sweeper III
Thank you! Works great.
Andy_Sismey
Champion Sweeper III
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)

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now