06-23-2008 10:08 AM - edited 03-18-2019 09:10 PM
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.
Solved! Go to Solution.
06-23-2008 11:50 PM
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.
06-23-2008 11:31 AM
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
06-23-2008 12:13 PM
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 :)
06-23-2008 12:20 PM
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.
06-23-2008 12:41 PM
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.
06-23-2008 12:44 PM
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.
06-23-2008 12:58 PM
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.
06-23-2008 11:50 PM
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.
06-24-2008 03:21 PM
06-25-2008 09:22 AM
Thanks chadstachowicz!
06-25-2008 10:26 AM
No probs!
Chad
06-25-2008 10:39 AM
Hy You!
how should the Pattern.txt look like
thanx Floh
06-25-2008 01:11 PM
06-04-2009 07:55 AM
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
06-04-2009 11:10 AM
Correction, I can send you the spreadsheet if your interested.
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: