Monday, March 19, 2012

CUCM SQL Queries: NumPlan Partition Allocation

The fourth query in my series on CUCM SQL queries provides another example of using the count() function to get a view of the number of patterns allocated to each partition provisioned in CUCM.
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 another method for showing allocation summaries. Specifically, we are going to show the number of patterns allocated to each partition configured in CUCM.
    The Moving Parts
    From a db perspective the following Informix database tables store information relevant to our query:
    • NumPlan.  This table contains all patterns configured in CUCM.
    • RoutePartition. This table contains all route partitions configured in CUCM.
    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 count(n.dnorpattern), rp.name as partition
    from numplan as n
    inner join routepartition as rp on n.fkroutepartition=rp.pkid
    group by rp.name
    


    Basically, we are getting a count [count()] of a specific table element [e.g. dnorpattern]. When using this function we need to use the "group by" clause. The output of this query will be something like:


    (count) name
    ======= ===================
    2       CL_Block-911_PT
    35      CL_Block-Intl_PT
    1       CL_Block-LD_PT
    4       CL_Block-Local_PT
    2       CL_Block-Std_PT
    2       CL_Block-Svcs_PT
    49      CL_DN-1_PT
    6       CL_PSTN-Glob_PT
    16      CL_PSTN-Std_PT
    1       CL_Svcs-Corp_PT
    7       CL_Svcs-Priv_PT
    12      CL_Svcs-Pub_PT
    1       CL_Tenant-Extern_PT
    3       CL_Tenant-Glob_PT
    10      CL_Tenant-Std_PT
    2       VM_DN-Port_PT
    2       VM_Tenant-Std_PT

    Based on the above output, there are 35 patterns associated with the CL_Block-Intl_PT partition, 4 devices associated with CL_Block-Local_PT, and so on. What if we wanted to see the actual pattern types?

    To determine the specific pattern types allocated to a route partition along with the actual counts, you can use the following query:


    select count(n.dnorpattern), rp.name as partition, tpu.name as PatternUsage
    from numplan as n
    inner join routepartition as rp on n.fkroutepartition=rp.pkid
    inner join typepatternusage as tpu on n.tkpatternusage=tpu.enum
    group by rp.name,tpu.enum
    order by rp.name
    

    The output from this query (using my CUCM 8.5 lab system):


    (count) partition           patterntype
    ======= =================== ==================================
    2       CL_Block-911_PT     Translation
    35      CL_Block-Intl_PT    Translation
    1       CL_Block-LD_PT      Translation
    4       CL_Block-Local_PT   Translation
    2       CL_Block-Std_PT     Translation
    2       CL_Block-Svcs_PT    Translation
    48      CL_DN-1_PT          Device
    1       CL_DN-1_PT          Device template
    6       CL_PSTN-Glob_PT     Route
    (rest deleted)

    We added a new table to our query: typePatternUsage. This table is an enumeration table that stores enumerators (or IDs) for pattern types. When adding another table column to a query where you are using the count() function, that column must be added to the group by clause.  I also added the order by clause in the above query to make the output more logical.

    Variations and Considerations
    [Edit: Correction/clarification on "unassigned" vs. "inactive" DNs]

    When querying the numplan table you may want to exclude (or single out) patterns that are not assigned to devices or you may want to single out patterns that are inactive. Inactive patterns are never associated with devices. However, unassociated patterns may be active. It is a "feature" that can bite you in the you-know-what if you don't catch it.

    To see all inactive DNs, you can use this query:

    select dnorpattern, iscallable from numplan where iscallable='f'

    Going back to our original query, if you wanted to exclude patterns that were inactive, you would use this query:

    select count(n.dnorpattern), rp.name as partition
    from numplan as n
    inner join routepartition as rp on n.fkroutepartition=rp.pkid
    where n.iscallable = 't'
    group by rp.name

    When directory numbers are associated to devices, this association is stored in the devicenumplanmap table. So, let's say that you want to list all of the directory numbers that are active in your UCM but are not associated to any devices. Something like this would work:

    select n.dnorpattern, n.tkpatternusage
    from numplan n
    where n.iscallable='t' 
          and n.tkpatternusage=2 
          and 0=(select count(dmap.pkid) from devicenumplanmap dmap where dmap.fknumplan=n.pkid)
    

    With this query we are saying show me active (n.iscallable='t') directory numbers (n.tkpatternusage=2) that are not associated to a device (0=select count from devicenumplanmap). 



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






    7 comments:

    1. Is there a command that will show the route list, route group, and gateway a pattern points to?

      ReplyDelete
      Replies
      1. Forgot to mention, I added this to blog site. Thanks again for the input!

        -Bill (@ucguerrilla)

        Delete
    2. Yes, this is certainly possible. I'll consider this as a request and add it to the queue.

      Thanks,
      Bill

      ReplyDelete
    3. William,

      For the past three days, all I have done is feast at your table! I must say what a banquet you have served on these series. Recently I decided to learn a bit about AXL/SQL side of CUCM and your series have been immense. Great Job! Infact I sent you a PM on CSC (I am aokanlawon), again thank you.

      I have a few questions and here they are

      1. I am trying to use the sql query analyzer on the old cucm4.x. Most of the tables are similar with informix tables however the analyzer cant seem to find the device table. I suspect its because on the server I am running it on, it has two databse (ccm0300 and ccm0301). It also has other cucm servers on it since its a cluster.
      Would you be able to help with me with this...I just need to know how to call the correct database and table in this scenario

      2. I read one of these series where you talked about running sql query on the CAR database. I must say that was brilliant. Where can I get the data dictionary for the CAR database?

      3. Finally, I was trying to run the axlsqltookit and I get this error Could not find or load main class axlsqltoolkit"

      c:\program files\Java>java -cp ./classes:./lib/saaj-api.jar:./lib/saaj-impl.jar:./lib/mail.jar:./lib/activation.jar:./lib/jaxm-api.jar:./lib/jaxm-runtime.jar:./
      lib/xercesImpl.jar:./lib/xml-apis.jar axlsqltoolkit -input=test.xml -username=ccmadmin -password=ccievoice -host=192.168.31.224

      error:Could not find or load main class axlsqltoolkit"

      Can you help.

      Thanks



      ReplyDelete
      Replies
      1. aokanlawon,

        First, thanks for the kind words. I am glad you found this series to be interesting and helpful.

        Answers to your questions:

        1. I am going from memory here since my 4.1 VM is off line. Assuming replication is healthy, you can attach to any SQL instance in the cluster. I would typically load SQL Query Analyzer on the publisher node and, therefore, would choose "(local)". You should pick the highest numbered database (CCM0301). There is definitely a device table in 4.1.

        2. I go to the Programming Guides section for CUCM on CCO (http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_programming_reference_guides_list.html) Go to the URL and search on the page for "Call Detail Record"

        3. You are possibly missing the path statement. I wrote a series on this toolkit for another blog I maintain on NetCraftsmen's corporate site: http://www.netcraftsmen.net/resources/blogs/running-sql-queries-on-cucm-6x7x-using-axl-soap-toolkit-part-1.html


        HTH.

        -Bill (@ucguerrilla)


        Delete
    4. Hey Bill

      Just a word of warning:

      select dnorpattern, iscallable from numplan where iscallable='f'


      This isn't going to match the unassigned DNs necessarily. I'm using PCP, and its batch-provisioning unassigned DNs with "Active" is checked on the GUI. So really, that field is not the contrubuting factor to assigned status.

      "Active" is defined by iscallable in numplan. I was thinking a join on devicenumplanmap.fdevice?

      Keen to hear your thoughts... This really hurt me this week.

      - Jonathan

      ReplyDelete
      Replies
      1. Jonathan,

        You are absolutely correct. I have updated the entry to remove the confusion. Thanks for taking the time to point out the mistake and sorry for the pain. Hopefully you find the additional information useful.

        HTH.

        -Bill (@ucguerrilla)

        Delete