Need a SWQL query to get the Last month availability of devices based on the Node category

Question

I got below SWQL query from one of the discussions where the output is giving me the availability for last 7 days. However I want to show the availability for last month in the Modern dashboard. 
 
 SELECT COUNT(*) AS [100% Available]
FROM (
 SELECT [Nodes].Caption
 , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
 FROM Orion.Nodes AS [Nodes]
 WHERE [Nodes].Category = '2' -- Put whatever filters here
 AND [Nodes].ResponseTimeHistory.DATETIME BETWEEN GETDATE() - 7
 AND GETDATE() -- this is where you define your time period
 GROUP BY [Nodes].Caption
 HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100
 )

Thanks 
 Indu

KMSigma.SWI · Accepted Answer

The problem with "Last Month" is that it means different things to different people.

Description 
 Start Time 
 End Time

Last Month (relative) 
 "today", but one month in the past 
 "now"

Last Month (fixed) 
 the first day of the previous month at midnight 
 The last day of the previous month at 23:59

Last Month (rolling) 
 the last 28 days 
 "now"

Regardless, you'll just need to change your WHERE clause to change the availability. 
 Last Month (relative) 
 SELECT COUNT(*) AS [100% Available]
FROM (
 SELECT [Nodes].Caption
 , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
 FROM Orion.Nodes AS [Nodes]
 WHERE [Nodes].Category = '2' -- Put whatever filters here
 -- From 1 month ago to right now
 AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDMONTH(GETUTCDATE(), -1)
 GROUP BY [Nodes].Caption
 HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100
 ) 
 Last Month (fixed) 
 SELECT COUNT(*) AS [100% Available]
FROM (
 SELECT [Nodes].Caption
 , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
 FROM Orion.Nodes AS [Nodes]
 WHERE [Nodes].Category = '2' -- Put whatever filters here
 -- Start on the first day of the previous month (inclusive)
 AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= DATETRUNC('month',(ADDMONTH(-1, GETUTCDATE())))
 -- End on the first day of this month (non-inclusive)
 AND [Nodes].ResponseTimeHistory.ObservationTimestamp < DATETRUNC('month', GETUTCDATE())
 GROUP BY [Nodes].Caption
 HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100
 ) 
 Last Month (rolling) 
 SELECT COUNT(*) AS [100% Available]
FROM (
 SELECT [Nodes].Caption
 , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
 FROM Orion.Nodes AS [Nodes]
 WHERE [Nodes].Category = '2' -- Put whatever filters here
 -- The last rolling month (28 days)
 AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDDAY(-28, GETUTCDATE())
 GROUP BY [Nodes].Caption
 HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100
 ) 
 You should also start checking your queries so that any time you use DATETIME as a field, you switch over to ObservationTimestamp . DATETIME is a function in SWQL and (sometimes) a field on some entities. This can cause issues when the interpreter doesn't know which you are referencing. Play it safe and use ObservationTimestamp .

Indumathi.E · Answer

KMSigma.SWI 
 Thank you so much, this works perfect.

KMSigma.SWI · Answer

This really should be a different thread because it's a separate question. 
 And it looks like you are remaking the wheel here. One of our THWACK MVPs already did a bunch of this work in Servers Summary - Server Status and Networks Summary . It would be easier to just import these dashboards and run with them.

KMSigma.SWI · Answer

I threw something together using the NCM one as a reference but tweaking for Availability. (Lots of notes in the code sample)

-- Query Construction
---------------------------------------------
-- The first SWQL Query in a UNION (ALL) *cannot* be surrounded by parenthesis
-- All other UNION (ALL) queries MUST be surrounded by parenthesis
-- If you want more ranges,
--    Copy one of the sample queries between the two UNION ALL statements to the end
--    Update the Description, Color, and WHERE Clause
--
-- Notes:
--   You MUST use the same timespan in the WHERE clause otherwise this data will cross barriers and make no sense
--   You can use the BETWEEN comparison operator for easier reading, which is functionally equivalent to
--        WHERE (
--          MyValue >= LowerBound
--          AND MyValue < UpperBound
--        )
--
-- Where did I get the colors?  I used an online tool to help find decent gradients between colors
-- * Green "Up" to Yellow "Warning" gradients
--     https://colordesigner.io/gradient-generator/?mode=lch#00A854-FEC406
-- * Yellow "Warning" to Red "Critical" radients
--     https://colordesigner.io/gradient-generator/?mode=lch#FEC406-DB2C00
-- Colors I'm working from in this example:
--    #00a754 <-- Very Good
--    #59b43c
--    #90be1f
--    #c6c300
--    #fec406 <-- Meh
--    #f9a300
--    #f18000
--    #e75b00
--    #db2c00 <-- Very bad


