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!