Reporting

robert751

Is there away to get a report of alerts generated and the times the alert manager actually sent an email to notify the customer.  We have a requirement to show mean time to notify (MTTN) and are looking for such a report.

 

Thanks

mezdem

Create a custom SQL report in report writer..something like the below should give you what you want.. but it depends what alert actions you have defined. It would be nice for the report writer to allow you to browse and select tables/columns though!!

SELECT     TOP (100) PERCENT dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, dbo.AlertLog.LogDateTime, dbo.AlertLog.ActionType,
                      dbo.AlertLog.Message, dbo.AlertLog.AlertDefID, dbo.AlertLog.ObjectType, dbo.AlertLog.ObjectID, dbo.Nodes.IP_Address, dbo.Nodes.Caption,
                      dbo.Nodes.Location, CAST(dbo.ActionDefinitions.Target AS varchar) AS EmailTarget
FROM         dbo.ActionDefinitions INNER JOIN
                      dbo.AlertDefinitions ON dbo.ActionDefinitions.AlertDefID = dbo.AlertDefinitions.AlertDefID INNER JOIN
                      dbo.AlertLog INNER JOIN
                      dbo.Nodes ON dbo.AlertLog.ObjectID = dbo.Nodes.NodeID ON dbo.AlertDefinitions.AlertDefID = dbo.AlertLog.AlertDefID
GROUP BY dbo.AlertLog.ObjectName, dbo.AlertDefinitions.AlertName, dbo.AlertLog.LogDateTime, dbo.AlertLog.ActionType, dbo.AlertLog.Message,
                      dbo.AlertLog.AlertDefID, dbo.AlertLog.ObjectType, dbo.AlertLog.ObjectID, dbo.Nodes.IP_Address, dbo.Nodes.Caption, dbo.Nodes.Location,
                      CAST(dbo.ActionDefinitions.Target AS varchar)
HAVING      (dbo.AlertLog.ActionType = 'email') AND (CAST(dbo.ActionDefinitions.Target AS varchar) <> '')
ORDER BY dbo.AlertLog.LogDateTime DESC

 

sorry i will look into this again, this does not show MTTN!