Friday, September 7, 2012

Using SQL to Report on Route Lists and Groups in CUCM

In this installment of the SQL query series I wanted to actually explore a query that is inspired by one of the comments on my blog. The reader asked:
Is there a command that will show the route list, route group, and gateway a pattern points to?

Seems like a reasonable request to me. Cisco Unified Communications Manager (CUCM) doesn't provide a command that will dump this information but there are a couple of ways to get at the data and using SQL is one.  

In the toolkit I developed to survey customer sites, I have a query that does exactly that. Using SQL, or more accurately using AXL/SOAP API to access SQL queries, is the best option when bulk surveying a site for the purpose of gathering data that will be processed off line. However, there is another method that is more straightforward for a quick view of the path a pattern will take through your dial plan.

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

The "Non-Query" Method

So, the question was: Is there a command that will show the route list, route group, and gateway a pattern points to?. There are a couple of ways to get at this data. I should start with the most simple method first: Route Plan Report. This method may satisfy the needs of most people and negate the need of building a complex query to view the data.

The Route Plan Report is accessible from the CCMAdmin portal (https://YourCUCM/ccmadmin). 
  1. Web to your CUCM publisher (or subscriber, if you must) node and log on using an account with appropriate permissions. 
  2. Go to Call Routing>Route Plan Report.
  3. Specify filter parameters and run report
When running a report, you will get output that looks like the following:

The output is pretty straightforward. You see the route pattern, partition assignment, the type of pattern, and the route detail. The route detail shows route list, route group, and gateway path.

Viewing the Report
From the Route Plan Report, you have the option to view the report in a file. This is accessed using the "Related Links" option (top-right of the screen). The report is handy but it will now show route detail beyond the Route List level. I am guessing this is because they wanted the report to follow a single line report structure. A more likely answer is that they built that report interface a while ago and are working on bigger and better features. 

Some Considerations
There are a couple of considerations when you are running a Route Plan Report. These considerations also apply if you are running your own queries to report on the same data. The first consideration is Standard Local Route Group. If a route list is provisioned to use Standard Local Route Group then gateway devices will not be enumerated. This makes sense given that the Route Plan Report does not provide a way for you to query from the perspective of a specific device. You would need to use Dialed Number Analyzer (DNA) for that type of view.

The second consideration is that when a SIP trunk, H.323 gatekeeper trunk, or H.323 gateway is in the route detail you do not see all relevant information. Which is to say that there are route details that are transparent to CUCM and cannot be rendered by the Route Plan Report. Why? Well, in all of the aforementioned cases, the dial plan processing is essentially distributed. There is dial plan logic on each of these devices that operates autonomously from the CUCM.

This Week's Query Queries

The original question posted by one of the readers was how to list route list, route group, and gateway(s) that a route pattern uses. We have already discussed a method available to admins that doesn't require the use of SQL queries. However, since this series is focused on demonstrating how to use SQL to get at data, let's explore how we can get the same data via SQL. 

The query needed to display the data is actually one of the more complicated queries to be explored in this series. So, we'll take the queries in pieces.
    The Moving Parts

    From a db perspective the following Informix database tables store information relevant to our queries:
    • Device: This table stores all physical and logical devices provisioned in the CUCM cluster. We are going to use this table to not only list out gateway names but to list out route list names as well.
    • DeviceNumplanMap: This is a mapping table that allows us to map the route patterns we are interested in to the route list or voice gateway provisioned to route the call.
    • Numplan: This table stores all patterns, including route patterns.
    • RoutePartition: Strictly speaking, we wouldn't need the RoutePartition table to execute our query but it is a good idea to display the partition name just in case you have more than one route pattern using the same digit string. 
    • RouteList: We use the route list table to access route group information as well as selection order of route groups (as applicable).
    • RouteGroup: The RouteGroup table provides a route group name and maps our gateway membership.
    As you can see, there are a lot of tables involved in this query. We are basically piecing together the story. We use the Numplan and the RoutePartition to get identifying information about the route pattern. We'll use the Device table to get two pieces of information: the route list name and gateway names. The Device table stores information about physical devices (e.g. IP Phones, SIP trunks, gateways) as well as logical device information (e.g. Route Lists, and Hunt Lists). The center pieces of our query are Numplan and Device. We use other tables to provide "context" (at least that is what I like to call it). We link the route pattern to the route list using DeviceNumplanMap. We link the route list to the associated route groups using the RouteList table. Finally, we use RouteGroup to get gateway associations.

    The Query
    The first query we are going to discuss is getting from route pattern to route group. You can run the query from the CLI using run sql [insert query]:

    select n.dnorpattern, as partition, as RouteList, rl.selectionOrder, as RouteGroup 
    from numplan as n 
      left join routepartition as rp on rp.pkid=n.fkroutepartition 
      inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid 
      inner join device as d on dmap.fkdevice=d.pkid 
      left join routelist as rl on rl.fkdevice = d.pkid 
      left join routegroup as rg on rg.pkid=rl.fkroutegroup 
    where n.tkpatternusage=5 
    order by n.dnorpattern, rl.selectionorder

    Let's start the dissection with a discussion on "join". As you can see, we are using "left outer joins" and "inner joins". Inner joins return rows only when there is at least one row from the joined tables that match the join condition. For example, you can have a route pattern in CUCM where the route partition is Null. If you use an inner join between Numplan and Routepartition, then your query wouldn't show records where the pattern is assigned to the "<none>" partition.

    In contrast, an outer join returns all rows from at least one table in the FROM clause as long as the rows match the WHERE condition(s). So, in our example, we can use an outer join to list all patterns, whether they have an assigned route partition or not.

    In our query we are pulling the route pattern from the Numplan table. We are providing the route partition name using the Routepartition table. This isn't a necessary element in our output row. As noted previously, I like to show the partition name just in case you have more than one route pattern using the same digit string. 

    We are exposing the assigned route list (or gateway) by using the DeviceNumplanMap table. In the context of our query, you can think of route lists like IP phones. On an IP phone you can assign one or more directory numbers (DNs). These DNs are stored in Numplan table and the phones are stored in the Devices table. The DNs are mapped to phones via the DeviceNumplanMap table (makes sense, right?). Route lists are basically handled in the same way, each route list can be associated to zero or more route patterns.

    Once we identify the route list, we will obviously need to know what is assigned to the route list. Only route groups can be assigned to route lists and you can have one or more route group. We are using the Routelist table to identify the route groups (via unique ID or "key" value) as well as identifying the priority order of said route groups.   

    Adding Gateway Information
    The Route Plan Report will list out the voice gateways which are assigned to route groups. To add that information to our query, we will need to add in a few tables and, yes, a few more joins. Think of it as a party where everyone involved in the call path is invited. Quite an inclusive event. The additional tables:
    • RouteGroupDeviceMap: As the name suggests, this is a mapping table where we map devices (i.e. gateways) to our route group(s).
    • Device: We have an additional join to the device table to get the gateway names
    The following query puts it all together:

    select n.dnorpattern, as partition, as RouteList, rl.selectionOrder, as RouteGroup, rgdp.DeviceSelectionOrder, as gateway 
    from numplan as n 
       left join routepartition as rp on rp.pkid=n.fkroutepartition 
       inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid 
       inner join device as d on dmap.fkdevice=d.pkid 
       left join routelist as rl on rl.fkdevice = d.pkid 
       left join routegroup as rg on rg.pkid=rl.fkroutegroup 
       inner join RouteGroupDeviceMap as rgdp on rgdp.fkRouteGroup=rg.pkid 
       inner join device as dd on dd.pkid=rgdp.fkDevice 
    where n.tkpatternusage=5 
    order by n.dnorpattern, rl.selectionorder, rgdp.DeviceSelectionOrder
    This query is a beast but, as we have seen before, sometimes you gotta break out the big guns. We added a few additional columns to our output. We also added another join to the Device table and we add a new join to the RouteGroupDeviceMap table. To keep out output organized, we also add a third sort level to our Order By clause.


    We have the same basic considerations as noted earlier for the Route Plan Report. In addition, since we are looking at queries where there are 1:many relationships (Route Group to Gateway and Route List to Route Group) there will be some output clutter to deal with.

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


    1. That was great. No wonder I had such a hard time making it work. Thank you.

    2. Morning Bill!

      Following up on my twitter request from last week (@geekycountrydad), and would appreciate some guidance on querying SIP trunks and their IP addresses.

      Thanks again,

      1. Greg,

        Here you go:


        -Bill (@ucguerrilla)