Node Status with Application, Memory, CPU, Disk Volume

Do you have to show a report in the exact format you showed? Honestly, that would be challenging and like you mentioned would require a custom SQL statement with several table joins. I'm no SQL guru so i can help there. There are Node statuses and node child status. You'd have to reference the NodeID for your joins.

The Node itself should contain the Response Time, Packet Loss, CPU and Memory. There is another table for volumes and probably a few tables for Application/Components.

There are some other ways that might still get you close to what you're looking for via a custom dashboard view or a report.

In the All Applications resource/widget, you can group application in a handful of ways. You just need to click the 'edit' link on the top right of the resource and look in the 'Grouping Applications' section. There are 3 levels deep you can group by.

In the end, these solutions may not fit what you're looking for, I'm just trying to show you other alternatives that might accomplish something close to what you're after that is a lot easier.

I was able to adapt some of my old queries to do this much of it pretty quickly, obviously mine is not 100% identitical to your request but hopefully you will be able to tailor it as you need.  There are a lot of assumptions built in here that may not apply in your case so I want to clarify them in case you need to modify the query.

The query assumes you HAVE to start from the list of groups, as in nodes that are not in any groups do not show up at all.  Also since we start from the group, and any node can be a member of multiple groups then it has the potential to have duplicates whenever a node is in multiple groups.  If you want all nodes regardless of group membership you could start from the nodes table instead of the way I did it and then left join the containermembers and get what you need. Next it assumes you wanted all the applications that are assigned to each group node and ignores if the groups had application members as part of the group.  So if, for example, you have a group where you had an application monitor, but the parent node is not in the group, then its not going to show that application here.  For me that would be an unlikely case so I didn't take the time to write logic for it. It also does not have anything in it for disk volumes, hopefully you can do that part yourself, you would just need to left join orion.volumes to nodes on volumes.nodeid=nodes.nodeid and pick which columns you want to include and how you want to define the critical/warning stuff for your icons.  Natively solarwinds reports volume status and the icon it uses based on up/down/unmanaged/unreachable kind of values, but if it was my report I would also add case logic into the report to switch to warning/critical icons based on the capacity thresholds as well.

select c.container.Name as [Group]
,c.container.DetailsUrl as [_linkfor_Group]
, n.Caption as Node
, c.detailsurl as [_linkfor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,CASE when cpuload < 0 then 'Not Polled'
else concat(cpuload,'%')
end as [CPU Load]
,concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgCPULoad&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_CPU Load]
,CASE WHEN cpuload>= n.CpuLoadThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'
WHEN cpuload< n.CpuLoadThreshold.Level2Value and cpuload >= n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN cpuload< n.CpuLoadThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_CPU Load]
,case when percentmemoryused < 0 then 'Not Polled'
else concat(percentmemoryused,'%')
end as [Memory Used]
,concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=HostAvgPercentMemoryUsed&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Memory Used]
,CASE WHEN percentmemoryused>= n.percentmemoryusedThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'
WHEN percentmemoryused< n.percentmemoryusedThreshold.Level2Value and percentmemoryused >= n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif' WHEN percentmemoryused< n.percentmemoryusedThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Memory Used]
,concat(responsetime,'ms') as [Latency]
,concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=AvgRt&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Latency]
,CASE WHEN responsetime>= n.responsetimeThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'
WHEN responsetime< n.responsetimeThreshold.Level2Value and responsetime >= n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN responsetime< n.responsetimeThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Latency]
,concat(percentloss,'%') as [Packet Loss]
,concat('/Orion/NetPerfMon/CustomChart.aspx?chartName=PercentLoss&NetObject=N:',n.nodeid,'&Period=Today') as [_linkfor_Packet Loss]
,CASE WHEN percentloss>= n.percentlossThreshold.Level2Value THEN '/Orion/images/StatusIcons/Small-Down.gif'
WHEN percentloss< n.percentlossThreshold.Level2Value and percentloss >= n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Warning.gif'
WHEN percentloss< n.percentlossThreshold.Level1Value THEN '/Orion/images/StatusIcons/Small-Up.gif'
END AS [_IconFor_Packet Loss]
,a.Name as [Application]
,a.detailsurl as [_linkfor_Application]
,'/Orion/images/StatusIcons/Small-' + a.statusdescription + '.gif' AS [_IconFor_Application]
,a.applicationalert.ComponentsWithProblems
from orion.nodes n
join orion.nodescustomproperties cp on cp.nodeid=n.nodeid
join Orion.ContainerMembers c on c.memberprimaryid=n.nodeid
left join orion.apm.application a on a.nodeid=n.nodeid
where MemberEntityType like 'orion.nodes'
--and c.containerid like ${id}
--and n.caption like '%${SEARCH_STRING}%'
order by n.caption

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

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

Thank you. I achieved it with some custom and complex SQL queries with the help of a DBA.

HI, Great,

Would you like to share  the SQL query please