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

  • I think there might already be a report like this in Reports - The Orion Platform - Content Exchange.  If not, then the table will be your problem.  I'd also recommend building this in SWQL if you can.  It's insulated from any database schema changes as you upgrade.

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

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

  • I've honestly spent too much time on this, but I thought it was an interesting challenge.  Some people might find a better way to do it, but this report is what I came up with.

  • 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

  • I uploaded the dashboard (which contains the query to the Content Exchange.

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