Using SQL to Report on Hunt Pilots and Line Groups

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 get a list of hunt pilots and the line groups they are using".  So, we are going to provide a quick tour of how one can get at that information using SQL. 

Background

In many ways the structure with Hunt Pilots, Hunt Lists, and Line groups is similar to that of Route Patterns, Route Lists, and Route Groups. Close enough anyway. I present a query example for the latter dial plan elements in the Using SQL to Report on Route Lists and Groups blog entry.

One of the interesting evolutions with CUCM release over the past year or so has been some nifty enhancements with Hunt Pilots. Particularly in the area of call coverage and queuing. We're not going to dive into that here. We'll stay focused on the question at hand and walk through reporting on the relationship between hunt pilot and line group.

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

The Non-Query Method


So, the question was: Is there a command that will show hunt pilots and assigned line groups?. 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 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 hunt pilot, partition assignment, the type of pattern, and the route detail. The route detail shows hunt list, line group, and associated directory numbers.

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 not show route detail beyond the Hunt List level. 

The Tables

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. Interestingly enough, Hunt Lists are treated as devices in CUCM.
• DeviceNumplanMap: This is a mapping table that allows us to map the hunt pilot patterns we are interested in to the hunt list.
• Numplan: This table stores all patterns, including hunt pilots.
• RouteList: We use the route list table to access line group information as well as selection order of line groups (as applicable).
• LineGroup: The LineGroup table provides a line group name and can be used access line group membership.

The Queries

To get a list of Hunt Pilot numbers with the associated line groups, we can use the following query:

select n.dNOrPattern as HuntPilot, d.name as HuntList, d.description, rl.selectionorder, lg.name as LineGroup 
from device as d 
  inner join routelist as rl on rl.fkDevice=d.pkid 
  inner join DeviceNumPlanMap as dmap on dmap.fkDevice=d.pkid 
  inner join NumPlan as n on n.pkid=dmap.fkNumPlan 
  inner join linegroup lg on rl.fklinegroup = lg.pkid  
order by n.dnorpattern

On my lab system, the above query gives the following results:

huntpilot     huntlist           description selectionorder linegroup
============= ================== =========== ============== =================
\+12025552500 SA_TestHuntList_HL             1              TestLineGroup1_LG
\+12025552500 SA_TestHuntList_HL             2              TestLineGroup2_LG
\+17035552500 SB_TestHuntList_HL             1              TestLineGroup3_LG

So, the output provided above addresses the core question. If we wanted to enumerate the line group membership then we can add a bunch of joins to the first query or we can explore that as a separate query. To keep things simple let's explore this latter option.

Line groups contain directory numbers that are associated to devices. So, we are going to use the Device, NumPlan, LineGroup, DeviceNumPlanMap, and Device tables again. In addition, we are going to use the LineGroupNumPlanMap and typeDistributeAlgorithm tables. The LineGroupNumPlanMap does what the name implies. It maps a line group to a line group directory number. The typeDistributeAlgorithm is a "type" enumerator table. It isn't strictly required but I like to use it so that I don't have to remember that type 4 is "broadcast". 

So, we can provide a breakdown of the line group membership using a query similar to the following:

select lg.name as LineGroup, tda.name as Algorithm, lgmap.lineSelectionOrder as Order, n.dnorpattern as DN, d.name as Station 
from linegroup  lg 
 inner join typedistributealgorithm tda on lg.tkdistributealgorithm=tda.enum 
 inner join linegroupnumplanmap lgmap on lgmap.fklinegroup=lg.pkid 
 inner join numplan n on lgmap.fknumplan=n.pkid 
 inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid 
 inner join device d on dmap.fkdevice=d.pkid 
order by lg.name, lgmap.lineSelectionOrder

Sample output from the above query:

linegroup         algorithm         order dn            station
================= ================= ===== ============= ===============
TestLineGroup1_LG Longest Idle Time 0     \+12025552001 SEP0FFFF086E065
TestLineGroup1_LG Longest Idle Time 1     \+12025552002 SEP0FFFF418EE10
TestLineGroup2_LG Longest Idle Time 0     \+12025552003 SEP3FFFF61747C7
TestLineGroup2_LG Longest Idle Time 1     \+17035553003 SEP8FFFF2D99ABF
TestLineGroup3_LG Broadcast         0     \+17035553001 SEP0FFFF82D486D
TestLineGroup3_LG Broadcast         1     \+17035553002 SEP0FFFF729C5A4

Using the results from the first query and the second give you a pretty complete picture of what you have provisioned. If you were so inclined, you could create a composite query that gives you everything from the two queries presented above. If you go this path you must be aware that you are joining some of the same tables more than once. 

Conclusions

If you just want to "see" a summary of hunt pilots and line groups then you may want to stick with the route plan report. However, if you needed to dig into the other table attributes (which I only explored lightly) or you wanted to execute a query with specific filter criteria then you will find that SQL gets you there faster than anything the native UI is providing.

---

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