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.

VoIP Lookup by Extension using NCM CDP Inventory Data

Now I am not sure how many of you would benefit from this but I thought i would share as it is so very useful to us.

Basic infrastructure is Cisco Switches, Mitel VoIP phones, NPM/NCM and CDP Inventory enabled.

To easily find which switch a phone was last seen on to add to the suppprt call notes (1st line support use+).

Add the extension number and search..and you get the below.

Switch Name, Port, Last Seen Date etc from CDP neighbour info.

pastedImage_0.png

This is a report in SQL Reporting Services. The code behind it is.

Create a datasource to your NCM database.

The query for this report is as below:

(anything with DCC is a custom field -so please amend to your needs)

/*

Testing only - you need to create a Parameter for @Extension

Declare @Extension int

Set @Extension = 31576

*/

SELECT TOP (100) PERCENT

dbo.Nodes.CoreNodeID,

dbo.Interfaces.NodeID,

dbo.Nodes.NodeCaption,

dbo.Nodes.DCC_Area_Code,

dbo.Nodes.dcc_Area_Description,

dbo.Nodes.DCC_Site_Code,

dbo.Interfaces.InterfaceName,

dbo.Interfaces.InterfaceAlias,

RIGHT(REPLACE(dbo.Cisco_CDP.RemotePlatform, ' ', ''),LEN(dbo.Cisco_CDP.RemotePlatform) - CHARINDEX('DN', dbo.Cisco_CDP.RemotePlatform) - 2) AS Extension,

dbo.Cisco_CDP.LastDiscovery,

dbo.Cisco_CDP.FirstDiscovery, dbo.Cisco_CDP.ifIndex, dbo.Cisco_CDP.RemoteDevice, dbo.Cisco_CDP.RemoteIPAddress, dbo.Cisco_CDP.RemoteVersion,

dbo.Cisco_CDP.RemotePlatform, dbo.Interfaces.InterfaceDescription

FROM         dbo.Cisco_CDP

INNER JOIN

dbo.Interfaces ON dbo.Cisco_CDP.NodeID = dbo.Interfaces.NodeID

AND dbo.Cisco_CDP.ifIndex = dbo.Interfaces.InterfaceIndex

INNER JOIN

dbo.Nodes ON dbo.Cisco_CDP.NodeID = dbo.Nodes.NodeID

WHERE    

(dbo.Cisco_CDP.RemotePlatform LIKE '%Mitel%')

AND (RIGHT(REPLACE(dbo.Cisco_CDP.RemotePlatform, ' ', ''),LEN(dbo.Cisco_CDP.RemotePlatform) - CHARINDEX('DN', dbo.Cisco_CDP.RemotePlatform) - 2) = @Extension)

ORDER BY dbo.Interfaces.NodeID

For Mitel CDP neighbour info you get the VoIP number in the 'dbo.Cisco_CDP.RemotePlatform' field, which what you see when you do a sh cdp nei detail

Check your inventory data - it is 100x easier to search this way than via NCM for this info quickly.


I hope someone will benefit from this emoticons_happy.png