Notification

Icon
Error

A report to show Hard Drive (free space & total) on GB size

Posted: Tuesday, February 11, 2020 7:39:19 AM(UTC)
Alex Wong

Alex Wong

Member Original PosterPosts: 2
0
Like
Dear All,
I have tried to generate a report with queries below but failed to get the Hard Drive size on xxxGB. Requesting your assistance to help generate a report that specifies queries below. Thanks.

1. Freespace of all HardDrive in GB
2. Size of all HardDrive in GB
---

AssetName|Domain|UserName|UserDomain|IPAddress|Manufacture|Model|OS|SP|Processor|Memory|Mac|Scanserver|Freespace of all HardDrive in GB|Size of all HardDrive in GB

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Processor,
tblAssets.Memory,
tblAssets.Mac,
tblAssets.Scanserver,
tblDiskdrives.Freespace,
tblDiskdrives.Size
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Where (tblAssets.IPNumeric >= 192018064001 And tblAssets.IPNumeric <=
192018095254) Or
(tblAssets.IPNumeric >= 192018192001 And tblAssets.IPNumeric <= 192018197254)
Or
(tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Or
(tblAssets.IPNumeric >= 192018004001 And tblAssets.IPNumeric <= 192018005254)
Or
(tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Order By tblAssets.Domain,
tblAssets.AssetName
RC62N
#1RC62N Member Posts: 430  
posted: 2/11/2020 8:47:18 PM(UTC)
Per the database documentation, you'll want to filter the drives for only local hard drive.

From the docs:
Code:
Value Meaning
  0   Unknown
  1   No Root Directory
  2   Removable Disk
  3   Local Disk
  4   Network Drive
  5   Compact Disc
  6   RAM Disk

You don't mention whether you're after advertised gigabytes (1000^3) or computer nerd gigabytes (1024^3), aka gibibytes, so I've included both.

Assuming it's a detailed list of drives you're after:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssets.Mac,
  tblAssets.Scanserver,
    -- computer nerds define a gigabyte as 1024*1024*1024 = 1,073,741,824
    -- hard drive manufacturers define a gigabyte as 1000*1000*1000 = 1,000,000,000 bytes
    -- they redefine the nerdy version as a gibibyte
  tblDiskdrives.Freespace,
  Floor(tblDiskdrives.Freespace/Power(2, 30)) AS FreeGiB,   -- or Power(1024, 3)
  Floor(tblDiskdrives.Freespace/Power(10, 9)) AS FreeGB,    -- or Power(1000, 3)
  tblDiskdrives.Size,
  Floor(tblDiskdrives.Size/Power(2, 30)) AS SizeGiB,
  Floor(tblDiskdrives.Size/Power(10, 9)) AS SizeGB
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID AND tblDiskDrives.DriveType = 3
Where
  (tblAssets.IPNumeric >= 192018064001 And tblAssets.IPNumeric <= 192018095254)
  Or (tblAssets.IPNumeric >= 192018192001 And tblAssets.IPNumeric <= 192018197254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
  Or (tblAssets.IPNumeric >= 192018004001 And tblAssets.IPNumeric <= 192018005254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Order By
  tblAssets.Domain,
  tblAssets.AssetName

If it's just a total of all drives you're after (I don't ask why; some people have asked after it):
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssets.Mac,
  tblAssets.Scanserver,
  Drives.TotalDrives,   -- in case you want to know if it's for more than one drive
    -- computer nerds define a gigabyte as 1024*1024*1024 = 1,073,741,824 bytes
    -- hard drive manufacturers define a gigabyte as 1000*1000*1000 = 1,000,000,000 bytes
    -- they redefine the nerdy version as a gibibyte
  Drives.TotalFree,
  Floor(Drives.TotalFree / Power(2, 30)) AS TotalFreeGiB,  -- or Power(1024, 3)
  Floor(Drives.TotalFree / Power(10, 9)) AS TotalFreeGB,   -- or Power(1000, 3)
  Drives.TotalSize,
  Floor(Drives.TotalSize / Power(2, 30)) AS TotalSizeGiB,
  Floor(Drives.TotalSize / Power(10, 9)) AS TotalSizeGB
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  INNER JOIN (SELECT
                AssetID,
                Count(tblDiskDrives.diskID) AS TotalDrives,  -- optional; not requested
                Sum(tblDiskdrives.Size) AS TotalSize,
                Sum(tblDiskDrives.Freespace) AS TotalFree
              FROM tblDiskdrives
              WHERE DriveType = 3  -- local disks
              GROUP BY AssetID) AS Drives ON Drives.AssetID = tblAssets.AssetID
Where
  (tblAssets.IPNumeric >= 192018064001 And tblAssets.IPNumeric <= 192018095254)
  Or (tblAssets.IPNumeric >= 192018192001 And tblAssets.IPNumeric <= 192018197254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
  Or (tblAssets.IPNumeric >= 192018004001 And tblAssets.IPNumeric <= 192018005254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Order By
  tblAssets.Domain,
  tblAssets.AssetName
Alex Wong
#2Alex Wong Member Original PosterPosts: 2  
posted: 2/13/2020 7:27:41 AM(UTC)
Dear RC62N,
Thank you very much. It is working perfectly. Appreciate your assistance on this.

Originally Posted by: RC62N Go to Quoted Post
Per the database documentation, you'll want to filter the drives for only local hard drive.

From the docs:
Code:
Value Meaning
  0   Unknown
  1   No Root Directory
  2   Removable Disk
  3   Local Disk
  4   Network Drive
  5   Compact Disc
  6   RAM Disk

You don't mention whether you're after advertised gigabytes (1000^3) or computer nerd gigabytes (1024^3), aka gibibytes, so I've included both.

Assuming it's a detailed list of drives you're after:
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssets.Mac,
  tblAssets.Scanserver,
    -- computer nerds define a gigabyte as 1024*1024*1024 = 1,073,741,824
    -- hard drive manufacturers define a gigabyte as 1000*1000*1000 = 1,000,000,000 bytes
    -- they redefine the nerdy version as a gibibyte
  tblDiskdrives.Freespace,
  Floor(tblDiskdrives.Freespace/Power(2, 30)) AS FreeGiB,   -- or Power(1024, 3)
  Floor(tblDiskdrives.Freespace/Power(10, 9)) AS FreeGB,    -- or Power(1000, 3)
  tblDiskdrives.Size,
  Floor(tblDiskdrives.Size/Power(2, 30)) AS SizeGiB,
  Floor(tblDiskdrives.Size/Power(10, 9)) AS SizeGB
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID AND tblDiskDrives.DriveType = 3
Where
  (tblAssets.IPNumeric >= 192018064001 And tblAssets.IPNumeric <= 192018095254)
  Or (tblAssets.IPNumeric >= 192018192001 And tblAssets.IPNumeric <= 192018197254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
  Or (tblAssets.IPNumeric >= 192018004001 And tblAssets.IPNumeric <= 192018005254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Order By
  tblAssets.Domain,
  tblAssets.AssetName

If it's just a total of all drives you're after (I don't ask why; some people have asked after it):
Code:
Select Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Processor,
  tblAssets.Memory,
  tblAssets.Mac,
  tblAssets.Scanserver,
  Drives.TotalDrives,   -- in case you want to know if it's for more than one drive
    -- computer nerds define a gigabyte as 1024*1024*1024 = 1,073,741,824 bytes
    -- hard drive manufacturers define a gigabyte as 1000*1000*1000 = 1,000,000,000 bytes
    -- they redefine the nerdy version as a gibibyte
  Drives.TotalFree,
  Floor(Drives.TotalFree / Power(2, 30)) AS TotalFreeGiB,  -- or Power(1024, 3)
  Floor(Drives.TotalFree / Power(10, 9)) AS TotalFreeGB,   -- or Power(1000, 3)
  Drives.TotalSize,
  Floor(Drives.TotalSize / Power(2, 30)) AS TotalSizeGiB,
  Floor(Drives.TotalSize / Power(10, 9)) AS TotalSizeGB
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  INNER JOIN (SELECT
                AssetID,
                Count(tblDiskDrives.diskID) AS TotalDrives,  -- optional; not requested
                Sum(tblDiskdrives.Size) AS TotalSize,
                Sum(tblDiskDrives.Freespace) AS TotalFree
              FROM tblDiskdrives
              WHERE DriveType = 3  -- local disks
              GROUP BY AssetID) AS Drives ON Drives.AssetID = tblAssets.AssetID
Where
  (tblAssets.IPNumeric >= 192018064001 And tblAssets.IPNumeric <= 192018095254)
  Or (tblAssets.IPNumeric >= 192018192001 And tblAssets.IPNumeric <= 192018197254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
  Or (tblAssets.IPNumeric >= 192018004001 And tblAssets.IPNumeric <= 192018005254)
  Or (tblAssets.IPNumeric >= 192024066001 And tblAssets.IPNumeric <= 192024066254)
Order By
  tblAssets.Domain,
  tblAssets.AssetName


Active Discussions

Lansweeper Find machines missing any 1 of several apps
by  BennettL   Go to last post Go to first unread
Last post: Yesterday at 8:35:28 PM(UTC)
Lansweeper Max Software Version and Deploy to Only Outdated
by  GenDev   Go to last post Go to first unread
Last post: Yesterday at 1:06:50 PM(UTC)
Lansweeper Patch Tuesday - Superseded MS Updates
by  doone128  
Go to last post Go to first unread
Last post: Yesterday at 11:27:10 AM(UTC)
Lansweeper Report on basic Router information
by  Liljack  
Go to last post Go to first unread
Last post: 3/30/2020 7:44:22 AM(UTC)
Lansweeper Windows: Static IP address configured (Built-in)
by  RobinPSU   Go to last post Go to first unread
Last post: 3/27/2020 8:52:12 PM(UTC)
Lansweeper Adding top user to asset report
by  Argon0  
Go to last post Go to first unread
Last post: 3/26/2020 5:20:51 PM(UTC)