→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here
‎02-11-2020 07:39 AM
‎02-11-2020 08:47 PM
Value Meaning
0 Unknown
1 No Root Directory
2 Removable Disk
3 Local Disk
4 Network Drive
5 Compact Disc
6 RAM Disk
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
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
‎02-13-2020 07:27 AM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now