cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jstrong71
Engaged Sweeper II
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
4 REPLIES 4
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
I'm pretty sure LANSweeper only records the locally-connected drives for Windows machines.
jstrong71
Engaged Sweeper II
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
Honored Sweeper II
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]

New to Lansweeper?

Try Lansweeper For Free

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

Try Now