Hey all, i need to create a report to calculate the number of generated alerts.
we have two types of alerts, the first one is based on the node status and the second one based on group status.
while creating the report i noticed that both alerts don't return the same values that's why i tried to come up with one report listing the history of all events.
One thing left is that when the alert is Serious (in my case the alert generated when one of the Node is UP and the other is Down) , i can see two enteries in the event history UP and DOWN.
i would like to filter that to show only the nodes that are Down , so i decided to create a CASE for status :
SELECT
OAH.TimeStamp
, CASE
When AO.RealEntityType= 'Orion.Groups' then Nodes.Status
ELSE AO.Node.Status
end AS [Status]
, CASE
When AO.RealEntityType= 'Orion.Groups' then Nodes.Caption
ELSE OAH.AlertObjects.EntityCaption
end AS [NodeName]
, CASE
When AO.RealEntityType= 'Orion.Groups' then Nodes.CustomProperties.Type_Node
ELSE AO.Node.CustomProperties.Type_Node
end AS [Type_Node]
, OAH.AlertObjects.AlertConfigurations.DisplayName AS [Alert Name]
FROM Orion.AlertHistory OAH
JOIN Orion.AlertObjects AO ON OAH.AlertObjectID = AO.AlertObjectID
LEFT JOIN Orion.Groups OrionGroups ON OAH.AlertObjects.EntityDetailsurl= OrionGroups.DetailsUrl
LEFT JOIN Orion.Nodes AS Nodes ON OrionGroups.Members.MemberUri = Nodes.Uri
WHERE EventType = 0 and OAH.AlertObjects.AlertConfigurations.DisplayName like 'TPFM%' AND OAH.TimeStamp > GetUtcDate()-7
GROUP BY OAH.TimeStamp , Nodes.Caption , OAH.AlertObjects.EntityCaption , Nodes.CustomProperties.Type_Node , AO.Node.CustomProperties.Type_Node
order by OAH.TimeStamp DESC
when i add Status <>1 in the where statement it only returns one value . the last event only.
any help would be appreciated .
