SOAP/AXL to pull the numbers

[balukr]

We need to change 1000 existing extensions in CM to new range.

Since there are lot of 2/3 lines and shared lines phones. I need some kind of tool/App so I can pull existing numbers from CM SQL database to Excel and replace it.

Even if I can't replace the numbers at least if I can pull the existing database to Excel that would be great.

I was checking on this forum more engr's are using this SOAP/AXL for similar requirement.

I have no experience on this SOAP/AXL so I need our experts advice how to do this.

Your help is much appreciated.

Thanks

Balu

[bmateev]

Let me know what version of CM you're running.

--Borislav

[balukr]

We are running CM 4.1.3 SR 3a

[bmateev]

I can write you a SQL query to pull all the data you need. Can you let me know what exactly you need?

The AXL is very slow plus very clumsy to write.

Let me know if interested.

[balukr]

We need Name/Description, MAC address, Directory numbers (include all line apperances).

Can we dump that to Excel spread sheet?

[bmateev]

Here's what you do:

1. On the CM run SQL Query Analyzer

2. Connect to the CM server

3. Change the database to CCM0304

4. Run my query:

SELECT d.Name, d.Description, np.DNOrPattern, dnp.NumPlanIndex

FROM NumPlan np INNER JOIN

DeviceNumPlanMap dnp ON np.pkid = dnp.fkNumPlan INNER JOIN

Device d ON dnp.fkDevice = d.pkid

WHERE (dnp.fkDevice IN

(SELECT d.pkid

FROM Device d, DeviceNumPlanMap dnp, NumPlan np

WHERE np.pkid = fkNumPlan AND d.pkid = dnp.fkDevice))

FYI: the last selected field is the line appearance number (1,2,3,etc.)

5. When the result is shown in the grid below the query, click on it to select it.

6. Go to File->Save As and select CSV format

7. Open the file with Excel

--Borislav

[balukr]

Thank you so much.You are the man...

Is it possible to also get Call Forward Busy and Forward No Answer for each extension in the same query.?

I just realized that I need to change that too to reflect the new numbers.

I really appreciate your help on this.

Thanks again

Balu

[bmateev · Accepted solution]

You're welcome.

Here's the query that has Forward All, Forward Busy and Forward No Answer included:

SELECT d.Name, d.Description, np.DNOrPattern, np.CFADestination, np.CFBDestination, np.CFNADestination, dnp.NumPlanIndex

FROM NumPlan np INNER JOIN

DeviceNumPlanMap dnp ON np.pkid = dnp.fkNumPlan INNER JOIN

Device d ON dnp.fkDevice = d.pkid

WHERE (dnp.fkDevice IN

(SELECT d.pkid

FROM Device d, DeviceNumPlanMap dnp, NumPlan np

WHERE np.pkid = fkNumPlan AND d.pkid = dnp.fkDevice))

[balukr]

Again you are the man ...Thanks a bunch

[balukr]

Hope you won't mind if I bug you again .

I was able to dump the queries in to a spread sheet.Now is there anyway I can use that spread sheet change the numbers and run more queries to replace them.

Again I really really ....appreciate your help.

Balu

[bmateev]

Yes, it is possible but a bit more complicated.

Do you need to update only the DN or FA, FNA, FB as well?

I might be able to help you but I don't know if I can attach an Excel template here.

--Borislav

[balukr]

Yeah I need to update DN,CFB and FNA not FA.

If I can do with Excel that would be great.Since there are almost 1000 extensions I can do all the numbers in Excel easily first then change it.

[bmateev]

Please post the Excel file with just one line so I can update it with the query you need.

If you can't post the file, just paste a CSV line from your export.

[balukr]

Here is the excel file.Again Thank you so much for doing this.

[thierry.berwart_2]

Hi !

I have to do almost the same :

For 1700 phones, we have to change All DN because we pass from 4 digits to 6 digits.

That means we have to update all DN, Forward on busy internal & external, Forward on no answer internet & external...

I checked if it's possible with BAT but it isn't.

The only way I found is :

To export all phones

Create a new BAT File

delete all phones

insert the new BAT File.

But do you think it's possible with SQL Queries ? is it supported by cisco ? I don't know maybe it's dangerous because some tables should be linked with the DN ?!

What do you think ?

Thanks you a lot !

Thierry

PS We are using CCM 4.2(3)sr1