‎07-23-2019 07:13 PM
‎07-25-2019 05:30 PM
Numeric value that corresponds to the type of disk drive this logical disk represents.
Value Meaning
0 Unknown
1 No Root Directory
2 Removable Disk
3 Local Disk
4 Network Drive
5 Compact Disc
6 RAM Disk
‎07-25-2019 05:23 PM
‎07-25-2019 03:25 PM
‎07-24-2019 04:22 PM
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
SQLServers.displayVersion As [SQL Server version],
tblDiskdrives.Caption,
tblDiskdrives.Volumename,
Cast(tblDiskdrives.Size / Power(10, 9) As Decimal(8,2)) As [GB],
Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / Power(10, 9) As Decimal(8,2)) As [GB used],
Cast(tblDiskdrives.Freespace / Power(10, 9) As Decimal(8,2)) As [GB free],
Cast((tblDiskdrives.Size - tblDiskdrives.Freespace) / tblDiskdrives.Size * 100 As Decimal(8,2)) As [Used %],
Cast(tblDiskdrives.Freespace / tblDiskdrives.Size * 100 As Decimal(8,2)) As [Free %]
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (SELECT Distinct tblSqlServers.AssetID, tblSqlServers.displayVersion FROM tblSqlServers) AS SQLServers On tblAssets.AssetID = SQLServers.AssetID
Inner Join tblDiskdrives ON tblDiskdrives.AssetID = tblAssets.AssetID AND tblDiskdrives.DriveType = 3
Where
tblAssetCustom.State = 1
Order by
tblAssets.AssetName,
[SQL Server version],
tblDiskdrives.Caption
Select Top 1000000
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Lastseen,
SQLServers.displayVersion As [SQL Server version],
Cast(c_drive.Size / Power(10, 9) As Decimal(8,2)) As [C GB],
Cast((c_drive.Size - c_drive.Freespace) / Power(10, 9) As Decimal(8,2)) As [C used],
Cast(c_drive.Freespace / Power(10, 9) As Decimal(8,2)) As [C free],
Cast((c_drive.Size - c_drive.Freespace) / c_drive.Size * 100 As Decimal(8,2)) As [C used %],
Cast(c_drive.Freespace / c_drive.Size * 100 As Decimal(8,2)) As [C free %],
Cast(d_drive.Size / Power(10, 9) As Decimal(8,2)) As [D GB],
Cast((d_drive.Size - d_drive.Freespace) / Power(10, 9) As Decimal(8,2)) As [D used],
Cast(d_drive.Freespace / Power(10, 9) As Decimal(8,2)) As [D free],
Cast((d_drive.Size - d_drive.Freespace) / d_drive.Size * 100 As Decimal(8,2)) As [D used %],
Cast(d_drive.Freespace / d_drive.Size * 100 As Decimal(8,2)) As [D free %],
Cast(e_drive.Size / Power(10, 9) As Decimal(8,2)) As [E GB],
Cast((e_drive.Size - e_drive.Freespace) / Power(10, 9) As Decimal(8,2)) As [E used],
Cast(e_drive.Freespace / Power(10, 9) As Decimal(8,2)) As [E free],
Cast((e_drive.Size - e_drive.Freespace) / e_drive.Size * 100 As Decimal(8,2)) As [E used %],
Cast(e_drive.Freespace / e_drive.Size * 100 As Decimal(8,2)) As [E free %]
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join (SELECT Distinct tblSqlServers.AssetID, tblSqlServers.displayVersion FROM tblSqlServers) AS SQLServers On tblAssets.AssetID = SQLServers.AssetID
Left Join tblDiskdrives AS c_drive On tblAssets.AssetID = c_drive.AssetID And c_drive.Caption = 'C:' AND c_drive.DriveType = 3
Left Join tblDiskdrives AS d_drive On tblAssets.AssetID = d_drive.AssetID And d_drive.Caption = 'D:' AND d_drive.DriveType = 3
Left Join tblDiskdrives AS e_drive On tblAssets.AssetID = e_drive.AssetID And e_drive.Caption = 'E:' AND e_drive.DriveType = 3
Where
tblAssetCustom.State = 1
Order by
tblAssets.AssetName,
[SQL Server version]
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now