SQL Drive Space report

[jstrong71]

I have recently been asked to build a report that will show all of our SQL servers and their drives (volumes) and if they are running low on space. I can generate a low drive space report, and can generate a report that shows all SQL servers. I am having a difficult time integrating the two. Has anyone done something like this?

Any help would be greatly appreciated.

Jim

[RCorbeil]

If you check the database documentation on tblDiskdrives, you'll find the following for DriveType:

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

My inventory only has types 2, 3 and 5 on our SQL servers, so the example queries are filtering for only type 3 since I have no interest in seeing optical drives or removable drives in the results. You may need to change the "DriveType = 3" to "DriveType IN (3, 4)" if you you have network drives connected.

[RCorbeil]

I'm pretty sure LANSweeper only records the locally-connected drives for Windows machines.

[jstrong71]

These are amazing, is there a way to break it out to see the specific volumes with a mountpoint? Or will it only see the mount point drive itself?

[RCorbeil]

Try this:

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

If you'd prefer to spell out specific drives and flatten the report, you can join the drives per-drive.

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]