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,822  
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 Drive Encryption statuses
by  JacobH   Go to last post Go to first unread
Last post: Today at 5:41:10 PM(UTC)
Report Center Local admin group report based on domain role
by  JacobH  
Go to last post Go to first unread
Last post: Today at 5:24:13 PM(UTC)
Lansweeper Number of scanned IPs
by  wayneRex   Go to last post Go to first unread
Last post: Today at 10:21:01 AM(UTC)
Lansweeper Ticket Summary by Agent?
by  susan.starr  
Go to last post Go to first unread
Last post: Yesterday at 4:41:26 PM(UTC)
Report Center Virtual machines and their host
by  klaus   Go to last post Go to first unread
Last post: Yesterday at 8:44:57 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D  
Go to last post Go to first unread
Last post: 9/16/2019 1:38:33 PM(UTC)
Lansweeper Query on 'Assets: Installed software by computer' Report
by  RC62N   Go to last post Go to first unread
Last post: 9/12/2019 5:12:40 PM(UTC)
Lansweeper Adding Comments
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 9/10/2019 7:39:40 PM(UTC)