Custom Query for Down Nodes Not Matching OOB Down Nodes

Question

Hello,

I have a custom query that folks on here helped me set up years ago.  Today I was creating a new dashboard. I added the existing SWQL code to the custom query widget and was suprirsed when I only had 6 nodes listed, while the OOB Down Nodes widget was showing 50+.  Any idea why this is occurring?  I have a view limitation to a group that includes specific nodes.

SELECT
NodeName AS [Node Name],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
DetailsUrl AS [_LinkFor_Node Name],Nodes.CustomProperties.Alert_Team AS [Alert Team], Nodes.CustomProperties.Business_Function AS [Business Function],
MAX(ToLocal(DOWNEVENT.EVENTTIME)) AS [Down Time]
FROM ORION.NODES NODES
INNER JOIN ORION.EVENTS DOWNEVENT
ON NODES.NODEID = DOWNEVENT.NETWORKNODE
WHERE STATUS = 2
GROUP BY NodeName, StatusIcon, DetailsUrl, Nodes.CustomProperties.Alert_Team, Nodes.CustomProperties.Business_Function
ORDER BY NodeName

This is what I'm seeing on the dashboard.

bobmarley · Answer

See if this gets you any closer, the Inner Join was removed as @mesverrum suggested. There are some edits you will need to make in the comments.

SELECT
[Nodes].NodeName AS [Node Name],
'/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Node Name],
DetailsUrl AS [_LinkFor_Node Name],Nodes.CustomProperties._Group AS [Alert Team], Nodes.CustomProperties._Application AS [Business Function], --replace _Application with your custom property
MAX(ToLocal([Nodes].Events.EVENTTIME)) AS [Down Time]
FROM ORION.NODES [NODES]
WHERE [Nodes].STATUS = 2 --AND [Nodes].Events.EVENTTIME NOT LIKE 'NULL'
GROUP BY [Nodes].NodeName, [Nodes].StatusIcon, [Nodes].DetailsUrl, [Nodes].CustomProperties._Group, [Nodes].CustomProperties._Application --replace _Application with your custom property and Alert Team with your custom property
ORDER BY NodeName

mesverrum · Answer

You need to check and make sure on the events table which eventid or eventtype, i dont recall the exact column name, actually shows up for node down events, then just add to your where conditions

and downevents.eventid = 2

or whatever the correct one is.

bharris1 · Answer

How would I update the query to include the event ID 2 vs what I have now?