Web Reports to SQL - need to convert normal web report to SQL queries

hi guys, pls help. I have these weekly reports I built in report builder, but now I need help with the SQL equivalent. I'm not so good at SQL queries. Pls see my screenshot for my reports table for my web report. I really appreacite any help, thank you.

Availibility and Response Time for nodes Weekly

Volume Usage Weekly

Memory Usage (Volume Usage) Weekly

CPU Weekly

Find more posts tagged with

reports_in_npm

sql_report

Comments

donrobert5

thwack.solarwinds.com/.../get-the-sql-query-of-web-based-report

RAJPAD

@donrobert5 thank you,can you advise which tables store the Node average availability and average reponse time

donrobert5

Unfortunately, I no longer have a Solarwinds environment so I can't really say. Try NodesData, I really don't keep it on top of my head. I just usually check via Database Manager before.

adam.beedell

Download the Orion SDK, there's a search bar on SWQL Stufio on there

Note: That gives you SWQL not SQL, but usually you want to use SWQL, and if not it's a good start to then plug into SSMS.

RAJPAD

@adam.beedell thank you, will let you know if I find what Im looking for, I do have the SWQL studio installed, and have SSMS on my server.

KMSigma.SWI

All of this data is easily shown in SWQL. The below has ALL the elements in it, but I'd personally break it out for different areas.

SELECT TOP 1000 [Nodes].NodeID
     , [Nodes].ObjectSubType
     , [Nodes].IPAddress
     , [Nodes].Caption AS [Node Name]
     , [Nodes].NodeDescription
     , [Nodes].Description
     , [Nodes].ResponseTimeHistory.ObservationTimestamp
     , [Nodes].ResponseTimeHistory.Availability
     , [Nodes].ResponseTimeHistory.AvgResponseTime
     , [Nodes].Volumes.Caption AS [Volume Name]
     , [Nodes].Volumes.VolumeUsageHistory.ObservationTimestamp AS [VolumeCheckDateTime]
     , [Nodes].Volumes.VolumeUsageHistory.AvgDiskUsed
     , [Nodes].CPULoadHistory.ObservationTimestamp AS [MemCPUCheckDateTime]
     , [Nodes].CPULoadHistory.AvgMemoryUsed AS [AvgMemoryUsed]
     , [Nodes].CPULoadHistory.AvgLoad AS [AvgCpuLoad]
FROM Orion.Nodes AS [Nodes]

Be aware that this is a MONSTEROUS query because it goes back as far as you have in history. You can always add WITH QUERYSTATS to the end of a SWQL query to get some details - including the actual SQL that's being executed.

This is the SQL behind this SWQL query:

SET DATEFIRST 7;
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT TOP (1000) [T1].[NodeID] AS C1
      ,[T1].[ObjectSubType] AS C2
      ,[T1].[IP_Address] AS C3
      ,[T1].[Caption] AS C4
      ,[T1].[Description] AS C5
      ,[T1].[MachineType] AS C6
      ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T2].[DateTime]) AS C7
      ,[T2].[Availability] AS C8
      ,[T2].[AvgResponseTime] AS C9
      ,[T3].[Caption] AS C10
      ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T4].[DateTime]) AS C11
      ,[T4].[AvgDiskUsed] AS C12
      ,DateAdd(second, DateDiff(second, GetDate(), GetUtcDate()), [T5].[DateTime]) AS C13
      ,[T5].[AvgMemoryUsed] AS C14
      ,[T5].[AvgLoad] AS C15
FROM dbo.NodesData AS T1
LEFT JOIN dbo.CPULoad AS T5
      ON [T5].[NodeID] = [T1].[NodeID]
            AND 0 = 0
LEFT JOIN dbo.Volumes AS T3
      ON [T3].[NodeID] = [T1].[NodeID]
            AND 0 = 0
LEFT JOIN dbo.VolumeUsage AS T4
      ON [T4].[VolumeID] = [T3].[VolumeID]
            AND 0 = 0
LEFT JOIN dbo.ResponseTime AS T2
      ON [T2].[NodeID] = [T1].[NodeID]
            AND 0 = 0

Before we run down this entire path, you should ask: what are they trying to accomplish with this set of reports? Are they trying to do forecasting? (because the platform already does that) Are they trying to do trending? (because the platform also does that).

Another final warning: There is a very real chance that the database behind this data will change and render the SQL useless. This is why we encourage people - whenever possible - to use SWQL. SWQL insulates you from backend database changes.

RAJPAD

@KMSigma.SWI thank you so much, I watched a few of your video, really awesome content. appreaciate all you effort. Really what I am trying to do is automate my reporting, so that I dont have to run them manually each week, and the reason for this is because there is a known bug within my reporting timestamp, checked by tech support #01267512, and there isnt a patch for it as yet. This is why I am trying to extract the reporting data using an alternative, SSIS JOB to PowerBI and produce, last week vs this week reports. many thanks.

adam.beedell

Under Manage Reports there's a Schedule option