SWQL for interface availability

Question

Hi, I'm still learning the ropes for SWQL and trying to get a query going for a modern dashboard widget. I want it to show the availability percentage for a given interface over the past 24 hours. I found this query but it doesn't seem to be working, wondering if anyone can help me understand what needs to be tweaked:

SELECT ROUND(AVG(Availability), 2) AS [Availability] FROM Orion.ResponseTime WHERE Day(DateTime) = DAY(ADDDAY(-1, GETDATE())) AND NodeID IN ({node_ID}) OR

InterfaceID IN ({interface_ID})

jasonclair · Answer

Thank you very much for the help Terry. Turns out I don't think "availability" is the data I'm looking for anyway, since I turned the machine off and it continued to report 100% availability. So I think SW only considers the interface to be "unavailable" if the node itself is up and the interface isn't. I need to find a metric that shows how much an interface is down for any reason, whether the parent node is up or not. "Status" seems like it would work, because it went to "unknown" when the node was shut down. Still need to figure out how to get that data onto a modern dashboard. I'm going to do some more tinkering and will post another question once I've poked around a bit more.

TerryHarding · Answer

Hi Jason. A few issues.

InterfaceID is not a column in the Orion.ResponseTime table, Interface Availability is reported in the Orion.NPM.InterfaceAvailability table.Separate queries would work as Custom Queries on the Node Details and Interface Details Views:

SELECT ROUND(AVG(Availability), 2) AS [Availability] FROM Orion.ResponseTime WHERE Day(DateTime) = DAY(ADDDAY(-1, GETDATE())) AND NodeID IN ({NodeID})SELECT ROUND(AVG(Availability), 2) AS [Availability] FROM Orion.NPM.InterfaceAvailability WHERE Day(DateTime) = DAY(ADDDAY(-1, GETDATE())) AND InterfaceID IN ({InterfaceID})I'm not sure how you could fit that into a Modern Dashboard though.