Alert report creation

Question

Anyone out there have a report for your alerts?

I am finding that as we keep adding alerts (over 350 now) they become hard to manage.  I'm thinking something as simple as a report that shows a coorelation between alert name, systems afftected, applications/components affected, etc.

This way it is much easier to handle downtimes of systems....

zackm · Answer

You won't get much more out of adding in the nodes/interface tables. AlertLog.ObjectName takes care of that for you.

for reference, this shows all active alerts and joins multiple tables to end up with about the same results:

SELECT    AL.LOGDATETIME AS 'TIMESTAMP', AD.ALERTNAME, AL.OBJECTTYPE, N.CAPTION AS 'NODE', 'NULL' AS 'INTERFACE'FROM ALERTLOG ALJOIN ALERTDEFINITIONS AD ON AL.ALERTDEFID=AD.ALERTDEFIDJOIN NODES N ON AL.OBJECTID = N.NODEIDJOIN ALERTSTATUS A ON AL.ALERTDEFID=A.ALERTDEFIDWHERE A.ACKNOWLEDGED = 0AND AL.MESSAGE = 'ALERT TRIGGERED'UNIONSELECT    AL.LOGDATETIME AS 'TIMESTAMP', AD.ALERTNAME, AL.OBJECTTYPE, 'NULL' AS 'NODE', I.CAPTION AS 'INTERFACE'FROM ALERTLOG ALJOIN ALERTDEFINITIONS AD ON AL.ALERTDEFID=AD.ALERTDEFIDJOIN INTERFACES I ON AL.OBJECTID = I.INTERFACEIDJOIN ALERTSTATUS A ON AL.ALERTDEFID=A.ALERTDEFIDWHERE A.ACKNOWLEDGED = 0AND AL.MESSAGE = 'ALERT TRIGGERED'ORDER BY TIMESTAMP DESC

geoffers · Answer

Here is what I came up with using SQL Server Managment Studio. It shows Alerts by ObjectName and a count of the alerts. Our system only keeps 30 days of Alerts in the AlertLog table. I haven't figured out how to associate with the host...yet.

SELECT [ObjectType]

,[ObjectName]

,Message

,COUNT(*)

FROM [SolarWindsOrion].[dbo].[AlertLog] (NOLOCK)

WHERE message = 'Alert Triggered'

GROUP BY [ObjectType]

,[ObjectName],Message

HAVING COUNT(*) >= 10

ORDER BY [ObjectType],COUNT(*) desc