Wednesday, January 16, 2013

Using SQL to Look at MOH Source Configurations

In this installment of the SQL query series I wanted to actually explore a request that came from one of my customers. The scenario/question:
Is there a query that could show me the music on hold audio source configurations for phones?

The driver behind the question is that my customer has been adding a lot of phones to his Cisco Unified Communications Manager (CUCM) environment. He has a very specific MoH configuration and lately has "heard" (sometimes, literally) issues with the MoH that is being played out. So, he wants to dump some data to look for inconsistencies.  

This is pretty straightforward and may be handy to other readers. So, in the words of Ben Grimm: "It's clobberin' time" (yeah, I'm a dork).

Primer

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

This Week's Query

So, the question is how do we dump out a recordset that shows the music on hold (MOH) audio source assignments for devices and lines. Let's take a moment to define the "audio source". The CUCM IPVMS service is responsible for streaming media in a typical Cisco UC environment. A CUCM cluster can have up to 51 audio sources, with "51" being the "fixed" audio source and 1-50 being audio files uploaded to the cluster.

Audio sources are given numeric IDs (1-51) and these source IDs can be assigned to a directory number, a device, or at a global (CUCM service) level. IDs assigned to a line are preferred over IDs assigned to the device. Sticking with that logic, device IDs are preferred over system default IDs.

When a phone places a remote party on hold, the audio source ID determines "what" music should be streamed. The Media Resource Group List (MRGL) assignment of the device being put on hold determines what MOH server should send the stream (unicast) or should be listened to (multicast). [Granted, slight over simplification here...]

Final word, there is the concept of "network" hold and "user" hold. User hold corresponds to a user pressing a hold button. Network hold corresponds to other transient events that put a remote party on hold (transfer, conference, etc.).

Tying this back into our question. If you know that a certain group of phones should use audio source 5 when initiating a hold event and you want to verify compliance then you need to look at the audio source assigned to every line on every phone, as well as the audio source assigned to the phones themselves. Keeping in mind that line configs take priority.

The query needed to display the data is actually pretty straightforward. The tables that play a role are the most commonly used tables in queries I have to use on a regular basis.
    The Moving Parts

    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. We are going to use this table to not only list out gateway names but to list out route list names as well.
    • DeviceNumplanMap: This is a mapping table that allows us to map the route patterns we are interested in to the route list or voice gateway provisioned to route the call.
    • Numplan: This table stores all patterns, including route patterns.
    We use the Numplan to get hold configurations for device lines and we use the Device table to get the same information for phones. We are using the DeviceNumplanMap to make the data a little more readable because we can organize the output so that we are looking at line assignments to specific devices.

    The Query
     You can run the query from the CLI using run sql [insert query]:


    select d.name,d.description,d.userholdmohaudiosourceid,d.networkholdmohaudiosourceid,
    dmap.numplanindex,n.dnorpattern,n.userholdmohaudiosourceid as numuserhold,
    n.networkholdmohaudiosourceid as numnethold 
    from device d 
      inner join devicenumplanmap as dmap on dmap.fkdevice=d.pkid 
      inner join numplan n on dmap.fknumplan=n.pkid 
    where d.tkclass=1 
    order by d.name,dmap.numplanindex
    


    If you have followed the SQL series then you recognize the "trifecta" join between device, devicenumplanmap, and numplan. We are organizing our output around the device ID and all directory numbers will be listed with their respective devices. I guess I should point out the "where d.tkclass=1" clause. This basically says: "only show me phones". Remember, there are lots of different records stuffed into that device table.

    Sample output:


    name            description                            userholdmohaudiosourceid networkholdmohaudiosourceid numplanindex dnorpattern   numuserhold numnethold
    =============== ====================================== ======================== =========================== ============ ============= =========== ==========
    SEPBEEF192CC232E Peter Parker          2                        2                           1            2025553098    3        3
    SEPBEEF192CC232E Peter Parker          2                        2                           2            2025557400    NULL        NULL
    SEPBEEF192CC232E Peter Parker          2                        2                           3            2025557401    NULL        NULL
    SEPBEEF192CC232E Peter Parker          2                        2                           4            2025557402    NULL        NULL
    SEPBEEF192CC232E Peter Parker          2                        2                           5            2025557403    NULL        NULL
    SEPBEEFC82D486D Ben Grimm              NULL                     NULL                        1            7035554465    NULL        NULL
    SEPBEEFC82D486D Ben Grimm              NULL                     NULL                        2            7035554461    NULL        NULL
    SEPBEEFD33A99C8 Bruce Banner           2                        2                           1            2025553033    NULL        NULL
    
    

    In the above example, we see that there are three phones. Parker's phone has five lines (1-5), Grimm's phone has two lines, and Banner's phone has one. The output looks pretty straightforward but there are some records that warrant a closer look. The first line on Parker's phone (DN: 2025553098) has a different MOH audio source on the line-side. So, calls held on this line will likely have a different music experience from calls held on any other line. Further, Grimm has no audio source defined for the device nor any associated line. So, the system wide configuration is applied. This may or may not be valid. 

    Considerations

    Well, the obvious consideration is that the output from this query is merely data. You need some context to understand the meaning of this data and to determine if there is something amiss. You could use a more "robust" where clause to focus your output. You could compare  the network hold source IDs to the device and only show records where those settings are inconsistent. You can search for specific MOH source IDs in any combination of fields. We could get even more complex and tie in Device Pool or something similar to help put the data in context. 

    I think I'll save some of those more complex queries for another day. You could always let me know via the comment section if there is some interest in seeing other methods. Thanks for reading!




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

    6 comments:

    1. "You could compare the network hold source IDs to the device and only show records where those settings are inconsistent."

      Could you share an example of how to incorporate some sort of if/then/else logic into a sql query? That would come in handy for sure. The only way I've been able to do that till now is via multiple axl executesqlquery's and additional logic. It'd be extremely handy to be able to do that directly from the cli!

      ReplyDelete
      Replies
      1. Will,

        I wasn't thinking if/else logic here. I was thinking more of a basic value comparison in the where clause. Complex clauses with logic operators can get a decent filter going but the CLI doesn't let you do complex operations. It would be handy though!

        -Bill (@ucguerrilla)

        Delete
    2. i am looking for a query where i can find the status of a CTI Route Point in a Contact Center Environment.

      ReplyDelete
    3. Jimmy,

      I assume by "status" you want to know registration status. This information is not maintained in the DB. I believe you can retrieve real time information on CTI RPs and Ports from the RISport API. I use that API to get device (phone) registration info but it has a lot more data to offer in regards to real time/registration status.

      Interestingly enough, I have not explored the options of querying realtime from the CLI. Aside from using the "show perf query" command. Which does provide decent data even though it isn't very flexible.

      If I am off base with my assumption that you are looking for real time status info (e.g. registration) then let me know. I have several queries that focus on CTI-specific configurations.

      If you are looking for real time info then I don't have anything to brain dump at present. I would need to go to the source code on my RISport toolkit and see what there is to see with CTI (my toolkit focuses on phones at the moment).



      -Bill (@ucguerrilla)

      ReplyDelete
    4. Excellent series Bill, I'm digging deeper and deeper into CUCM AXL SQL queries and this site is very insightful. You mentioned CTI-specific queries, I'm interested in registration status, real time via AXL, and from another angle on the same issue, do you knowhow to/have you ever tried to poll (via SNMP) CTI specific information?

      Regards,

      Michael

      ReplyDelete
    5. Michael,

      You cannot access real time information via the AXL API. However, you can access real time information via RISport API. I haven't addressed that heavily on this blog but I did do a write up a while ago on my company's blog site:

      http://www.netcraftsmen.net/blogs/entry/good-bye-devicelistxasp-hello-risport.html

      It is a tad dated. I should probably look into refreshing that content and bring it over to ucguerrilla.com.

      I have polled via SNMP but I found it to be somewhat cumbersome because you have to poll the mib tables independently on each UCM node. With RISPort, the API on one UCM node can provide the entire cluster view. You still have to contend with different registrations states as reported by each node (i.e. if a phone fails from one node to another, node A says "unregistered" while node B says "registered").

      So, I'd recommend taking a closer look at RISport API.

      -Bill

      ReplyDelete