This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject

→ 🚀What's New? Join Us for the Fall Product Launch! Register Now !

Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

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

alexwong85
Engaged Sweeper II

‎02-11-2020 07:39 AM

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
Labels:
0 Kudos
2 REPLIES 2
RCorbeil
Honored Sweeper II

‎02-11-2020 08:47 PM

Per the database documentation, you'll want to filter the drives for only local hard drive.

From the docs:
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:
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):
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
alexwong85
Engaged Sweeper II
In response to RCorbeil

‎02-13-2020 07:27 AM

Dear RC62N,
Thank you very much. It is working perfectly. Appreciate your assistance on this.

RC62N wrote:
Per the database documentation, you'll want to filter the drives for only local hard drive.

From the docs:
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:
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):
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


0 Kudos

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now