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="thwack.solarwinds.com/.../View.aspx,cast(NodeID as varchar),'">',StatusDescription,'</>') AS [v] from nodes where nodeid=${nodeid}

union all (select 'IP Address' as [c], concat('<a href="thwack.solarwinds.com/.../View.aspx,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="thwack.solarwinds.com/.../View.aspx,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