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

Indumathi.E

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

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.