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: 8
0
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,738  
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.

Active Discussions

Lansweeper Top 10 Ticket Types Year To Date
by  LGuth   Go to last post Go to first unread
Last post: 7/10/2019 8:37:48 PM(UTC)
Lansweeper Fonts
by  Spectrum  
Go to last post Go to first unread
Last post: 6/25/2019 11:24:19 AM(UTC)
Action Powershell script for WOL on VLAN
by  psmail   Go to last post Go to first unread
Last post: 5/30/2019 12:00:43 AM(UTC)
Action Schedule Reboot using AT and psshutdown with time input
by  spatchE  
Go to last post Go to first unread
Last post: 5/21/2019 5:35:46 PM(UTC)
Action Open users local Temp directory
by  studerje   Go to last post Go to first unread
Last post: 5/14/2019 7:24:24 PM(UTC)
Lansweeper CMD- CFI MS Update remover 2
by  Rodney Stowell   Go to last post Go to first unread
Last post: 4/18/2019 6:55:01 PM(UTC)
Action Skype User
by  cctech  
Go to last post Go to first unread
Last post: 4/18/2019 4:14:54 PM(UTC)