Looking for a report (Advanced SQL) that combines Down Nodes and Down Interfaces with a Time Stamp of when they went down

bfreking

I am looking to combine these two reports into one easy to read report that shows ALL Down Nodes and All Down Interfaces on one chart with a Time Stamp of when each event took place.

Report 1 (shows Down Nodes and a time stamp)

SELECT max(Events.EventTime) AS Event_Time, Nodes.Caption AS NodeName
FROM Nodes
INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

WHERE
(Nodes.Status = '2')  AND

(
  (Events.EventType = 5000) OR
  (Events.EventType = 1)
)

group by Nodes.Caption
ORDER BY 1 DESC

 

Report 2 Shows Down Interfaces

SELECT
Nodes.NodeID AS NodeID, Interfaces.InterfaceID AS InterfaceID, Nodes.VendorIcon AS Vendor_Icon, Nodes.Caption AS NodeName, Interfaces.InterfaceIcon AS Interface_Icon, Interfaces.Caption AS Interface_Caption, Interfaces.StatusLED AS Status_Icon
 FROM
Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)
 WHERE 
(
  (Interfaces.Status = '2') OR
  (Interfaces.Status = '0')
)

 

Can I have some input on how I combine these two reports into one to get one chart that shows all down nodes and all down interfaces with a time stamp of when they occured?

 

Thanks for any and all help.

sean.martinez

I hope this works the way you want it to. By the way this was a Nice little Report that came out.

 

--Side Note, This has a Left Join to make sure that if there are no Interfaces Assigned that it will still report the Node with no Interface. I took out the NodeID and InterfaceID to clean it up, and also added the Node Status.

 

SELECT max(Events.EventTime) AS Event_Time, Nodes.Caption AS NodeName, Nodes.StatusLED as Node_Status, Nodes.VendorIcon AS Vendor_Icon,

 Interfaces.InterfaceIcon AS Interface_Icon, Interfaces.Caption AS Interface_Caption, Interfaces.StatusLED AS Interface_Status_Icon

 

FROM Nodes 

Inner JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

Left JOIN Interfaces ON (Interfaces.NodeID = Nodes.NodeID)

 

WHERE 

((Nodes.Status = '2')  AND

((Events.EventType = 5000) OR

(Events.EventType = 1)))

OR  

((Interfaces.Status = '2') OR 

 (Interfaces.Status = '0'))

Group by Nodes.Caption, Nodes.NodeID, Interfaces.InterfaceID, Nodes.VendorIcon, Nodes.Caption, Interfaces.InterfaceIcon, Interfaces.Caption, Interfaces.StatusLED, Nodes.StatusLED

ORDER BY 1 DESC