I'm new to running sql queries and I need to run a report that solarwinds support has told me I need a custom sql report. Can someone help me build a query that will return the following: Site Server name Physical or virtual (model of server if physical) OS Version # of CPU Memory Hard drive capacity and in use I know some of these I can get from the regular drop downs but when I got to physical or virtual I couldn't find a drop down for that and solarwinds support told me I needed a custom sql query. How can I build this query and is there a good learning tool somewhere that will help me learn this on my own?

Here is his tweaked code: SELECT Nodes.Site AS Site, Nodes.Caption AS ServerName, Nodes.Vendor AS Vendor, Nodes.MachineType AS OSModel, Nodes.Description AS Hardware, Nodes.IOSVersion AS OSVersion, Nodes.CPULoad AS CPUUtil, Nodes.PercentMemoryUsed AS PercentMemory, Nodes.TotalMemory AS TotalMemory, Volumes.Caption AS Volume, Volumes.VolumeSize AS VolumeSize, Volumes.VolumeSpaceUsed AS SpaceUsed, CPU.CPUCount, CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType FROM dbo.Nodes AS Nodes LEFT JOIN ( SELECT [HostNodes].[NodeID] AS NodeID, N'H' AS NodeType FROM dbo.VIM_VirtualMachineNodes AS VMNodes LEFT JOIN dbo.VIM_HostNodes AS HostNodes ON ([HostNodes].[HostID] = [VMNodes].[HostID]) UNION ( SELECT [MACAddresses].[NodeID] AS C4, N'VM' AS C6 FROM dbo.NodeMACAddresses AS MACAddresses LEFT JOIN dbo.VIM_HostNodes AS HostNodes2 ON ([HostNodes2].[NodeID] = [MACAddresses].[NodeID]) WHERE ( ([MACAddresses].[MAC] LIKE N'0003FF%') OR ([MACAddresses].[MAC] LIKE N'000C29%') OR ([MACAddresses].[MAC] LIKE N'005056%') OR ([MACAddresses].[MAC] LIKE N'000569%') OR ([MACAddresses].[MAC] LIKE N'001C14%') ) AND ([HostNodes2].[HostID] IS NULL) ) ) AS Res ON [Res].[NodeID] = [Nodes].[NodeID] INNER JOIN dbo.Volumes AS Volumes ON Volumes.NodeID = Nodes.NodeID INNER JOIN ( SELECT Nodes.NodeID, COUNT(DISTINCT CPUIndex) AS CPUCount FROM CPUMultiLoad_Detail CPU WITH(NOLOCK) JOIN Nodes ON CPU.NodeID = Nodes.NodeID GROUP BY Nodes.NodeID ) AS CPU ON CPU.NodeID = Nodes.NodeID Good luck!

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.

How to run custom SQL reports

krfitzgerald over 11 years ago

I'm new to running sql queries and I need to run a report that solarwinds support has told me I need a custom sql report. Can someone help me build a query that will return the following:

Site
Server name
Physical or virtual (model of server if physical)
OS Version
# of CPU
Memory
Hard drive capacity and in use

I know some of these I can get from the regular drop downs but when I got to physical or virtual I couldn't find a drop down for that and solarwinds support told me I needed a custom sql query. How can I build this query and is there a good learning tool somewhere that will help me learn this on my own?

Top Replies

krfitzgerald over 11 years ago in reply to jayharris +1

