This discussion has been locked.

You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Uptime Report SQL query

Hello,

I am working on creating an uptime report on our systems. Management would like the report to include totals in the report as well as notes.

I have a SQL query written that pulls this data and reports correctly. When I put this query into Solarwinds, the query is not supported. I am thinking this has something to do with the temp table I am creating. How can I modify this query for Solarwinds to create the report?

here is the query

/* Gets average availability for the last 7 days */

SELECT Nodes.Vendor AS Vendor, Nodes.Caption AS Node_Name, Nodes.MachineType AS Machine_Type, Nodes.Server_Class,
   AVG(ResponseTime.Availability) AS Availability
INTO #UptimeReport
FROM Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
   ( datetime >= DATEADD(DAY, -7, GETDATE()) AND datetime < GETDATE() )
   AND ((Nodes.Vendor NOT LIKE 'Cisco%') AND (Nodes.Vendor NOT LIKE 'Compa%') AND (Nodes.Vendor NOT LIKE 'Dell%')
    )
GROUP BY Nodes.Caption, Nodes.MachineType, Nodes.Vendor, Nodes.Server_Class
go

Select * from #UptimeReport
Order by Availability Desc
go

Select COUNT (*) as TOTAL from #UptimeReport
go

select count(*) as '100%' from #UptimeReport
where Availability >= 100
go

select COUNT(*) as '>=99.9%' from #UptimeReport
where Availability > 99.9 and Availability < 100

select COUNT(*) as '<=99.9-0%' from #UptimeReport
where Availability < 99.9
go

Select #UptimeReport.Node_Name, #UptimeReport.Availability, dbo.NodeNotes.Note
from #UptimeReport, dbo.NodeNotes, dbo.NodesData
Where #UptimeReport.Node_Name = dbo.NodesData.Caption
   and dbo.NodeNotes.NodeID = dbo.NodesData.NodeID
   and Availability < 99.9
order by Availability
go

drop table #UptimeReport
go

Top Replies

0 Prineel 11 months ago in reply to KMSigma

KMSigma can you please assist with SWQL behind these KPI widgets?
Cancel
Up 0 Down

Cancel

0 stevenstadel 11 months ago in reply to Prineel

I imported and edited the dashboard to get the SWQL. From the great KMSigma Importing/Exporting Modern Dashboards - Orion SDK - The Orion Platform - THWACK - SolarWinds Community

)

SELECT COUNT(*) AS [100% Available]
FROM (
SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].VendorInfo.Name = 'Windows' -- 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
)

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

SELECT COUNT(*) AS [99% Available]
FROM (
SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].VendorInfo.Name = 'Windows' -- 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) < 99.9
   AND AVG([Nodes].ResponseTimeHistory.Availability) >= 99
)

SELECT COUNT(*) AS [95% Available]
FROM (
SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].VendorInfo.Name = 'Windows' -- 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) < 99
   AND AVG([Nodes].ResponseTimeHistory.Availability) >= 95
)

SELECT COUNT(*) AS [90% Available]
FROM (
SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].VendorInfo.Name = 'Windows' -- 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) < 95
   AND AVG([Nodes].ResponseTimeHistory.Availability) >= 90
)

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

0 mmayer5823MortgageConnect 10 months ago in reply to stevenstadel

Thanks Steven, works like a champ
Cancel
Up 0 Down

Cancel
0 Prineel 10 months ago in reply to stevenstadel

Thanks stevenstadel
Cancel
Up 0 Down

Cancel
0 Prineel 10 months ago in reply to stevenstadel

stevenstadel what would the query be to list the nodes that are 100% or 99% for example?

Doesn't have to be a modern dashboard widget, could also be report or custom query widget.
Cancel
Up 0 Down

Cancel

0 stevenstadel 10 months ago in reply to Prineel

Here is an example for listing the nodes that are between 99 and 99.9%. The second example from above. You just need to remove the Count section.

SELECT [Nodes].Caption
     , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
FROM Orion.Nodes AS [Nodes]
WHERE [Nodes].VendorInfo.Name = 'Windows' -- 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) < 99.9
   AND AVG([Nodes].ResponseTimeHistory.Availability) >= 99