14 Replies Latest reply on Mar 12, 2017 1:52 PM by jodelgado

    Report CDP Neighbors

    s_che

      Hi there,

       

      I'm trying to create a report to list CDP neighbors for our every Cisco nodes.

      I'm able to retrieve the Node ID, the Local Interface, the Remote Node, the remote Interface and the IP address of the remote node.

       

      SELECT cps.NodeID AS [Node], cps.Status AS [Local Interface], cdp.IPAddress AS [IP Address], cdp.DeviceId AS [Neighbor], cdp.DevicePort AS [Remote Interface]

      FROM Orion.NodeCdpEntry cdp

      INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular cps

      ON cdp.NodeID = cps.NodeID

      WHERE cdp.IfIndex LIKE cps.RowID AND cdp.NodeID = ${NodeID}

       

      1.png

      Now I would like to have the name of the Local Node instead of the Local Node ID, but I'm struggling with the SWQL query.

       

      Any idea on how to display the local Node name?

       

      Thanks in advance!

       

      Regards,

      Sylvain.

        • Re: Report CDP Neighbors
          d09h

          Would this work for you?  Seems to be what you're trying to accomplish unless I'm missing something.

           

          How to change an SQL query into SWQL query

           

          Also, according to this Use SolarWinds Query Language (SWQL - SWIS) - SolarWinds Worldwide, LLC. Help and Support

           

          Node.Caption might be what you're looking for.

           

          Built-in SWQL Nodes Properties

          Node.AvgResponseTimeNode.CPULoadNode.Caption
          Node.ContactNode.DNSNode.Description
          Node.GroupStatusNode.IOSImageNode.IOSVersion
          Node.IPAddressNode.LastBootNode.LastSync
          Node.LocationNode.MachineTypeNode.MaxResponseTime
          Node.MemoryUsedNode.MinResponseTimeNode.NodeID
          Node.ObjectSubTypeNode.OrionIDNode.PercentLoss
          Node.PercentMemoryUsedNode.ResponseTimeNode.Severity
          Node.StatusNode.StatusDescriptionNode.SysName
          Node.SysObjectIDNode.SystemUpTimeNode.TotalMemory
          Node.VendorNode.VendorIconNode.ID
            • Re: Report CDP Neighbors
              jodelgado

              I'm using NCM 7.5 and under "All Reports"   there's already a very detailed CDP neighbor report you can run.

               

               

              In case this report does not show you all of your devices/interfaces, you will have to run an inventory update.

              • Re: Report CDP Neighbors
                s_che

                Hi d09h,

                 

                This is the field I wanted to use but it belongs to another table (Orion.NodesData) and I don't find the right SWQL statement to refer to it. That's my problem.

                  • Re: Report CDP Neighbors
                    d09h

                    In the first like I provided, How to change an SQL query into SWQL query

                    there was a solution provided by blsanner -- note that this is a SWQL query and not a SQL query:

                     

                    SELECT cps.Status AS [Local Interface],

                    cdp.IPAddress AS [IP Address],

                    cdp.DeviceId AS Device,

                    cdp.DevicePort AS [Remote Interface]

                    FROM  Orion.NodeCdpEntry cdp

                    INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular cps

                    ON cdp.NodeID = cps.NodeID

                    WHERE cdp.IfIndex LIKE cps.RowID

                    AND cdp.NodeID = ${NodeID}

                     

                    The link I provided also has a solution leveraging Report Writer.

                      • Re: Report CDP Neighbors
                        s_che

                        I have already read your link, and I took the SWQL query from blsanner as a basis.

                         

                        What I would like is to pick the name of the local device, e.g. the Node.Caption field and insert it into my CDP report.

                        BUT the Node.Caption belongs to another table (Orion.NodesData) and I don't know how to refer to it in the SWQL query. (because there is already a JOIN statement).

                         

                        Do you understand my problem?

                          • Re: Report CDP Neighbors
                            blsanner

                            You can get it by simply adding a second JOIN statement such as the following:

                             

                            SELECT n.Caption AS [Local Device],

                            cps.Status AS [Local Interface],

                            cdp.IPAddress AS [IP Address],

                            cdp.DeviceId AS Device,

                            cdp.DevicePort AS [Remote Interface]

                            FROM  Orion.NodeCdpEntry cdp

                            INNER JOIN Orion.NPM.CustomPollerStatusOnNodeTabular cps

                            ON cdp.NodeID = cps.NodeID

                            INNER JOIN Orion.Nodes n

                            ON cdp.NodeID = n.NodeID

                            WHERE cdp.IfIndex LIKE cps.RowID

                            AND cdp.NodeID = ${NodeID}

                            1 of 1 people found this helpful
                    • Re: Report CDP Neighbors
                      Craig Norborg

                      You might find this of interest...  Been pretty popular...

                       

                       

                      CDP_Neighbor_Resource_v2.SWQL

                        • Re: Report CDP Neighbors
                          d09h

                          For something like this that we administrators can create and leverage, I have to wonder why it's not already part of NPM.  Perhaps because it was never specifically requested as a feature?

                           

                          Some things seem so obviously useful (CDP neighbors, duplex and speed mismatch between nodes), it seems an easy kill for SolarWinds to bake into the product.  Especially for Cisco and Microsoft nodes. As I typed this last sentence, an even bigger one came to mind--native ability to show SNMP or WMI not functioning.