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


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)
    ( 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

Select * from #UptimeReport
Order by Availability Desc

Select COUNT (*) as TOTAL from #UptimeReport

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

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

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

drop table #UptimeReport