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
3
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,982  
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

Action Find lost space the easy way (spacesniffer.exe)
by  krozar   Go to last post Go to first unread
Last post: 5/5/2020 10:27:33 PM(UTC)
Lansweeper Querying a web server or web services
by  mmo  
Go to last post Go to first unread
Last post: 5/5/2020 9:00:36 PM(UTC)
Action Password Status
by  brodiemac-too   Go to last post Go to first unread
Last post: 4/23/2020 9:01:03 PM(UTC)
Action Email user
by  Michael Kop  
Go to last post Go to first unread
Last post: 4/7/2020 11:24:12 AM(UTC)
Lansweeper actualizacion de datos importando csv
by  jhon freddy   Go to last post Go to first unread
Last post: 3/31/2020 9:04:45 PM(UTC)
Lansweeper Asset Actions - RunAs Prompt
by  Socal_s197  
Go to last post Go to first unread
Last post: 3/26/2020 10:02:16 PM(UTC)
Lansweeper Remote Registry 2019
by  CyberCitizen   Go to last post Go to first unread
Last post: 3/6/2020 12:25:39 AM(UTC)
Lansweeper Installation Parameters
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 3/3/2020 10:17:18 AM(UTC)