‎01-20-2015 11:22 AM
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
‎05-21-2024 12:01 AM
Only lists the first group. If a workstation is in multiple groups. The report only shows one group
‎05-21-2024 07:30 AM
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
‎05-21-2024 04:32 PM
Holy necro-thread, batman.🤣
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now