Query about node availability on SWQL

mjonathan

Greetings to the community,

How do I create a query on SWQL that shows me node availability for today, the last 7 days, the last 30 days, and the last year? There's a widget that shows it, but I want to know the query. Thank you very much.

maciej.sandecki

It would be something like this:

SELECT n.Caption, rttoday.Today, rt7days.Last7Days, rt30days.Last30Days, rt365days.Last365Days
FROM Orion.Nodes n
LEFT JOIN (
    SELECT rt.NodeID, SUM(rt.Availability * rt.Weight) / SUM(rt.Weight) AS Today
    FROM Orion.ResponseTime rt
    WHERE rt.ObservationTimestamp >= CONCAT(YEAR(GETUTCDATE()), '-', MONTH(GETUTCDATE()), '-', DAY(GETUTCDATE()))
    GROUP BY rt.NodeID
    ) rttoday ON rttoday.NodeID = n.NodeID
LEFT JOIN (
    SELECT rt.NodeID, SUM(rt.Availability * rt.Weight) / SUM(rt.Weight) AS Last7Days
    FROM Orion.ResponseTime rt
    WHERE rt.ObservationTimestamp >= CONCAT(YEAR(ADDDAY(-7, GETUTCDATE())), '-', MONTH(ADDDAY(-7, GETUTCDATE())), '-', DAY(ADDDAY(-7, GETUTCDATE())))
    GROUP BY rt.NodeID
    ) rt7days ON rt7days.NodeID = n.NodeID
LEFT JOIN (
    SELECT rt.NodeID, SUM(rt.Availability * rt.Weight) / SUM(rt.Weight) AS Last30Days
    FROM Orion.ResponseTime rt
    WHERE rt.ObservationTimestamp >= CONCAT(YEAR(ADDDAY(-30, GETUTCDATE())), '-', MONTH(ADDDAY(-30, GETUTCDATE())), '-', DAY(ADDDAY(-30, GETUTCDATE())))
    GROUP BY rt.NodeID
    ) rt30days ON rt30days.NodeID = n.NodeID
LEFT JOIN (
    SELECT rt.NodeID, SUM(rt.Availability * rt.Weight) / SUM(rt.Weight) AS Last365Days
    FROM Orion.ResponseTime rt
    WHERE rt.ObservationTimestamp >= CONCAT(YEAR(ADDDAY(-365, GETUTCDATE())), '-', MONTH(ADDDAY(-365, GETUTCDATE())), '-', DAY(ADDDAY(-365, GETUTCDATE())))
    GROUP BY rt.NodeID
    ) rt365days ON rt365days.NodeID = n.NodeID