Tuesday, April 10, 2012

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!

    17 comments:

    1. Great! Like this query a lot. Well all of this series are great. (Now I have to learn SQL to get the most out of it)

      I have one little correction and i know this is a bit picky.

      There is a small Syntax error in the first two queries.

      I think it should be "order by n.dnorpattern"

      As a I just get known with such queries it took me a little bit to figure out what I'm doing wrong.

      I'm looking forward to see more!

      Best Regards
      Stefan

      ReplyDelete
      Replies
      1. Stefan,

        You are correct. There was a syntax error in the first two queries. I am correcting now. Thanks for catching it!

        -Bill (@ucguerrilla)

        Delete
    2. Any idea how to show all of the call forward configurations for a directory number and filter based on specific directory number?

      ReplyDelete
    3. Anon,

      Sure. We'll put that in the queue. Thanks for the feedback!

      -Bill (@ucguerrilla)

      ReplyDelete
    4. This is another side to Call manager which most administrators dont know about, thank you very much for introducing this to me.

      ReplyDelete
    5. Hi,

      I am having an issue deleting a CSS and while I have used the SQL queries above, I have been able to delete two of them, I still need to delete one more. I cannot find what is using this CSS and I think it is being referenced in one of these call forwarding options.

      Forward Busy Internal
      Forward Busy External
      Forward No Answer Internal
      Forward No Answer External
      Forward No Coverage Internal
      Forward No Coverage External
      Forward on CTI Failure
      Forward No Unregistered Internal
      Forward No Unregistered External

      Is there an SQL query that I can use to see where this CSS is being used up?

      Thanks
      Anthony.

      ReplyDelete
    6. Hi,

      I am having an issue deleting a CSS and while I have used the SQL queries above, I have been able to delete two of them, I still need to delete one more. I cannot find what is using this CSS and I think it is being referenced in one of these call forwarding options.

      Forward Busy Internal
      Forward Busy External
      Forward No Answer Internal
      Forward No Answer External
      Forward No Coverage Internal
      Forward No Coverage External
      Forward on CTI Failure
      Forward No Unregistered Internal
      Forward No Unregistered External

      Is there an SQL query that I can use to see where this CSS is being used up?

      Thanks
      Anthony.

      ReplyDelete
    7. Do you have a query to set CFA a comma-seperated list of numbers to the same RP ?

      ReplyDelete
    8. Great work. Do you know where I can find the call manager database diagram?

      ReplyDelete
      Replies
      1. I have never seen a db diagram for the CUCM database.

        -Bill

        Delete
    9. http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/9_1_1/datadictionary_911.pdf

      ReplyDelete
    10. Hello everybody,

      I am new in SQL and in Call manager.

      I am asked to give a list of who and what permissions they have for the calls.
      I have run on of the above SQL request and I can see the permissions for "Forward" Calls. I need the same thing for normal calls.
      I know there is a dropdown menu for the permissions, but I dont know at all wich table to use and which field to use.

      + I also have to give a list of users with Extension mobility
      + a list of users with a voice mailbox.

      anybody could help me with these requests ?

      Thank you in advance

      ReplyDelete
      Replies
      1. I assume you are asking about the class of restriction (COR) assigned to the phone and/or directory number on the phone. In UCM, there are a couple of different ways to provision COR. The method you apply to determine what is actually provisioned will be determined by the dial plan design approach that intended to be applied.

        So, there isn't a one size-fits-all query here. It depends on your environment. If you are using an approach where "blocking" patterns are provisioned in CSS's that are applied to the line, then you need to query for data in the numplan table.

        If, on the other hand, you take the approach of including permissive partitions at a device level (i.e. implicitly restricting call behaviors) then you need to query the device table.

        Your queries must include some parameters based on your institutional knowledge. CSS naming conventions and the like.

        HTH

        -Bill (@ucguerrilla)

        Delete
    11. Bill,

      Thanks for the query info. I have a request from a user to try to automate a query/data pull for the current call forward information for a specific line. I am familiar with both running a job, and CLI Query to pull the information. What I'm not sure of is if there's a way to create some automation to pull the Call forward info and then dump to an excel file or alternative for an end user's retrieval. I was thinking there may be an API/SDK tool or something to automate this. Maybe I can set up a repeatable scheduled job, and then use Filezilla (ftp client) to log in automatically and download the job results file? Any idea or help would be greatly appreciated. We are currently running CUCM 9.1(2).

      Thanks in advance,

      Arras

      ReplyDelete
      Replies
      1. Arras,

        The best application for something like this is using a script or app on a *nix or Windows host that leverages the AXL/SOAP API to collect and parse the data. You could look at the toolkit that comes with CUCM.

        That will easily get you the data in XML format. Then you just need to work on a way to parse it. There are probably tools that do this. I use my own scripts that I haven't published anyway (yet, anyway).

        HTH.

        -Bill

        Delete
    12. hi folks,

      we run CUCM v9.1.2 here, when entering the SQL command above (adjusting the number as required):

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

      results in an error:

      Column (n) not found in any table in the query (or SLV is undefined).

      I researched and referred to the CUCM data dictionary, finding that the query needs updating to the following:

      run sql select n.dnorpattern, cfd.cfadestination from numplan as n inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where cfd.cfadestination like 'NUMBER' order by n.dnorpattern

      where NUMBER is the number required.

      We also use extension mobility and a lot of extensions have call forward busy, no answer and/or unregistered set to the same number (often a hunt group) - a query for this is as follows:

      run sql select n.dnorpattern, n.cfbdestination from numplan as n where cfbdestination like 'NUMBER' order by n.dnorpattern

      - again, replacing NUMBER with the number required. Hope this helps others ...


      Dan SJK.

      ReplyDelete
      Replies
      1. Dan,

        Thanks for the catch. It was a typo. Fixed.

        -Bill (@ucguerrilla)

        Delete