Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

WSQL script for availability

Hello,

I've been trying to get accurate availability information using SQL for use with the modern dashboards, but I'm running into issues with the information. I am seeing a big discrepancy in the numbers. The code I am using I pulled from this forum, but is not pulling the information I am looking for.

The Code:

SELECT [Nodes].Caption
, AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].Caption = '??????????'
AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDDAY(-30, GETUTCDATE())
GROUP BY [Nodes].Caption

Out put:

??????????

45.6265984654731

The GUI for the machine is showing:

Last 30 Days

85.409 %

Can anyone show me where I am going wrong?

Thanks,

Find more posts tagged with

Accepted answers

Seashore

All monitored values in SolarWinds are aggregated after some time. Defaults are that detailed info is aggregated into hourly data after 7 days. After 30 days hourly data is aggregated into daily values. And after one year, data is removed. (Different types of data might differ from this). These are defaults but can be adjusted.

So, when calculating availability for example, the weight of the value - aggregation level - has to be taken into account.

This is a SWQL code part I grabbed seeing the GUI is running:

SELECT TOP 365
    DateTrunc('Day', toLocal(ObservationTimestamp)) as DateTime,
    SUM(Availability * Weight) / SUM(Weight) AS[Availability],
    SUM(Weight) AS Weight
FROM Orion.ResponseTime
WHERE NodeID = @nodeId
GROUP BY NodeID, DateTrunc('Day', toLocal(ObservationTimestamp))
ORDER BY DateTime

Hope this helps.

Seashore

Happy to see your progress!

If I understand you correct you want an average availability of all your windows server with that owner.

I took your query and made it a subquery of another that makes the average of all the servers:

SELECT
    CONCAT(AVG(Tbl1.Availability), '%') as AvgAvailability
FROM parentheses
    SELECT 
        SUM([Nodes].ResponseTimeHistory.Availability * [Nodes].ResponseTimeHistory.Weight) / SUM([Nodes].ResponseTimeHistory.Weight) AS [Availability]
            , Nodes.Caption
            --, Nodes.Status
            --, Nodes.DetailsUrl
            --, Nodes.ChildStatus
     FROM Orion.Nodes AS [Nodes]
     WHERE 
        Nodes.MachineType LIKE '%Windows%' 
        AND Nodes.MachineType NOT LIKE 'Windows 10%' 
     --   AND Nodes.CustomProperties.ServiceNow_Owner_1 LIKE '@User%'
        AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDDAY(-60, GETUTCDATE())
     GROUP BY Nodes.Caption--, Nodes.Status, Nodes.DetailsUrl, Nodes.ChildStatus
     ) AS Tbl1

-> CHANGE THE "parentheses" texy to a "(" - for some reason I can't save the code insert here when there is a ( there. Strange

All comments

dmdundaytc

Update:

I found part of what is causing my issues with the calculations, but I am not sure how to work around it. The database shows all records before the 13^th of this month as being set to 1-hour increments, where after the 13^th it moves to 2-minute increments. This is blowing the averages out of proportion, and it gets worse the further back you go. Anyone have a way around this?

3937	2025-03-13 02:00:00	100
3937	2025-03-13 03:00:00	100
3937	2025-03-13 04:00:00	100
3937	2025-03-13 05:00:42.3288729	100
3937	2025-03-13 05:02:42.3345967	100
3937	2025-03-13 05:04:42.3315031	100
3937	2025-03-13 05:06:42.3472006	100

Seashore

So, when calculating availability for example, the weight of the value - aggregation level - has to be taken into account.

This is a SWQL code part I grabbed seeing the GUI is running:

SELECT TOP 365
    DateTrunc('Day', toLocal(ObservationTimestamp)) as DateTime,
    SUM(Availability * Weight) / SUM(Weight) AS[Availability],
    SUM(Weight) AS Weight
FROM Orion.ResponseTime
WHERE NodeID = @nodeId
GROUP BY NodeID, DateTrunc('Day', toLocal(ObservationTimestamp))
ORDER BY DateTime

Hope this helps.

dmdundaytc

When I try to run this I get, "Conversion failed when parsing the value to data type int."

Seashore

Can it be that you have to put in a valid node id on this line:

WHERE NodeID = @nodeId

The node id of the node who's availability you are trying to get.

dmdundaytc

I believe I have it working now. Wow what a mess. I also found an item I need to fix at this end, but it does not affect the outcome for my test node. Your information pointed me in the right direction. Thank you for your help.

dmdundaytc

When I use the code below it works beautifully for nodes associated with an admin as a list, but how do I get it to give me one number, averaged, for the time period selected? What I need is to be able to give a single number for a group of servers based off the Server Engineer(a custom field).

SELECT SUM([Nodes].ResponseTimeHistory.Availability * [Nodes].ResponseTimeHistory.Weight) / SUM([Nodes].ResponseTimeHistory.Weight) AS [Availability]
        , Nodes.Caption
        , Nodes.Status
        , Nodes.DetailsUrl
        , Nodes.ChildStatus
     FROM Orion.Nodes AS [Nodes]
     WHERE Nodes.MachineType LIKE '%Windows%' AND Nodes.MachineType NOT LIKE 'Windows 10%' AND Nodes.CustomProperties.ServiceNow_Owner_1 LIKE '@User%'
          AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDDAY(-60, GETUTCDATE())
    GROUP BY Nodes.Caption, Nodes.Status, Nodes.DetailsUrl, Nodes.ChildStatus
    ORDER BY Availability

Seashore

Happy to see your progress!

If I understand you correct you want an average availability of all your windows server with that owner.

I took your query and made it a subquery of another that makes the average of all the servers:

SELECT
    CONCAT(AVG(Tbl1.Availability), '%') as AvgAvailability
FROM parentheses
    SELECT 
        SUM([Nodes].ResponseTimeHistory.Availability * [Nodes].ResponseTimeHistory.Weight) / SUM([Nodes].ResponseTimeHistory.Weight) AS [Availability]
            , Nodes.Caption
            --, Nodes.Status
            --, Nodes.DetailsUrl
            --, Nodes.ChildStatus
     FROM Orion.Nodes AS [Nodes]
     WHERE 
        Nodes.MachineType LIKE '%Windows%' 
        AND Nodes.MachineType NOT LIKE 'Windows 10%' 
     --   AND Nodes.CustomProperties.ServiceNow_Owner_1 LIKE '@User%'
        AND [Nodes].ResponseTimeHistory.ObservationTimestamp >= ADDDAY(-60, GETUTCDATE())
     GROUP BY Nodes.Caption--, Nodes.Status, Nodes.DetailsUrl, Nodes.ChildStatus
     ) AS Tbl1

-> CHANGE THE "parentheses" texy to a "(" - for some reason I can't save the code insert here when there is a ( there. Strange