SQL for unused DN?

Version 1
    This document was generated from CDN thread

    Created by: Michael Schmidt on 24-06-2013 06:30:59 AM
    Hi,

    is there any SQL command which I can use to find all unused / not configured DN in a specified range.

    For example with this SQL I can see the used DN in the range 6110 - 6119:

    select num.dnorpattern, d.name, d.description from device as d,devicenumplanmap,numplan as num where d.pkid=devicenumplanmap.fkdevice and devicenumplanmap.fknumplan=num.pkid and num.dnorpattern like '611%'

    For example the DN 6112 and 6119 are free (unused and alos not configured at CUCM).

    It there an SQL which can display only these both unused / not configured and also not assigned phone numbers?

    BR
    Michael

    Subject: RE: SQL for unused DN?
    Replied by: Martin Sloan on 24-06-2013 07:58:18 AM
    Hi Michael,

    As far as 'not configured' DN's go, you'll need to maintain a tracking system with the numbers that should be in the system and manage them accordingly.  It boils down to a process issue where numbers need to be removed/updated whenever someone comes or goes.  It's a challenge of maintaining the sytem and I've seen customers use specific partitions to house DN's that are 'available' to be assigned to a new user, but the system doesn't provide the function of tracking which numbers are missing, outside of you or someone else doing a basic searches to find out.  There might be a 3rd party management tool that does this, but I haven't seen it.

    To get a list of DN's that are unassiged, you could use the Administration GUI and go to System->Route Plan Report and the far left hand menu contains an item for 'unassigned DN's'.  This is good for a quick check for someone who's not requesting the info with the API.  You can also use a SQL query like this:

    select n.dnorpattern from numplan n left outer join devicenumplanmap m on m.fkdevice = n.pkid where m.fkdevice is null and n.tkpatternusage = 2 order by n.dnorpattdnorpattern

    As an approach to your problem, get a difinitive list of extension numbers and start checking the sytem from there.  Say you have a 3XX range of DN's you could run a query to ask for all numbers in the numpan table that begin with 3 (use the typepatternusage enum 2 for DN's) and programatically compare the results to your defined list of extensions that should exist.  This will return you the missing extensions.  From there, use the query above to locate 'unassigned' DN's and you have the complete list.

    HTH
    Marty

    Subject: RE: SQL for unused DN?
    Replied by: Martin Sloan on 24-06-2013 08:20:11 AM
    Martin Sloan:
    Hi Michael,

    As far as 'not configured' DN's go, you'll need to maintain a tracking system with the numbers that should be in the system and manage them accordingly.  It boils down to a process issue where numbers need to be removed/updated whenever someone comes or goes.  It's a challenge of maintaining the sytem and I've seen customers use specific partitions to house DN's that are 'available' to be assigned to a new user, but the system doesn't provide the function of tracking which numbers are missing, outside of you or someone else doing a basic searches to find out.  There might be a 3rd party management tool that does this, but I haven't seen it.

    To get a list of DN's that are unassiged, you could use the Administration GUI and go to System->Route Plan Report and the far left hand menu contains an item for 'unassigned DN's'.  This is good for a quick check for someone who's not requesting the info with the API.  You can also use a SQL query like this:

    select n.dnorpattern from numplan n left outer join devicenumplanmap m on m.fkdevice = n.pkid where m.fkdevice is null and n.tkpatternusage = 2 order by n.dnorpattdnorpattern

    As an approach to your problem, get a difinitive list of extension numbers and start checking the sytem from there.  Say you have a 3XX range of DN's you could run a query to ask for all numbers in the numpan table that begin with 3 (use the typepatternusage enum 2 for DN's) and programatically compare the results to your defined list of extensions that should exist.  This will return you the missing extensions.  From there, use the query above to locate 'unassigned' DN's and you have the complete list.

    HTH
    Marty

    Sorry for my copy/paste oversight, but I wouldn't use the 'order by n.dnorpattdnorpattern' at the end of the select.  No use in making your CUCM do the dirty work when you could do this on the client side just as easy with a sort function.

    Subject: RE: SQL for unused DN?
    Replied by: Michael Schmidt on 25-06-2013 12:57:29 AM
    Hi Marty,

    many thanks for your reply.

    BR
    Michael