Comprehensive Server Report (CPU, Memory, Disk)

Smells like SQL/SWQL

First you need to understand what you mean by CPU, Memory ... are these last values, average values? If average - do you really think that it is going to give you good idea about performance over 30 days? Same for network usage - it can be at the very bottom for the most part of the month but then one day springs to very top and overloads your channel, resulting in P1 - kind of a scenario that should be picked up by the alert. However, your report will not reflect this was happening. Besides, in your report you show KBps - is it good, bad - how do I know that? Maybe percentage is better? Then again - will it be average, max, min, current?

Once again, someone asks for a simple server summary report - this should be out of the box.

So many threads, so little reports.

ye, spot on.

I actually advise all greenfield SolarWinds engineers to go though all reports and see what they like. It will save a lot of time down the line not needing to re-invent the wheel, but rather do some minor fine-tuning

Yes, I used SQL.

I want to combine the CPU, memory and disk volume tables to get the required report data.

agree.....you try to make the product easy even for the average admin yet the reports section you have to know SQL to get even a basic CPU/MEMORY/DISK asset report which is ridiculous.  Please make our lives easier and let me pick all the items I want displayed.

Let's not overstate the problem, cpu/memory/disk is extremely easy to do in the GUI, no SQL at all.

Where things get complicated it when you want to start building reports with multiple different types of child objects, and this is tricky in the GUI because it is tricky in SQL.  For the most part the developers have opted to not put options into the GUI that would be messy in SQL and I tend to agree with that approach.

I literally just ran a training session for my coworkers this morning and we covered this exact scenario and why it gets messy.

When you start your report it asks you what kind of object you want to report on, in this case you would pick volumes.

Then you'd probably just pick the columns for Node Caption, Node CPULoad, Node PercentMemoryUsed, Volume Caption, VolumePercentUsed

Done.  Easy.  You have an output roughly similar to this:

The Node level stuff gets duplicated which is kind of annoying but you can live with it.

Now you can't choose more than one type of object in the GUI because it gets super ugly in SQL/SWQL to be joining different tables of other object types back to the node like that.

The common request is "I want a report showing everything I'm monitoring on a node and I don't want it to be 3 different tables, it needs to all be in one"

So lets say you want the above info, plus the name of each interface, and probably the utilization, and you want the name and status of any SAM apps on the node.  In SQL or SWQL if you start fusing together different types of objects you end up with a mess like this:

1 node, with 3 volumes, 1 interface, 3 app monitors requires 9 lines to display and there ends up being a ton of duplicates because of fundamental aspects of how SQL joins data sets.

If i had a 2nd interface on this node it would double the number of rows to 18 for just this node and make things even more of a mess to display.  Every child object adds exponentially to the number of rows it takes to present the data.

A tool that can parse that information and make it look clean starts to be a something unlike the web report writer and a lot more like SQL Reporting Services, which runs a WYSIWYG part for the layout overall, and then you populate the subsections within by using lots of smaller queries.  Any of you who are strong in SSRS aren't going to be the ones complaining about Orion because Orion is child's play compared to the level of complexity you find in a tool like that.  Solarwinds hasn't set out to build a tool like that because those tools already exist and if you are trained in using them then why would you even bother to buy/learn the Solarwinds version?  SSRS is already free for everyone who is using Solarwinds since it is included in your MS SQL license.  Solarwinds web based reporting is like a multitool with a little screwdriver in it.  For an IT handyman it does the job fairly well for most use cases.  If you have plans to build a shed full of reports then you might want to spring for a power drill, and get your goggles on because things are about to get a lot more complicated than what you had been doing with your leatherman.

Now, because I hate to see people complain without offering a productive solution, I wrote a report that DOES in fact give you all the info you want, and it DOESN'T cause all that ugly duplication.  You know what it requires?  A relatively strong understanding of relational data sets and some of the advanced functions of SQL as well as the limitations when using those functions.  Trying to embed this kind of trickery into a GUI based, general purpose, web based tool that any noob can walk up to and figure out in a few minutes is hard.  I can't find fault in Solarwinds focusing dev efforts on things that don't just duplicate features of SSRS.

