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,
DriveMerci!
