< BACK TO TO THE MAGIC OF SQL SCRIPTS SERIES LIST
Here is a simple SQL script for getting info on your routing neighbours
Final Result:
SQL Source Code:
SELECT
NPM_RoutingNeighbor_V.NodeID
,'Down.gif' AS 'ICON-STAT'
,NPM_RoutingNeighbor_V.NeighborID
,Nodes.Caption AS 'NODE'
,Nodes.IP_Address AS 'IP'
,NPM_RoutingNeighbor_V.NeighborIP AS 'NIEGHBOR'
,rp.DisplayName AS 'PORT'
,NPM_RoutingNeighbor_V.DisplayName + ' (' + si.StatusName + ')' AS 'STAT'
,rn.AutonomousSystem AS 'ASN'
,CASE
WHEN DATEDIFF(DAY, rn.LastChange, getdate()) > 3
THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, rn.LastChange, getUTCdate())) + ' days ago'
ELSE CASE
WHEN DATEDIFF(HOUR, rn.LastChange, getdate()) > 3
THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, rn.LastChange, getdate())) + ' hours ago'
ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, rn.LastChange, getdate())) + ' min ago'
END
END AS 'CHANGED'
FROM SolarWinds.dbo.NPM_RoutingNeighbor_V
---------------------------------------------
INNER JOIN SolarWinds.dbo.Nodes Nodes ON Nodes.NodeID=NPM_RoutingNeighbor_V.NodeID
INNER JOIN SolarWinds.dbo.NPM_RoutingProtocol rp ON rp.ProtocolID=NPM_RoutingNeighbor_V.ProtocolID
INNER JOIN Solarwinds.dbo.NPM_RoutingNeighbor rn ON rn.NeighborID=NPM_RoutingNeighbor_V.NeighborID
INNER JOIN Solarwinds.dbo.StatusInfo si ON si.StatusId=NPM_RoutingNeighbor_V.OrionStatus
---------------------------------------------
WHERE
Nodes.n_mute_dashb <> 'True' AND --either create custom property "n_mute_dashb" as YES/NO for muting nodes in this report OR delete this line
Nodes.UnManaged = 0 AND --node is not unmanaged
(
NPM_RoutingNeighbor_V.OrionStatus = 2 AND
NPM_RoutingNeighbor_V.IsDeleted <> 1 --Exclude deleted routes
)
To add "on page" resource simply use the above SQL to create Report with Report Writer (or download attached one) and then add this report on any page with "Report from Orion Writer" resource. Remember to update Database name in SQL script
Best Luck,
Alex Soul
www.pixace.com
