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

Lansweeper Is there a limit to the number of incoming mail domains?
by  DrewT   Go to last post Go to first unread
Last post: Today at 1:33:44 PM(UTC)
Lansweeper License renewal - but why
by  mrusso  
Go to last post Go to first unread
Last post: Yesterday at 5:01:47 PM(UTC)
Lansweeper Deployment Package Error Message
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 2:04:25 PM(UTC)
Lansweeper Asset Type Mail Server
by  MarkPayton  
Go to last post Go to first unread
Last post: Yesterday at 1:03:54 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  Jean-FB   Go to last post Go to first unread
Last post: 10/28/2020 7:34:29 PM(UTC)
Lansweeper Uptime only shows Standby
by  Gst4r  
Go to last post Go to first unread
Last post: 10/28/2020 4:19:33 PM(UTC)
Lansweeper Excepciones
by  Pablo   Go to last post Go to first unread
Last post: 10/27/2020 7:35:21 PM(UTC)
Lansweeper Help desk API
by  Skylar@Hennig  
Go to last post Go to first unread
Last post: 10/27/2020 5:01:18 PM(UTC)