Notification

Icon
Error

Low Hard Disk Space (coloured) - Win+Linux

Posted: Friday, November 16, 2018 12:06:12 PM(UTC)
AndyIps

AndyIps

Member Original PosterPosts: 9
1
Like
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.


Code:
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

tcilmo
#1tcilmo Member Posts: 25  
posted: 1/4/2019 8:39:31 PM(UTC)
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.
Esben.D
#2Esben.D Member Administration Posts: 1,977  
posted: 1/7/2019 3:28:54 PM(UTC)
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.
Olivier J.
#3Olivier J. Member Posts: 3  
posted: 8/23/2019 4:16:02 PM(UTC)
Same issue with converting navcar to numeric...
LS7.11, SQL SRVR

Active Discussions

Lansweeper Report on clients with no LSAgent
by  RobertB   Go to last post Go to first unread
Last post: Yesterday at 6:55:32 PM(UTC)
Lansweeper Lansweeper report to match computer name
by  RC62N   Go to last post Go to first unread
Last post: 2/19/2020 5:02:27 PM(UTC)
Lansweeper Distinct user logins per PC
by  Bruce Garoutte   Go to last post Go to first unread
Last post: 2/17/2020 6:36:29 PM(UTC)
Lansweeper Software version report issue
by  RC62N  
Go to last post Go to first unread
Last post: 2/17/2020 4:07:20 PM(UTC)
Lansweeper Patch Tuesday - Exclude Win 2008 & Win7
by  doone128   Go to last post Go to first unread
Last post: 2/17/2020 1:34:36 PM(UTC)
Lansweeper Performance statistics combined into one report
by  djs1789  
Go to last post Go to first unread
Last post: 2/15/2020 1:50:32 AM(UTC)