Configured alarms report

Has anyone written a report that tells what alarm actions are configured for each node or interface? Basically I have setup quite a few alarms with various actions. Unfortunately my SQL skills are very sub par and I don’t see a report that will tell me what nodes are configured for what alarm actions. Or what nodes have no alarm actions.

Thanks for any help

Ron

Find more posts tagged with

sql

custom_report

Alerts

Accepted answers

All comments

njoylif

Agreed, that would be a very useful report!

mgibson

I recall a report like you descibe. Do a search for "Tally", heres the code

-- Active Alert Report (active-alerts.sql)
-- Copyrighted: 07/22/05, Ross Warren(ross.warren@segoviaip.com) and Andre Deleage
--

-- Outline:
-- Break up Array in Alerts.NetObjects into Results table via Tally table
-- Find all Alerts
-- Determine if the Alerts are Interface, Node or Volume Alerts
-- Find all Devices assigned to specific alerts
-- Report the Device name, Trigger for Alert and the Alert Action
--

-- Check if Table "Tally" exists, drop if it does
-- Tally is used to break the array that exists in Alerts.NetObjects in conjunction with the Results table

--
-- Does Table Tally Exists
--

IF exists (SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[Tally]'))
DROP TABLE Tally
GO

CREATE TABLE Tally ("ID" int)
GO

DECLARE @Data INT
SET @Data = 0
WHILE @Data < 2000
BEGIN
set @Data = @Data + 1
INSERT into TALLY values(@Data)
END

--
-- Does Table Result Exists
--

IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Results]'))
DROP TABLE Results
GO

--
-- Read Array of numbers in Alerts.Netobjects and place in "Results.NetObjectID", using Tally for temp storage
--

SELECT AlertID, Alertname,
NullIf(SubString(',' + convert(varchar(500),NetObjects) + ',' , ID ,
CharIndex(',' , ',' + convert(varchar(500),NetObjects) + ',' , ID) - ID) , '') AS NetObjectID
INTO Results
FROM Tally, Alerts
WHERE ID <= Len(',' + convert(varchar(500),NetObjects) + ',')
AND SubString(',' + convert(varchar(500),NetObjects) + ',' , ID - 1, 1) = ','
AND CharIndex(',' , ',' + convert(varchar(500),NetObjects) + ',' , ID) - ID > 0

--
-- Returns information from Tables INTERFACES, NODES and VOLUMES
--

SELECT Alerts.AlertName, Interfaces.Fullname AS "Device Name", Alerts.[Trigger], AlertActions.Title AS Action
FROM Interfaces, Results, Alerts, AlertActions
WHERE Results.NetObjectID is not NULL
and Results.NetObjectID <> '*'
and Alerts.PropertyID= '58' -- INTERFACE Specific, ie. Alerts.PropertyID = 58
and AlertActions.Alertid = Alerts.AlertID
and Results.AlertID = Alerts.AlertId
and Results.NetObjectID = Interfaces.InterfaceID
and Alerts.Enabled = '1'

UNION ALL

SELECT Alerts.AlertName, Nodes.Caption AS "Device Name", Alerts.[Trigger], AlertActions.Title AS Action
FROM Nodes, Results, Alerts, AlertActions
WHERE Results.NetObjectID is not NULL
and Results.NetObjectID <> '*'
and Alerts.PropertyID= '63' --NODE Specific, ie. Alerts.PropertyID = 63
and AlertActions.Alertid = Alerts.AlertID
and Results.AlertID = Alerts.AlertId
and Results.NetObjectID = Nodes.NodeID
and Alerts.Enabled = '1'

UNION ALL

SELECT Alerts.AlertName, Volumes.Caption AS "Device Name", Alerts.[Trigger], AlertActions.Title AS Action
FROM Volumes, Results, Alerts, AlertActions
WHERE Results.NetObjectID is not NULL
and Results.NetObjectID <> '*'
and Alerts.PropertyID= '156' --VOLUME Specific, ie. Alerts.PropertyID = 156
and AlertActions.Alertid = Alerts.AlertID
and Results.AlertID = Alerts.AlertId
and Results.NetObjectID = Volumes.VolumeID
and Alerts.Enabled = '1'

order by Alerts.AlertName