Managing Volumes Space Thresholds Based On Percentage And Megabytes Values

Version 4

    < BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST

     

    Hi All,

     

    Challenge:

     

    You have a bunch of BIG disks, quite a number of small disks and anything that goes in between those... So... how do you monitor space having just one threshold, "Disk Usage" in %?

    • For example: At 10% 20GB disk will be 2GB left - good threshold to alert on. However, 2TB disk will still have 200GB and in most cases that's way too much to have an alert fired at this level

     

    Solution:

     

    Let's introduce second threshold variable, which will determine threshold in Megabytes. We will then calculate space based on two thresholds. Both of them must be breached at the same time for the event to trigger.

     

    Final result:

    final.JPG

     

    Step-By-Step:

     

    (1)

    I like to be able to change global thresholds easily, without modifying SQL script. So, I placed them into AUX Database. You can do the same, or you can hard code them into SQL.

     

    In AUX DB I have created a table "CustomVariables" where I hold all my variables I use in SQL scripts:

    a1.PNG

    Here is a record for Global threshold in Megabytes:

    a2.PNG

     

    (2)

    Next, head of to ORION SETTINGS > ORION THRESHOLDS and set global native Orion Disk Usage Threshold warning level to what you want this to be in %

    a3.JPG

     

    (3)

    Create two custom properties for your Volumes to override global thresholds on a per disk bases if needed

     

    SETTINGS > MANAGE CUSTOM PROPERTIES > ADD CUSTOM PROPERTY

    a4.JPG

     

    (4)

    Use attached report, save it to your Report Writer and add this as a new resource to your summary page via "Orion Report Writer" resource

     

    ---

    SQL query used in the report is below:

     

    /* ============================================
    Author: Alex Soul
    Date: 23/03/2015
    Description:
      Volumes with High Space Usage
    Version:
      Version | Comments | Date | Author
      1.0 | for Thwack forum | 23/03/15 | AS
    --============================================ */
    
    SELECT
       Nodes.NodeID
      ,Volumes.VolumeID
      ,'warning.gif' AS 'ICON'
      ,Nodes.Caption AS 'NODE' --/Orion/View.aspx?View=NodeDetails&NetObject=N:${NodeID}
      ,Volumes.Caption AS 'VOLUME' --/Orion/View.aspx?View=VolumeDetails&NetObject=V:${VolumeID}
      ,CONVERT(NVARCHAR(50), ROUND(100-Volumes.VolumePercentUsed,2)) + ' %' AS 'FREE,%'
      ,CONVERT(NVARCHAR(50), ROUND(Volumes.VolumeSpaceAvailable/1024/1024,0)) + ' MB' AS 'FREE,MB'
    FROM SolarWinds.dbo.Volumes WITH(NOLOCK)
    
    ---------------------------------------------
    
    INNER JOIN SolarWinds.dbo.Nodes WITH(NOLOCK) ON Nodes.NodeID = Volumes.NodeID
    INNER JOIN SolarWindsAux.dbo.CustomVariables cv_mb WITH(NOLOCK) ON cv_mb.Name = 'DiskWarn_FreeMB'
    INNER JOIN SolarWinds.dbo.Settings s WITH(NOLOCK) ON s.SettingID = 'NetPerfMon-DiskSpace-Warning'
    
    ---------------------------------------------
    
    WHERE
    
      Nodes.Status IN ('1','3') AND -- Node is (1)Up or (3)Warning
      Nodes.UnManaged = 0 AND
      Volumes.VolumeType = 'Fixed Disk' AND
    
      (
        (
          --free space has dropped below configured thresholds
          ((Volumes.VolumeSpaceAvailable/1024/1024) < cv_mb.ValueNumeric) AND
          (Volumes.VolumePercentUsed > s.CurrentValue) AND
          (Volumes.v_ovrd_mb IS NULL) AND
          (Volumes.v_ovrd_prcnt IS NULL)
        ) OR
        (
          --or free space has dropped below BYTES override (if custom PERCENT override has not been set)
          (Volumes.v_ovrd_prcnt IS NULL) AND
          (Volumes.v_ovrd_mb IS NOT NULL) AND
          ((Volumes.VolumeSpaceAvailable/1024/1024) < Volumes.v_ovrd_mb)
        ) OR
        (
          --or free space has dropped below PERCENT override (if custom BYTES override has not been set)
          (Volumes.v_ovrd_prcnt IS NOT NULL) AND
          (Volumes.v_ovrd_mb IS NULL) AND
          (Volumes.VolumePercentUsed > (100 - Volumes.v_ovrd_prcnt))
        ) OR
        (
          --or free space has dropped below both PERCENT and BYTES values for custom set overrides
          (Volumes.v_ovrd_mb IS NOT NULL) AND
          (Volumes.v_ovrd_prcnt IS NOT NULL) AND
          ((Volumes.VolumeSpaceAvailable/1024/1024) < Volumes.v_ovrd_mb) AND
          (Volumes.VolumePercentUsed > (100 - Volumes.v_ovrd_prcnt))
        )
      )
    
    
    
    

     

     

    Future improvements:

     

    • At the moment all events are treated as "warning". By fiddling with script and thresholds you may upgrade this to be able to differentiate between warning/critical states based on different values for thresholds. I decided not to complicate things for now and act on all of these alerts as if they are critical. The other option might be just duplicating this solution and updating critical thresholds within script - so, you will end-up with two scripts - one for warning and another one for critical events

     

    To Your Monitoring Success,

    Alex Soul

    www.pixace.com