cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
28015
Views
111
Helpful
20
Replies

Information on how to run SQL queries on the CUCM itself please

alalli
Level 2
Level 2

Good Day All,

I need to run an sql query on the CUCM to list all of my directory numbers, their partition, and their external mask values.

I came across this excerpt below earlier so I have a bit of an idea how to do it but iw would be great to see some other examples of sql queries.

Any assistance is most appreciated.

Also, is there a document somewhere to tell me how to run these queries?

Thanks in advance

Regards

Amanda

*******************************************************************************************************************************************************************************

Try if running this SQL query from the CLI helps you, it should list all DN's that have CFA enabled to VM or a DN:

run sql select dnorpattern,cfadestination,cfavoicemailenabled from CallForwardDynamic c, numplan n where c.fknumplan = n.pkid and (cfadestination != '' or cfavoicemailenabled ='t')
2 Accepted Solutions

Accepted Solutions

Aaron Harrison
VIP Alumni
VIP Alumni

Hi Amanda

Basically it's standard SQL, so it wouldn't hurt to google 'informix select statements' and do a little reading to get the basics. There are millions of permutations of queries so it's a matter of understanding the syntax, and then applying that to the database in question. The only difference when running commands from the CLI are that:

- You prefix the standard informix SQL statement with 'run sql'

- You don't get any help from CUCM with the syntax, so you might be well advised to use something that understands SQL a little and colorises it as you type, and then paste the resulting commands into the CUCM SSH window. I use a text editor named JEdit, if you create a text file and save it as a file ending in .sql it will highlight your syntax.

- Other programs are available that do reasonable syntax highlighting (e.q. SquirrelSQL) that are designed for querying the DB directly, but you can't actually query directly against the DB for security reasons. You'd still have to copy/paste the commands.

Now... to understand the DB you'll need a reference describing all the tables etc. This is here:

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_programming_reference_guides_list.html

Pick your version of CUCM and download the 'Data Definition' document.

A few notes on the command:

run sql : is just the CLI command that tells the shell to run the following text as SQL.

select : the SQL command to retrieve data

dnorpattern,cfadestination,cfavoicemailenabled : the column names to retrieve

callforwarddynamic c, numplan n : the names of two tables, and the abbreviations you want to refer to them as

where c.fknumplan = n.pkid : this tells SQL to return values from the two tables where these fields match up. In the data definition you'll see notes that c.fknumplan (i.e. the fknumplan column in the callforwarddynamic table, as noted by the c. prefix) refers to the PKID column in the numplan field. This is a very standard type of join in the CCM DB.

and (cfadestination != '' or cfavoicemailenabled ='t') : another clause, basically in this query we want to see only rows where cfadestination isn't blank or cfavoicefmailenabled is set to 't' for true).

Most tables are linked in one of two ways in this database:

- a column prefixed 'fk' refers to the pkid field (there is always only one pkid field per table) in the table following the 'fk' prefix. E.g. above fknumplan refers to the numplan table, pkid field. fkdevice would refer to the device table, pkid field.

- a column prefiex 'tk' refers usually to an enum table which is prefixed with 'type'. This is a table that maps the number value in the 'tk' field to a string. An example would be tkmodel - this represents the phone physical model type (e.g. 7962), and maps to a table called typemodel, and the 'enum' column in that table.

Regards

Aaron Harrison

Principal Engineer at Logicalis UK

Please rate helpful posts...

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

View solution in original post

OK - from memory it's e164mask or something very similar, and it's in the devicenumplanmap table.

Try editing your SQL like so:

select d.name,d.description,dmap.numplanindex, n.dnorpattern, dmap.e164mask

.. etc

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

View solution in original post

20 Replies 20

Aaron Harrison
VIP Alumni
VIP Alumni

Hi Amanda

Basically it's standard SQL, so it wouldn't hurt to google 'informix select statements' and do a little reading to get the basics. There are millions of permutations of queries so it's a matter of understanding the syntax, and then applying that to the database in question. The only difference when running commands from the CLI are that:

- You prefix the standard informix SQL statement with 'run sql'

- You don't get any help from CUCM with the syntax, so you might be well advised to use something that understands SQL a little and colorises it as you type, and then paste the resulting commands into the CUCM SSH window. I use a text editor named JEdit, if you create a text file and save it as a file ending in .sql it will highlight your syntax.

- Other programs are available that do reasonable syntax highlighting (e.q. SquirrelSQL) that are designed for querying the DB directly, but you can't actually query directly against the DB for security reasons. You'd still have to copy/paste the commands.

Now... to understand the DB you'll need a reference describing all the tables etc. This is here:

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_programming_reference_guides_list.html

Pick your version of CUCM and download the 'Data Definition' document.

A few notes on the command:

run sql : is just the CLI command that tells the shell to run the following text as SQL.

select : the SQL command to retrieve data

dnorpattern,cfadestination,cfavoicemailenabled : the column names to retrieve

callforwarddynamic c, numplan n : the names of two tables, and the abbreviations you want to refer to them as

where c.fknumplan = n.pkid : this tells SQL to return values from the two tables where these fields match up. In the data definition you'll see notes that c.fknumplan (i.e. the fknumplan column in the callforwarddynamic table, as noted by the c. prefix) refers to the PKID column in the numplan field. This is a very standard type of join in the CCM DB.

