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

Parents
  • Hi. when you mention the query runs correctly, is that via the database manager connecting back to your SolarWinds SQL box? 

    When entering the command structure into the build process of the report, double-check when you enter the SQL code into the content field you are selecting the correct syntax, as within the advanced reporting option you can select SQL or SWQL that are slightly different from each other. 

    Another thing that sometimes when I am writing code is that it can get funny around "spacing" or hashed out comments, this is something that could also be tried (remove un-needed spaces and hashed comments)? 

Reply
  • Hi. when you mention the query runs correctly, is that via the database manager connecting back to your SolarWinds SQL box? 

    When entering the command structure into the build process of the report, double-check when you enter the SQL code into the content field you are selecting the correct syntax, as within the advanced reporting option you can select SQL or SWQL that are slightly different from each other. 

    Another thing that sometimes when I am writing code is that it can get funny around "spacing" or hashed out comments, this is something that could also be tried (remove un-needed spaces and hashed comments)? 

Children
No Data