Smarter Volume Capacity Forecast SWQL alert

jest4kicks

Hey all,

I just finished working this up and wanted to share.

Objective: Update the Out-of-box Volume Capacity Forecast alert to allow for volume alert muting and known net-snmp size reporting issues.

For Reference: Here's the Out-of-box alert.

SELECT VolumesForecastCapacity.Uri, VolumesForecastCapacity.DisplayName FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

WHERE

  VolumesForecastCapacity.InstanceCaption != 'Virtual Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Physical Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Real Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Memory Buffers'

  AND VolumesForecastCapacity.InstanceCaption != 'Cached Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Shared Virtual Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Shared Real Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'swap space'

  AND VolumesForecastCapacity.DaysToCapacityAvg <= 60

  AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

  AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

My Version:  I had to include two additional joins to pull in volume size data and custom properties.  "AlertSuppression_Vol" is our method of alert muting, inspired by adatole​ in this write-up: TIPS &amp; TRICKS: Stop the madness! Avoiding alerts but continuing to pull statistics.​  Instead of multiple mute custom properties though, we use one or more character strings in the single suppression field.  Next, I wanted to work around the known issue with net-snmp misreporting volume usage over 2 TB.  This should prevent any linux volumes (/%) that are over 2 TB in size from triggering this alert.

SELECT VolumesForecastCapacity.Uri, VolumesForecastCapacity.DisplayName FROM Orion.VolumesForecastCapacity AS VolumesForecastCapacity

LEFT JOIN Orion.Settings s ON s.SettingID = 'ForecastMinDays'

LEFT JOIN Orion.VolumesCustomProperties p ON p.VolumeID = VolumesForecastCapacity.InstanceID

LEFT JOIN Orion.Volumes v ON v.VolumeID = VolumesForecastCapacity.InstanceID

WHERE

  ISNULL(p.AlertSuppression_Vol, '0None') NOT LIKE '%6VF%'

  AND ((v.Caption LIKE '/%' AND v.VolumeSize < '2199023255552') OR (v.Caption NOT LIKE '/%'))

AND VolumesForecastCapacity.InstanceCaption != 'Virtual Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Physical Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Real Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Memory Buffers'

  AND VolumesForecastCapacity.InstanceCaption != 'Cached Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Shared Virtual Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'Shared Real Memory'

  AND VolumesForecastCapacity.InstanceCaption != 'swap space'

  AND VolumesForecastCapacity.DaysToCapacityAvg <= 60

  AND VolumesForecastCapacity.MetricName = 'Forecast.Metric.PercentDiskUsed'

  AND DayDiff(VolumesForecastCapacity.MinDateTime, VolumesForecastCapacity.MaxDateTime) >= ISNULL(s.CurrentValue, 7)

Initial results are promising, but I'm open to comments and suggestions!

Thanks!

dkobrosly

Has anyone ran into a situation where the days left to capacity field is returning negative values? I've been monitoring some servers for the past year or so in our Solarwinds instance and noticed this alert gets triggered because the VolumesForecastCapacity.DaysToCapacityAvg field is negative. I noticed the daystocapacitypeak, daystocriticalavg, daystocriticalpeak

VolumesForecastCapacity.DaysToCapacityAvg <= 60