Jetzt Ausprobieren

Domain Computers and Their AD Groups

Active Directory Hardware

Find All the Active Directory Domain Computers and Their Groups

Get detailed information about the AD computer, the computer’s user, attributes and it’s active directory group information. By using this report you can easily get an overview of all your AD computers and their groups to find computers which are not part of an AD group or are in the wrong AD group. Domain computers and their AD groups

Domain Computers and Their AD Groups Query

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  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
  Left 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
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
Where Case tblComputersystem.PartOfDomain
    When 0 Then 'No'
    When 1 Then 'Yes'
  End = 'Yes' And tblState.Statename = 'Active'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  ADGroupName

Show

Hide