Noc view for Routing.Neighbors table
I use custom property on node - WAN_router = 1 to just report on WAN routers.
SWQL script:
select distinct t2.NodeID, t2.caption as [Caption], ' /Orion/images/StatusIcons/Small-' + t2.StatusLED + ' ' AS [_IconFor_Caption], t2.info as [Total Conn],
t2.OSPF as [OSPF Conn], t2.OSPFUpCount as [OSPF UP],
case when t2.OSPFUpCount > 0 then '/Orion/images/StatusIcons/Small-Up.gif' else '' end as [_IconFor_OSPF UP] ,
t2.OSPFNotUpCount as [OSPF Down] ,
case when t2.OSPFNotUpCount > 0 then '/Orion/images/StatusIcons/Small-Down.gif' else '' end as [_IconFor_OSPF Down],
t2.BGP as [BGP Conn], t2.BGPUpCount as [BGP UP],
case when t2.BGPUpCount > 0 then '/Orion/images/StatusIcons/Small-Up.gif' else '' end as [_IconFor_BGP UP] ,
t2.BGPNotUpCount as [BGP Down] ,
case when t2.BGPNotUpCount > 0 then '/Orion/images/StatusIcons/Small-Down.gif' else '' end as [_IconFor_BGP Down]
FROM Orion.Routing.Neighbors rn
join (SELECT rn.NodeID, n.Caption, n.StatusLED, count (*) as [Info],
sum (case when rn.ProtocolID = 13 then 1 else 0 end ) as [OSPF],
sum (case when (rn.ProtocolID = 13 and rn.ProtocolOrionStatus = 1) then 1 else 0 end ) as [OSPFUpCount] ,
sum (case when (rn.ProtocolID = 13 and rn.IsDeleted = 'True' ) then 1 else 0 end ) as [OSPFNotUpCount] ,
sum (case when rn.ProtocolID = 14 then 1 else 0 end ) as [BGP],
sum (case when (rn.ProtocolID = 14 and rn.ProtocolOrionStatus = 1) then 1 else 0 end ) as [BGPUpCount] ,
sum (case when (rn.ProtocolID = 14 and rn.ProtocolOrionStatus <> 1) then 1 else 0 end ) as [BGPNotUpCount]
FROM Orion.Routing.Neighbors rn
join Orion.Nodes n on rn.NodeID = n.NodeID
join Orion.NodesCustomProperties ncp on rn.NodeID = ncp.NodeID
where ncp.WAN_router = 1 -- << Custom property for nodes : WAN_router, to only report on WAN routers
GROUP by rn.NodeID, n.Caption, n.StatusLED
) as t2 on rn.NodeID = t2.nodeid
