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

Report for acklowledged low drive space

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.

0 Kudos
5 Replies
Level 12

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

0 Kudos

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'

0 Kudos

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.

Level 12

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.

0 Kudos

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

0 Kudos