This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL query for last Month availability report

Hi,

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.

Parents
  • SELECT n.Caption AS [Name],m.Comments, n.DetailsUrl, n.Status,
            TOSTRING(ROUND(AVG(Availability),2))+' %' AS [SLA], 
    
            AVG(Availability) AS [SLATemp],
    
            CASE 
                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'
        		OR
       	 n.Vendor = 'Linux'
        		OR
        	(n.vendor = 'net-snmp' AND n.Category = 2)--Server
        		OR
       	 (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:

Reply
  • SELECT n.Caption AS [Name],m.Comments, n.DetailsUrl, n.Status,
            TOSTRING(ROUND(AVG(Availability),2))+' %' AS [SLA], 
    
            AVG(Availability) AS [SLATemp],
    
            CASE 
                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'
        		OR
       	 n.Vendor = 'Linux'
        		OR
        	(n.vendor = 'net-snmp' AND n.Category = 2)--Server
        		OR
       	 (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:

Children
No Data