Products
Applications
Support
Company
How To Buy
Skip to main content (Press Enter).
Sign in
Skip auxiliary navigation (Press Enter).
Register
Skip main navigation (Press Enter).
Toggle navigation
Search Options
Home
Communities
All Communities
Enterprise Software
Mainframe Software
Symantec Enterprise
Blogs
All Blogs
Enterprise Software
Mainframe Software
Symantec Enterprise
Events
All Events
Enterprise Software
Mainframe Software
Symantec Enterprise
VMware
Water Cooler
Groups
Enterprise Software
Mainframe Software
Symantec Enterprise
Members
Service Operations Insight
Private Community
View Only
Community Home
Threads
Library
Events
Members
Back to Library
Tech Tip: CA SOI - Database query to view Alerts from a specific time period
1
Recommend
Jun 06, 2018 10:00 AM
Ryan Dunn
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
Jun 14, 2018 09:02 PM
Very helpful KB article, Thanks Ryan.
Related Entries and Links
No Related Resource entered.
Copyright 2019. All rights reserved.
Powered by Higher Logic