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
