cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Node Details - SQL

The out-of-the-box node details does not give much information, plus it's enormous.  In some cases, it takes up half of the screen.  Too much white space between the lines.  There are also fields which we generally don't need also - we can't organize it in a customizable way.  For example, the client does not normally need to see location, contact, sysObjectID.

The SQL script will let you customize the Node Details to display much more relevant information (CPULoad and number of CPUs, Memory percent and total memory, etc)

pastedImage_1.png

select 'Status' as [c], concat('<img src="/Orion/images/StatusIcons/Small-',StatusLED,'"></>','<a href="https://thwack.solarwinds.com/Orion/View.aspx?View=NodeDetails&NetObject=N:',cast(NodeID as varchar),'">',StatusDescription,'</>') AS [v] from nodes where nodeid=${nodeid}

union all (select 'IP Address' as [c], concat('<a href="https://thwack.solarwinds.com/Orion/View.aspx?View=NodeDetails&NetObject=N:',cast(NodeID as varchar),'">',IP_Address,'</>') AS [v] from nodes where nodeid=${nodeid})

union ALL (select 'Machine Type' as [c], concat('<img src="/NetPerfMon/Images/Vendors/',VendorIcon,'"></>','<a href="https://thwack.solarwinds.com/Orion/View.aspx?View=NodeDetails&NetObject=N:',cast(NodeID as varchar),'">',vendor,' - ',machinetype,'</>') AS [v] from nodes where nodeid=${nodeid})

union ALL (select 'DNS' as [c],dns as [v] from nodes where nodeid=${nodeid})

union ALL (select 'System Name' as [c], sysname as [v] from nodes where nodeid=${nodeid})

union ALL (select 'Last Boot' as [c], cast(lastboot as varchar) as [v] from nodes where nodeid=${nodeid})

union ALL (select 'Software Version' as [c], IOSversion as [v] from nodes where nodeid=${nodeid})

union ALL (select 'Software Image' as [c], IOSImage as [v] from nodes where nodeid=${nodeid})

union all (select 'CPU' as [c], case when CPULoad < 0 then 'N/A' else (select cast(count(nodeid) as varchar)  as [v] from CPUMultiLoad_Current where nodeid=c.nodeid) + '  (' + cast(CPULoad as varchar) +'% Load)' end as [vv] from nodes c where c.nodeid=${nodeid} group by c.nodeid, cpuload)

union all (select 'MEM' as [c],

case when PercentMemoryUsed <0 then 'N/A' else

concat(case when TotalMemory/1024/1024/1024>1 then cast(round(TotalMemory/1024/1024/1024,2) as varchar)+' GB'

when TotalMemory/1024/1024>1 then cast(round(TotalMemory/1024/1024,2) as varchar)+' MB'

        when TotalMemory/1024>1 then cast(round(TotalMemory/1024,2) as varchar)+' KB'

        else cast(TotalMemory as varchar)+' B' end,

' (', cast(PercentMemoryUsed as varchar) ,'% Used)') end as [v] from nodes where nodeid=${nodeid})

union all (SELECT 'Hardware' as [c], (select case when vim.HostID is null then 'Physical' else 'Virtual' end as [v] FROM Nodes n left outer join VIM_VirtualMachines vim on vim.IPAddress=n.IP_Address where n.nodeid=${nodeid}))

union all

(

select 'Polling' as [c]--, (select ObjectSubType from nodes where nodeid=${nodeid}) as [v]

,(select

case when n.ObjectSubType = 'WMI' then 'WMI: '+(Select distinct c.Name from Credential c inner join NodeSettings ns on c.ID=ns.SettingValue inner join Nodes nn on ns.NodeID=n.NodeID where ns.SettingName ='WMICredential'  and ns.nodeid =n.nodeid)

when n.ObjectSubType ='SNMP' and n.Community='' then 'SNMPv3: '+(SELECT c.Name FROM NodeSettings ns join Credential c on c.ID=ns.SettingValue where ns.NodeID=n.nodeid and (ns.settingname like '%wmi%' or ns.settingname like '%snmp%'))

when n.ObjectSubType = 'SNMP' then 'SNMPv2: '+(case when n.Community not like '' then n.Community else 'SNMPv3'end) collate SQL_Latin1_General_CP1_CI_AS

when n.ObjectSubType = 'ICMP' then 'ICMP'

when n.ObjectSubType = 'Agent' then 'Agent'

else 'Unknown' end as [SNMP]

from nodes n

where nodeid=${nodeid}

) as [x]

)

union all (SELECT 'CP: Department' as [c], concat(n.department,'')  as [v] FROM Nodes n where n.nodeid=${nodeid})

Thank you,

Amit

-CSCOENGINEER

Loop1 Systems: SolarWinds Training and Professional Services

Labels (1)
Tags (2)
Comments

@cscoengineer , I hope you don't mind but I added your widget to the collection I'm putting together on Github.

https://github.com/Mesverrum/MyPublicWork/blob/master/ViewsToShare/SingleWidgets/Node_Details%20-%20...

You can use the resource importer tool to import these single widget XML files on to a view

https://github.com/Mesverrum/MyPublicWork/blob/master/ResourceImporter.ps1

I'm going through Thwack and trying to gather up as many useful bits like this and aggregate them all so that it becomes easier to distribute custom widgets around the community and get everything set up in new environments.

 

 

Excellent effort @cscoengineer  I'm going to steal this for sure.

@mesverrum I like the GitHub repository idea, that is an excellent way to share these little nuggets without having to go through the hassle of trawling the forums.

Very nice!

Cool stuff. You could probably get a widget looking pretty much identical to the default Node details by using a Custom HTML + CSS + AJAX + SWQL.

Will post the code if  I get around to working on it.

I oft wondered the same but never had time to dig into this. This is excellent work @cscoengineer . Well done. A beer for you on me if you are ever in Ireland 😉

Taking this for myself as well! Hoping to share some of my own some day. I got some charts and stuff I want to share but still figuring out how to do it via SQL/SWQL still. And I don't want to create the same 3 charts, 382 times. 😭

 

How do we import this via the UI?

Version history
Revision #:
1 of 1
Last update:
‎02-18-2020 04:00 PM
Updated by: