topic Hello, in Unified Communications Infrastructure

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

alalli — Tue, 19 Mar 2019 11:46:32 GMT

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

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

Currently Being Moderated

05/04/2011 5:18 AM (in response to Joshua Royle)

Re: Is there a way of pulling a report off CM showing all phones that have diverts on?

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')

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

Aaron Harrison — Thu, 19 Apr 2012 07:44:19 GMT

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...

Re: Information on how to run SQL queries on the CUCM itself ple

alalli — Thu, 19 Apr 2012 08:02:15 GMT

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

Re: Information on how to run SQL queries on the CUCM itself ple

Aaron Harrison — Thu, 19 Apr 2012 08:08:59 GMT

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

Re: Information on how to run SQL queries on the CUCM itself ple

alalli — Fri, 20 Apr 2012 07:51:55 GMT

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

Re: Information on how to run SQL queries on the CUCM itself ple

Fred Nielsen (ePlus) — Fri, 20 Apr 2012 19:00:53 GMT

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.

Re: Information on how to run SQL queries on the CUCM itself ple

William Bell — Sat, 21 Apr 2012 01:15:56 GMT

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

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

Anthony Holloway — Tue, 24 Apr 2012 02:38:06 GMT

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!

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

William Bell — Tue, 24 Apr 2012 11:29:21 GMT

Anthony,

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

Regards,

Bill

http://ucguerrilla.com

Hello how about exporting a

dragos_sony — Tue, 07 Oct 2014 11:32:46 GMT

Hello

how about exporting a query to a file?

Do you know the command?

HiThere's no 'command' for

Aaron Harrison — Thu, 16 Oct 2014 08:59:01 GMT

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

Thanks Aaron! This

Calin Lupas — Wed, 17 Jun 2015 20:00:27 GMT

Thanks Aaron! This information is still relevant.

Hello,

fiestas1980 — Tue, 22 Dec 2015 15:51:01 GMT

Hello,

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

Cesar Fiestas

Thanks. very informative and

milestone1300 — Wed, 16 Mar 2016 04:18:12 GMT

Thanks. very informative and easy to step in.

I am facing the same issue as

milestone1300 — Wed, 16 Mar 2016 04:19:40 GMT

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.

Aaron,

aohareuri — Tue, 05 Apr 2016 12:15:21 GMT

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.

Hi

Aaron Harrison — Tue, 05 Apr 2016 15:12:41 GMT

If you run the query over AXL then it will come back in a format you can read with whatever script language you call it with, and you can then format it however you like for output.

Are you running it from the server CLI or otherwise?

Aaron

From the CLI today, I have an

aohareuri — Tue, 05 Apr 2016 15:14:36 GMT

From the CLI today, I have an SSIS instance that connects via SSH and runs the query.

Mileston1300 - in CCMAdmin,

Aaron Harrison — Tue, 05 Apr 2016 15:15:24 GMT

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

I'm trying AXL and its

aohareuri — Tue, 05 Apr 2016 16:59:14 GMT

I'm trying AXL and its failing, I've started a new discussion on it.

https://supportforums.cisco.com/discussion/12954431/cucm-axl-query-returned-rows