Solved: Using SQL to convert from to 6 digit dial plan

alabedz99 · ‎06-23-2008

I'm wondering whether or not anyone has used SQL to modify the Numplan and DeviceNumPlanMap tables in order to migrate from a 4 to 6 digit dial plan. I have a small implementation so I figured I would make the few changes to route patterns/translation patterns/hunt pilto/etc... by hand. I wrote a stored procedure and have been testing it without any issues (at this point at least). All I'm changing is the DNOrPattern (Device table), the label (DeviceNumPlanMap) and deletions to CFBDestination/CFBIntDestination/PFFDestination/PFfIntDestination.

Tim Smith · ‎06-23-2008

Hi,

Yep I was going to say that.. Cisco doesnt like you to touch the SQL DB directly.

They have AXL which is an API to CallManager.

It is the supported way to provision and make changes to the database programatically. (if i even spelt that right ;)

You access via SOAP over HTTP requests.

Dont trust it completely though. It is supposed to be a middle ware layer to protect you from doing something stupid. But it has let me add duplicate route patterns before :)

You can download the AXL programming guide from Cisco web site, and if you go to the Developer Support Central you can get the XML schema etc.

You can get information out using pre-defined methods - getPhone, getLine etc

Or you can send a SQL query in.

You can then process the information and send back the modifications.

If you are doing this just once, then the initial BAT suggestion is a good one.. but learning AXL and writing some tools in the long run can save you a heap of time.

Cheers,

Tim.

View solution in original post

Tommer Catlin · ‎06-23-2008

Programmers!...... just joking. There is actually an easier way. If your 4 digit plan in Unity is all unique, and you were to simply prepend a number for a "site code" you can do this through the Bulk Edit Tool. It even has a test function.

For example:

Subscriber with xxxx

Pre pend with 60

New subscriber number is 60xxxx

In your Voicemail profiles on CUCM, you will want to create a profile for the 60xxxx from that phone.

Now after re-reading your plan, I would recommend not going into SQL on CUCM. (or Unity if you can )

If you were to migrage to a new plan in CUCM, I would use BAT to change the digits on the lines from 4 to 6 in CSV, then push it back through CUCM to update the devices.

Create all your RPs, Translations, etc by hand or by bulk if you like.

It is sometimes easier to just go ahead and BAT the phones/Lines/users out to a file. Delete all phones/users/lines and then re-add them back in with the correct settings. I find it easy in CSV/excel to do this and quicker. With this method though, be careful about speeddials, fastdials, ccmuser settings, etc.

hope this helps

alabedz99 · ‎06-23-2008

Thanks for reply and the Unity tip is great. In CUCM 4.2.3, I was really trying to prevent using the BAT utility because of the amount of speed dials and ccmuser settings. Also, I have to go in and run a query anyway to set the 'Logged into hunt group' parameter because BAT will not do it (TAK Engineer recommendation). Why do you think Cisco considers it a greater benefit to export ALL phones, delete ALL phones and then import ALL phones instead changing a single (non-linked)field? Sorry, the little programmer in me popped out again :)

Tommer Catlin · ‎06-23-2008

I dont think it is necessary cisco saying delete all the phones, I was simply speaking from experience on what has worked for me.

You find a better administrative experience in CUCM 6.x. There are more parameters and more available BAT tools with the GUI interface to update, change, etc. But then again, you will not the luxury of SQL anymore. ;)

When you BAT you the phones/Users/Lines, it will include all the speed dial stuff, etc. just have to use BAT a couple times to get it all back into place.

So BAT everything out. This is your master copy.

Delete all phones, users, etc in CUCM

Now, BAT the phones in with their new 6 digit lines.

Now BAT the users in (without speed dials.)

Bat in devices/lines associated to users

Bat in the associated devices/users with their old speeddial settings.

The thing with BAT is (or at least I have found) it's a "stepping" process. It will not handle everything at once.

alabedz99 · ‎06-23-2008

Glad to here they made an effort in v6. Do you know what the actual recommended method is from cisco? I know my management will ask me that at some point. I'm still very apprehensive about making such huge changes to numerous tables (when I delete everything and import) as opposed to a few fields in 2 tables.

Tommer Catlin · ‎06-23-2008

Well, the one I did awhile back, that is what I did. I had about 10 sites and 400 phones to "adjust". I ended up deleting them by site, then adjusting the dial plan. Once the adjustment was done, I went into Unity and fixed their subscriber extensions.

You can email TAC ask them what they recommend. But its more of an engineering "battle plan" more than a recommended way from cisco.

alabedz99 · ‎06-23-2008

I actually have an open ticket with TAC, it seems as though they will not technically 'support' my solution and I am still waiting on a response regarding any Cisco recommended solution. I will post when they answer.

Now I'm just curious if anyone else has done this? any which way, recommended or not.

Tim Smith · ‎06-23-2008

Hi,

Yep I was going to say that.. Cisco doesnt like you to touch the SQL DB directly.

They have AXL which is an API to CallManager.

It is the supported way to provision and make changes to the database programatically. (if i even spelt that right ;)

You access via SOAP over HTTP requests.

Dont trust it completely though. It is supposed to be a middle ware layer to protect you from doing something stupid. But it has let me add duplicate route patterns before :)

You can download the AXL programming guide from Cisco web site, and if you go to the Developer Support Central you can get the XML schema etc.

You can get information out using pre-defined methods - getPhone, getLine etc

Or you can send a SQL query in.

You can then process the information and send back the modifications.

If you are doing this just once, then the initial BAT suggestion is a good one.. but learning AXL and writing some tools in the long run can save you a heap of time.

Cheers,

Tim.

Chad Stachowicz · ‎06-24-2008

Quick PERL Example! Adding route patterns!

alabedz99 · ‎06-25-2008

Thanks chadstachowicz!

Chad Stachowicz · ‎06-25-2008

No probs!

Chad

florian.kroessbacher · ‎06-25-2008

Hy You!

how should the Pattern.txt look like

thanx Floh

Chad Stachowicz · ‎06-25-2008

Floh,

it should be called RoutePatternInput.txt it should be in the same folder. Attached is my test import I did. all 70k route patterns succeeded. Please rate useful stuff!

HTH,

Chad

alabedz99 · ‎06-04-2009

I ended up building a spreadsheet which queried Call Manager and retrieved a list of phones by type. This allowed me to highlight a range of phones, then (using VB and a stored procedure shown below) modify the existing DN to the new DN including the phone label. Attached is the spreadsheet also.

ALTER PROCEDURE [dbo].[AppendDN]

(@DN varchar(128)

,@AppendVal varchar(6)

,@ResultStat varchar(100) OUTPUT )

AS

BEGIN

SET NOCOUNT ON;

Update_Data:

-- Update the NumPlan.DNOrPattern (DN) with new value

UPDATE dbo.NumPlan

SET DNOrPattern = @AppendVal + DNOrPattern

WHERE tkPatternUsage = '2'

and DNOrPattern = @DN

-- Update the DeviceNumPlanMap.Label with new value

UPDATE dbo.DeviceNumPlanMap

SET Label = @AppendVal + @DN

WHERE Label = @DN

Set @ResultStat = ((@DN + ': Numplan records changed: ' + convert(varchar(3),(Select count(DNOrPattern) from dbo.NumPlan where DNOrPattern = @AppendVal + @DN)) + ' - DeviceNumPlanMap records changed: ' + convert(varchar(3),(Select count(Label) from dbo.DeviceNumPlanMap where Label = @AppendVal + @DN))))

END

alabedz99 · ‎06-04-2009

Correction, I can send you the spreadsheet if your interested.