Monday, March 24, 2014

Using SQL To Query Translation Patterns

For this installment of the SQL Query Series I am going to address a question posed in one of the comments I received from a reader. The reader asked: "how can I run a numplan query that shows all translation patterns with called party transformations".  Excellent question and it touches on one of the queries I use on a fairly regular basis. 


Some of the queries we have explored get quite involved with inner/outer joins and the like. Most useful queries will tend to rely on joining between tables to get useful data. This query is no different. Fortunately, we don't have to get too "join-heavy" to get at the core answer for the question posed by the reader. 

So, we'll show a couple of queries. Just to make sure we cover all bases. 

A brief primer on the SQL query series is provided here.

The Tables

From a db perspective the following Informix database tables store information relevant to our queries:
  • NumPlan: This is the table that stores all digit patterns in your system. It is the star of the show for a bulk of the queries we discuss in this series.
  • RoutePartition: This table contains route partition names. This is not a required table for our purposes but it is handy when trying to analyze a large dial plan.
  • DigitDiscardInstruction:  What do you think this contains? 
When I first started exploring the CUCM DB, I was at first surprised to find out that the DigitDiscardInstruction was not a type enumeration table. You likely have seen that whole "PreDot, PreDot-Trailing#" drop down list under route patterns and translation patterns. Well the values that make up that list are in the DigitDiscardInstruction table. 

The reason it is not just a type enumeration table is because the table contains all sorts of data used in support of the national numbering plan feature in CUCM. You may recognize this feature by its street name "9dotAt" (9.@). This pattern likes to hang out with some thugs called the Route Filters. All in all they are an obscure bunch and should be avoided any time you are trying to do real dial plan design.

Ok, ok. I digress. You can tell I am not a fan of the built in macro functionality of the national numbering plan feature. Anyway, the DigitDiscardInstruction table actually holds a lot of interesting clues to how CUCM digit analysis does its thing outside of basic "PreDot" functionality. Give it a look some time. 

The Queries

The short answer to the question that inspired this blog entry is the following query:

select dnorpattern, calledpartytransformationmask
from numplan
where tkpatternusage=3 and calledpartytransformationmask is not NULL

This will list all translation patterns with the called party transformations. Using the tkpatternusage value of 3 means we are only interested in translation patterns. I put the condition of checking for NULL because we just want to see the patterns where there is a direct transformation applied.

Of course, the above query fails to capture all of the ways you can manipulate the called party information with a translation pattern. To get at that data, we need to get a little more extravagant with our query. For instance:

select n.dnorpattern as Translation, as Partition, as DiscardDigits, n.prefixdigitsout, n.calledpartytransformationmask 
from numplan n 
 left join routepartition rp on n.fkroutepartition=rp.pkid 
 left join digitdiscardinstruction ddi on n.fkdigitdiscardinstruction=ddi.pkid 
order by n.dnorpattern

Sample output from the above query:

translation           partition           discarddigits prefixdigitsout calledpartytransformationmask
===================== =================== ============= =============== =============================
2XXX                  CL_Tenant-Std_PT    NULL          +1202555        NULL
3XXX                  CL_Tenant-Std_PT    NULL          +1703555        NULL
7XXX                  CL_Tenant-Std_PT    NULL          +1202107        NULL

As you can see from the above query, we are presenting all of the called party digit manipulation options for the translation patterns. In the query, we are using the left outer join clause (left join). We are doing this because you may have a translation pattern that doesn't have an assigned partition (shame on you) or you may have opted to not assign a digit discard instruction. If we used an inner join clause then we would only present results where a partition and a digit discard instruction was provisioned for the pattern. 

Where to go from here

You can spice up the queries by using various "where" clauses to filter the results to show specific patterns. For instance, let's say you had an environment where you used a 4-digit abbreviated dialing pattern and an E.164 DN for all phones. Further, let's say you wanted to check to make sure that you wanted to double check and make sure that your translations were configured correctly. 

The following query would dump translations that were 4-digits long and did NOT have a prefix assigned:

select n.dnorpattern as Translation, as Partition, as DiscardDigits, n.prefixdigitsout, n.calledpartytransformationmask 
from numplan n 
 left join routepartition rp on n.fkroutepartition=rp.pkid 
 left join digitdiscardinstruction ddi on n.fkdigitdiscardinstruction=ddi.pkid 
where tkpatternusage=3 and length(n.dnorpattern) = 4 and n.prefixdigitsout is NULL 
order by n.dnorpattern

Thanks for reading. If you have time, post a comment!


  1. Thanks for the great example! Your SQL series has been an inspiration for me to learn how to better expose data that may not be readily available with the GUI. I certainly appreciate it!

    1. Fancy seeing you here :) The UC guerrilla is awesome :)

  2. You are a life saver; every time i google how to do something i get pointed here. not only do i get the answer but a better understanding of why it works how it does! For your next segment could you perhaps do something with licensing? We can pull licensing for a cluster, but each node in the cluster purchases their own licenses to add into ELM. We need to track by devicepool (it's standardized with a node name) how many licenses a node uses rather than how many licenses the cluster uses.

    run sql SELECT name,value FROM TABLE (FUNCTION LicenseTotals()) (pkid,name,value,UserValue,DeviceValue)


    run sql select, from typeproduct, typelicensedresource, typelicensedresourceproductmap where typelicensedresource.enum = typelicensedresourceproductmap.tklicensedresource and typelicensedresourceproductmap.tkproduct = typeproduct.enum

    seem to be good starts. Anyway if you could possibly cover this in more detail, that would be amazing! Keep up the awesome work and thank you for sharing your thoughts/teaching the community!

  3. Hi,

    Is there a way to export this data into a csv or a txt file?

    1. You can export data to a CSV file but not if you are running the command from the CLI as provided in the examples. We are using the examples for demonstration purposes (primarily).

      You would need to develop or use a tool that can create the CSV output file format. I use a homegrown app for this (and I am not ready to share that at this time). If you are comfortable with Java, you may be able to use the source code for the AxlSqlToolkit (plugin on UCM) and create a routine to create a CSV.


    2. I use Bill's guide as a reference and then create SOAP queries. I then use powershell to run the query (or update as the case may be) and output to text or CSV via powershell depending on which serves my needs best. I used the java axl toolkit before, but powershell was just easier for me to utilize.