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 

  • what would the query be to list the nodes that are 100% or 99% for example?

    Doesn't have to be a modern dashboard widget, could also be report or custom query widget.

  • Here is an example for listing the nodes that are between 99 and 99.9%. The second example from above. You just need to remove the Count section.

    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
    
    

Reply
  • Here is an example for listing the nodes that are between 99 and 99.9%. The second example from above. You just need to remove the Count section.

    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
    
    

Children
No Data