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

List of Nodes under Availability threshold

Hi there, trying to edit the following script from @KMSigma.SWI Modern Dashboard, looking to list nodes that are under 99.5% available.

https://thwack.solarwinds.com/content-exchange/the-orion-platform/m/modern-dashboards/3047

SELECT n.Caption AS [Node Name]
, n.IP AS [IP Address]
, n.StatusDescription AS [Status Description]
, n.DetailsURL AS [Node Details URL]
, n.ResponseTime AS [Response Time]
, n.PercentLoss AS [Packet Loss]
FROM Orion.Nodes n
WHERE n.ResponseTimeHistory.DateTime BETWEEN GETDATE() - 1 AND GETDATE() -- this is where you define your time period
HAVING AVG(n.ResponseTimeHistory.Availability) > 99.5

Would appreciate any assistance.

Thanks

Find more posts tagged with

Availability

Modern Dashboards

orionsdk

widget

Accepted answers

shuth

If you don't want the count, then you could list the nodes in a table by just using the inner query (drop all the COUNT part).

SELECT [Nodes].Caption
, [Nodes].IPAddress
, AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
GROUP BY [Nodes].Caption, [Nodes].IPAddress
HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 99.9

All comments

stuartd

Where are you getting stuck?

We have a sub 90% availability covering the last 7 days. The code also JOINs to Orion.NodesCustomProperties so that we can pull in a CP for customer prefixes to restrict the widget to specific clients. Anyway the code for that is:

SELECT COUNT(*) AS [Sub 90% Available]
FROM (
SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
JOIN Orion.NodesCustomProperties AS NCM ON [Nodes].NodeId = NCM.NodeID
-- next line is for restricting to a specific client via a custom property
WHERE NCM.ClientPrefix LIKE '%Insert_Client_CP%'
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) < 90

Or do you need a how to import the dashboard? In which case go here: https://thwack.solarwinds.com/product-forums/the-orion-platform/f/orion-sdk/92479/importing-exporting-modern-dashboards

Or do you just need some general guidance on starting and building? In which case come back here and ask.

HTH?

Prineel

Thanks @stuartd

I believe its problem with the syntax in the query i posted.

Basically trying to get a list of the nodes which are below 99.9%

stuartd

Did you try the code I posted?

Just change the last line to whatever figure you want, and remove any JOIN or link to custom properties that you don't need/want.

e.g.

SELECT COUNT(*) AS [Sub 99.9% Available]
FROM (
    SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [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) < 99.9
    )

Prineel

Get the following: