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!