Computer AD group membership - STUFF

[TomFox]

Dear Lansweeper community,

I am trying to write a report to list every computer and it's specific AD group membership.  I want to use STUFF to join all AD groups containing *ROLE* into a single column.  In our environment, almost every computer is member of 1 or more AD groups containing ROLE.  This should be a left join to show computers that don't have a ROLE assigned.  The resulting groups should be Ordered A-Z.

I have tried following the example given for monitors, but can't work it out, as AD group membership is based on multiple joins.

Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADComputers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID

SQL 2017 has the STRING_AGG() function to achieve this, when will it be implemented in Lansweeper? It simplifies the solution to 

STRING_AGG(tblADGroups.Name, ', ')

[Mister_Nobody · Accepted solution]

try add filter to this query (*without nested membership):

Select Top 1000000 tblADObjects.domain,
  tblADObjects.samaccountname,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADcomputers On tblADMembership.ChildAdObjectID =
          tblADcomputers.ADObjectID
    Where adc.ADObjectID = tblADcomputers.ADObjectID For Xml Path('')), 1, 2,
  '') Groups
From tblADMembership As admem
  Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
  Inner Join tblADcomputers As adc On admem.ChildAdObjectID = adc.ADObjectID
  Inner Join tblADObjects On adc.ADObjectID = tblADObjects.ADObjectID
Group By tblADObjects.domain,
  tblADObjects.samaccountname,
  adc.ADObjectID

[Mister_Nobody]

You can try to combine

[Mister_Nobody]

Stuff user membership (as I see you use it)

Select Top 1000000 adu.Username,
  adu.userdomain,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADusers On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID
    Where tblADusers.Username = adu.Username And tblADusers.Userdomain =
      adu.userdomain For Xml Path('')), 1, 2, '') Groups
From tblADMembership As admem
  Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
  Inner Join tblADusers As adu On admem.ChildAdObjectID = adu.ADObjectID
Group By adu.Username,
  adu.userdomain

[Mister_Nobody]

I have some reports for groups and computers

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

[Mister_Nobody]

You can see my report

https://community.lansweeper.com/t5/general-discussions/report-ad-groups-and-their-members-incl-grou...

and concatenate strings

[TomFox]

Hi Mister_Nobody,

I tried the example that you gave, but can't get the filtering per computer to work correctly.  Please can you look at the following code, maybe you can find what I'm missing.

Select Top (1000000) tblAssets.Assetname,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADComputers On tblADMembership.ChildAdObjectID =
          tblADComputers.ADObjectID Inner Join tblAssets On
          tblADComputers.AssetID = tblAssets.AssetID
    Where tblADGroups.Name Like '%_ROLE_%' For Xml Path('')), 1, 2, '') Groups
From tblADMembership
  Inner Join tblADGroups On tblADMembership.ParentAdObjectID =
      tblADGroups.ADObjectID
  Inner Join tblADComputers On tblADMembership.ChildAdObjectID =
      tblADComputers.ADObjectID
  Left Join tblAssets On tblADComputers.AssetID = tblAssets.AssetID

[Mister_Nobody · Accepted solution]

try add filter to this query (*without nested membership):

Select Top 1000000 tblADObjects.domain,
  tblADObjects.samaccountname,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADcomputers On tblADMembership.ChildAdObjectID =
          tblADcomputers.ADObjectID
    Where adc.ADObjectID = tblADcomputers.ADObjectID For Xml Path('')), 1, 2,
  '') Groups
From tblADMembership As admem
  Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
  Inner Join tblADcomputers As adc On admem.ChildAdObjectID = adc.ADObjectID
  Inner Join tblADObjects On adc.ADObjectID = tblADObjects.ADObjectID
Group By tblADObjects.domain,
  tblADObjects.samaccountname,
  adc.ADObjectID

[TomFox · Accepted solution]

Hi Mister_Nobody,

Thank you very much for working this out for me.  I modified it slightly to the following, so that I can easily use it as a Subquery, matching on AssetID.

If anybody from Lansweeper staff reads this, are there plans to introduce STRING_AGG() ?
It would turn this whole subquery into a single line....

Select Top 1000000 adc.AssetID,
  tblADObjects.samaccountname,
  Stuff((Select ', ' + tblADGroups.Name As [text()]
    From tblADMembership Inner Join tblADGroups On
          tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
      tblADcomputers On tblADMembership.ChildAdObjectID =
          tblADcomputers.ADObjectID
    Where adc.ADObjectID = tblADcomputers.ADObjectID And
      tblADGroups.Name Like '%_ROLE_%' For Xml Path('')), 1, 2, '') Groups
From tblADMembership As admem
  Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
  Inner Join tblADcomputers As adc On admem.ChildAdObjectID = adc.ADObjectID
  Inner Join tblADObjects On adc.ADObjectID = tblADObjects.ADObjectID
Group By adc.AssetID,
  tblADObjects.samaccountname,
  adc.ADObjectID

[Mister_Nobody]

Some SQL-functions are locked in LS report builder.

CTE is required to expand AD-membership tree but CTE is locked too.