This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report CDP Neighbors

s_che over 7 years ago

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}

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.

Top Replies

0 d09h over 7 years ago

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.AvgResponseTime	Node.CPULoad	Node.Caption
Node.Contact	Node.DNS	Node.Description
Node.GroupStatus	Node.IOSImage	Node.IOSVersion
Node.IPAddress	Node.LastBoot	Node.LastSync
Node.Location	Node.MachineType	Node.MaxResponseTime
Node.MemoryUsed	Node.MinResponseTime	Node.NodeID
Node.ObjectSubType	Node.OrionID	Node.PercentLoss
Node.PercentMemoryUsed	Node.ResponseTime	Node.Severity
Node.Status	Node.StatusDescription	Node.SysName
Node.SysObjectID	Node.SystemUpTime	Node.TotalMemory
Node.Vendor	Node.VendorIcon	Node.ID

0 jodelgado over 7 years ago in reply to d09h

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.
Cancel
Vote Up +2 Vote Down

Cancel
0 s_che over 7 years ago in reply to jodelgado

We don't use NCM. We're using NPM 12.0.1 and this report doesn't exist unfortunately.
Cancel
Vote Up 0 Vote Down

Cancel
0 s_che over 7 years ago in reply to d09h

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 s_che over 7 years ago in reply to jodelgado

Could you share the settings of this report so I would be able to create it?
Thanks.
Sylvain.
Cancel
Vote Up 0 Vote Down

Cancel
0 d09h over 7 years ago in reply to s_che

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 s_che over 7 years ago in reply to d09h

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?
Cancel
Vote Up 0 Vote Down

Cancel
0 blsanner over 7 years ago in reply to s_che

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}
Cancel
Vote Up +3 Vote Down

Cancel
0 s_che over 7 years ago in reply to blsanner

I was so close of the solution...
Thanks a lot!
Cancel
Vote Up 0 Vote Down

Cancel
0 cnorborg over 7 years ago

You might find this of interest... Been pretty popular...
CDP_Neighbor_Resource_v2.SWQL
Cancel
Vote Up +1 Vote Down

Cancel