Know Your Routing Neighbours

Version 3

    < 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:

    rn.JPG

     

    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

    report.JPG

     

    Best Luck,

     

    Alex Soul

    www.pixace.com