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
  • 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?

  • 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.

  • 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.

  • 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.

Reply
  • 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.

Children
  • 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.