Friday, March 2, 2012

CUCM SQL Queries: A Series

A couple of years ago I started a blog series on the AXL SOAP Toolkit that Cisco provides with Unified Communications Manager (CUCM). Recently, I was talking to a colleague of mine about starting a new series that simply focused on providing SQL queries that I use when I need to get data from the CUCM server. I figure, why not? So, I am going to go for a query a week. Let's see how it goes.



Primer

Anyone looking to build a custom query to run against their CUCM system may want to take a look at the following resources:
I classify tables in the CUCM databases into three (3) categories:

Standard Tables: Tables which contain the core data values of a particular object like devices, number patterns, end users, etc.

Enumeration or "type" Tables: Used by other tables to resolve enumerator IDs to some name or moniker.  That is understating their purpose somewhat, but from a SQL query point of view you will use type tables mainly to resolve enumerator values to a human readable format. 

Mapping Tables: Basically connects unique records from one table to unique records in another table.

Standard tables and mapping tables use a unique key that is always named: pkid. Enumeration or "type" tables also have a unique key that is always named: enum. Whenever a field in a table is linked to another table or another record in the same table, the following field name conventions are used:
  • fk(x): This is a foreign key (unique value) in another table. That table will be named (x) and the foreign key field will always be pkid.
  • fk(x)_(y): This is a foreign key (unique value) in another table. The table will be named (x) and the "_(y)" is a descriptor which usually indicates that in a given record there is more than one field pointing to the same foreign table.
  • tk(x): This is a type key (enumerator) in another table. The table will be named type(x) and the foreign key will always be enum.
  • ik(x): This is used when a field is de-referencing another unique record in the same table as the source record. The table will be named (x).
For some basic examples on using the various table types, you can check out one of the posts I wrote on the Chesapeake NetCraftsmen blog site.

Disclaimer

Throughout this series, the examples are built using the CLI. Some of these queries can return rather bulky data sets and I have not tested performance impact of running these queries from the CLI. While I do run queries from the CLI in production and have not encountered issues, your mileage may vary. 
I recommend using a tool that leverages the AXL/API for production use. If you are going to use the CLI to execute queries in production, and are unsure of the resource impact, I recommend testing queries and monitoring your systems with RTMT to make sure you understand the impact. Further, I would not run queries from the CLI on call processing systems (i.e. Subscribers running CCM service) during core production hours.
  Basic Syntax

Throughout this series I will present SQL queries as a command block such as:
select name,description
from device
where description like '%Guerrilla'
You can execute the above query directly from the CLI (which is assumed throughout this series) or by using the executeSQLQuery command via the AXL/SOAP interface. From the CLI the above would look like:
admin:run sql select name,description from device where description like '%Guerrilla'

Notice that we use the "run sql" command from the CLI.

The Weekly Query

Let's get down to the nitty gritty. For this week's post I am going to discuss a request I fielded where the user show line group members that are not logged in.
The Moving Parts

From a db perspective the following Informix database tables store information related to line groups and hunt lists:
  • LineGroup This table defines the line group name, hunt algorithms, etc.
  • LineGroupNumPlanMap This will map a line group to a specific numplan entry, such as a phone line directory number.
  • NumPlan This is the master table for all digit patterns configured in your CUCM cluster. This includes phone lines, Hunt Pilots, translations, route patterns, etc.  For your purposes you need this value to get to identify a device and you may want to use it for display purposes.
  • DeviceNumPlanMap This is another mapping table. It will match up phone line directory numbers in NumPlan to the IP phones in the Device table.
  • Device  This table will contain devices like the IP  phone.  Since users log into/out of line groups from a device level  (instead of the line level), you will need to know which phone you are  dealing with. Note, that Hunt Lists are also stored in the Device table.
  • DeviceHlogDynamic This table simply keeps trag of whether a device has the "HLOG" status  toggled.  True means that the phone lines are  logged into all line  groups.  False means that the lines are logged out of line groups.  I  use the term "phone lines" here simply because when a phone use toggles  HLog to false, they are logged out of all line groups and this includes  all lines on the phone.

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 lg.name as LineGroup, n.dnorpattern, dhd.hlog
from linegroup as lg 
inner join linegroupnumplanmap as lgmap 
   on lgmap.fklinegroup=lg.pkid
inner join  numplan as n on lgmap.fknumplan = n.pkid
inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid
inner join  device as d on dmap.fkdevice=d.pkid
inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid
order by lg.name
Yeah, it is ugly but it works.  Essentially we are going to display the Line Group name, directory numbers for all LG members, and whether the HLog status for the device that owns the directory number. If we were to run this from the admin interface you may have something like this:

