we have a requirement to get historical alerts triggered in last month(30 days)
any SQL query which can get this data. what table needs to be Queried?
Any help is appreiciated.
SAM includes an out of the box report that seems to meet your criteria perfectly. It's called "Triggered Alerts - Last 30 Days" and can be found under the "Reports" section of the "Home" tab.
I took a look at how that Report works and it looks like it requires the Alert to log out to the Events as part of the Trigger Action, is that the case?
I ran the report and several of my Alerts are not captured.
Home -> Event Summary -> Click on Alert Triggered (or select Event Type : Alert Triggered) and set Time Period: Last 30 Days/Last Month
Yeah, I understand that bit; however, I have alerts triggering that are not showing up in there.
I use the AlertLog table. Since that table logs the triggers and resets of all advanced alerts, it was just a matter of correlating the AlertDefID to the advanced alert ID and ObjectID to node ID. Try this, it is a report that counts the total number of minutes for each unique alert between its Triggering and Resetting. Even if the same alert definition is triggered multiple times (e.g. in the case of app alerts), the query will track them properly since the ObjectID is unique across all the system. you just need to be specific with the ObjectType. Below is my Node and Application queries. Note the differences and use them as you like. You will need to replace my AlertDefID = '?????????' with your targeted alert definitions (use the dbo.AlertDefinitions table). Hope it helps.
DECLARE
@LastMonth1 datetime = (SELECT DATEADD(month, DATEDIFF(month, -1, getdate()) - 2, 0)),
@Lastmonth2 datetime = (SELECT DATEADD(ss, -1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))),
@Lastmonth3 datetime = (SELECT DATEADD(ss, 1, DATEADD(month, DATEDIFF(month, 0, getdate()), 0))),
@minutes float
SET
@minutes = DATEDIFF(MINUTE, 0, DATEADD(DAY, DATEDIFF(DAY, @LastMonth1, 0), @Lastmonth2))+1
SELECT
StartTime.LogDateTime,
Nodes.Caption,
StartTime.Message,
(DATEDIFF(Mi, StartTime.LogDateTime,
(SELECT TOP 1
LogDateTime
FROM [DL-ORION].dbo.AlertLog AS Endtime
WHERE EndTime.LogDateTime > StartTime.LogDateTime AND EndTime.Message = 'Alert Reset'
AND EndTime.ObjectType = 'Node'
AND EndTime.ObjectID = StartTime.ObjectID
AND EndTime.LogDateTime > 15
ORDER BY EndTime.LogDateTime))) AS OutageDurationInMinutes
FROM
[DL-ORION].dbo.AlertLog StartTime INNER JOIN [DL-ORION].dbo.Nodes ON StartTime.ObjectID = Nodes.NodeID
WHERE
StartTime.ObjectID = NODES.NodeID
AND
StartTime.Message = 'Alert Triggered'
AND (AlertDefID = '56C08749-24AA-41B5-A534-BDC7F4AC95BC'
OR AlertDefID = '23AC8483-F686-4997-A812-E1AC0F00C406'
OR AlertDefID = '7D700769-B32C-4F04-8AAA-B4A2A2620C7D'
OR AlertDefID = '6FC017DE-E039-4278-9057-EA6997095D32'
OR AlertDefID = 'CF255298-AE6E-4A16-BAE7-FAE718503214')
AND StartTime.ObjectType = 'Node'
and ( LogDateTime between @LastMonth1 and @Lastmonth2
)
ORDER BY 1, 2 ASC
~~~~~~~~~~~~~~~~~~~~~~~~~~~End of Node Query~~~~~~~~~~~~~~~~~~~~~~~~
[APM_Application].Name,
AND EndTime.ObjectType = 'APM: Application'
[DL-ORION].dbo.AlertLog StartTime INNER JOIN [DL-ORION].dbo.[APM_Application] ON StartTime.ObjectID = [APM_Application].ID,
[DL-ORION].dbo.nodes
APM_Application.NodeID = nodes.NodeID
StartTime.ObjectID = [APM_Application].ID
AND (AlertDefID = '6888CE10-0E6E-43B1-83FA-60F8C1BFEA53' )
AND StartTime.ObjectType = 'APM: Application'
Thanks for this. For some reason it seems like this should be a much more simple task.
