SWQL query for last Month availability report


I am looking for swql query to get the last month availability of devices for modern dashboard. I want to get the MTD availability % to show on my dashboard.

  • SELECT n.Caption AS [Name],m.Comments, n.DetailsUrl, n.Status,
            TOSTRING(ROUND(AVG(Availability),2))+' %' AS [SLA], 
            AVG(Availability) AS [SLATemp],
                WHEN AVG(Availability) = 100 THEN 1 --Up/Green
                WHEN AVG(Availability) <100 AND AVG(Availability) >= 99.9  THEN 3 --Warning/Yellow
                WHEN AVG(Availability) <99.9  AND AVG(Availability) >= 99.8  THEN 14 --Down/Red
                ELSE 2 --<99.8 --Critical/Crimson
            END AS [SLAStatus]
            FROM Orion.Nodes n
            JOIN Orion.ResponseTime r ON n.NodeID = r.NodeID
            JOIN Orion.NodesCustomProperties m ON n.NodeID = m.NodeID
                    WHERE r.DateTime >= AddDay( -31, GETUTCDATE()) AND
      	  n.Vendor = 'Windows'
       	 n.Vendor = 'Linux'
        	(n.vendor = 'net-snmp' AND n.Category = 2)--Server
       	 (n.vendor = 'Unknown' AND n.Category = 2)--Server
            GROUP BY n.Caption, n.DetailsUrl, n.Status, m.Comments

    I'm using this just to give an idea/starting point - feel free to play with ranges/statuses for different colorings and with vendors depending on your needs.

    It looks like that: