cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Is it possible to create an alert based on disk space usage change percentage?

Jump to solution

I am trying to figure out a way to alert on say the % disk space available but only create the alert if it has changed by X percent over a specific time period

so say a server was sitting at 50% of its C:\ drive used and if over the last 24 hours it jumped up to 75% used i would want to know that a massive change occurred.  however if the same server increased to 75% but it took a year to get there that's just normal utilization.

1 Solution
Level 17

Yes. it's possible. It would be a custom SQL alert and would like below. Below is for over 10% in 2 hours. Just change these two values as per your requirement.

pastedImage_0.png

The bottom half of the query to complete is below for your convenience.

LEFT JOIN VolumeUsage_Detail V

ON Volumes.VolumeID = V.VolumeID

AND Volumes.VolumeType = 'fixed disk'

AND DATEDIFF(HH,DateTime, GETDATE()) < 2

GROUP BY Volumes.NodeID, Volumes.VolumeID, Volumes.FullName

HAVING MAX(PercentDiskUsed) - MIN(PercentDiskUsed) > 10

View solution in original post

12 Replies
Level 17

Yes. it's possible. It would be a custom SQL alert and would like below. Below is for over 10% in 2 hours. Just change these two values as per your requirement.

pastedImage_0.png

The bottom half of the query to complete is below for your convenience.

LEFT JOIN VolumeUsage_Detail V

ON Volumes.VolumeID = V.VolumeID

AND Volumes.VolumeType = 'fixed disk'

AND DATEDIFF(HH,DateTime, GETDATE()) < 2

GROUP BY Volumes.NodeID, Volumes.VolumeID, Volumes.FullName

HAVING MAX(PercentDiskUsed) - MIN(PercentDiskUsed) > 10

View solution in original post

Level 11

Hi

Thanks for the info on this how would I go about getting this to report on one server only ?

Hans

0 Kudos
Level 11

Hi All

I went about it a little differently in the end and created an application monitor with a SQL server user experience monitor

I used the query below

Hope this helps some else

 

IF(SELECT SUM(cnt) FROM (

SELECT COUNT(*) cnt FROM
Volumes

LEFT JOIN
VolumeUsage_Detail V

  1. ON
    Volumes
    .VolumeID = V.VolumeID
  2. AND
    Volumes
    .VolumeType = 'fixed
    disk'

AND Volumes.NodeID = YOURNODEID

AND DATEDIFF(HH,DateTime, GETDATE()) < 24

GROUP BY
Volumes
.NodeID,
Volumes
.VolumeID,
Volumes
.FullName

HAVING MAX(PercentDiskUsed) - MIN(PercentDiskUsed) > 10) a) > 0

SELECT 1

ELSE

SELECT 0

0 Kudos
Level 7

This answer was a good start, but alerting with regards to max vs. min can give false alerts. For example, if I clean out a volume more than 10% it will alert. What I need is the last line to subtract the most recent PercentDiskUsed from the minimum. How would I do this?

0 Kudos

tomiannelli​ sounds like an easier way to monitor what you monitor for storage, maybe?

designerfx​ I do like this solution, by HolyGuacamole, for way to alert on a dramatic change in the last 24 hours. So from day to day it highlights a change. The approach we took was too try and look at longer range and say, was this change out of the ordinary. Our workers can have a project [volume] sit ideal for days. Then work on large drawing sets and cause a very large change. So to tell if it is ordinary or not we needed some longer sample sets and a bit more statistically.

0 Kudos

Naturally this won't pick up long term trends as you yourself note, but it absolutely will pick up if a project volume changes should you tune this accordingly.

0 Kudos
Level 9

This is perfect. Just what I have been looking for.. Now how can I change the scope to a selection of servers or volumes...

something like nodename contains "xxx" or volumeid in the list of "volume1, volume2, etc"

0 Kudos
Level 17

Easiest way is perhaps Volume custom properties. The SQL should only change by 1 additional criteria like Volumes.CustomProperty1 = 'Yes' (may not be the exact names but you get the idea)

Level 9

Thank You So Much!

This worked perfectly!

We are quite excited to get this information in a more proactive state rather then waiting for the high % usage crisis alerts.

Level 17

Great! Please share the entire SQL for the benefit of the community

0 Kudos
Level 9

After adding a custom volume property to all the volumes associated with the nodes needing to be monitored, here is the SQL code I used. It monitors volumes having an ID of 'XXXXX' that have change 10% over 2 hrs.

LEFT JOIN VolumeUsage_Detail V

ON Volumes.VolumeID = V.VolumeID

AND Volumes.VolumeType = 'fixed disk'

AND Volumes.Vol_ID = 'XXXXX'

AND DATEDIFF(HH,DateTime, GETDATE()) < 2

GROUP BY Volumes.NodeID, Volumes.VolumeID, Volumes.FullName

HAVING MAX(PercentDiskUsed) - MIN(PercentDiskUsed) > 10

0 Kudos