How to retrive informations from Alert on Nodes, Volumes, Application and Component:
Note / bug:
If you write "Nodes" table, you have to write it "nodes". Because if you apply limitation (in the customize page), you gonna get an error that Nodes.NodeID could not be found.
--Union style:
SELECT nd.StatusLED AS Status
,ad.AlertName AS nom
,nd.PercentLoss AS Pourcentage
,ast.TriggerTimeStamp AS TriggerTime
,ast.ObjectType
,nd.NodeID
,nd.Caption AS NodeName
,ast.ObjectName AS ServiceName
,nd.IP_Address AS IP
,ast.Notes
,ast.ActiveObject
,nd.Fonction AS Fonction
,nd.Ligne_de_services AS Ligne_de_services
,nd.Lignes_de_services_en_escalade AS Lignes_de_services_en_escalade
FROM AlertStatus AS ast ,AlertDefinitions AS ad
,nodes AS nd
WHERE ast.ObjectType IN('Node')
AND ast.AlertDefID = ad.AlertDefID
AND ast.ActiveObject = nd.NodeID --AND ast.ObjectName = nd.Caption
UNION ALL
SELECT Volumes.StatusLED AS Status
,ad.AlertName AS nom
,vlmNode.PercentLoss AS Pourcentage
,ast.TriggerTimeStamp AS TriggerTime
,ast.ObjectType
,Volumes.NodeID
,Volumes.FullName AS NodeName --,Volumes.FullName, Volumes.Caption --???
,ast.ObjectName AS ServiceName
,vlmNode.IP_Address AS IP
,ast.Notes
,ast.ActiveObject
,vlmNode.Fonction AS Fonction
,vlmNode.Ligne_de_services AS Ligne_de_services
,vlmNode.Lignes_de_services_en_escalade AS Lignes_de_services_en_escalade
FROM AlertStatus AS ast ,AlertDefinitions AS ad
,Volumes
,nodes AS vlmNode
WHERE ast.ObjectType IN('Volume')
AND ast.AlertDefID = ad.AlertDefID
AND ast.ActiveObject = Volumes.VolumeID --AND ast.ObjectName = Volumes.FullName
AND Volumes.NodeID = vlmNode.NodeID
UNION ALL
SELECT AppND.StatusLED AS Status
,ad.AlertName AS nom
,AppND.PercentLoss AS Pourcentage
,ast.TriggerTimeStamp AS TriggerTime
,ast.ObjectType
,AppND.NodeID
,AppND.Caption AS NodeName
,ast.ObjectName AS ServiceName
,AppND.IP_Address AS IP
,ast.Notes
,ast.ActiveObject
,AppND.Fonction AS Fonction
,AppND.Ligne_de_services AS Ligne_de_services
,AppND.Lignes_de_services_en_escalade AS Lignes_de_services_en_escalade
FROM AlertStatus AS ast ,AlertDefinitions AS ad
,APM_Application AS App
,nodes AS AppND
WHERE ast.ObjectType IN('APM: Application')
AND ast.AlertDefID = ad.AlertDefID
AND ast.ActiveObject = App.ID --AND ast.ObjectName = App.Name
AND App.NodeID = AppND.NodeID
UNION ALL
SELECT CmpND.StatusLED AS Status
,ad.AlertName AS nom
,CmpND.PercentLoss AS Pourcentage
,ast.TriggerTimeStamp AS TriggerTime
,ast.ObjectType
,CmpND.NodeID
,CmpND.Caption AS NodeName
,ast.ObjectName AS ServiceName
,CmpND.IP_Address AS IP
,ast.Notes
,ast.ActiveObject
,CmpND.Fonction AS Fonction
,CmpND.Ligne_de_services AS Ligne_de_services
,CmpND.Lignes_de_services_en_escalade AS Lignes_de_services_en_escalade
FROM AlertStatus AS ast ,AlertDefinitions AS ad
,APM_Component AS Cmp
,APM_Application AS AppCmp
,nodes AS CmpND
WHERE ast.ObjectType IN('APM: Component')
AND ast.AlertDefID = ad.AlertDefID
AND ast.ActiveObject = Cmp.ID --AND ast.ObjectName = Cmp.Name
AND Cmp.ApplicationID = AppCmp.ID
AND AppCmp.NodeID = CmpND.NodeID
ORDER BY TriggerTime DESC
--Case style (nice to work in a view, but look out for case choice):
SELECT
(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN Volumes.StatusLED --vlmNode.StatusLED
WHEN App.ID IS NOT NULL THEN AppND.StatusLED
WHEN Cmp.ID IS NOT NULL THEN CmpND.StatusLED
WHEN nd.NodeID IS NOT NULL THEN nd.StatusLED
ELSE NULL END) AS Status
,ad.AlertName AS nom
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN vlmNode.PercentLoss
WHEN App.ID IS NOT NULL THEN AppND.PercentLoss
WHEN Cmp.ID IS NOT NULL THEN CmpND.PercentLoss
WHEN nd.NodeID IS NOT NULL THEN nd.PercentLoss
ELSE NULL END) AS Pourcentage
,ast.TriggerTimeStamp AS TriggerTime
,ast.ObjectType
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN Volumes.NodeID
WHEN App.ID IS NOT NULL THEN AppND.NodeID
WHEN Cmp.ID IS NOT NULL THEN CmpND.NodeID
WHEN nd.NodeID IS NOT NULL THEN nd.NodeID
ELSE NULL END) AS NodeID
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN Volumes.FullName
WHEN App.ID IS NOT NULL THEN AppND.Caption
WHEN Cmp.ID IS NOT NULL THEN CmpND.Caption
WHEN nd.NodeID IS NOT NULL THEN nd.Caption
ELSE NULL END) AS NodeName
--,Volumes.FullName,Volumes.Caption --???
,ast.ObjectName AS ServiceName
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN vlmNode.IP_Address
WHEN App.ID IS NOT NULL THEN AppND.IP_Address
WHEN Cmp.ID IS NOT NULL THEN CmpND.IP_Address
WHEN nd.NodeID IS NOT NULL THEN nd.IP_Address
ELSE NULL END) AS IP
,ast.Notes
,ast.ActiveObject
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN vlmNode.Fonction
WHEN App.ID IS NOT NULL THEN AppND.Fonction
WHEN Cmp.ID IS NOT NULL THEN CmpND.Fonction
WHEN nd.NodeID IS NOT NULL THEN nd.Fonction
ELSE NULL END) AS Fonction
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN vlmNode.Ligne_de_services
WHEN App.ID IS NOT NULL THEN AppND.Ligne_de_services
WHEN Cmp.ID IS NOT NULL THEN CmpND.Ligne_de_services
WHEN nd.NodeID IS NOT NULL THEN nd.Ligne_de_services
ELSE NULL END) AS Ligne_de_services
,(CASE
WHEN Volumes.VolumeID IS NOT NULL THEN vlmNode.Lignes_de_services_en_escalade
WHEN App.ID IS NOT NULL THEN AppND.Lignes_de_services_en_escalade
WHEN Cmp.ID IS NOT NULL THEN CmpND.Lignes_de_services_en_escalade
WHEN nd.NodeID IS NOT NULL THEN nd.Lignes_de_services_en_escalade
ELSE NULL END) AS Lignes_de_services_en_escalade
FROM AlertStatus AS ast INNER JOIN AlertDefinitions AS ad
ON ast.AlertDefID = ad.AlertDefID
LEFT OUTER JOIN nodes AS nd
ON ast.ActiveObject = nd.NodeID --ON ast.ObjectName = nd.Caption
LEFT OUTER JOIN Volumes
ON ast.ActiveObject = Volumes.VolumeID --ON ast.ObjectName = Volumes.FullName
LEFT OUTER JOIN nodes AS vlmNode
ON Volumes.NodeID = vlmNode.NodeID
LEFT OUTER JOIN APM_Application AS App
ON ast.ActiveObject = App.ID --ON ast.ObjectName = App.Name
LEFT OUTER JOIN nodes AS AppND
ON App.NodeID = AppND.NodeID
LEFT OUTER JOIN APM_Component AS Cmp
ON ast.ActiveObject = Cmp.ID --ON ast.ObjectName = Cmp.Name
LEFT OUTER JOIN APM_Application AS AppCmp
ON Cmp.ApplicationID = AppCmp.ID
LEFT OUTER JOIN nodes AS CmpND
ON AppCmp.NodeID = CmpND.NodeID
WHERE ast.ObjectType IN('Node', 'Volume', 'APM: Application', 'APM: Component')
--(ast.ObjectType = 'Node' OR ast.ObjectType = 'Volume' OR ast.ObjectType = 'APM: Application')
ORDER BY TriggerTime DESC