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

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

  • 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 ObservationTimestampDATETIME 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   

    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.

  • Hi  

    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)

  •   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   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).

       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   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.