I bet it will, but when I ran the query I recieved the error message Incorrect syntax near the keyword 'WITH'. here is literally what I pasted and thank you very much: WITH RES AS ( SELECT [HostNodes…

0 sirpaw over 11 years ago

Hi krfitzgerald,
I believe Site is a Custom Property you created?
You can try experimenting with some code in the Database Manager, accessible in the Advanced Features folder. Perhaps you can try the code below:
/* start */
SELECT Nodes.Site AS Site,
Nodes.Caption AS ServerName,
Nodes.Vendor AS Vendor,
Nodes.MachineType AS OS_Model,
Nodes.Description AS Hardware,
Nodes. IOSVersion AS OS Version,
Nodes.CPULoad AS CPU_Util,
Nodes.PercentMemory AS PercentMemory,
Nodes.TotalMemory AS TotalMemory,
Volumes.Caption AS Volume,
Volumes.VolumeSize AS VolumeSize,
Volumes.VolumeSpaceUsed AS SpaceUsed
FROM
Nodes INNERJOIN Volumes ON Nodes.NodeID = Volumes.NodeID
/* You can put a filter here if you want, using WHERE */
/* end */
In learning T-SQL, you have to look for books out there that can help you learn. My favorite is "Sams Teach Yourself Microsoft SQL Server T-SQL in 10 Minutes"
Regards,
Paulo
Cancel
Vote Up 0 Vote Down

Cancel
0 krfitzgerald over 11 years ago in reply to sirpaw

I just tried to copy and paste your code into the custom sql report and run the query and it said syntax error. What did I do wrong, here is the past:
/* start */
SELECT Nodes.Site AS Site,
Nodes.Caption AS ServerName,
Nodes.Vendor AS Vendor,
Nodes.MachineType AS OS_Model,
Nodes.Description AS Hardware,
Nodes. IOSVersion AS OS Version,
Nodes.CPULoad AS CPU_Util,
Nodes.PercentMemory AS PercentMemory,
Nodes.TotalMemory AS TotalMemory,
Volumes.Caption AS Volume,
Volumes.VolumeSize AS VolumeSize,
Volumes.VolumeSpaceUsed AS SpaceUsed
FROM
Nodes INNERJOIN Volumes ON Nodes.NodeID = Volumes.NodeID
/* You can put a filter here if you want, using WHERE */
/* end */
Cancel
Vote Up 0 Vote Down

Cancel
0 borgan over 11 years ago in reply to krfitzgerald

You really do not need to use a custom SQL approach. If you open a new report of the type "Custom Status of Nodes, Volumes", you should find every variable you have mentioned, including the Site if you have already created it in the Custom Property Editor. After that, all you need do is add any filters or Group by options that you need.
Cancel
Vote Up 0 Vote Down

Cancel
0 krfitzgerald over 11 years ago in reply to borgan

Really, can you tell me where the physical or virtual is? I haven't been able to find it and solarwinds support said I need to run a custom sql report.
Cancel
Vote Up 0 Vote Down

Cancel
0 jayharris over 11 years ago in reply to krfitzgerald

Assuming that you have the custom property called Site then try this edited version:
SELECT Nodes.Site AS Site,
Nodes.Caption AS ServerName,
Nodes.Vendor AS Vendor,
Nodes.MachineType AS OS_Model,
Nodes.Description AS Hardware,
Nodes. IOSVersion AS OS_Version,
Nodes.CPULoad AS CPU_Util,
Nodes.PercentMemoryUsed AS PercentMemory,
Nodes.TotalMemory AS TotalMemory,
Volumes.Caption AS Volume,
Volumes.VolumeSize AS VolumeSize,
Volumes.VolumeSpaceUsed AS SpaceUsed
FROM Nodes INNER JOIN Volumes ON Nodes.NodeID = Volumes.NodeID
Cancel
Vote Up 0 Vote Down

Cancel
0 krfitzgerald over 11 years ago in reply to jayharris

Thank you, that query worked. However it is not giving everything I want. I want to know how many cpus are in each server, and whether the server is physical or virtual. Everything else looks good, I've been looking through the database trying to find the physical or virtual but have been unsuccessful. I can see it in the node details page so I know it's tracking the info somewhere. do you know where I can find the last two items?
Cancel
Vote Up 0 Vote Down

Cancel
0 jayharris over 11 years ago in reply to krfitzgerald

Ok, so I did some digging on THWACK and cobbled this together. Does this work for you?
WITH
RES AS
(
SELECT
[HostNodes].[NodeID] AS NodeID,
N'H' AS NodeType
FROM dbo.VIM_VirtualMachineNodes AS VMNodes
LEFT JOIN dbo.VIM_HostNodes AS HostNodes
   ON ([HostNodes].[HostID] = [VMNodes].[HostID])
UNION
   (
    SELECT
     [MACAddresses].[NodeID] AS C4,
     N'VM' AS C6
                 FROM dbo.NodeMACAddresses AS MACAddresses
                 LEFT JOIN dbo.VIM_HostNodes AS HostNodes2
      ON ([HostNodes2].[NodeID] = [MACAddresses].[NodeID])
    WHERE
     (
      ([MACAddresses].[MAC] LIKE N'0003FF%') OR
                        ([MACAddresses].[MAC] LIKE N'000C29%') OR
                        ([MACAddresses].[MAC] LIKE N'005056%') OR
                        ([MACAddresses].[MAC] LIKE N'000569%') OR
                        ([MACAddresses].[MAC] LIKE N'001C14%')
                     )
                        AND ([HostNodes2].[HostID] IS NULL)
   )
),
CPUS AS
(
SELECT Nodes.NodeID, COUNT(DISTINCT CPUIndex) AS CPUCount
FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)
JOIN Nodes
ON CPU.NodeID = Nodes.NodeID
GROUP BY
Nodes.NodeID
)
SELECT Nodes.Site AS Site,
Nodes.Caption AS ServerName,
Nodes.Vendor AS Vendor,
Nodes.MachineType AS OSModel,
Nodes.Description AS Hardware,
Nodes.IOSVersion AS OSVersion,
Nodes.CPULoad AS CPUUtil,
Nodes.PercentMemoryUsed AS PercentMemory,
Nodes.TotalMemory AS TotalMemory,
Volumes.Caption AS Volume,
Volumes.VolumeSize AS VolumeSize,
Volumes.VolumeSpaceUsed AS SpaceUsed,
CPU.CPUCount,
CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType
FROM dbo.Nodes AS Nodes
LEFT JOIN Res AS Res
ON [Res].[NodeID] = [Nodes].[NodeID]
INNER JOIN dbo.Volumes AS Volumes
ON Volumes.NodeID = Nodes.NodeID
INNER JOIN CPUS AS CPU
ON CPU.NodeID = Nodes.NodeID
To give credit where it is due, I pulled new field data from these articles:
http://thwack.solarwinds.com/message/115930#115930
http://thwack.solarwinds.com/message/161293#161293
Cancel
Vote Up 0 Vote Down

Cancel
0 krfitzgerald over 11 years ago in reply to jayharris

I bet it will, but when I ran the query I recieved the error message Incorrect syntax near the keyword 'WITH'. here is literally what I pasted and thank you very much:
WITH
RES AS
(
SELECT
[HostNodes].[NodeID] AS NodeID,
N'H' AS NodeType
FROM dbo.VIM_VirtualMachineNodes AS VMNodes
LEFT JOIN dbo.VIM_HostNodes AS HostNodes
   ON ([HostNodes].[HostID] = [VMNodes].[HostID])
UNION
   (
    SELECT
     [MACAddresses].[NodeID] AS C4,
     N'VM' AS C6
                 FROM dbo.NodeMACAddresses AS MACAddresses
                 LEFT JOIN dbo.VIM_HostNodes AS HostNodes2
      ON ([HostNodes2].[NodeID] = [MACAddresses].[NodeID])
    WHERE
     (
      ([MACAddresses].[MAC] LIKE N'0003FF%') OR
                        ([MACAddresses].[MAC] LIKE N'000C29%') OR
                        ([MACAddresses].[MAC] LIKE N'005056%') OR
                        ([MACAddresses].[MAC] LIKE N'000569%') OR
                        ([MACAddresses].[MAC] LIKE N'001C14%')
                     )
                        AND ([HostNodes2].[HostID] IS NULL)
   )
),
CPUS AS
(
SELECT Nodes.NodeID, COUNT(DISTINCT CPUIndex) AS CPUCount
FROM CPUMultiLoad_Detail CPU WITH(NOLOCK)
JOIN Nodes
ON CPU.NodeID = Nodes.NodeID
GROUP BY
Nodes.NodeID
)
SELECT Nodes.Site AS Site,
Nodes.Caption AS ServerName,
Nodes.Vendor AS Vendor,
Nodes.MachineType AS OSModel,
Nodes.Description AS Hardware,
Nodes.IOSVersion AS OSVersion,
Nodes.CPULoad AS CPUUtil,
Nodes.PercentMemoryUsed AS PercentMemory,
Nodes.TotalMemory AS TotalMemory,
Volumes.Caption AS Volume,
Volumes.VolumeSize AS VolumeSize,
Volumes.VolumeSpaceUsed AS SpaceUsed,
CPU.CPUCount,
CASE WHEN Res.NodeType in ('H','VM') Then 'Virtual' Else 'Physical' END AS NodeType
FROM dbo.Nodes AS Nodes
LEFT JOIN Res AS Res
ON [Res].[NodeID] = [Nodes].[NodeID]
INNER JOIN dbo.Volumes AS Volumes
ON Volumes.NodeID = Nodes.NodeID
INNER JOIN CPUS AS CPU
ON CPU.NodeID = Nodes.NodeID
Cancel
Vote Up +1 Vote Down

Cancel
0 PavelSrot over 11 years ago in reply to jayharris

It looks good. Anyway Nodes.Site is probably your custom column, because on standard NPM instalation is not present and if I will not remove it from SELECT query I get errror. Otherwise it seems, that it produces correct results.
Cancel
Vote Up 0 Vote Down

Cancel
0 jayharris over 11 years ago in reply to krfitzgerald

Humm...I pasted from your reply into my SolarWind Report Writer as a new report and it worked as expected in both the Report Writer interface and the NPM Reports web page.

I have the following SolarWinds Apps installed on my system: Orion Core 2012.1.1, SAM 5.2.0, NCM 7.0.2, NPM 10.3.1, IVIM 1.4.0
What versions are you using?
Cancel
Vote Up 0 Vote Down

Cancel