5 Replies Latest reply on Dec 11, 2017 3:00 PM by rarbuckle1892

    Report for acklowledged low drive space

    trilobite rex

      I need to generate a report for an alert called "Server/Storage - Alert me when the drive space available on a c$ is less than or equal to 1 GB"

       

      What I want to do is create a list of who acknowledged the alert.

       

       

       

        • Re: Report for acklowledged low drive space
          rarbuckle1892

          We use something similar which I have posted below. You would need to modify the alertdefid field to match your alert definition ID and the where statement to trigger on your alert id instead of the volume use percentage but this could be a starting point for you... 

           

          SELECT v.StatusLED, n.Caption As NodeName, n.NodeID, v.Caption As VolumeName, v.VolumeSize, v.VolumeSpaceAvailable, v.VolumeID, v.VolumePercentUsed, a.AcknowledgedBy, a.Notes, a.ActiveObject, a.AlertDefID

          FROM Volumes v

          INNER JOIN Nodes n on v.NodeID = n.NodeID

          LEFT OUTER JOIN AlertStatusView a ON a.ActiveObject = v.VolumeID AND a.AlertDefID = N'B7616149-7E54-415E-A9DA-F6361A4690FF'

          WHERE v.VolumePercentUsed >= 95  AND v.VolumeType != 'RAM' AND v.Caption !='Cached memory' AND v.VolumeType !='Virtual Memory' and n.StatusDescription like '%up%' AND v.Caption !='SHARED MEMORY'

          ORDER BY a.Acknowledged ASC, v.VolumePercentUsed DESC

          • Re: Report for acklowledged low drive space
            trilobite rex

            SELECT v.StatusLED, n.Caption As NodeName, n.NodeID, v.Caption As VolumeName, v.VolumeSize, v.VolumeSpaceAvailable, v.VolumeID, v.VolumePercentUsed, a.AcknowledgedBy

            FROM dbo.Volumes v

            INNER JOIN dbo.Nodes n on v.NodeID = n.NodeID

            LEFT OUTER JOIN dbo.AlertStatusView a ON a.ActiveObject = v.VolumeID AND a.AlertDefID = N'B7616149-7E54-415E-A9DA-F6361A4690FF'

            WHERE v.VolumePercentUsed >= 95  AND v.Caption Like '%c:\%'  AND a.LastUpdate > DATEADD(DAY,-7,GETDATE())

            ORDER BY a.Acknowledged ASC, v.VolumePercentUsed DESC

             

            I run it and it comes up with 0 responses even though I have recently acknowledged one.

            • Re: Report for acklowledged low drive space
              trilobite rex

              Figured out that I had the wrong alert definition in there, however now when I try to go back to 30 days ago replacing DATEADD (DAY,-7,GETDATE... with DATEADD (DAY,-30,GETDATE the amount of nodes does not change

                • Re: Report for acklowledged low drive space
                  rarbuckle1892

                  This pulls from the active alert tables so if the alert has reset it will drop off of this report. If you want historical reporting you would need to change from alertstatusview to the historical alerts table... I'm not in the office today so I can't get into the database to find the exact table but I'll try to look when I get in on Monday and rework the report for cleared alerts as well.

                  • Re: Report for acklowledged low drive space
                    rarbuckle1892

                    So I found the table and it appears that this is alot easier then what I do for the other report for numerous reasons as everything you need for the most part is in one table... The query below should give trigger time, acknowledge time, reset time, along with nodes and the account ID you can customize the select statement as you like but the table I pulled from is AlertHistoryView you could probably join in the nodes and volumes table if you wanted to gain more info about current node and drive state...

                     

                    Also if you have multiple volume alerts and you only want to query against one swap the where clause from a.ObjectType = 'Volume' to a.AlertRefID = whatever your alert definition from above is... Let me know if you need this refined or changed at all.

                     

                    select a.TimeStamp as 'Alert Triggered',ab.timestamp as 'Acknowledged', ac.TimeStamp as 'Reset', a.eventtypeword, a.ObjectType, a.EntityCaption, a.RelatedNodeID, a.relatednodecaption, A.AlertNote, ab.AccountID

                    from

                    AlertHistoryView a

                    join (select alertactiveid, timestamp, accountid from AlertHistoryView where EventTypeWord = 'Acknowledged') ab on a.alertactiveid = ab.alertactiveid

                    join (select alertactiveid, timestamp, accountid from AlertHistoryView where EventTypeWord = 'Reset') ac on a.alertactiveid = ac.alertactiveid

                    where a.ObjectType ='Volume' and a.EventTypeWord = 'Triggered'