12/7/2021 7:32 AM

12/7/2021 7:32 AM

This query uses a weighted average to calculate node availability. This may be useful in circumstances where you may prefer to configure the maximum weight to filter out records that happen after the polling marks the node down or the node was unmanaged. This query should be used with detailed response time records only as hourly and daily records will be aggregated using normal averages.
Download the report and import it to the Orion Web Console.

Orion Platform

Download and import the report

To use this report, download it and import it to your Orion Platform.

1. Download the xml report from THWACK.
2. Log in to your Orion Web Console using an account with report management privileges.
3. Click Reports > All Reports in the menu bar, and click Manage Reports in the upper-right corner.
4. Display the web-based reports.
5. Click Export/Import, and then click Import Report.
6. Navigate to the required XML file on a network drive, and then click Open.

The file will be imported and its name displayed at the top of the list of reports.

Query 

You can also copy the following SQL query and use it where appropriate.

DECLARE @DefaultWeight AS INT, @InitialWeight AS INT, @MaxWeightThreshold AS INT SET @DefaultWeight = 300; SET @InitialWeight = @DefaultWeight; SET @MaxWeightThreshold = 300; WITH weighted AS ( SELECT resp.NodeID, resp.PercentLoss, resp.Availability, resp.Timestamp, CASE WHEN ISNULL(DATEDIFF(SECOND, LAG(resp.Timestamp,1) OVER (PARTITION BY resp.NodeID ORDER BY resp.Timestamp), resp.Timestamp), @InitialWeight) > @MaxWeightThreshold THEN @DefaultWeight ELSE ISNULL(DATEDIFF(SECOND, LAG(resp.Timestamp,1) OVER (PARTITION BY resp.NodeID ORDER BY resp.Timestamp), resp.Timestamp), @InitialWeight) END as [limitedWeight] FROM ResponseTime_CS_cur resp ) SELECT w.NodeID, DATEADD(hour, DATEDIFF(hour, 0, [TimeStamp]), 0) AS GroupedHourly, SUM(w.PercentLoss) / COUNT(w.PercentLoss) AS AvgPercentLoss, SUM(w.PercentLoss * w.[limitedWeight])/SUM(w.[limitedWeight]) AS AvgWeightedPercentLoss, SUM(w.Availability) / COUNT(w.Availability) AS AvgAvailability, SUM(w.Availability * w.[limitedWeight])/SUM(w.[limitedWeight]) AS AvgWeightedAvailability FROM weighted w WHERE w.[limitedWeight] > 0 -- do not count in multiple polls within the same second GROUP BY DATEADD(hour, DATEDIFF(hour, 0, [TimeStamp]), 0), NodeID ORDER BY GroupedHourly ;

-- Scripts are not supported under any SolarWinds support program or service.
-- Scripts are provided AS IS without warranty of any kind. SolarWinds further
-- disclaims all warranties including, without limitation, any implied warranties
-- of merchantability or of fitness for a particular purpose. The risk arising
-- out of the use or performance of the scripts and documentation stays with you.
-- In no event shall SolarWinds or anyone else involved in the creation,
-- production, or delivery of the scripts be liable for any damages whatsoever
-- (including, without limitation, damages for loss of business profits, business
-- interruption, loss of business information, or other pecuniary loss) arising
-- out of the use of or inability to use the scripts or documentation.