and (cfadestination != '' or cfavoicemailenabled ='t') : another clause, basically in this query we want to see only rows where cfadestination isn't blank or cfavoicefmailenabled is set to 't' for true).

Most tables are linked in one of two ways in this database:

- a column prefixed 'fk' refers to the pkid field (there is always only one pkid field per table) in the table following the 'fk' prefix. E.g. above fknumplan refers to the numplan table, pkid field. fkdevice would refer to the device table, pkid field.

- a column prefiex 'tk' refers usually to an enum table which is prefixed with 'type'. This is a table that maps the number value in the 'tk' field to a string. An example would be tkmodel - this represents the phone physical model type (e.g. 7962), and maps to a table called typemodel, and the 'enum' column in that table.

Regards

Aaron Harrison

Principal Engineer at Logicalis UK

Please rate helpful posts...

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

Awesome

Thanks mate.

This is brilliant.

I will find the file and have a look

My call manager version is 8.0 and I am getting a bit of a report with the query below, but I need to find out where the external mask part is and add that onto it.

regards

amanda

*********************************************

run sql

select d.name,d.description,dmap.numplanindex, n.dnorpattern

          

from device as d

          

inner join devicenumplanmap as dmap

on dmap.fkdevice=d.pkid

          

inner join numplan as n

on dmap.fknumplan = n.pkid

Here is a small excerpt from the output on CUCM

SEP0021A084B618               Naomi Wilson                                              2            66850

SEP001F9E250662               Pavanni Arumugam RECEPTION                                3            66850

SEP001F9EACB6EA               Melissa McLeod                                            3            66850

SEP001B54C9F818               Aida Kurtovic                                             1            54727

SEP001B54C9F818               Aida Kurtovic                                             2            66850 SEP0021A084B618               Naomi Wilson                                              2            66850
SEP001F9E250662               Pavanni Arumugam RECEPTION                                3            66850
SEP001F9EACB6EA               Melissa McLeod                                            3            66850
SEP001B54C9F818               Aida Kurtovic                                             1            54727
SEP001B54C9F818               Aida Kurtovic                                             2            66850

OK - from memory it's e164mask or something very similar, and it's in the devicenumplanmap table.

Try editing your SQL like so:

select d.name,d.description,dmap.numplanindex, n.dnorpattern, dmap.e164mask

.. etc

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

Thanks again,

I i am not getting away with using d.name, d.description etc.

I can only run the commands like

run sql select name,description,e164mask from device, devicenumplanmap

It is running, but producing a lot of output.

I am getting closer to what I will need.

Regards,

Amanda

run sql select numplan.dnorpattern, routepartition.name, devicenumplanmap.e164mask from numplan
inner join routepartition on numplan.fkroutepartition = routepartition.pkid
inner join devicenumplanmap on numplan.pkid = devicenumplanmap.fknumplan
where numplan.tkpatternusage = 2
order by routepartition.name, numplan.dnorpattern

Should give what you were originally looking for.  Sorted by Partition and then Extension, and filtered to only include directory number type entries (tkpatternusage = 2).  If an extension is shared, it will show up multiple times in the output, once for each device, since E164 mask is a device specific parameter.

Excellent thread. Good stuff Mr. Nielsen and Mr. Harrison (+5 each). On a related topic I am sponsoring a weekly series on CUCM SQL queries where I am exploring different tables, methods, etc.. Basically, a tour of the CUCM database schema by example.

If interested, you can find the whole series here:

http://www.ucguerrilla.com/search/label/Query_Series

HTH.

Regards,

Bill

http://ucguerrilla.com

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Wow! I just read all of your SQL Query Series and these are fantastic.  It was actually your original blog posts that got me going on CUCM database discovery, and now I am literally in the database every day!  Thank you so much for being so generous with your knowledge Bill!

Anthony,

Thanks for reading and the feedback. Glad you found it helpful/interesting.

Regards,

Bill

http://ucguerrilla.com

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

Hello

 

how about exporting a query to a file?

Do you know the command?

Hi

There's no 'command' for that per se... You have two options:

Hard - use AxlSqlToolkit to run the query and generate whatever output you want. Requires dev skills.

Easy - connect to the server with putty, log 'printable output' to a text file with putty options and then run the command. Edit the file to trim all the output except for the SQL result, then load into Excel as 'tab delimited'.

Aaron

 

 

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

Hello,

Can someone provide the query to locate devices/phones that are not associated with users. Thanks

Cesar Fiestas

I am facing the same issue as well...... need to found out all DN not associated with any Device Profile or Phone......

anyone please!!!!

Thanks in advance.

Milestone1300 - in CCMAdmin, go to Call Routing/Route Plan Report. Select 'unassigned dn' and hit 'find'.

Aaron Please remember to rate helpful posts to identify useful responses, and mark 'Answered' if appropriate!

Aaron,

I'm running into a challenge handling the output data, it doesn't appear the output from UCM 9.1.2 is tab delimited, is there a way to force the output to be tab delimited or add a delimiter to the output from UCM? The field length being variable is causing issues with a daily import from Informix.

AO

Getting Started

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: