CUCM SQL Queries: Listing Call Forward All Settings

In this week's installment of my Cisco Unified Communications Manager (CUCM) SQL Queries series I wanted to dive into the call forward all (CFA) configurations in a production environment. This seems to be a "favorite" for folks who follow my NetCraftsmen blog.

I have found that being able to see where phones are forwarded is a common need. I have seen the question come up often in the Cisco support forums and I need to query CFA status on a pretty regular basis in my own job.

Primer

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

This Week's Query

For this week's post we are going to dump a listing of phone lines and their call forward all (CFA) status. This type of query is helpful for several tasks. I use it often when doing upgrades where we are building a parallel CUCM cluster and migrating data (such as when a customer is upgrading from a Cisco MCS platform to a Cisco Unified Computing System (UCS) platform). 

When doing migrations, I like to minimize disruption to end users as much as possible. In most migrations there is a change freeze window. The length of this window will vary but it is quite common that end users will make changes anyway. What kind of changes? Well, users can modify CFA configurations from their phones, add speed dials, fast dials, etc. So, I like to use a tool I developed which queries some key tables in the CUCM db at the beginning of a change freeze window [DataSet_1]. After data is moved to the new CUCM system, I run the query tool on the "old" CUCM system again [DataSet_2]. From there, I can rectify the differences between DataSet_1 and DataSet_2. 

Another common use is during configuration/design assessments. During assessments, I like to check CFA configurations to identify scenarios where users are forwarding calls to calling areas that they aren't supposed to.  

The Moving Parts

From a db perspective the following Informix database tables store information relevant to our queries:

• NumPlan: This table stores all digit patterns (e.g. route, translation, directory number, etc.) provisioned on the system.
• RoutePartition: This table contains the master list of partitions on the system.
• CallForwardDynamic: This table stores all CFA configurations for any directory number provisioned on the system.
• CallingSearchSpace: As the name implies, this table contains the master list of CSS configurations on the system.
• typeCFAAssociationPolicy: A type table that contains enumerators for the CFA activation policies assigned to directory numbers in the NumPlan table.

The information we are looking to present is pretty straightforward. We want to dump a list of directory numbers and associated call forwarding configurations for further processing.

The Queries
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 n.dnorpattern, cfd.cfadestination, cfd.cfavoicemailenabled
from numplan as n
  inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid
order by n.dnorpattern

The following is a sample of the output one could expect from the above query:

dnorpattern   cfadestination cfavoicemailenabled
============= ============== ===================
7031013098    913105551212   f
2021077400                   t
2021077401                   t
2021077402                   f
2021077403                   f
7031013040    94105551212    f

Based on the above output, we know that 7031013098 is forwarding their line to 913105551212 (a long distance call). We also know that 2021077400 and 7401 are forwarding their lines to voicemail. Finally, 7031013040 is forwarded to the directory assistance number in Maryland (410 NPA).  

We could tweak the query to actually filter on specific CFA destinations. For instance:

select n.dnorpattern, cfd.cfadestination, cfd.cfavoicemailenabled
from numplan n
  inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid
where cfd.cfadestination like '9011%'
order by n.dnorpattern

In the above query, we are only looking at directory numbers forwarded to international destinations (note: assuming "9" is the off net access code). 

Considerations for CSS Validation

One of the key considerations, especially when doing a configuration audit, is identifying what CSS is active for unconditional call forwarding on a line. Let's cover some 101 basics before getting to the ugly queries. When you look at a line appearance in CUCM you have the option of specifying whether the directory number should use the system default, a configured CSS, or the provisioning device/line CSS for CFA provisioning. The options are illustrated in the following figure.

In the above figure the admin can modify the "Calling Search Space Activation Policy" to use either:

• Use System Default
• With Configured CSS
• With Activating Device/Line CSS

Using the system default will leverage a CUCM service parameters to determine behavior. With "Configured CSS", the options highlighted in blue (see above figure) are applied. So, the effective CSS in our example would be CL_COR-LD_CSS + HQ_User-Std_CSS. With "Activating Device/Line CSS" the line level (CL_COR-Local_CSS in our example) and the device level (not shown) are concatenated to build the effective CSS.

So, how do we query for the CSS information? We could try to dump a big record set showing all combinations in one tabular stream or we can break it into pieces. Let's do the latter. First, let's look at the configuration for all directory numbers that are using the "With Configured CSS" option:

select n.dnorpattern, css1.name as primaryCSS, css2.name as secondaryCSS 
from numplan as n 
  inner join callforwarddynamic as cdn on cdn.fknumplan=n.pkid 
  left join callingsearchspace as css1 on cdn.fkcallingsearchspace_cfa=css1.pkid 
  left join callingsearchspace as css2 on cdn.fkcallingsearchspace_scfa=css2.pkid 
where n.tkcfacssactivationpolicy = 1 or n.tkcfacssactivationpolicy = 0
order by n.dnorpattern

This will result in output similar to the following (NOTE: We are assuming that the CUCM service parameter for CSS activation policy uses the default setting: "Configured CSS"):

dnorpattern   primarycss    secondarycss
============= ============= ================
1000          NULL          NULL
4465          COR-LD_CSS    User-Std_CSS
7031077400    COR-LD_CSS    User-Std_CSS
7031077401    COR-LD_CSS    User-Std_CSS
7031077402    COR-LD_CSS    User-Std_CSS
7031077403    COR-LD_CSS    User-Std_CSS 

In the above query we are listing the directory number, primary CSS used for CFA and the secondary CSS used for CFA. To do this we are using foreign keys in the CallForwardDynamic table to de-reference the CallingSearchSpace table. As shown in the example, we are joining the CallingSearchSpace table twice. 

Additionally, we are using a "left join" (or "left outer join") clause. All this means is that we are going to show all records in the "left" table (as provided in the on clause) even if the join condition doesn't return any records from the "right" table. We are doing this because we don't want to omit records where either the primary or secondary CSS has a parameter of <none> (or Null).

The last noteworthy aspect for the above query is that we are filtering records based on the CFA CSS Activation policy. In the NumPlan table the field tkcfacssactivationpolicy identifies the policy applied to a specific directory number.  A value of 0 is used for default, 1 for configured CSS, and 2 for activating line/CSS. You can see the enumerators that are defined by querying the type table: select enum, name from typecfacssactivationpolicy

Moving on to the next query: listing CSS configuration when using the activating device and line configurations. We can use the following query to list configurations for devices that are using the activating device and line CSS:

select n.dnorpattern, css1.name as primaryCSS, css2.name as secondaryCSS 
from numplan as n 
  inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid 
  inner join device as d on dmap.fkdevice=d.pkid
  left join callingsearchspace as css1 on n.fkcallingsearchspace_sharedlineappear = css1.pkid 
  left join callingsearchspace as css2 on d.fkcallingsearchspace=css2.pkid 
where n.tkcfacssactivationpolicy = 2 
order by n.dnorpattern

This query follows a similar structure as the previous one. The main difference is we are actually looking at the line level and device level calling search spaces. Again, we use "left join" so we do not omit NULL values. We further identify we are only interested in seeing directory numbers that are provisioned with a CFA CSS activation policy of "Activating Device/Line CSS".
 

---

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