I would like to generate a mail with the status of all fans of a device in a user-friendly output.
chassis-1 Chassis Fan Tray 1 => critical
chassis-1 Power Supply 1 Fan => normal
I use 2 pollers:
Poller "ciscoEnvMonFanStatusDescr" get the name of the fans.
Poller "ciscoEnvMonFanState2" get the status of the fans.
If I write a SQL query in the e-mail message box of the Alert Manager, I get only the first element of the SQL result. For this reason I try to combine the result in one string to avoid this restriction. Because of the missing SQL array, I use a temporary table.
With the SQL Server Management Studio I get the right result. The Report Writer shows only "Querying Database ..." and when sending a mail, only the SQL query is shown not the result.
I use NPN 10.1 with a SQL Server Express 2008 database.
My SQL query:
-----------------------------------------------------------------------
USE NetPerfMon;
DECLARE @device varchar(50), @poller1 varchar(50), @poller2 varchar(50), @count int, @anz int, @out varchar(255), @tt varchar(255), @akt varchar(255);
SET @device = ' ${NodeID}';
SET @poller1 = 'ciscoEnvMonFanStatusDescr';
SET @poller2 = 'ciscoEnvMonFanState2';
SET @anz = (
SELECT count(c.status)
FROM Nodes
LEFT JOIN CustomPollerAssignment ON (Nodes.NodeID = CustomPollerAssignment.NodeId)
LEFT JOIN CustomPollers ON (CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID)
LEFT JOIN CustomPollerStatus c ON (CustomPollerAssignment.CustomPollerAssignmentID = c.CustomPollerAssignmentID)
WHERE Nodes.SysName = @device AND (CustomPollers.UniqueName = @poller1 )
)
DECLARE @TEMP TABLE (
id int identity(1,1),
CustomPollerAssignmentID varchar(255) NOT NULL,
RawStatus varchar(255),
Status varchar(255)
)
INSERT INTO @TEMP (CustomPollerAssignmentID, RawStatus, Status)
SELECT Type = CustomPollerAssignment.CustomPollerAssignmentID, (
SELECT TOP 1 CPA.Status FROM Nodes
LEFT JOIN CustomPollerAssignment ON (Nodes.NodeID = CustomPollerAssignment.NodeId)
LEFT JOIN CustomPollers ON (CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID)
LEFT JOIN CustomPollerStatus CPA ON (CustomPollerAssignment.CustomPollerAssignmentID = CPA.CustomPollerAssignmentID)
WHERE (Nodes.SysName = @device AND CPA.RowID = c.RowID AND CustomPollers.UniqueName = @poller1 )
),
c.Status
FROM Nodes
LEFT JOIN CustomPollerAssignment ON (Nodes.NodeID = CustomPollerAssignment.NodeId)
LEFT JOIN CustomPollers ON (CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID)
LEFT JOIN CustomPollerStatus c ON (CustomPollerAssignment.CustomPollerAssignmentID = c.CustomPollerAssignmentID)
WHERE Nodes.SysName = @device AND (CustomPollers.UniqueName = @poller2 )
Order by c.RowID
set @count=1;
SET @out=';';
WHILE (@count < @anz)
BEGIN
SET @tt = @out;
SET @akt = (select RawStatus + ' ' + Status from @TEMP where id = @count);
SET @Count = @Count + 1;
SET @out = @tt + ' ' + @akt + '${CrLf}';
END
select @out;
-----------------------------------------------------------------------
Any Idea?
