Wednesday, July 24, 2013

CUCM SQL Queries: Finding that CSS Dependency

Sometime in May (or was it April?) I was working on a project to migrate a customer's dial plan from their legacy design to a new "normalized" (my flavor of Cisco's "globalized") dial plan. I did most of the conversion leveraging AXL/SOAP and SQL queries. One of the "interesting" issues I came across was during the clean up process. 

There was this one CSS that just didn't want to leave the party. The CUCM barked at me and said the CSS was in use. However, the Dependency Records report said it wasn't. Why? Well, the CSS table is one of the cool kids on campus. It is heavily referenced by other tables in the database. It may even be more popular than the device table. The core issue is that the dependency report is not checking every table reference.  

Apparently, I am not the only one to stumble across this issue. One of the readers of my blog posed a similar question. I figured that instead of burying my response in an obscure comment I'd post it as a new entry. I needed to add something to blog anyway. 

Background

I am going to keep this one quick and dirty. I'll forego the standard format used for other blogs in this series. Hopefully that isn't a problem for anyone. As noted above, the basic issue I ran into is that the "Dependency Records" report that you can access from most config pages in CCMAdmin does not report on every dependency. So, when I needed to find out what database record was keeping me from removing a particular CSS I had to find an alternate solution. 

How do I love thee...

There are around 15 tables that have references to the CallingSearchSpace table. The NumPlan table actually has multiple foreign key references to the CallingSearchSpace table.

How many times? Well, we can find this out with a simple query:

select t.tabname, count(c.colname) as count 
from systables t, syscolumns c 
where t.tabid=c.tabid and c.colname like 'fkcallingsearchspace%' 
group by t.tabname

This query will give us a list of tables that have references to the callingsearchspace table. This is the query results from a CUCM 8.5 system (CDR table references removed):

tabname                       count
============================= =====
callforwarddynamic            2
callingsearchspacemember      1
device                        15
devicenumplanmap              1
devicepool                    15
enduser                       1
extensionmobilitydynamic      2
externalcallcontrolprofile    1
incomingtransformationprofile 4
numplan                       19
recordingprofile              1
routelist                     1
sipdevice                     1
vipre164transformation        2
voicemessagingpilot           1

Wow. So, the NumPlan table references the CallingSearchSpace table 19 times. That means each record in the NumPlan table has 19 opportunities to hook up with the CallingSearchSpace table. The Device and Device Pool tables have 15 field references each. 

A rose by any other name...

OK. So, the NumPlan table has 19 fields that are foreign key references to the CallingSearchSpace table. What are those field names? Let's take a quick peak using a variation of the query we used to get allocation info:


select c.colname 
from systables t, syscolumns c 
where t.tabid=c.tabid and t.tabname='numplan' and c.colname like 'fkcallingsearchspace%'

On a CUCM 8.5 system, we'll see that the NumPlan table has the following references to the CallingSearchSpace table:


colname
=====================================
fkcallingsearchspace_sharedlineappear
fkcallingsearchspace_translation
fkcallingsearchspace_cfb
fkcallingsearchspace_cfna
fkcallingsearchspace_devicefailure
fkcallingsearchspace_mwi
fkcallingsearchspace_cfapt
fkcallingsearchspace_cfbint
fkcallingsearchspace_pff
fkcallingsearchspace_pffint
fkcallingsearchspace_reroute
fkcallingsearchspace_cfnaint
fkcallingsearchspace_cfhr
fkcallingsearchspace_cfhrint
fkcallingsearchspace_cfur
fkcallingsearchspace_cfurint
fkcallingsearchspace_revert
fkcallingsearchspace_pkmonfwdnoret
fkcallingsearchspace_pkmonfwdnoretint

First, I have to take a quick moment to highlight the convention here. When you see a field that starts with fk[name] then that field is a foreign key referencing another table. What table? The text following fk is the exact name of the table. The underscore ("_") is used when the same table is reference more than one time. The string after the underscore is someone's attempt to indicate how the reference is used. Clearly, some of these relationships need to be, shall we say, translated? Here are a few...
  • _translation: The NumPlan table stores translation patterns and translation patterns have calling search spaces
  • _mwi: NumPlan also stores MWI patterns (On/Off) and they have calling search spaces
  • _revert: I believe this is for Directed Call Park. Also stored in the NumPlan table
  • _sharedlineappear: This is the line level calling search space for calls placed from any line associated with the NumPlan entry
  • _cfb and _cfbint: Call forward busy  ("int" means internal and sans "int" means external)
  • _cfna and _cfnaint: Call forward no answer
  • _cfur and _cfurint: Call forward unregistered
  • _pff and _pffint: Call forward, no coverage
  • _devicefailure: Call forward on failure (CTI)
  • _pkmonfwdnoret and _pkmondfwdnoretint: Park monitoring forward no retrieval
