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.

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.

Yes we need the availability chart like above backup chart.

What would the breakdowns be? >> Breakdown to be > 98.5 , < 98.5 %  it should be added with color code as well. 

What would the breakdowns be? >> Breakdown to be > 98.5 , < 98.5 %  it should be added with color code as well. 

In the above dashboard, the SWQL behind the scenes is:

SELECT 'Older than 7 days' AS TimeRangeDescription
     , '#950000' AS Color
     , COUNT(1) AS TheCount
     , '/apps/ncm/config-files/last-backup-date/500' AS Link
FROM NCM.ConfigBackupStatistic
WHERE TimeRangeHours = - 1

UNION ALL

(
     SELECT 'Last 7 days' AS TimeRangeDescription
          , '#dd2c00' AS Color
          , COUNT(1) AS TheCount
          , '/apps/ncm/config-files/last-backup-date/168' AS Link
     FROM NCM.ConfigBackupStatistic
     WHERE TimeRangeHours = 168
     )

UNION ALL

(
     SELECT 'Last 48 hours' AS TimeRangeDescription
          , '#fec406' AS Color
          , COUNT(1) AS TheCount
          , '/apps/ncm/config-files/last-backup-date/48' AS Link
     FROM NCM.ConfigBackupStatistic
     WHERE TimeRangeHours = 48
     )

UNION ALL

(
     SELECT 'Last 24 hours' AS TimeRangeDescription
          , '#00a754' AS Color
          , COUNT(1) AS TheCount
          , '/apps/ncm/config-files/last-backup-date/24' AS Link
     FROM NCM.ConfigBackupStatistic
     WHERE TimeRangeHours = 24
     )

You can see how the colors and elements are retrieved.

 For anyone who wanted to know how I got to that so fast...

#requires -Version 7
$Dashboard = Get-Content -path ".\Downloads\SYSTEM_Config Summary.json" | ConvertFrom-Json
$WidgetName = 'Last config backup date'
$Dashboard.widgets | Where-Object { $_.configuration.header.properties.title -eq $WidgetName } | 
    Select-Object -Property @{ Name = 'SwqlQuery'; Expression = { $_.configuration.chart.providers.dataSource.properties.swql } } |
    Select-Object -ExpandProperty SwqlQuery

1. Yes @kmsigma. . But we tried to modify the availability query but it doesn’t support this where we are unable to union the select statements 

The first SELECT Statement cannot be surrounded by parenthesis.  Each other one with UNION (ALL) between needs to be surrounded by parenthesis.

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.

Thanks you So Much for your help KMSigma.SWI