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?

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

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

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

  • can you please assist with SWQL behind these KPI widgets?

  • I imported and edited the dashboard to get the SWQL. From the great KMSigma Importing/Exporting Modern Dashboards - Orion SDK - The Orion Platform - THWACK - SolarWinds Community


    )

    SELECT COUNT(*) AS [100% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) = 100
    )
    
    SELECT COUNT(*) AS [99.9% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 100
       AND AVG([Nodes].ResponseTimeHistory.Availability) >= 99.9
    )
    
    SELECT COUNT(*) AS [99% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 99.9
       AND AVG([Nodes].ResponseTimeHistory.Availability) >= 99
    )
    
    SELECT COUNT(*) AS [95% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 99
       AND AVG([Nodes].ResponseTimeHistory.Availability) >= 95
    )
    
    SELECT COUNT(*) AS [90% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 95
       AND AVG([Nodes].ResponseTimeHistory.Availability) >= 90
    )
    
    SELECT COUNT(*) AS [Sub 90% Available]
    FROM (
    SELECT [Nodes].Caption
         , AVG([Nodes].ResponseTimeHistory.Availability) AS [Availability]
    FROM Orion.Nodes AS [Nodes]
    WHERE [Nodes].VendorInfo.Name = 'Windows' -- Put whatever filters here
    AND [Nodes].ResponseTimeHistory.DateTime BETWEEN GETDATE() - 7 AND GETDATE() -- this is where you define your time period
    GROUP BY [Nodes].Caption
    HAVING AVG([Nodes].ResponseTimeHistory.Availability) < 90
    )

  • Thanks Steven,  works like a champ