cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
AndyIps
Engaged Sweeper II
Hi,

I thought I'd share this report. We don't have Macs, so not sure if this works for them, but this is basically a low disk warning for any Windows drive or Linux partition. Currently Lansweeper only provides a Windows low disk report, and you can hunt around on this forum for a Linux one. This is a combination of those, with colour coding.

Set your thresholds for amber/red in the CASE at the top, and the cutoff point for reporting in the WHERE at the bottom.


Select Top 1000000 *,
Case
When Drives.[%SpaceLeft] < 11 Then '#f7caca'
When Drives.[%SpaceLeft] <= 15 Then '#f7f0ca'
Else '#ffffff'
End As backgroundcolor
From (Select tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblDiskdrives.Caption As [Drive/Filesystem],
Cast(tblDiskdrives.Freespace / 1024 / 1024 / 1024 As numeric) As FreeGB,
Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As TotalSizeGB,
Ceiling(tblDiskdrives.Freespace / (Case tblDiskdrives.Size
When 0 Then 1
Else tblDiskdrives.Size
End) * 100) As [%SpaceLeft],
Null As [Linux Mount Point]
From tblAssets
Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
Inner Join tblComputersystem On
tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType =
tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblState.Statename = 'Active' And Case tblDiskdrives.DriveType
When 3 Then 'Local Disk'
End = 'Local Disk'
Union
Select tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblLinuxSystem.OSRelease,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblLinuxHardDisks.Filesystem As [Drive/Filesystem],
(100 - Cast(Replace(tblLinuxHardDisks.Percentage, '%', '') As bigint)) *
Cast(tblLinuxHardDisks.Size As bigint) / 100 As FreeGB,
Cast(tblLinuxHardDisks.Size As numeric) As TotalSizeGB,
100 - Cast(Replace(tblLinuxHardDisks.Percentage, '%', '') As numeric) As
[%SpaceLeft],
tblLinuxHardDisks.MountedOn As [Linux Mount Point]
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
Inner Join tblLinuxSystem On tblLinuxSystem.AssetID = tblAssets.AssetID
Inner Join tblLinuxHardDisks On tblLinuxHardDisks.AssetID =
tblAssets.AssetID
Where tblState.Statename = 'Active') Drives
Where Drives.[%SpaceLeft] <= 15
Order By Drives.[%SpaceLeft],
Drives.AssetName

4 REPLIES 4
OlivierJ
Engaged Sweeper II
Same issue with converting navcar to numeric...
LS7.11, SQL SRVR
Esben_D
Lansweeper Employee
Lansweeper Employee
It works for me on an SQL Server installation, are you using SQL Compact? That might be the cause. Otherwise maybe an incomplete copy/paste.
tcilmo
Engaged Sweeper II
Tried to use this report, but got the following error.


Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

Use try_cast.
In Sql Server 2012 and up it will return null when the conversion fails instead of an error if the value isn't numeric.

tblLinuxHardDisks.Filesystem As [Drive/Filesystem],
(100 - Try_Cast(Replace(tblLinuxHardDisks.Percentage, '%', '') As bigint)) *
Try_Cast(tblLinuxHardDisks.Size As bigint) / 100 As FreeGB,
Try_Cast(tblLinuxHardDisks.Size As numeric) As TotalSizeGB,
100 - Try_Cast(Replace(tblLinuxHardDisks.Percentage, '%', '') As numeric) As
[%SpaceLeft],