This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

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

Uptime Report SQL query

tburns.mcts over 2 years ago

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

Parents

0 KMSigma.SWI over 2 years ago

Are you specifically looking for a report? Because this isn't hard to mock up in a Modern Dashboard.

If it is a report, what fields do you want and over what timeframe?
Cancel
Vote Up +1 Vote Down

Cancel

Reply

0 KMSigma.SWI over 2 years ago

Are you specifically looking for a report? Because this isn't hard to mock up in a Modern Dashboard.

If it is a report, what fields do you want and over what timeframe?
Cancel
Vote Up +1 Vote Down

Cancel

Children

0 tburns.mcts over 2 years ago in reply to KMSigma.SWI

I need a report since management doesn't have a login to Solarwinds. This report will show that availability over the last 7 days

I am looking for something like the dashboard shown. But we will also need the Node, Availability, and Node Notes for all nodes with less than say 99% uptime over the week.
Cancel
Vote Up 0 Vote Down

Cancel
0 tburns.mcts over 2 years ago in reply to KMSigma.SWI

We are setting up the report to email weekly. This is mostly going to our Exec team and I know they will not log in to Solarwinds. They will probably only spend about 2 min looking at the report.
Cancel
Vote Up +1 Vote Down

Cancel
0 KMSigma.SWI over 2 years ago in reply to tburns.mcts

Sorry - dumb question, but how are they going to get reports if they don't have a login?

Why not give them a (read-only) login and make their default page this dashboard?

I'm just trying to understand the needs.
Cancel
Vote Up 0 Vote Down

Cancel
0 naga.vempati over 2 years ago in reply to KMSigma.SWI

HI,

Can you share this query to create the above mentioned dashboard.

If i click on any tab , will it take it to list of nodes ? Please share me query on naga.vempati@gmail.com
Cancel
Vote Up 0 Vote Down

Cancel
0 KMSigma.SWI over 2 years ago in reply to naga.vempati

I uploaded the dashboard (which contains the query to the Content Exchange.
Cancel
Vote Up 0 Vote Down

Cancel
0 mmayer5823 over 2 years ago in reply to KMSigma.SWI

Hey Kevin, I would like to run just the SWQL queries separately to add some of these to a exec dashboard without the JSON. Is that possible?
Cancel
Vote Up 0 Vote Down

Cancel
0 Prineel over 2 years ago in reply to KMSigma.SWI

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

Cancel

0 stevenstadel over 2 years 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 mmayer5823 over 1 year ago in reply to stevenstadel

Thanks Steven, works like a champ
Cancel
Vote Up 0 Vote Down

Cancel
0 Prineel over 1 year ago in reply to stevenstadel

Thanks stevenstadel
Cancel
Vote Up 0 Vote Down

Cancel