Domain computers and the Active Directory groups they belong to

[Susan_A]

The report below lists your domain computers and the Active Directory groups they belong to. You can filter the ADGroupName column within the report results to list members of a specific group. AD group scanning was added in Lansweeper 5.1. If you have an old Lansweeper installation, update to the latest release and rescan your computers.

The report will only list assets that meet all of the following criteria:

• The asset is a Windows computer.
  
• The computer's state is set to "active".
  
• The computer has been successfully scanned at least once.
  
• The computer is part of a domain.

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

[Lucix]

Only lists the first group.  If a workstation is in multiple groups.  The report only shows one group

[Mister_Nobody]

try this

Select Top 2000000 tblADObjects.domain,
  tblADObjects.sAMAccountName,
  (Select tblADObjects.domain From tblADObjects
    Where tblADObjects.ADObjectID = tblADGroups1.ADObjectID) As Domain_L1,
  tblADGroups1.Name As Group_L1,
  tblADGroups1.Description As Group__Descr_L1,
  (Select tblADObjects.domain From tblADObjects
    Where tblADObjects.ADObjectID = tblADGroups2.ADObjectID) As Domain_L2,
  tblADGroups2.Name As Group_L2,
  tblADGroups2.Description As Group__Descr_L2,
  (Select tblADObjects.domain From tblADObjects
    Where tblADObjects.ADObjectID = tblADGroups3.ADObjectID) As Domain_L3,
  tblADGroups3.Name As Group_L3,
  tblADGroups3.Description As Group__Descr_L3
From tblADObjects
  Inner Join tblADMembership On tblADObjects.ADObjectID =
      tblADMembership.ChildAdObjectID
  Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
      tblADGroups1.ADObjectID
  Inner Join tblADComputers On tblADObjects.ADObjectID =
      tblADComputers.ADObjectID
  Left Join tblADMembership tblADMembership1 On tblADGroups1.ADObjectID =
      tblADMembership1.ChildAdObjectID
  Left Join tblADGroups tblADGroups2 On tblADMembership1.ParentAdObjectID =
      tblADGroups2.ADObjectID
  Left Join tblADMembership tblADMembership2 On tblADGroups2.ADObjectID =
      tblADMembership2.ChildAdObjectID
  Left Join tblADGroups tblADGroups3 On tblADMembership2.ParentAdObjectID =
      tblADGroups3.ADObjectID
Where tblADGroups1.Name <> 'Domain Computers'
Order By Group_L3 Desc,
  Group_L2 Desc,
  Group_L1 Desc

[francisswest]

Holy necro-thread, batman.🤣