Low Hard Disk Space (coloured) - Win+Linux

[AndyIps]

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

[OlivierJ]

Same issue with converting navcar to numeric...
LS7.11, SQL SRVR

[Esben_D]

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]

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.

[AuxiantAndy]

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],