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:
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:
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?
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:
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, rp.name as Partition, ddi.name 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, rp.name as Partition, ddi.name 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!
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!
ReplyDeleteFancy seeing you here :) The UC guerrilla is awesome :)
DeleteYou 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.
ReplyDeleterun sql SELECT name,value FROM TABLE (FUNCTION LicenseTotals()) (pkid,name,value,UserValue,DeviceValue)
and
run sql select typeproduct.name, typelicensedresource.name 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!
Hi,
ReplyDeleteIs there a way to export this data into a csv or a txt file?
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).
DeleteYou 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.
-Bill
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.
DeleteGreat tutorial !!! Thanks !!!
ReplyDeleteIs there any way to insert phone number to Database blacklist, if I want to block incoming calls with this phone number?
Yes, you just need to have an "upstream" translation that provides the directive to route next hop by calling party. Use a CSS that has a PT where you intend to store the "black listed" patterns. Then you can insert into the PT with translations that are configured to block calls.
DeleteUsing the UCM feature described here:
https://www.netcraftsmen.com/cisco-cucm-blocking-calls-by-calling-party-number-id/
-Bill