Now, I haven't gone through each one of these settings to see what is actually missed by the dependency records report. I do know that the dependency records report does not check any of the "call forward" settings on the line (other than [possibly] CFA primary and secondary, which is in yet another table). 

Meanwhile, deep in the bowels of a data center in MD... 

Getting back to the issue of deleting this pesky calling search space. I suspected almost immediately that the issue was with one of the call forwarding CSS configs on a line. The next step was flushing it out. There was no "sexy" way to do this. Or, I should say, I don't know of a "sexy" way to get at the data. Well, I guess you could write a tool to report on dependencies. 

What I did was execute a bunch of quick queries to get at the data quickly. Here is a sample:
/*css distro for line level*/
run sql select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_sharedlineappear=css.pkid group by css.name

/*css distro CFA primary and secondary*/
run sql select css.name, count (*) as Count from callingsearchspace css inner join callforwarddynamic cfd on cfd.fkcallingsearchspace_cfa=css.pkid group by css.name
run sql select css.name, count (*) as Count from callingsearchspace css inner join callforwarddynamic cfd on cfd.fkcallingsearchspace_scfa=css.pkid group by css.name

/*css distro for CallFwd */
select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfb=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfbint=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfna=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfnaint=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfur=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_cfurint=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_pff=css.pkid group by css.name

select css.name, count(n.dnorpattern) as ipline from callingsearchspace css inner join numplan as n on n.fkcallingsearchspace_pffint=css.pkid group by css.name


I have a tool I developed a while ago that lets you put in multiple SQL queries to run as a batch and create an Excel spreadsheet with the data results. So, I solved my problem by adding the queries listed above to my batch tool and running a report. I found my errant CSS  reference within a minute and then I was able to proceed with the clean up.

You don't need a batch tool. You can easily copy/paste the queries into the CLI. The output will show counts for all CSS's. I used this method because I had multiple CSS's that I wanted to clean up. One could easily query on PKID or leverage an inner join and query by name. 

OK. Time to get back to work! My 15 min is up.



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

8 comments:

  1. Thanks for the article. Very interesting & informative.

    I'm going to use to try to find some of the more obscure uses of CSSs I have tripped up on. For example: 1) Where are the CSSs used in System Parameters? 2) Transformation Patterns? Maybe Device table?

    ReplyDelete
  2. No problem. Glad you like.

    >1) Where are the CSSs used in System Parameters?

    Great question. I assume you are referring to the various CSS references you will see in the Call Manager service parameters. Well, these are stored in the processconfig table. Which is a little challenging to navigate. You will need to inner join the typeservice table (on tkservice) to get the "Cisco CallManager" service by name (I don't recall the enum value). What is interesting is that it is NOT a foreign key reference to CSS.

    I'll need to check a few things and update.

    > 2) Transformation Patterns?

    A couple of places. I'd check the device and devicepool tables.


    HTH.

    -Bill


    ReplyDelete
  3. Hello,

    I've just one little question, how do you extract the received sql data ?

    Best Regards
    Danny

    ReplyDelete
  4. Danny,

    If using the CLI, you can log the output to a file. I use the CLI for demo purposes but I use a tool I built to do my "day to day" job. That tool reads data and writes to CSV or XLSX.

    So, it depends on what UI or API you are using.

    -Bill

    ReplyDelete
  5. Hi WIlliam,
    I can't the way to discover the directory number CSS... by the numplan table I just get the forward CSSs. Do you have any hint?
    Thanks in advance!
    Fabio

    ReplyDelete
    Replies
    1. Fabio,

      The field you are interested in is fkCallingSearchSpace_SharedLineAppear. It is in the numplan table.

      Example:

      select css.name, count(n.dNOrPattern) as 'ipline'
      from CallingSearchSpace as css
      inner join NumPlan as n on n.fkCallingSearchSpace_SharedLineAppear= css.pkid
      group by css.name

      -Bill (@ucguerrilla)

      Delete
  6. Would you have a way to get a list of devices (or which lines more specifically) that have permission to dial a specific translation pattern?

    ReplyDelete
    Replies
    1. That could get hairy using just straight SQL queries. There are several dependancies. First, the line CSS and device CSS are cumulative. So, you would need to account for that. In a query you could check for a partition in the device css or line css. Or you could look in the PT clause of a CSS and then account for the CSS association to the line or device. Sort of the same challenge from a structured query PoV.

      Line CSS is attached to numplan table entries. Device CSS is in the device table. Lines associated to devices are in the devicenumplanmap table. These need to be interrogated to get the CSS portion of the view you are trying to create.

      The translation is a pattern and it has a route partition (numplan table and routepartition table). You need to use this partition assignment to find the CSS that can "see" the partition and then check the devices/lines to see if they are provisioned with the specific CSS(s).

      I think that you would need to pull data and then post process to refine it a bit to get at what you want. Alternatively, you could script it out and run multiple queries via some scripting language.

      HTH

      -Bill (@ucguerrilla)

      Delete