Monday, March 12, 2012

CUCM SQL Queries: List Phones and Line Appearances

The third query in my series on CUCM SQL queries provides a simple but effective query that dumps a list of devices and the directory numbers assigned to those devices. This is probably one of the first queries I learned (other than the basic "hello world-ish" queries). 


Primer

A brief primer is provided in the first blog of this series.

This Week's Query

For this week's post I am going to demonstrate a method to dump a device list with basic directory number assignment information.
    The Moving Parts
    From a db perspective the following Informix database tables store information relevant to our query:
    • Device. This table contains device level information.
    • DeviceNumPlanMap. This is an example of what I call a "mapping" table in the primer (see above). This table maps numbers in numplan to devices in device
    • NumPlan.  This table contains all patterns configured in CUCM.
    • RoutePartition. This table contains all route partitions configured in CUCM.
    The Device table contains objects that are considered "devices" by CUCM. This includes the obvious things like IP Phones and gateways. It also includes some less obvious things like route lists. For our immediate purposes, we are interested in IP phones and the directory numbers assigned to them. 

    The NumPlan table is a staple for any query that is interrogating aspects of the dial plan that involves digit patterns. Every pattern provisioned on the system is stored in this table. Like other tables, this table has a unique key ("pkid" is the column name) but you can use a composite query to get unique values. The "dNOrPattern" column stores the actual pattern and the "fkRoutePartition" stores a reference value to the RoutePartition table. If you put "dNorPattern" together with "fkRoutePartition", you will have a unique entry in the NumPlan table. 

    The RoutePartition table stores every partition provisioned in the system (including intercom partitions). I typically join the RoutePartition table when I am querying the NumPlan table for pattern information because I want the "human readable" route partition "name". That is how we will use the RoutePartition in this week's CUCM query installment. In a later installment, we'll explore other uses for RoutePartition.

    The Query

    Now that we have some information, we can build the query. You can run the query from the CLI using run sql [insert query]

    select d.name,d.description,n.dnorpattern as DN
    from device as d
    inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
    inner join numplan as n on dmap.fknumplan=n.pkid
    where d.tkclass=1
    order by d.name
    

    Basically, we are getting a list of devices along with the directory numbers that are assigned. We limit the output to "typeClass" 1 (which is an IP phone). We are also using deviceNumPlanMap as a pivot table to join device and numplan. The output of this query will be something like:


    name            description     dn
    =============== =============== =============
    SEP3037A61747C7 WJB 9951        \+13011055202
    SEP3037A61747C7 WJB 9951        \+13011055200
    SEPDEADBEEF0002 Bria on iPod    \+13011055200
    SEPDEADBEEF0003 Bria on Mac     \+13011055204
    TCTWJBIPOD      WJB IPOD        \+13011055205

    Based on the above output (which is truncated to keep things tidy) we see that there are four (4) devices and that one of those devices (SEP3037A61747C7) has two line appearances. We may also "assume" that DN +13011055200 is a shared line appearance between the iPod Bria and the 9951 device. However, this assumption would be false. 

    We need a better data set, to know whether a DN is unique we need to know the partition as well. So, let's modify our query in the following manner:

    select d.name,d.description,n.dnorpattern as DN,rp.name as partition
    from device as d
    inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid
    inner join numplan as n on dmap.fknumplan=n.pkid
    inner join routepartition as rp on n.fkroutepartition=rp.pkid
    where d.tkclass=1
    order by d.name
    

    We've added an inner join to the routepartition table to resolve the partition assignment for our DNs. We could have dumped the GUID stored in the numplan table's fkroutepartition column but we want our output to be readable. The output from this query:


    name            description     dn            partition
    =============== =============== ============= ==========
    SEP3037A61747C7 WJB 9951        \+13011055202 CL_DN-1_PT
    SEP3037A61747C7 WJB 9951        \+13011055200 CL_DN-2_PT
    SEPDEADBEEF0002 Bria on iPod    \+13011055200 CL_DN-1_PT
    SEPDEADBEEF0003 Bria on Mac     \+13011055204 CL_DN-1_PT
    TCTWJBIPOD      WJB IPOD        \+13011055205 CL_DN-1_PT

    We now see that the +13011055200 DN on the 9951 device is actually in a different partition than the same DN on the iPod Bria device.

    Variations and Considerations

    Whenever you get into the business of using joins (inner, outer, left, right, etc.) you have to be careful about how you interpret the output. For example, if you were in the bad habit of not assigning a partition to all directory numbers then the queries we provided in this installment wouldn't catch all data elements. I think that this is best covered in a separate installment so that I can let the reader get back to their day.



    Thanks for reading. If there is a specific query you are interested in seeing, let me know using the comments!








    12 comments:

    1. Thanks a lot for this, very nice work!
      This is my query with the output:

      admin:run sql select d.name as Device_Name,d.description as Device_Description,rp.name as line_partition,n.dnorpattern as line_DN,n.description as line_Description,dmap.display as line_display from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid inner join routepartition as rp on n.fkroutepartition=rp.pkid where d.tkclass=1 order by d.name
      device_name device_description line_partition line_dn line_description line_display
      =============== ================== ============== ======= ================ ============
      SEP111122223333 SEP111122223333 Branch_office 100 Description 100 Display 100
      SEP333322221111 SEP333322221111 Headquarters 200 ciao bello display 200

      P.s. it works in version 8.6 :)

      ReplyDelete
    2. Is their a way I could display the name(MAC), Description, DN, PT AND THE IP ADDRESS OF THE PHONE

      ReplyDelete
      Replies
      1. There is but not using the SQL method discussed in this series. There is an API called RISport that you can use. I did a small write up on that in the blog I maintain on the NetCraftsmen site:

        http://www.netcraftsmen.net/component/content/article/70-unified-communications/452-good-bye-devicelistxasp-hello-risport.html

        HTH

        -Bill (@ucguerrilla)

        Delete
    3. Hi Bill,

      I am trying to obtain DN description when a DN is associated to a phone. I do get that when the DN is associated to more than one phone, but not when the DN is associated to only one phone.

      I do get the result I want when I run this on the CUCM cli, but not when I include the SQL statement in a php script, and view via a web browser.

      Below is my sql statement:


      select d.name,d.description,n.dnorpattern as DN, n.description as LOCATION from device as d inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid inner join numplan as n on dmap.fknumplan=n.pkid where n.dnorpattern = '66666'

      As soon as I associate the DN to more than one phone, then it displays the description, but once I remove the association to the second phone , I dont get the description. Any ideas as to why please?

      Olu

      ReplyDelete
    4. Olu,

      If I follow the query you provided works fine from the CLI whether you have 1 device association to a DN or more than one. However, the query does not give you the expected results when ran via a PHP script you developed.

      Specifically, the PHP script fails to give you expected results when there is a single device associated with a specific DN. Though, you get the expected results when you you have more than one device associated with the DN.

      If my understanding is correct then the first thing I would look at is your script logic. I would put a step in your script to dump the raw XML output from the AXL response and look at what UCM is providing you.

      I tested your query in my lab (on UCM 9.0) and it works as expected. What version of UCM are you using?

      -Bill

      ReplyDelete
    5. hi bill,

      i want to have a sql query that gives list of all ip phones that starts with "sep" with their description, and their registration status.

      i am facing problem to get the registration status..

      Please help out..

      ReplyDelete
      Replies
      1. Registration status is not stored in the database. You need to get this using a different mechanism. The best options are SNMP or the RISport API. The latter being an API accessing the RealTime Information Service.




        HTH

        -Bill (@ucguerrilla)

        Delete
    6. hello bell

      Thanks for your effort

      I need sql query to get all DN's created on CUCM with its partition either assigned or unassigned numbers

      thanks

      ReplyDelete
      Replies
      1. Sherif,

        select n.dnorpattern, rp.name as partition
        from numplan n
        inner join routepartition rp on n.fkroutepartition=rp.pkid
        where n.tkpatternusage=2

        The above query will list all DNs and route partitions

        HTH

        -Bill

        Delete
    7. Hi Bill,

      I'm struggling to get the "Line 2" number for a handset as the SQL schema for the CUCM is a nightmare!

      Any quick SQL solution to this? (It's a CUCM 7.1 btw)

      Cheers!

      Paul

      ReplyDelete
      Replies
      1. Paul,

        I recommend that you look at the "numplanindex" field in the devicenumplanmap table. A numplandex value of 2 would be the 2nd line appearance on the device referenced in the devicenumplanmap table.

        HTH.
        -Bill

        Delete
    8. Cheers Bill, you are a star. That did the trick :-)

      ReplyDelete