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

Agreed, that would be a very useful report!

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