Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 

Using SQL Query to obtain the list of Directory Numbers assigned to devices

For CUCM 4.x and earlier, you can use the following query in the SQL query analyzer  in order to get the list of your directory numbers as they are assigned to devices.


  1. Select Start > Programs > Microsoft SQL Server > Query Analyzer on the Windows 2000 server console of the Cisco CallManager server, to run the SQL Server Query Analyzer application.
  2. In the Database field, click the drop-down arrow and choose the highest numbered Cisco CallManager database. Cisco CallManager databases are labeled with the format CCM03xx (where xx is the number of the database).

  3. Enter this SQL query in the body of the Query - (local) window:


              select d.name,  d.description, n.dnorpattern, dmap.numplanindex

              from  device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid

                   inner  join numplan as n on dmap.fknumplan=n.pkid

              order  by d.name,dmap.numplanindex

    The above query will give you a list of devices with their associated directory numbers.The dmap.numplanindex corresponds to the line appearance on the phone. This will also show directory numbers multiple times if they are shared line appearances OR if you had identical Directory Numbers in different partitions.


  4. If you want to list the unique appearances of lines only, you can try this:


              select n.DNOrPattern as 'DN', r.name as 'partition', d.name as 'devname', d.description as 'devdescription',dmap.E164Mask
               from NumPlan as n
                   inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
                  inner join Device as d on dmap.fkDevice = d.pkid
                  inner join RoutePartition as r on r.pkid = n.fkRoutePartition
               where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 =(select count(*)
                       from NumPlan as ndup
                           inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
                           inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
                           inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
                           where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like 'ADP%')))))
               order by n.DNOrPattern,r.name


  5. Using the above query, you will get patterns that are not shared line appearances (shared line DNs are not displayed).  If you want just shared line DNs, then you can use the following query:



              select distinct n.DNOrPattern,r.name
               from NumPlan as n
                   inner join DeviceNumPlanMap as dmap on dmap.fkNumPlan = n.pkid
                   inner join Device as d on dmap.fkDevice = d.pkid
                   inner join RoutePartition as r on r.pkid = n.fkRoutePartition
               where ((n.tkPatternUsage = 2 and d.name not like ('%ADP%')) and (1 != (select count(*)
                       from NumPlan as ndup
                           inner join RoutePartition as rdup on rdup.pkid=ndup.fkRoutePartition
                           inner join DeviceNumPlanMap as dmapdup on dmapdup.fkNumPlan = ndup.pkid
                           inner join Device as ddup on ddup.pkid=dmapdup.fkDevice
                           where ((ndup.dNOrPattern = n.dNOrPattern) and (rdup.name = r.name) and (ddup.name not like '%ADP%')))))
               order by n.DNOrPattern,r.name



    The information is sourced from: https://supportforums.cisco.com/thread/2007848?tstart=150        

Version history
Revision #:
1 of 1
Last update:
‎05-31-2010 04:31 AM
Updated by:
 
Labels (1)