Is it possible to Add route patterns in bulk?

[lkrol]

Does anyone know of any means of adding route patterns into Cisco Callmanager in bulk? I have to add about 400 route patterns and I would like to do it with a CSV file or something similar if it is at all possible.

Thank you!

[pcresswell]

I am not aware of any tools that perform this task. However, if you are familiar with SQL it should be simple enough to add the route patterns using a SQL script.

In the CCM database the route patterns are stored in the NumPlan table (along with all DNs). The way I would go about this would be to manually add the first route pattern to the database using CCMAdmin, then examin this new record in the NumPlan table. You can then write a SQL script to insert new records that match the data in this record, but changing the necessary fields.

I.E:

SQL Field: CCMAdmin Field:

dNOrPattern Route Pattern

fkRoutePartition Partition (Foreign Key from Route Partition table)

fkDialPlan Numbering Plan (Foreign Key from Dial Plan table)

fkRouteFilter Route Filter (Foreign Key from Route filter table)

............................... you get the idea.

I know this seems a lot like hard work but it's really not that bad once you get started, especially if it saves you doing 400 manually :-)

[bphelps]

Do you know a good resource to learn SQL, at least the basic stuff?

I want to be able to search for calling search spaces that are applied to phone lines, but there is no way to do this using the current web interface. You can only find css's associated with devices.

And other batch editing scripts sound useful. Just backup before you hackup.

[pcresswell]

There are loads of books on SQL, you can pick up the basics in no time. Even if you have to refer to the book when writing the query.

Meanwhile try:

select NumPlan.dNOrPattern, CallingSearchSpace.name

from NumPlan, CallingSearchSpace

where NumPlan.fkCallingSearchSpace_SharedLineAppear = CallingSearchSpace.pkid

[lkrol]

I took a look at the CCM database and i have a question.

How is the pkid field generated? Is it coming from another table or will it be generated automatically if i add records to the NumPlan table.

thanks!

[pcresswell]

the pkid field is a guid, so when you write your INSERT statement use

NEWID() to generate a new guid.

e.g:

insert into CallingSearchSpace

values (NEWID(), 'CSS_NOPSTN', 'No access to PSTN', NULL,'PAR_INTERNAL',NULL)

Pete