This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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?

  • 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 

Reply Children
No Data