cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
alexwong85
Engaged Sweeper II
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
2 REPLIES 2
RCorbeil
Honored Sweeper II
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
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