cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
MartinM
Engaged Sweeper

I tried to make this report but i was not able to 😕

All Servers that are not in 'ADGroup1' or not in 'Group2'

I tried the reports i found in the community but i was not able to change them to Computer AD Group.

2 REPLIES 2
MartinM
Engaged Sweeper

 i found that too but it does not work.

Because it lists all the group memberships.
wehen i filter the groups with not like '%group1%' then i get the same hostname multible times because this list lists alle the memberships.

Mister_Nobody
Honored Sweeper II

One of suitable query - you have to add AD group filter

 

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblADGroups.Name As ADGroupName,
  tblADGroups.Description As ADGroupDescription,
  Case tblADGroups.GroupType
    When -2147483646 Then 'security - global'
    When -2147483644 Then 'security - local'
    When -2147483643 Then 'built-in'
    When -2147483640 Then 'security - universal'
    When 2 Then 'distribution - global'
    When 4 Then 'distribution - local'
    When 8 Then 'distribution - universal'
  End As ADGroupType
From tblAssets
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADMembership On tblADMembership.ChildAdObjectID =
      tblADComputers.ADObjectID
  Left Join tblADGroups On tblADMembership.ParentAdObjectID =
      tblADGroups.ADObjectID
Where tblComputersystem.PartOfDomain = 1 And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName,
  ADGroupName