Thursday, April 2, 2015

Using SQL To Survey Phone Station Line Appearances

A reader comment on one of the entries in the SQL Query series asks the question:

I have multiple lines associated to the same phone and i'm trying to write a query to get only Line [1] "main line" ,any help please ??
We talked about querying line appearances associated phones in one of the early installments. Now we want to turn some extra knobs to focus on specific data views. I want to provide an example query to address the readers question and also touch on another, related query to show an example of how we can find anomalous data in our UCM solution. 

There are lots of ways to look at Device/Line associations. Especially if you get into the business of identifying user/line and Directory URI associations. We won't get into all of that in this installment but I think it is a good thread to follow. So, let's consider this a "Part 1" for the time being.


In this installment, we are going to kick around a few tables to render data views that will help us identify device and directory number relationships. We'll focus our attention on two primary tables along with a "mapping table". THE mapping table when you are dealing with directory numbers. 

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

The Tables

The tables we are going to focus on are as follows:
  • Device: This table contains all devices (physical and logical) provisioned in the system. This includes phones, gateways, trunks, route lists, hunt lists, etc. 
  • Numplan: This table contains all digit patterns provisioned in the system. This includes directory numbers, route patterns, translation patterns, etc.
  • Devicenumplanmap: This is a "mapping" table. It is used to store device to numplan mappings. Lot's of magic happening here.

The Queries

Primary Line Appearance

The inspiration for this entry comes from the following question posed by a reader:
How do I write a query to only show the main line (Line 1) on my phones?
Getting this information is fairly straightforward and it all comes down to the extra bits of data that are stored in the devicenumplanmap table. Here is a sample query:

select as Phone,d.description,n.dnorpattern as PrimaryDN
from device d
inner join devicenumplanmap dmap on dmap.fkdevice=d.pkid
inner join numplan n on dmap.fknumplan=n.pkid
where d.tkclass=1 and dmap.numplanindex=1
order by

So, we are listing phones with descriptions and a single directory number entry. Since we are using an "inner join" method here, we will only list phones that actually have a primary DN. Listing phones that don't have a DN at all is also a bit of data one may want to see but we'll get back to that.

Remember that the device table stores ALL devices (not just phones). So, we are limiting our query to only look at phones by using the tkclass field in the device table. The "magic" (if you will) comes from devicenumplanmap table. Specifically, the numplanindex field is used to identify the DN position on the device. A value of "1" means this is the first line appearance on the phone.  

Null Line Appearance

Let's go the other way with this. Assume you wanted to show phones where there is no primary line appearance. It happens all of the time on systems of all shapes and sizes. One of the things I look at when a customer asks us to do some optimization is to look for phones that are sitting there with no DNs. Sometimes this is legit and sometimes it is an absolute mess. 

Here is one way to get the info we want:

select as Phone,d.description
from device d
where d.tkclass=1 and 
   1>(select count(dmap.pkid) from devicenumplanmap dmap where dmap.fkdevice=d.pkid and dmap.numplanindex=1)
order by

So, this query will list phones and descriptions for devices that do not have a directory number associated with their primary line appearance (i.e. first button on the phone). Again, we are filtering on type class of 1 (tkclass). This gets us "phones". We are also running an inner select query to get a count for the number of entries in the devicenumplanmap table where the phone exists AND the numplanindex is actually "1" (which means the first line appearance). 

If the count returned from the inner select is "1" then are criteria fails and that phone isn't listed in the output. If the count is "0" then we know that we have a phone that doesn't have a DN association on the primary line.

What Else?

The queries provided in this installment are actually part of a "set" of queries I use when doing an assessment on a customer system. This query set has grown over time because there are many different ways to look at the data in UCM. Associations to specific route partitions, shared line appearances, DNs that aren't shared lines, external phone masks, calling search space associations, and etc., etc. etc.. 

I have to get back to my regular J.O.B. So, I think maybe I will just throw a few of these in the mix from time to time. If you have a specific data view you were interesting in, let me know in the comments!

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


  1. This is good to know, and another one that I always want to know is the opposite. Do I have and DN's out there that aren't assigned to a device? How would you find that?

    1. Brian,

      You can get this by focusing on the "iscallable" field in the numplan table. The value is 't' when a pattern is valid (i.e. assigned to a device) and 'f' when it is not routable.

      So, the following would list numplan patterns that are DNs and are not assigned to a device:

      select dnorpattern from numplan where iscallable='f' and tkpatternusage=2



  2. Thanks. Very useful

  3. Greetings,
    Having trouble making use of the numplanuri table. Ideally would like a report of DN's that do NOT have the directory URI field populated. Any help pointing me in the right direction would be appreciated


    1. Interesting query. I will put something together and post it separately. I'll drop the link here once complete. Thanks for the feedback!

      -Bill (@ucguerrilla)

    2. A little (or a lot) late on this reply. My apologies. To get a list of DNs that don't have associated URIs: select n.dnorpattern,uri.uri from numplan n left outer join numplanuri uri on n.pkid=uri.fknumplan where uri.uri is null and n.tkpatternusage=2


      -Bill (@ucguerrilla)

  4. How about a query to display learned URI's?

  5. I am need to bulk update option under end user, Service Settings >> Home Cluster and Enable User for Unified CM IM and Presence (Configure IM and Presence in the associated UC Service Profile)

    Let me know possible via SQL