tblADGroups OR tblADMembership do not show all AD memberships of PC

[lunesolitaire]

Hello,

I created a report to display the desktops in an Active Directory group that have less than 10% free disk space, referencing a Lansweeper report titled "Windows: Workstations less than 10% free disk space".

Everything seemed fine at first, but I realized that the database tables tblADGroups and/or tblADMembership don't contain all the "Memberships" records, or they contain records with tblADGroups.Name that are empty.

It's also strange that, for a given PC in the screenshot,  some AD groups are there and others aren't in the output.

Do you have any idea if this is a known bug? Or should I do it differently?

Merci!  

Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblADGroups.Name As AD_Group_Membership,
  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 As [Last successful scan],
  tblAssets.Lasttried As [Last scan attempt],
  indirect.lastIndirectScan As [Last indirect scan],
  tblDiskdrives.Caption As Drive,
  Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
  Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
  Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
    When 0 Then 1
    Else tblDiskdrives.Size
  End) * 100) As [%SpaceLeft],
  tblDiskdrives.Lastchanged
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblDomainroles On tblDomainroles.Domainrole =
      tblComputersystem.Domainrole
  Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join (Select Max(tblIndirectScan.LastChanged) As lastIndirectScan,
      tblIndirectScan.AssetId
    From tblIndirectScan
    Group By tblIndirectScan.AssetId) indirect On tblAssetCustom.AssetId =
      indirect.AssetId
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join tblADMembership On tblADComputers.ADObjectID =
      tblADMembership.ChildAdObjectID
  Left Join tblADGroups On tblADMembership.ParentAdObjectID =
      tblADGroups.ADGroupID
Where Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) <> 0 And
  Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
    When 0 Then 1
    Else tblDiskdrives.Size
  End) * 100) < 10 And tblState.Statename = 'Active' And
  tblDomainroles.Domainrolename In ('Stand-alone workstation',
  'Member workstation') And Case tblDiskdrives.DriveType
    When 3 Then 'Local Disk'
  End = 'Local Disk'
Order By tblAssets.Domain,
  tblAssets.AssetName,
  Drive

 Merci!

[lunesolitaire]

Note: Some of my PCs belong to 15 or more AD GROUPs.

[Mister_Nobody]

Try this (AI Generated, LS 12.5+):

With DiskInfo As (Select tblDiskdrives.AssetID,
      tblDiskdrives.Caption As Drive,
      Cast(tblDiskdrives.Freespace / 1024.0 / 1024 / 1024 As numeric) As FreeGB,
      Cast(tblDiskdrives.Size / 1024.0 / 1024 / 1024 As numeric) As TotalSizeGB,
      Ceiling(tblDiskdrives.Freespace * 100.0 / NullIf(tblDiskdrives.Size,
      0)) As [%SpaceLeft],
      tblDiskdrives.Lastchanged
    From tblDiskdrives
    Where Cast(tblDiskdrives.Size / 1024.0 / 1024 / 1024 As numeric) <> 0 And
      Ceiling(tblDiskdrives.Freespace * 100.0 / NullIf(tblDiskdrives.Size, 0)) <
      10 And tblDiskdrives.DriveType = 3),
  ComputerInfo As (Select a.AssetID,
      a.AssetName,
      a.Domain,
      a.Username,
      a.Userdomain,
      a.IPAddress,
      a.SP,
      a.Lastseen As [Last successful scan],
      a.Lasttried As [Last scan attempt],
      Coalesce(os.Image, ast.AssetTypeIcon10) As icon,
      loc.IPLocation,
      ac.Manufacturer,
      ac.Model,
      os.OSname As OS,
      cs.Domainrole,
      indirect.lastIndirectScan As [Last indirect scan]
    From tblAssets a
      Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
      Inner Join tblState s On ac.State = s.State
      Inner Join tblComputersystem cs On a.AssetID = cs.AssetID
      Inner Join tsysAssetTypes ast On a.Assettype = ast.AssetType
      Inner Join tsysIPLocations loc On a.LocationID = loc.LocationID
      Inner Join tblDomainroles dr On cs.Domainrole = dr.Domainrole
      Left Join tsysOS os On a.OScode = os.OScode
      Left Join (Select tblIndirectScan.AssetId,
          Max(tblIndirectScan.LastChanged) As lastIndirectScan
        From tblIndirectScan
        Group By tblIndirectScan.AssetId) indirect On
          ac.AssetId = indirect.AssetId
    Where s.Statename = 'Active' And
      dr.Domainrolename In ('Stand-alone workstation', 'Member workstation')),
  GroupMembership As (Select adc.AssetID,
      Stuff((Select ', ' + g.Name
        From tblADMembership m Inner Join tblADGroups g On m.ParentAdObjectID =
              g.ADObjectID
        Where m.ChildAdObjectID = adc.ADObjectID For Xml Path(''),
        Type).value('.', 'NVARCHAR(MAX)'), 1, 2, '') As AD_Group_Membership
    From tblADComputers adc
    Where adc.ADObjectID Is Not Null)
Select Top (1000000) ci.AssetID,
  ci.AssetName,
  gm.AD_Group_Membership,
  ci.Domain,
  ci.Username,
  ci.Userdomain,
  ci.icon,
  ci.IPAddress,
  ci.IPLocation,
  ci.Manufacturer,
  ci.Model,
  ci.OS,
  ci.SP,
  ci.[Last successful scan],
  ci.[Last scan attempt],
  ci.[Last indirect scan],
  di.Drive,
  di.FreeGB,
  di.TotalSizeGB,
  di.[%SpaceLeft],
  di.Lastchanged
From ComputerInfo ci
  Inner Join DiskInfo di On ci.AssetID = di.AssetID
  Left Join GroupMembership gm On ci.AssetID = gm.AssetID
Order By ci.Domain,
  ci.AssetName,
  di.Drive