Service Operations Insight

Back to Library

Tech Tip: CA SOI - Database query to view Alerts from a specific time period 

06-06-2018 10:00 AM

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. 

Statistics
0 Favorited
0 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

YAN.XU
06-14-2018 09:02 PM

Very helpful KB article, Thanks Ryan.

Related Entries and Links

No Related Resource entered.