linegroup          dnorpattern hlog
================== =========== ====
DC_Operators_LG 2025553299  t
DC_Operators_LG 2025553801  t
DC_Operators_LG 2025553802  t
DC_Operators_LG 2025553803  t
DC_Operators_LG 2025550207 f
VM_CUC-A-PUB_LG    1091001     t
VM_CUC-A-PUB_LG    1091002     t
VM_CUC-B-Pub_LG    1092001     t
VM_CUC-B-Pub_LG    1092002     t
admin:

Based on the output, we can see that 2025550207 is not logged into the line group. More specifically, a device provisioned with this directory number has the "HLog" feature disabled. 


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

31 comments:

  1. William,

    I just found this blogspace yesterday and am starting through the SQL piece now. We're hoping to mine Informix to provide the DN for each user to our AD records as well as some other applications. This information you provide is great, and I'm looking forward to working through all the resources and your subsequent posts related to this subject. Thanks!

    ReplyDelete
  2. Mark,

    Thanks for the feedback. I have used SQL to do what you are looking to do. When we work with customers to integrate UCM with AD, where there are existing users in both and they are not synchronized, we use a script to dump data from both, do a compare, and dump out a report.

    So, the data is there for you to mine. If users are programmed in UCM with telephone numbers already, you can hang out around the enduser table. If you need to splice data together from device and/or line associations to end user objects, then you will need to look at enduser, device, and a mapping table between the two.

    Good luck!

    -Bill

    ReplyDelete
  3. Thanks for all this posts, beautifully laid out and very informative. Helped me greatly with a project I am working on.
    Simon

    ReplyDelete
    Replies
    1. Simon,

      Thanks for the feedback and kind words. Greatly appreciated.

      -Bill (@ucguerrilla)

      Delete
    2. Have you tried linking MS SQl to the CUCM informix DB?

      I would like to be able to import the device/number plan tables into my CDR reporting database

      Delete
    3. Eric,

      In general, direct access to the Informix DB via something like ODBC is not possible. The AXL/SOAP interface is the API that Cisco provides for accessing configuration information. I suppose you could have a process on your MS SQL box or other box that could retrieve the recordsets from UCM via AXL and feed the data into your MS SQL db.

      BTW, I assume you already have the CDRs being funneled to your off box DB from UCM.

      HTH.

      -Bill (@ucguerrilla)

      Delete
  4. Hi William,

    Thanks for your queries. They are great!
    Only on this one there seems to be missing the 'dmap' table in your query. I assume this is the devicenumplanmap?

    ReplyDelete
    Replies
    1. Anon,

      Thanks for the feedback and the catch. Apparently I botched up a copy/paste. Query sample fixed. Thanks again.

      -Bill (@ucguerrilla)

      Delete
  5. Bill,

    I am trying to use the AXL/SOAP interface to get call detail information, which I've not been able to do successfully because I don't know how to refer to the CAR database in my xml input file...here's what's in it now:






    ]>




    ...can you tell me how I select the CAR database? In TSQL it would simply be "USE CAR" on its own line but no clue what it would be in this XML packet. Thanks in advance.

    Steven Heathcock

    ReplyDelete
  6. Steven,

    With UCM 5.x - 7.x you could reference the tables in CAR using a syntax like car:[table-name] in the "from" clause. This worked with AXL/SOAP and with CLI. In 8.x and later it doesn't work that way. You can still select data from the CLI but I haven't found a way to do it via AXL.

    -Bill (@ucguerrilla)

    ReplyDelete
  7. Hello Bill,
    this is a good blog here. I do my first steps with SQL and have a question. It is possible to get the info Logged in User ID and MAC Address from the extensionmobilitydynamic or somewehere else?
    We need the mac and login ID to set in the next step the OwnerUserID for the licensing in CUCM 9.1. We run at the moment 8.6.
    Thx for help
    joerg

    ReplyDelete
  8. Joerg,

    Yes, you can get login information from extensionmobility dynamic. You would need to do some joins to the device and enduser tables. You may need to join the device table twice if you are looking to display both physical device and UDP (device profile) information.

    Now, if you are simply looking for a list of users with device profile (UDP) associations then you don't need to bother with extensionmobilitydynamic. I recently posted an entry covering orphaned device profiles. http://www.ucguerrilla.com/2013/09/finding-orphaned-user-device-profiles.html

    You can use the same tables/fields in the above blog to get user / UDP associations. Of course, the logic of the query will be different because you are interested in listing users and devices. So, something like:

    select eu.userid, udp.name from device udp inner join enduserdevicemap edm on edm.fkdevice=udp.pkid inner join enduser eu on edm.fkenduser=eu.pkid where udp.tkclass=254 order by eu.userid

    HTH.

    -Bill (@ucguerrilla)

    ReplyDelete
  9. Any way to make this work with Extension Mobility users, they do not display in these queries.

    ReplyDelete
    Replies
    1. The huntgroup logout option is device-based and is not present on user device profiles (i.e. EM profiles). So, the reason your EM profiles wouldn't show up in the query is because the query is doing an inner join between device and devicehlogdynamic.

      To get the EM profiles to just show up, you can do an outer join between device and devicehlogdynamic.

      That is the short answer. I may look at this in a bit to see if there is a more optimal solution. I am sure there is but I have to circle back around.

      HTH.

      -Bill (@ucguerrilla)

      Delete
  10. Hi Bill, Just found this blog and i must say its a great read. Was wondering if you can help. Im in need for a couple of queries. 1. a query where i get a list of all hunt pilots and the Line groups they are using. 2. A Numberplan querey that shows all Translation patterns and the Called party transform mask they are pointing to. Please help!

    Jon

    ReplyDelete
    Replies
    1. Jon,

      Thanks for your questions. For your first question, I opted to add a new entry to the series as an answer. Thanks for the inspiration. You can access the answer here:
      http://www.ucguerrilla.com/2014/03/using-sql-to-report-on-hunt-pilots-and.html

      For the translation patterns, you need to query the numplan table. To see a list of translations with calling and called transformation masks, you can use the following query:

      select dnorpattern,callingpartytransformationmask,calledpartytransformationmask from numplan where tkpatternusage=3

      A tkpatternusage value of 3 means translation pattern (select enum,name from typepatternusage to verify).

      I am actually going to publish a more involved discussion of your translation pattern question early next week.

      Again, thanks for your question and for reading.

      HTH.

      -Bill

      Delete
  11. Hi,
    I am trying to query the cucm so that when a call is made from a certain phone it runs an external program. Specifically I want the security camera program to be opened when the phone next to the door goes offhook and rings its hotdial css. Is this possible?

    ReplyDelete
    Replies
    1. Hello. What you are looking to do is not possible using SQL queries or the AXL/SOAP API. The SQL queries are basically grabbing at static data. Not real time data. Even the RISport API (which gets real time information) wouldn't be an effective solution to your problem.

      You would need a solution that can hook into your call control process. So, CTI/JTAPI/TAPI interfaces. Depending on your environment, you could look at loading Jabber and leveraging the video capabilities of the desktop client with a video-enabled phone device.

      I have seen apps that hook into security video. Not the same work flow but maybe a company like IPCelerate has a solution that fits your need.

      In any case, using SQL to query UCM won't get you there.

      HTH

      -Bill (@ucguerrilla)

      Delete
  12. Hi Bill,

    I have found your blog very helpful. Thanks for putting this together.

    I have around 1000 phones that I need to enable the web access on so the HTTP server runs on the phone. Do you know if this can be done with SQL queries/updates? I haven't found a good way to do this with the BAT Tool.

    Thanks!

    ReplyDelete
    Replies
    1. Travis,

      You can do this using BAT but you have to limit the scope to devices of the same type. I believe you can also control web access using the Common Phone Profile configurations. Go to Device > Device Settings > Common Phone Profile.

      Via SQL, the challenge is that the web access parameter is embedded in a XML string. See this entry for a discussion on the XML field for a similar device parameter: http://www.ucguerrilla.com/2013/10/checking-peer-firmware-sharing-using-sql.html

      HTH.

      -Bill (@ucguerrilla)

      Delete
    2. I need to enable the "Settings Access" field for 1000 phones. Many have overridden the "Settings Access" at the phone level. In 8.5 the "Settings Access" field is not updatable through Bulk Phone Update. Any advice?

      Delete
    3. John,

      Are you sure 8.5 won't let you bulk update that field? You would have to query for a specific phone model (in BAT) in order to access these settings. For instance, use BAT Update Phone to query for Cisco 7965 phones (if you have those) and you should then have the ability to update the "Settings Access" field.

      HTH
      -Bill (@ucguerrilla)

      Delete
  13. Hi Bill, like the poster before, thanks for posting this information on your blog. I ran across it while searching for a way to find all DNs with CallFwd set. A reply you posted in a Cisco forum got me half way there but was hoping you could move me forward further towards my goal. I'm looking for DNs with their CallFwd property set, the time/date it was set and how long it has been set. Also is there a way to filter the DN's by first number of DN and exported into a csv file with neat and tidy columns?

    Current code I used from your post :
    run sql select d.name as device, n.dnorpattern, cfd.cfadestination from device as d inner join devicenumplanmap as dmap on dmap.fkdevice = d.pkid inner join numplan as n on n.pkid=dmap.fknumplan inner join callforwarddynamic as cfd on cfd.fknumplan=n.pkid where ((cfd.cfadestination != '') or (cfd.cfaVoicemailEnabled='t'))

    Device information is not needed for my report.

    Your wisdom will be appreciated!

    ReplyDelete
    Replies
    1. The callforwarddynamic table contains a timestamp value. From what I can tell, it is modified when the CFD table is modified. The value is in epoch time. It appears you can use that value for "last known config" and then do some subtraction from current epoch time to get the value of duration.

      When you say filter the DNs by first number, is your intention to only select DNs that start with a specific number or are you asking about sorting?

      To sort, you can use "order by n.dnorpattern" at the end of your query.

      To filter, you can use "n.dnorpattern like '3%' " or similar as part of your where clause. The example would catch all DNs or Patterns that start with the number 3. Note, this will also catch route patterns, translations, etc.. You can use the tkpatternusage field in the numplan table to limit your query to pattern type of DN. (tkpatternusage = 2)

      HTH.

      -Bill (@ucguerrilla)

      Delete
  14. 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 ??

    ReplyDelete
    Replies
    1. Sarah,

      Great question! I just published an entry that should answer your question.
      http://ucguerrilla.com/2015/04/using-sql-to-survey-phone-station-line.html

      Thanks for reading!

      HTH.

      -Bill (@ucguerrila)

      Delete
  15. We have a custom LDAP Directory running OpenLDAP used for DirSync to our CUCM 10.5 cluster. We also use a number of non-standard attributes to store data. After seeing your posts here, I dug in the Data Dictionary a little bit and came across the directorypluginattribute table, which appears to manage LDAP to CUCM attribute mapping. Have you ever tried updating these values? If so, have you ever needed to go back and tweak after an upgrade?

    To my surprise, I was actually able to update them with a 'run sql update' CLI command. Not only did the DirSync work as expected, but the LDAP Directory definition in CCMAdmin for that agreement also shows the value from this table!!!

    Thanks for the awesome knowledge transfer!!!

    Joe

    ReplyDelete
  16. Hi and thanks a lot for this very useful blog.
    I propose you a slightly modified version of your request in order to take into account the use of extension mobility with huntgroups :
    run sql select lg.name as LineGroup, n.dnorpattern, dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=d.pkid UNION ALL select lg.name as LineGroup, n.dnorpattern, dhd.hlog from linegroup as lg inner join linegroupnumplanmap as lgmap on lgmap.fklinegroup=lg.pkid inner join numplan as n on lgmap.fknumplan = n.pkid inner join devicenumplanmap as dmap on dmap.fknumplan=n.pkid inner join device as d on dmap.fkdevice=d.pkid inner join extensionmobilitydynamic as emd on emd.fkdevice_currentloginprofile =d.pkid inner join devicehlogdynamic as dhd on dhd.fkdevice=emd.fkdevice order by lg.name

    Bye

    ReplyDelete
  17. Hi, trying to figure out how to count unique trunks on a 8.6 cluster. I've tried:

    run sql select count(tm.name) as Total from Device as d inner join DevicePool as dp on(d.fkDevicePool = dp.pkid) inner join typemodel as tm on(tm.enum = d.tkmodel) where (tm.name <> 'Analog Phone' and tm.name <> 'Conference Bridge' and tm.name <> 'CTI Port' and tm.name <> 'MGCP Station' and tm.name <> 'Route List' and tm.name <> 'H.323 Gateway' and tm.name <> 'Music On Hold' and tm.name <> 'Media Termination Point' and tm.name <> 'Tone Announcement Player' and tm.name <> 'Cisco IOS Conference Bridge (HDV2)' and tm.name <> 'Cisco IOS Software Media Termination Point (HDV2)' and tm.name <> 'Cisco IOS Media Termination Point (HDV2)' and tm.name <> 'CTI Route Point' and tkdeviceprofile = '0' and tm.moniker NOT like 'MODEL_CISCO%')

    But it doesn't list trunks with same name and different route lists

    ReplyDelete
    Replies
    1. Jeremy,

      May need you to clarify the objective. What are you classifying as a trunk? I classify a trunk as one of the following:
      - MGCP T1 CAS or PRI
      - H323 (GK or no GK)
      - ICT (GK or no GK)
      - SIP

      To get a count of these devices, I would use the query:
      select count(pkid) as Total from device
      where tkmodel in (62, 121, 125, 131)

      That gives you a count of trunks provisioned on your system.

      HTH.

      -Bill (@ucguerrilla)

      Delete
  18. Bill,

    This blog entry helped me solve a problem where I needed to list DN's which are members of Line Groups - I need this as part of a data validation exercise - the customer's CUCM is all over the place as they have no management process for users who leave and new starters often just use a phone with an old staff members profile!!

    Anyway my query is below in case others find it useful.

    run sql SELECT linegroupnumplanmap.fklinegroup, LineGroup.name, numplan.dnorpattern FROM ((linegroupnumplanmap INNER JOIN LineGroup ON linegroupnumplanmap.fklinegroup = LineGroup.pkid) INNER JOIN numplan ON linegroupnumplanmap.fknumplan = numplan.pkid) order by LineGroup.name;

    Thanks again - love your blog!

    James

    ReplyDelete