Notification

Icon
Error

Adding Group by and Sum to Existing Report

Posted: Thursday, September 24, 2020 8:18:54 PM(UTC)
Xaerie

Xaerie

Member Original PosterPosts: 4
0
Like
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
RC62N
#1RC62N Member Posts: 479  
posted: 9/25/2020 3:43:49 PM(UTC)
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.
Code:
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 */

Active Discussions

Installer Deploy SolarWinds Take Control Agent
by  Corey Lambert   Go to last post Go to first unread
Last post: 8/6/2020 2:23:22 PM(UTC)
Installer Microsoft Edge (Chromium) 84.0.522.44
by  PLSJohnJohn   Go to last post Go to first unread
Last post: 7/24/2020 10:22:19 PM(UTC)
Installer Windows 10 Upgrade
by  CyberCitizen  
Go to last post Go to first unread
Last post: 7/8/2020 1:07:47 AM(UTC)
Installer Installer - Microsoft Office
by  Florian_Eigsi   Go to last post Go to first unread
Last post: 6/29/2020 3:33:20 PM(UTC)
Installer Firefox ESR 68.8.0 MSI Installer
by  PLSJohnJohn  
Go to last post Go to first unread
Last post: 5/11/2020 4:33:50 PM(UTC)
Installer Update Chrome browser with GoogleUpdate.exe ?
by  Inna Ptushkina   Go to last post Go to first unread
Last post: 5/4/2020 8:39:29 PM(UTC)
Installer Bios Update for Dell all in one
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 4/2/2020 11:36:43 AM(UTC)