SELECT   

    n.caption as Node,   

    '/netperfmon/images/vendors/' + n.VendorIcon as [_IconFor_Node],

    n.DetailsUrl as [_LinkFor_Node],

    n.ipaddress as IP_Address,   

    n.ObjectSubType as Polling_Method,   

    n.StatusDescription as Status,  

    '/Orion/images/StatusIcons/Small-' + n.StatusLED AS [_IconFor_Status], 

    '' as [ ],

    n.nodeid as [_linkfor_ ]

FROM orion.nodes n  

UNION ALL (  

SELECT   

    (' - '+ I.Caption) as Interface,  

    '/netperfmon/images/interfaces/' + i.InterfaceIcon AS [_IconFor_Interface],    

    i.DetailsUrl as [_LinkFor_Interface],

    CASE WHEN i.IPAddress.IPAddress is not null then i.IPAddress.IPAddress

    ELSE ip.IPAddress

    END AS IP_Address,    

NULL as Polling_Method,   

concat(tostring(InPercentUtil),'% RX Utilization, ',tostring(OutPercentUtil),'% TX Utilization') as Status,

    '/Orion/images/StatusIcons/Small-' + i.Statusicon AS [_IconFor_Status],

    '' as [ ],

    i.nodeid as [_linkfor_ ]

FROM Orion.NPM.Interfaces I

left join orion.NodeIPAddresses ip on ip.NodeID=i.NodeID and ip.IPAddressType='IPv4' and ip.interfaceindex is null

)

UNION ALL (  

SELECT   

    (' -  '+V.Caption) as Volume,  

    '/NetPerfMon/images/Volumes/' + V.VolumeTypeIcon AS [_IconFor_Volume], 

    v.DetailsUrl as [_LinkFor_Volume],

    NULL as IP_Address,   

    NULL as Polling_Method,   

    concat(round(v.VolumePercentUsed,0),'% of ',round(v.volumespaceavailable/1073741824,0),' GB total') as Status, 

    '/Orion/images/StatusIcons/Small-' + v.StatusLED AS [_IconFor_Status],

    '' as [ ],

    v.nodeid as [_linkfor_ ]

FROM Orion.Volumes V  

)  

UNION ALL (

SELECT   

    (' -   '+A.Name) as Application,  

    '/orion/apm/images/statusicons/Small-App-Blank.gif' AS [_IconFor_Application], 

    a.DetailsUrl as [_LinkFor_Volume],

    NULL as IP_Address,   

    NULL as Polling_Method,   

    a.StatusDescription as Status, 

    '/Orion/images/StatusIcons/Small-' + a.StatusDescription + '.gif' AS [_IconFor_Status],

    '' as [ ],

    a.nodeid as [_linkfor_ ]

FROM Orion.APM.Application a  

)  

ORDER BY [_linkfor_ ], node desc

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

I'm getting errors trying to add this to a custom chart web report - is this query working for 12.3?

I guess I'll try legacy report writer next.

Thanks

its written for the custom query resource, won't work correctly in any other resource.

You'll never see me post anything for legacy report writer haha, I constantly discourage people from using it.

Thank you sir. It still doesn't resolve the issue of getting a general server inventory report though. Your view works, but for 1500 node shops with 50 different customers this resource view just won't really work from an inventory reporting standpoint.

I realize SSRS is the way to go, but the mere mortals out here in "I can't code SQL" land are out of luck. Coding is a beautiful talent, but most SolarWinds admins in the field are not coding experts. I sure wish I was. I could probably afford that new boat I've been wanting if I had nnja coding skills.

I think a basic report showing each server's hardware components should be readily available out of the box. I sure wish a hero would create something one day that all Solar admins really need. One other issue is other inventory reports I've used that are posted here don't show CPU count for Linux hosts, but do for Windows. Getting all the CPU/MEM/Disk details in a grouped inventory report is the holy grail for me. I had to spend about 3 hours making a manual one the other day for the boss. Copy/Paste hell indeed.

Thanks again as always for your insanely awesome contributions to the community.