Introduction: We need a database query to export/view the alert history from the SOI database for last 6 months. Instructions: The Alerts database table will hold this information, we would usually recommend you work with your DBA to create a query based on that table.
Here is an example which you can use and modify if required:
All acknowledged alerts:
SELECT dbo.AlertQueueAssignments.QueueID, dbo.AlertQueues.QueueName, dbo.Alerts.ConnectorID, dbo.ConnectorConfiguration.ConnectorName, dbo.AuditRecords.Type,
dbo.AuditRecords.Action, dbo.AuditRecords.ActionDetail, dbo.AuditRecords.UserName, dbo.AuditRecords.TimeStamp, dbo.AlertQueueAssignments.AlertID,
dbo.Alerts.DeviceID, dbo.Alerts.SituationMessage, dbo.Alerts.AlertDetail, dbo.Alerts.LoggedTime, dbo.Alerts.ModelElementID, dbo.Alerts.ClearedTime, dbo.Alerts.Acknowledged
FROM dbo.AlertQueueAssignments INNER JOIN
dbo.Alerts ON dbo.AlertQueueAssignments.AlertID = dbo.Alerts.AlertID INNER JOIN
dbo.AlertQueues ON dbo.AlertQueueAssignments.QueueID = dbo.AlertQueues.QueueID INNER JOIN
dbo.AuditRecords ON dbo.Alerts.AlertID = dbo.AuditRecords.InternalID INNER JOIN
dbo.ConnectorConfiguration ON dbo.Alerts.ConnectorID = dbo.ConnectorConfiguration.ConnectorID
WHERE dbo.AuditRecords.Action = 9 and ClearedTime between '2018-01-01 19:00:00' and '2018-06-01 21:00:00'
All acknowledged alerts from a specific alert queue:
SELECT dbo.AlertQueues.QueueName,
dbo.AuditRecords.Action, dbo.AuditRecords.ActionDetail, dbo.AuditRecords.UserName, dbo.AuditRecords.TimeStamp, dbo.AlertQueueAssignments.AlertID,
dbo.Alerts.DeviceID, dbo.Alerts.SituationMessage, dbo.Alerts.AlertDetail, dbo.Alerts.ModelElementID,dbo.Alerts.Active
FROM dbo.AlertQueueAssignments INNER JOIN
dbo.Alerts ON dbo.AlertQueueAssignments.AlertID = dbo.Alerts.AlertID INNER JOIN
dbo.AlertQueues ON dbo.AlertQueueAssignments.QueueID = dbo.AlertQueues.QueueID INNER JOIN
dbo.AuditRecords ON dbo.Alerts.AlertID = dbo.AuditRecords.InternalID
WHERE dbo.AuditRecords.Action = 10 and dbo.AlertQueues.QueueName = 'Minor' and dbo.Alerts.Active = 1
Note: For Active & Inactive alerts, there is a flag “Active” in Alerts table (Active = 1 -> alert is active) and 0 is for cleared alerts. |