cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Xaerie
Engaged Sweeper
I'm using this report to show me all our windows servers and their used disk size. The sizes show from independent disks. The only way to consolidate is to export to excel and consolidate and sum the data - but I was thinking something similar could be done in lansweeper.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Serialnumber As [Service Tag Number],
tsysAssetTypes.AssetTypename,
tsysOS.OSname,
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
tblDiskdrives.FileSystem,
Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],
Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],
Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10,
9)) As [Used (GB)],
Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size *
100 As Decimal(8,2)) As [Pct Used],
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDomainroles.Domainrolename
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole =
tblComputersystem.Domainrole
Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID And
tblDiskdrives.DriveType = 3
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblAssetCustom.State = 1 And tblComputersystem.Domainrole > 1
Order By tblAssets.AssetUnique,
tblDiskdrives.Caption
1 REPLY 1
RCorbeil
Honored Sweeper II
I've left the original code, commented out, so you can see what's been changed/replaced. The LANSweeper report editor should automatically eliminate the commented-out bits.
Select Top 1000000
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssetCustom.Manufacturer As Vendor,
tblAssetCustom.Serialnumber As [Service Tag Number],
tsysAssetTypes.AssetTypename,
tsysOS.OSname,
/*
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
tblDiskdrives.FileSystem,
Ceiling(tblDiskdrives.Size / Power(10, 9)) As [Capacity (GB)],
Ceiling(tblDiskdrives.Freespace / Power(10, 9)) As [Free (GB)],
Ceiling((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10, 9)) As [Used (GB)],
Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 As Decimal(8,2)) As [Pct Used],
*/
CombinedDrives.DriveCount AS [Number of Drives],
Ceiling(CombinedDrives.Size / Power(10, 9)) As [Combined Capacity (GB)],
Ceiling(CombinedDrives.Freespace / Power(10, 9)) As [Free (GB)],
Ceiling((CombinedDrives.Size - CombinedDrives.Freespace) / Power(10, 9)) As [Used (GB)],
Cast((CombinedDrives.Size - CombinedDrives.Freespace) / CombinedDrives.Size * 100 As Decimal(8,2)) As [Pct Used],

tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDomainroles.Domainrolename
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblDomainroles On tblDomainroles.Domainrole = tblComputersystem.Domainrole
-- Inner Join tblDiskdrives On tblDiskdrives.AssetID = tblAssets.AssetID And tblDiskdrives.DriveType = 3
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode

Inner Join (SELECT
tblDiskdrives.AssetID,
Count(*) AS DriveCount,
Sum(tblDiskdrives.Size) AS Size,
Sum(tblDiskdrives.Freespace) AS Freespace
FROM
tblDiskdrives
WHERE
tblDiskdrives.DriveType = 3
GROUP BY
tblDiskdrives.AssetID
) AS CombinedDrives ON CombinedDrives.AssetID = tblAssets.AssetID

Where
tblAssetCustom.State = 1
And tblComputersystem.Domainrole > 1

Order By
tblAssets.AssetUnique /*,
tblDiskdrives.Caption */