Tuesday, April 3, 2012

CUCM SQL Query Series: List Calling Search Spaces

In last week's installment of my series on CUCM SQL queries we did a quick dip in the CDR pool. This week we are going to meander our way back to some basic configuration gathering. Specifically, we are going to run a basic query that dumps a list of calling search spaces with associated partitions.


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 list of calling search spaces (CSS) along with assigned partitions. We are going to keep things pretty straightforward this week.
    The Moving Parts

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

    • CallingSearchSpace: As the name implies, this table contains the master list of CSS configurations on the system.
    • RoutePartition: This table contains the master list of partitions on the system.
    • CallingSearchSpaceMember: This is what I call a "mapping" table. Like the deviceNumPlanMap table we have seen in previous queries, the CallingSearchSpaceMember table maps partitions to calling search spaces.
    The information we are looking to present is pretty straightforward. We want to list out CSS and partition configurations using the CallingSearchSpaceMember table to give us some necessary context on the relationships provisioned on the system.

    The Query
    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 css.name as css, css.description, csm.sortorder, rp.name as partition, rp.description
    from callingsearchspace as css
      inner join callingsearchspacemember as csm on csm.fkcallingsearchspace = css.pkid
      inner join routepartition as rp on csm.fkroutepartition = rp.pkid
    order by css.name, csm.sortorder

    The following is a sample of the output one could expect from the above query:
    css                   description                              sortorder partition           description
    ===================== ======================================== ========= =================== ====================================
    HQ_User-Std_CSS       HQ Standard User CSS                     0         HQ_Tenant-Std_PT    HQ Office Tenant Control Patterns
    HQ_User-Std_CSS       HQ Standard User CSS                     1         CL_Tenant-Std_PT    Standard Tenant Control Patterns
    HQ_User-Std_CSS       HQ Standard User CSS                     2         CL_Svcs-Priv_PT     Private Services
    HQ_User-Std_CSS       HQ Standard User CSS                     3         CL_Svcs-Corp_PT     Corporate Services
    HQ_User-Std_CSS       HQ Standard User CSS                     4         HQ_Block-Std_PT     COR Standard Blocking Patterns
    HQ_User-Std_CSS       HQ Standard User CSS                     5         CL_IST-Std_PT       InterSwitch Call Routing Patterns
    HQ_User-Std_CSS       HQ Standard User CSS                     6         HQ_PSTN-Std_PT      HQ Office Users PSTN Routing
    IPVC_Tenant-Std_CSS   Standard CSS for IPVC-VCS                0         CL_Tenant-IST_PT    PBX Tenant Control Patterns
    IPVC_Tenant-Std_CSS   Standard CSS for IPVC-VCS                1         CL_Svcs-Corp_PT     Corporate Services
    IPVC_Tenant-Std_CSS   Standard CSS for IPVC-VCS                2         CL_PSTN-Std_PT      Default PSTN Routing for Cluster

    Each partition has a single and unique entry in the RoutePartition table just as each CSS has a unique entry in the CallingSearchSpace table. The CallingSearchSpaceMember table will actually show multiple instances of both partitions and CSS. Further, the CallingSearchSpaceMember table defines the calling search spaces are partition is linked too. The sortorder field is gives us a way to order the results so that they line up with the actual priority order of individual partitions in a CSS.

    There really isn't much more to it than that.

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


    1. Is there a query to display the phone firmware version each phone in the cluster ?

      1. Are you looking for firmware versions that are configured or are you looking for firmware that is actually running on the phone. They aren't always the same.

        To get a list of phones that have a special firmware assigned use the following:
        run sql select name,specialloadinformation from device where specialloadinformation != ''

        To see the default load:

        run sql select tm.name as model,dd.loadinformation from defaults dd inner join typemodel tm on dd.tkmodel=tm.enum

        The above queries give you data on what is CONFIGURED. To find out what is running I usually use a custom tool I built that queries the phones directly. Not an easy thing to explain via comments as it is all custom code. Basically, you can web to the IP phones and scrape the HTML code to parse out the relevant information.

        -Bill (@ucguerrilla)

    2. I'm looking for firmware that is actually running on the phone. Is there way to run sql for Active Load ID = firmware version name?

    3. Hey Bill,

      Looking for something similar if you could give more information on what how you do these scrapes that would be great.
      We have some 7925's that we need to audit.

      1. In general, I enable HTTP access for phones from UCM. Then I have a script that webs to the phone and pulls the html. You have to customize the script to parse out the HTML. Most phone models can be parsed using the same logic. Some (like the older 7940/60/etc.) are parsed differently.

        I haven't incorporated conference phones and VoWLAN devices into my script. This is because when I first put it together, these devices required authentication and I couldn't figure out how to do that correctly. So, I skipped them. I never got around to fixing or re-testing that.


    4. Hey Bill,

      I'm looking for a similar query. I want to get a list of CSS' that have a particular name (CSS which contains "XLate_OnNet" in the name) which do not contain a certain partition.

    5. Bill,

      I am looking for a query that will give me a list of CSS that have a specific naming convention (containing "XLate_OnNet") which do not contain a particular partition (given the name).


      1. Thanks for your question. For this request, you could use a query that uses the following tables:

        callingsearchspace - where you will find the name of your CSS
        routepartition - where you will find the name of your partition
        callingsearchspacemember - where you find the linkage between the two

        You may also be able to do this with just a query to the CSS table. I'd start with just the CSS first. You would use the 'name' field (name of your CSS) and the 'clause' field (contains a list of partitions in the CSS). I believe the clause field is still present in the current schema. AFAIK, the clause field is used by the digit analyzer when making a routing decision. In some ways, it is redundant to the data you can extrapolate from the callingsearchspacemember table, which is just a mapping table.

        Anyway, try a query like this (assuming the partition you care about is named: MyPartition. ::

        select name,description from callingsearchspace where name like '%XLate_OnNet%' and clause like '%MyPartition%'

        To see what you are working with, I recommend dumping a query where you look at the native format of the clause field. You'll see what I mean when you run a query like:

        select name,clause from callingsearchspace


        -Bill (@ucguerrilla)