VoIP Lookup by Extension using NCM CDP Inventory Data

Version 1

    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.

     

     

    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