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

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

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.

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

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.

KMSigma.SWI  

Thank you so much, this works perfect. 

Hi KMSigma.SWI  

I need another help on below query.

I am creating a summary dashboard in which added below widgets to display the server and Network devices status. For server by status widget I got it from Thwack forum, so i just edited the widget query to match with Network devices.

Server status dashboard also we have so it linked to this, however I have separate dashboard for Network status too, but when I click the on the Numbers Network widget, its not redirecting me to the Network Status dashboard. Could you please help me to identify where the issue with?

below is the SWQL below that I am using for Network by Status

SELECT N.TheCount
, CASE WHEN D.Link is null then NULL
ELSE CONCAT(D.Link,'?filters=', d.InstanceSiteId, '_Orion.Nodes_Status:eq:2')
END AS Link
FROM
(SELECT COUNT(1) as TheCount FROM Orion.Nodes WHERE Category=1 AND Status = 2) N
LEFT JOIN
(
SELECT TOP 1 InstanceSiteId, '/apps/platform/dashboard/' + TOSTRING(DashboardID) as Link
FROM Orion.Dashboards.Instances
WHERE DisplayName='Network Status'
ORDER BY DashboardID
) D ON 1=1

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.

This is great, Thank you so much KMSigma.SWI 

Hi KMSigma.SWI 

I have imported below dashboard which shows the count of 100% availability devices, 99% availability devices and so on. Could you please help me to get the Node list which are having 100% availability when I am clicking on the 100% tile.

(+) Overall Node Availability (Last 7 Days) - Modern Dashboards - The Orion Platform - THWACK (solarwinds.com)

KMSigma.SWI  has provided a link to my colleague's excellent post on advanced Modern Dashboard capability, which includes this function. Using a metric tile to drill down in to a sub page showing the list. 

(+) All in on Modern Dashboards | THWACK - Newsroom - Hybrid Cloud Observability - THWACK (solarwinds.com)

If you look at the SWQL code for an alert tile, you will see the structure and code to create an inline link, where a separate modern dashboard page is being linked to, taking in a querystring to determine how to filter.

Hi m_roberts  I have gone through the link, in which it was explained for Servers/Network list of devices , so that when the tile is clicked, it will take us to servers/network list dashboard and the SWQL query used here is with only Orion.Nodes table. But in my case, I want to display the list of devices dashboard along with it have to show the device availability over a month. For this, two or more tables are involving (Nodes and ResponseTime tables).

m_roberts KMSigma.SWI  If someone help me to get the SWQL query to show the list of devices along with last 30 days average availability, It would be very much helpful. Thank you.

Just add the Node's caption...

SELECT [N].Caption, 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
     ) AS [N]
GROUP BY [N].Caption

Hi KMSigma.SWI  I would like combined the output of all different  % of availability in pie chart. instead created different values seperately.

Ex: > 90%, <90 into one Pie chart. But by using HAV clause we are unable to UNION the two different select statement.

your help would much appreciated. Our requirement is to present the different % of availability in pie chart  for last 7 days, 30 days. so on. similar to NCM backup status Pie chart. 

Something like this?

What would the breakdowns be?

We've got the Config Summary [System Dashboard] available for download so you can look at the queries behind the scenes.