Adding Group by and Sum to Existing Report

[Xaerie]

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

[RCorbeil]

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 */