Aurus Blog

This blog is to share our expertise in Cisco UCM, UCCX/UCCE and Cisco Telepresence.

  • Archive

    «   October 2017   »
    M T W T F S S
    2 3 4 5 6 7 8
    9 10 11 12 13 14 15
    16 17 18 19 20 21 22
    23 24 25 26 27 28 29
    30 31          

Useful CUCM CLI SQL Queries for DN and CSS

Recently I needed to collect the detailed information on Cisco IP phones, extension numbers, CSS and other parameters. The communication network was really huge but no inventory had been performed for years.

Lots of phones and extension numbers with no common attributes. Actually, I was given the list of extensions for which I had to return the detailed info.

This is where the CLI SQL Queries can hardly be underestimated. With CLI you can query the CUCM database directly. A query can be executed from the command line if you access the CUCM server via SSH.

To execute the SQL query, run the following command:
run sql <query body>

For example:
run sql select dnorpattern from numplan where dnorpattern like '1%'

The result will contain all the directory numbers, route patterns and translation patterns beginning with "1".

More examples:

Show the DNs assigned to the phones on the list:
run sql select n.dnorpattern as DN from device as d, numplan as n, devicenumplanmap as dnpm where dnpm.fkdevice =d.pkid and dnpm.fknumplan = n.pkid and d.tkclass = 1 and ( in ('SEP7C96F3C9ACDC' , 'SEPB8BEBA229A9E';))

Show the CSS configured for the DNs on the list:
run sql select from numplan as np join callingsearchspace as css on np.fkcallingsearchspace_sharedlineappear=css.pkid where np.dnorpattern in ('6229' , '3118';)

Check if the DNs on the list are members of any Line Groups:
run sql select as LineGroup,n.dnorpattern,dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan = n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid where n.dnorpattern in ('2480' , '1601';)

Check if the DNs are members of any Call PickUp Groups and show that groups:
run sql select np.dnorpattern, from pickupgrouplinemap as pgl join numplan as np on pgl.fknumplan_line=np.pkid join pickupgroup as pg on pg.pkid=pgl.fkpickupgroup where np.dnorpattern in ('' , '3118' , '5109';)

Find out if there is a line assigned to a phone and if the line has an External Phone Number Mask configured:
run sql select dnpm.e164mask as EPNM from devicenumplanmap as dnpm join device as d on dnpm.fkdevice=d.pkid where in ( 'SEP1CAA07E2060D' , 'SEPF07F06B8D6B2';)

You can obtain the information as well as modify the data in the database. For example, you can modify the device description:
run sql update device set description = 'MARK_PHONE' where name in ('SEPD8CB8A379237', 'SEP80E86F23F5E7';)

This list of examples can go on endlessly. Actually, the direct access to the database is a tool of almost unlimited flexibility, suitable for obtaining information and making massive data changes.