-- The first query - don't surround this with parenthesis
SELECT COUNT([Data].NodeID) AS Quantity
     , '#00a754' AS [Color]
     , '100% Availability' AS [Description]
FROM (
     SELECT [RT].NodeID AS [NodeID]
          , AVG([RT].Availability) AS [AverageAvailability]
     FROM Orion.ResponseTime AS [RT]
     -- Rolling last 28 days
     WHERE [RT].ObservationTimestamp >= ADDDAY(- 28, GETUTCDATE())
     GROUP BY [RT].NodeID
     HAVING AVG([RT].Availability) = 100
     ) AS [Data]

UNION ALL

-- the next segment - surround with parenthesis
(
     -- 95 - 100 % range
     SELECT COUNT([Data].NodeID) AS Quantity
          , '#59b43c' AS [Color]
          , '>95% Availability' AS [Description]
     FROM (
          SELECT [RT].NodeID AS [NodeID]
               , AVG([RT].Availability) AS [AverageAvailability]
          FROM Orion.ResponseTime AS [RT]
          -- Rolling last 28 days
          WHERE [RT].ObservationTimestamp >= ADDDAY(- 28, GETUTCDATE())
          GROUP BY [RT].NodeID
          HAVING AVG([RT].Availability) BETWEEN 95
                    AND 100
          ) AS [Data]
     )

UNION ALL

-- the next segment - surround with parenthesis
(
     -- 90 - 95 % range
     SELECT COUNT([Data].NodeID) AS Quantity
          , '#90be1f' AS [Color]
          , '>90% Availability' AS [Description]
     FROM (
          SELECT [RT].NodeID AS [NodeID]
               , AVG([RT].Availability) AS [AverageAvailability]
          FROM Orion.ResponseTime AS [RT]
          -- Rolling last 28 days
          WHERE [RT].ObservationTimestamp >= ADDDAY(- 28, GETUTCDATE())
          GROUP BY [RT].NodeID
          -- Update this with your range from the description
          -- Be sure to not overlap with other segments
          HAVING AVG([RT].Availability) BETWEEN 90
                    AND 95
          ) AS [Data]
     )
UNION ALL

-- the next segment - surround with parenthesis
(
     -- 75 - 90 % range
     SELECT COUNT([Data].NodeID) AS Quantity
          , '#fec406' AS [Color]
          , '>75% Availability' AS [Description]
     FROM (
          SELECT [RT].NodeID AS [NodeID]
               , AVG([RT].Availability) AS [AverageAvailability]
          FROM Orion.ResponseTime AS [RT]
          -- Rolling last 28 days
          WHERE [RT].ObservationTimestamp >= ADDDAY(- 28, GETUTCDATE())
          GROUP BY [RT].NodeID
          -- Update this with your range from the description
          -- Be sure to not overlap with other segments
          HAVING AVG([RT].Availability) BETWEEN 75
                    AND 90
          ) AS [Data]
     )
-- Additional Segments can go here [BEGIN]
--
-- 
-- Additional segments can go here [END]

UNION ALL

-- the final segment - surround with parenthesis
(
     -- < 50 % range
     SELECT COUNT([Data].NodeID) AS Quantity
          , '#db2c00' AS [Color]
          , '<50% Availability' AS [Description]
     FROM (
          SELECT [RT].NodeID AS [NodeID]
               , AVG([RT].Availability) AS [AverageAvailability]
          FROM Orion.ResponseTime AS [RT]
          -- Rolling last 28 days
          WHERE [RT].ObservationTimestamp >= ADDDAY(- 28, GETUTCDATE())
          GROUP BY [RT].NodeID
          -- Update this with your range from the description
          -- Be sure to not overlap with other segments
          HAVING AVG([RT].Availability) < 50
          ) AS [Data]
     )

When I put it into a Proportional (Pie Chart) Widget and set the Value, Category, and Color it displays like this.

My query is missing several segments I would normally include, but I skipped them to just give you the framework. You can tweak from there.

Need a SWQL query to get the Last month availability of devices based on the Node category

Top Replies

Last Month (relative)

Last Month (fixed)

Last Month (rolling)