Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Need a report on Customer device availability

I need a little help with a custom SWQL report that shows the average availability of each customer devices over a defined time period, grouped by customer. I used to have a custom SQL report that worked, but it must be on an old DB schema cuz it doesn't work now on 2020.2.6. Having a good SWQL version is better anyway, right? Also, in additional to showing the availability for each individual node, I would like to show an overall "roll up" availability number for the entire collection of custom devices. Maybe this would be better a a Group report? Thoughts? Thanks.

Find more posts tagged with

Accepted answers

All comments

donrobert5

You can create custom property so that you can easily group them and then create an availability report from the web report.

support.solarwinds.com/.../Create-a-Custom-Node-Availability-Web-Based-Report-in-Orion

Also, creating it via report writer would mean it would be migrated for future use even if the Schema is changed.

KMSigma.SWI

Also - do you have the original SQL we could work from? Might be a good lesson in converting SQL to SWQL.

patriot

SELECT TOP 10000 CONVERT(DATETIME, LTRIM(MONTH(DATETIME)) + '/01/' + LTRIM(YEAR(DATETIME)), 101) AS SummaryMonth
      , Nodes.NodeID AS NodeID
      , Nodes.CustomerName AS CustomerName
      , Nodes.Caption AS NodeName
      , Nodes.IP_Address AS IP_Address
      , AVG(ResponseTime.Availability) AS AVERAGE_of_Availability
      , (
            SELECT AVG(ResponseTime.Availability)
            FROM ResponseTime
            INNER JOIN Nodes ON Nodes.NodeID = ResponseTime.NodeID
            WHERE Nodes.CustomerName = 'ACME Bird Seed'
                  AND DATETIME BETWEEN CONVERT(NUMERIC(9, 4), DATEADD(month, - 3, GETDATE()))
                        AND CONVERT(NUMERIC(9, 4), GETDATE())
            ) AS CustomerAverage
FROM Nodes
INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE (
            DATETIME BETWEEN CONVERT(NUMERIC(9, 4), DATEADD(month, - 3, GETDATE()))
                  AND CONVERT(NUMERIC(9, 4), GETDATE())
            )
      AND ((Nodes.CustomerName = 'ACME Bird Seed'))
GROUP BY CONVERT(DATETIME, LTRIM(MONTH(DATETIME)) + '/01/' + LTRIM(YEAR(DATETIME)), 101)
      , Nodes.NodeID
      , Nodes.CustomerName
      , Nodes.Caption
      , Nodes.IP_Address
ORDER BY SummaryMonth ASC
      , 2 ASC

borgan

I'd be interested in seeing this "conversion" too.

sum_giais

Hi @patriot, I made an attempt at converting this query over to SWQL. I'm not entirely sure it is going to provide the same output and work in the same manner that you may expect so I'll ask if you could give it a test and let us know. I didn't go so far as to make the SQL query work in my environment so wasn't able to compare the two on my end...

Regardless the SWQL query below I believe is close to what you need just is very possible it may require some other minor tweaks.

Anyway, just uncomment (remove the double dashes) on line 19 if you want to limit the output to a specific CustomerName. I also made the assumption that the "CustomerName" is a Custom Property assigned to Nodes.

SWQL Query:

SELECT
     DATETRUNC('month', Nodes.ResponseTimeHistory.DateTime) AS [SummaryMonth]
    ,Nodes.NodeID
    ,Nodes.CustomProperties.CustomerName
    ,Nodes.Caption AS [NodeName]
    ,Nodes.IP_Address
    ,ROUND(AVG(Nodes.ResponseTimeHistory.Availability), 2) AS [AVERAGE_of_Availability]
    ,ROUND(CustAvg.CustomerAverage, 2) AS [CustomerAverage]
FROM Orion.Nodes AS Nodes
INNER JOIN (
    SELECT
          AVG(n.ResponseTimeHistory.Availability) AS [CustomerAverage]
         ,n.CustomProperties.CustomerName
    FROM Orion.Nodes AS n
    WHERE MONTHDIFF(GETUTCDATE(), n.ResponseTimeHistory.Availability) <= 3
    GROUP BY n.CustomProperties.CustomerName
) AS CustAvg ON CustAvg.CustomerName = Nodes.CustomProperties.CustomerName
WHERE MONTHDIFF(GETUTCDATE(), Nodes.ResponseTimeHistory.Availability) <= 3
-- AND Nodes.CustomProperties.CustomerName = 'ACME Bird Seed'
GROUP BY DATETRUNC('month', Nodes.ResponseTimeHistory.DateTime)
    ,Nodes.NodeID
    ,Nodes.CustomProperties.CustomerName
    ,Nodes.Caption
    ,Nodes.IP_Address
    ,CustAvg.CustomerAverage
ORDER BY [SummaryMonth] ASC</pre>

patriot

Getting errors on the Edit Resource page. As you can see in the screenshot, there is a specific error message for the CustomerAverage field, but I can't tell what the complaint is about the other four fields.

Ideas?

sum_giais

What if you delete all the columns and re-add them? Typically thats due to mismatching column names likely a result of updating the datasource to swql

patriot

That worked. One more question. What is the time period for the report? The Where statement seems to indicate it is for the last 3 months. Is that right?