19 Replies Latest reply on Aug 16, 2019 1:07 PM by gcow

    Alerting on Volume Growth

    waheed


      Hello! I am new to using Solarwinds but have manged to create some basic alerts. One thing I would like to be able to do is alert if volume growth spikes above a certain level. I know I can alert if a volume exceeds a percentage or fixed amout of space etc but I think real value would come from knowing if a volume suddenly starts increasing at a rapid rate. Anyone know if this is possible?

       

      Thanks!

        • Re: Alerting on Volume Growth
          Steven Klassen

          What would you consider a rapid rate?

          • Re: Alerting on Volume Growth
            trilobite rex

            Was there ever any alert created for this?

            • Re: Alerting on Volume Growth
              beastroy

              I have this same question. Did anyone find a solution for this?

                • Re: Alerting on Volume Growth
                  mesverrum

                  It has been done in custom SQL/SWQL queries.  Basically you need to determine the size of your window (lets say an hour), what growth percent would you consider "alarming" (lets say 10% over that hour)  and then write a query to find the value from an hour ago, divide that by the current value, and if the result is greater than 1.1 trigger an alert. 

                   

                  In practice though this kind of alerts generate WAY more heat than light where I have seen them in use. 

                  For example, I would trigger them all the time while doing solarwinds upgrades since the installers tend to be a one time jump of several GB on disk.  If you make the time window too large you miss the things you are worried about like db logs suddenly filling up, but if you make the window too narrow you have problems with one time bumps.  In order to make this type of alert more useful I have had to do things like check 3-4 timestamps along the window and try to measure if the growth trend stays high across all of them (also keeping in mind that disk space is only polled once every 15 minutes in most environments).  All told it gets to be pretty complex, but doable.

                   

                  The SAM Appinisght for SQL template does daily growth rate calculations for you automatically so you could use that for your DB or logs too if that is the use case you have in mind, but it is doing it across 24 hours so it has limited utility in catching those fast bursts.

                  • Re: Alerting on Volume Growth
                    Seashore

                    Hi

                     

                    Try to make an alert out of this SQL query. (Createa a Custom SQL alert and choose Volumes as "target"):

                     

                    SELECT Volumes.FullName, Volumes.VolumeID FROM Volumes

                    Inner join [dbo].[VolumeUsage_Detail]  D on Volumes.VolumeID=d.VolumeID

                    where

                    D.datetime>Dateadd(HOUR,-2,Getdate())  -- In how many hours

                    AND Volumes.VolumeTypeID=4

                    group by Volumes.FullName, Volumes.VolumeID

                    HAVING Max(D.PercentDiskUsed)-Min(D.PercentDiskUsed)>10  -- Growth to look for in percent

                     

                     

                    Adjust hours and percent growth as you want.

                     

                    Good luck!

                    2 of 2 people found this helpful
                  • Re: Alerting on Volume Growth
                    cahunt

                    A Bit of a variation of the script above - where the script looks at the last two polls to alert on a spike in % used. Also has a reset query to set a threshold for the reset.

                      Volume Usage Spike Detection Alert

                    2 of 2 people found this helpful
                      • Re: Alerting on Volume Growth
                        jbaechtel

                        First  all, thanks everyone for your posts which gave me the basis for what I was looking for which was alerting on Linux swap volume growth that gets out of control.

                         

                        Second, we needed more flexibility than a single hard-coded Percent Growth and Time Interval.  So by adding the following two Custom Properties to volumes, you can add the flexibility to either use the "defaults" or customize the parameters on a per volume basis:

                            

                         

                             VolumeSizePercentageChangeTrigger          Floating Point       Percent Change to be used as a Trigger condition (default = 50.00)

                         

                             VolumeSizeChangeRateIntervalInMinutes     Int                         Interval (in minutes) to "look back" in time for the trigger percent trigger condition (default = 30 days)

                         

                        Here is the custom SQL query to achieve this:

                         

                        SELECT Volumes.[VolumeID], Volumes.[FullName] FROM [Orion.Solarwinds].[dbo].[Volumes]
                            WHERE Volumes.VolumeTypeID IN ('4','10','100')                                                                                                                            -- Fixed Disk; Network Disk; Mount Point
                                AND (SELECT CASE WHEN (SELECT (SELECT MAX(D.PercentDiskUsed) FROM [dbo].[VolumeUsage_Detail] AS D WHERE D.VolumeID = Volumes.[VolumeID])                            -- Get Max PercentDiskUsed
                                                - (SELECT MIN(D.PercentDiskUsed) FROM [dbo].[VolumeUsage_Detail]  AS D WHERE D.VolumeID = Volumes.[VolumeID] AND D.datetime >                         -- Minus Min PercentDiskUsed
                                                    Dateadd(MINUTE,(-1 * (Select CASE WHEN ((SELECT C.VolumeSizeChangeRateIntervalInMinutes FROM [dbo].[Volumes] C                                    -- Get VolumeSizeChangeRateIntervalInMinutes
                                                            WHERE C.VolumeID = Volumes.[VolumeID]) IS NULL)
                                                        THEN '43200' ELSE (SELECT C.VolumeSizeChangeRateIntervalInMinutes FROM [dbo].[Volumes] C WHERE C.VolumeID = Volumes.[VolumeID])                -- EDIT DEFAULT INTERVAL IN MINUTES (30 Days) HERE
                                                            END)),Getdate())))               
                                                > (SELECT CASE WHEN ((SELECT C.VolumeSizePercentageChangeTrigger FROM [dbo].[Volumes] AS C WHERE C.VolumeID = Volumes.[VolumeID]) IS NULL)
                                                    THEN '50.00' ELSE (SELECT C.VolumeSizePercentageChangeTrigger FROM [dbo].[Volumes] AS C WHERE C.VolumeID = Volumes.[VolumeID]) END)                -- EDIT DEFAULT PERCENTAGE (50.00) CHANGE HERE
                                            THEN (SELECT 'True') ELSE (SELECT 'False')
                                        END AS 'Percent Growth Exceeds Set Point'   
                                ) = 'True'

                        VolumeSizeChangeRateIntervalInMinutes

                          • Re: Alerting on Volume Growth
                            rgivens

                            Hello,

                             

                            So I'm relatively new to SolarWinds.  Where would you set this SQL query up to run.  Is it in an alert?  I've setup the custom properties and am attempting to set this SQL query up in a trigger condition but I'm not having much success yet.  I'm I in the correct place or do you set this SQL query up to run somewhere else and then attempt to alert on the custom property value?

                              • Re: Alerting on Volume Growth
                                Seashore

                                Hi, yes it's an alert.

                                (Createa a Custom SQL alert and choose Volumes as "target")

                                  • Re: Alerting on Volume Growth
                                    rgivens

                                    So that's what I did but when I copy the SQL query into the field and select validate it complains about the query.  I copied the query in SSMS and it doesn't like the query either so I'm presuming it's a syntax issue?

                                     

                                      • Re: Alerting on Volume Growth
                                        mesverrum

                                        This specific script relies on you having created two custom properties on the volumes ahead of time, from the post

                                         

                                          VolumeSizePercentageChangeTrigger          Floating Point       Percent Change to be used as a Trigger condition (default = 50.00)

                                         

                                          VolumeSizeChangeRateIntervalInMinutes     Int                         Interval (in minutes) to "look back" in time for the trigger percent trigger condition (default = 30 days)

                                         

                                        Assuming you have those two then the other thing that looks weird to me is the last line, i suspect that's some kind of copy paste error, i belive the query should end at 'True'

                                          • Re: Alerting on Volume Growth
                                            rgivens

                                            Thanks for your reply.  The first task I completed was to create the two custom properties.  Presently, I have no values set for the custom properties.  Do I have to enter a value?

                                             

                                            I removed the last line and I still get an error when I select validate.

                                             

                                              • Re: Alerting on Volume Growth
                                                rgivens

                                                Unfortunately, I'm not a SQL guy but it seems the database name is different from mine along with some table names.  In the script that is posted it says Orion.Solarwinds while my database is named SolarWindsOrion.  The query also mentions a table called VolumeUsage_Detail.  I don't have a table by that name in my SolarWindsOrion database.  I have the ones listed below.

                                                 

                                                Maybe I need to get a dba involved.

                                                  • Re: Alerting on Volume Growth
                                                    Seashore

                                                    Hi

                                                    First, I always try out my queries in SSMS first, that way I get more details on the errors.

                                                    Second, VolumeUsage_details is a view, not a table. I'm sure you have that.

                                                    Third, I think you have to have some values in those custom properties, at least "VolumeSizePercentageChangeTrigger", I get an error in SSMS if a set that value to NULL (Meaning you have to set that on all volumes otherwise it will fail.)

                                                     

                                                    I would suggest you start with my simpler query above instead.

                                                    SELECT Volumes.FullName, Volumes.VolumeID FROM Volumes

                                                    Inner join [dbo].[VolumeUsage_Detail]  D on Volumes.VolumeID=d.VolumeID

                                                    where

                                                    D.datetime>Dateadd(HOUR,-2,Getdate())  -- In how many hours

                                                    AND Volumes.VolumeTypeID=4

                                                    group by Volumes.FullName, Volumes.VolumeID

                                                    HAVING Max(D.PercentDiskUsed)-Min(D.PercentDiskUsed)>10  -- Growth to look for in percent

                                                     

                                                     

                                                    Adjust hours and percent growth as you want.

                                                      • Re: Alerting on Volume Growth
                                                        rgivens

                                                        Thanks again for the help.  I don't see a view named VolumeUsage_details in SMSS under views.  I do see one named VolumeUsage so I changed the query to use that and it seems to return some accurate info.  Just using the query without changing the values of hours or percentage I see three volumes in my environment that have met the query.  I validated by using the performance analysis graphs.  I don't know if there is any functional difference between the VolumeUsage view and the VolueUsage_details view but it seems to have the information in it needed to execute the query. 

                                      • Re: Alerting on Volume Growth
                                        gcow

                                        Hi.  I appreciate this is an ancient thread but it was very helpful to me today.  I thought I'd share my end result in case it helped others.  I wanted to see any large spikes that occurred in the last 24 hours - specifically comparing the lowest value to the current value (i.e. ignoring spikes that had now dropped off).  Thanks to those above, I got this SWIS query that is perfect (showing those increasing >5% here) :

                                         

                                        SELECT v.FullName, round(AVG(vh.PercentDiskUsed),2) as Average_Disk_Used, round(Min(vh.PercentDiskUsed),2) as Lowest, round(Max(Now.VolumePercentUsed),2) as Current, round(Max(Now. VolumePercentUsed)-Min(vh.PercentDiskUsed),2) as Variance

                                        FROM Orion.Volumes v

                                        JOIN Orion.VolumeUsageHistory vh

                                            on v.VolumeID = vh.VolumeID

                                        JOIN (

                                            SELECT FullName,Size, VolumeDescription, VolumePercentAvailable, VolumePercentUsed, VolumeSize

                                            FROM Orion.Volumes

                                            where VolumeTypeID=4

                                        ) as Now on Now.Fullname = v.Fullname

                                        WHERE vh.datetime> ADDHOUR(-24, GETUTCDATE())

                                        AND v.VolumeTypeID=4

                                        and vh.PercentDiskUsed > 0

                                        group by v.FullName, v.VolumeID

                                        HAVING Max(Now.VolumePercentUsed)-Min(vh.PercentDiskUsed)>5

                                        order by Max(Now.VolumePercentUsed)-Min(vh.PercentDiskUsed) desc