Using SQL to Look at EM Profile MOH Sources

In this installment of the SQL query series I wanted to actually explore a request that came by way of the Cisco Support Community. The scenario/question:

What would the query be to obtain the MOH source for DN's that are assigned to EM Profiles?

The individual posing the question was primarily interested in listing MOH sources for directory numbers associated with Extension Mobility profiles. The query to grab this data is a simple variation on a query I presented a few months ago in the blog entry: Using SQL to Look at MOH Source Configurations.

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 lines associated with EM profiles. As noted in this blog 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 dealing with Device Profiles, you can configure a user hold audio source at the device level but NOT a network hold audio source. Directory Numbers associated with device profiles have the same options for MOH as those assigned to physical devices.

The query needed to display the data is actually pretty straightforward and nearly identical to the previous MOH query we presented. 

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, dmap.numplanindex, d.userholdmohaudiosourceid, 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=254 
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. In this variant of the MOH config query, we don't need to grab the device level network hold audio source. This is because, well, device profiles don't have that parameter. 

Aside from tweaking the output fields, the main difference with the above query when compared to the previous MOH SQL query is the device type. We are using the "where d.tkclass=254" clause to limit our output to EM profiles.  Remember, there are lots of different records stuffed into that device table.

Sample output:

name          numplanindex devuserhold dnorpattern numuserhold numnethold
============= ============ =========== =========== =========== ==========
TestEMProfile 1            NULL        2001        1           1
TestEMProfile 2            NULL        2003        NULL        NULL
TestEMProf2   1            NULL        2004        2           2
TestEMProf3   1            NULL        2005        1           1

In the above example, we see that there are three profiles. TestEMProfile has two lines (1-2), TestEMProf2 and TestEMProf3 each have one. The output looks pretty straightforward but there are some records that warrant a closer look. None of the profiles have a user hold audio source defined at the "device-level". Directory number 2001 is provisioned to use audio source ID 1 for user and network hold while Directory number 2003 has no user/network hold audio sources. This means that the default system settings will be applied. Extension 2004 is used audio source ID 2 and 2005 is using audio source 1. Nothing spectacular here. 

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. 

---

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