Wednesday, October 2, 2013

Checking Peer Firmware Sharing using SQL

For this installment of the SQL Query Series I am going to keep it short and sweet. I was recently doing implementation planning for a project where we need to update the firmware on a few thousand phones. One of the things we like to do is leverage Peer Firmware Sharing to shorten the time needed to push out firmware upgrades. 

One of the pre-requisites to leverage Peer Firmware Sharing is to actually verify it is enabled. This is the perfect job for SQL.
Background

If you aren't sure what Peer Firmware Sharing is then I recommend taking a look at a write up I did on the NetCraftsmen site covering options for distributing phone firmware. It is the most optimal way to manage firmware upgrades when you have a need to update firmware for a large number of phones distributed across many WAN sites.

Whenever I work with customers on UCM upgrades, I always upgrade the phone firmware on the existing cluster as preparatory task. I find that many customers are cautious when it comes to firmware upgrades. So, we have developed a process to keep the risk and impact relatively low. The Peer Firmware Sharing feature is a fundamental tool in that process.

By default this device-level feature is disabled. I will validate this parameter as part of my discovery process for upgrade projects. The interesting thing about this parameter is that it is one of the product (or model) specific parameters on the device page. This makes getting at the data from BAT (or other native interface) a challenge.

It isn't a huge challenge because you can leverage SQL to get a quick report of phones that have this parameter enabled or disabled.

[Edit: (10/4) Added version-specific information. Thanks to reader G.R. for pointing out the oversight.]

The Tables

The table(s) we need to dissect will depend on which version of CUCM you are running. In all instances, we are dealing with one of the more "funky" fields in the database. The CUCM database has a few fields strewn about that have more complex data structures than you see in most fields. Today, we get to play with one of them. Lucky us!

Pre-8x CUCM Versions

For versions prior to CUCM 8.x, we only have to deal with one table: device. Further, we only need to look at one field in the device table to answer our burning question. 

The field we are interested in is the xml field. When you edit a device in CUCM, you will see a bunch of product specific parameters at the bottom of the page. The parameters rendered on a specific device configuration page is based on the hardware model you are attempting to configure. From a database perspective, these parameters are stored as an XML data structure in the device table.

CUCM 8x and 9x

Starting with CUCM 8.0, Cisco did some house cleaning and decided to remove the xml field from the device table. Not to worry. The field wasn't completely tossed away. Instead, it was relocated to another table. Actually three tables.

Presumably, the DB developers were trying to optimize IDS replication (or fix a IDS repl issue) and took an already funky data structure and slapped it into three different tables:

  • devicexml4k
  • devicexml8k
  • devicexml16k 

The field structure of all three tables is the same: pkid, fkdevice, xml. The only difference is with the xml field data type. In all three tables, the xml field is of type string. The difference is the max allocated size for that field: 4000, 8000, 16000. 


The Queries

The XML data is essentially a string and, as a string, it can be parsed using the various wild cards in a where clause. The XML child node that we are interested in is 'peerFirmwareSharing'.

Let's start with a simple count query. The following two queries will give us a count of device that have peer firmware sharing enabled or disabled.

/*Query for count of devices with peer firmware sharing is enabled*/
select count(pkid) 
from device 
where xml like '%peerFirmwareSharing>1%'

/*Query for count of devices with peer firmware sharing is disabled*/
select count(pkid) 
from device 
where xml like '%peerFirmwareSharing>0%'

/*Sample query for CUCM 8x/9x*/
select count(d.pkid)
from device d
  inner join devicexml4k dxml on dxml.fkdevice=d.pkid
where dxml.xml like '%peerFirmwareSharing>0%'


You may find you want to dig deeper. It really depends on the query results and if/how they line up with your expectations. For instance, you may want to see a distribution of devices with peer firmware sharing disabled sorted by device type. In this case, the following query would do the job:


/*Pre-8x example*/
select tm.name,count(d.pkid) 
from device d 
   inner join typemodel tm on d.tkmodel=tm.enum 
where d.xml like '%peerFirmwareSharing>0%' 
group by tm.name

/*Sample query for CUCM 8x/9x*/
select tm.name,count(d.pkid)
from device d
  inner join devicexml4k dxml on dxml.fkdevice=d.pkid
  inner join typemodel tm on d.tkmodel=tm.enum
where dxml.xml like '%peerFirmwareSharing>0%'
group by tm.name

Now, let's suppose you have a situation where most of the device have peer firmware sharing enabled but there were a few stragglers. In this case, you want a list of devices. The following query is an example of how you can get a list of devices (tweaking fields to suit your needs is fine):


select d.name,d.description,tm.name as phoneModel 
from device d 
  inner join typemodel tm on d.tkmodel=tm.enum 
where d.xml like '%peerFirmwareSharing>0%' and d.name like 'SEP%'
order by tm.name, d.name

I am adding the "d.name like 'SEP%'" to the where clause to avoid dumping device templates. There are other query variations that you may want to tinker with. I think that the above queries express the general ideas. 


Considerations

With CUCM 8x and 9x there is a twist. The XML values stored in devicexml4k won't necessarily have a child node for peerFirmwareSharing. At least, that is the case on my 9.1(2) cluster. This is because of the Common Phone Profile configurations. Default values in the phone profile are not duplicated in the XML field of the devicexml4k table. This makes sense. Unnecessary duplication of data is always a poor design choice.

What to do? Well, if you only have one common phone profile then you can run the queries above and then also inspect the configuration on the phone profile. Just understand that the device level configuration overrides the common phone profile

OK. Time to get back to my 9.1 upgrade.



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

9 comments:

  1. Once you have run the query and figured out which phones need Peer Firmware Sharing enabled, how would you go about doing the update? Using BAT? I have 3500 phones that I need to update and would like to use the Peer Firmware Sharing.

    ReplyDelete
  2. Tim,

    The answer to your question depends, in part, on the version of UCM. If you have a UCM 9.x cluster then you can use the Common Phone Profile settings. Out of the box, UCM 9x (at least 9.1.2) enables peer firmware sharing.

    UCM 8.6 may exhibit the same behavior as 9x. My 8.6 lab system is offline at the moment.

    If you run the queries in this blog and you identify phones where peer firmware sharing is disabled then modifying the common phone profile won't get you where you need to be. UCM prefers the device level settings over the common phone profile settings.

    In cases where it is determine that peer firmware sharing needs to be enabled on devices, I recommend using BAT. This approach may require multiple BAT transactions because you would need to group devices together by device type.

    Using SQL update won't work for the devicexml4k table due to permissions constraints.

    HTH

    -Bill






    ReplyDelete
  3. Is there a query to list all devices along with their current firmware versions? Thank you!

    ReplyDelete
    Replies
    1. Alik,

      If by "current" you mean what is actually running then that information is not stored in the database. The configuration database does store info on the configured device firmware. The device default settings are available in the "defaults" table (field: loadinformation). If a phone is configured with a specific load then that is stored in the "device" table (field: specialloadinformation).

      You can query with a criteria of device.specialloadinformation != defaults.loadinformation and find phones that are configured to use something other than the default.

      Of course, this only covers how the devices are configured. Not what they are actually doing. In recent versions of UCM, you can see the Active Load information on the phone configuration pages. I believe this shows what the phone is actually running (though, I haven't probed this too heavily). Not all phones support this: 3rd party devices and older phones (7940, 7960, 7985) don't report active versions.

      AFAIK, the active load is not stored in the database. Likely, it is tracked by RIS (as is the registration status and phone IP address). Which means, it may be exposed via the RISport API (I haven't looked yet).

      You may also want to check Cisco Unified Reporting. There is a report called "Unified CM Phones with Mismatched Load". It looks like it will compare "active load" to "configured load".

      HTH.

      -Bill (@ucguerrilla)

      Delete
  4. Hi Bill,

    when running the following on my 8.6.2 cluster:

    run sql select tm.name,count(d.pkid) from device d inner join devicexml4k dxml on dxml.fkdevice=d.pkid inner join typemodel tm on d.tkmodel=tm.enum where dxml.xml like '%peerFirmwareSharing>0%'

    I get the following error:

    The column (name) must be in the GROUP BY list.

    I know nothing about SQL, so any help would be appreciated.

    Cheers,
    Johan

    ReplyDelete
    Replies
    1. Johan,

      That is correct. Whenever using "count" with multiple columns, you must include the "group by". I see the mistake in my example and will correct it. Thanks!

      -Bill

      Delete
  5. Hi I run the command on CUCM9.1 and get the result like this
    admin:run sql select d.name,d.description,tm.name as phoneModel from device d inner join typemodel tm on d.tkmodel=tm.enum where d.xml like '%peerFirmwareSharing>0%' and d.name like 'SEP%' order by tm.name, d.name
    Column (xml) not found in any table in the query (or SLV is undefined).

    ReplyDelete
  6. The query failed in CUCM 9.1

    admin:run sql select d.name,d.description,tm.name as phoneModel from device d inner join typemodel tm on d.tkmodel=tm.enum where d.xml like '%peerFirmwareSharing>0%' and d.name like 'SEP%' order by tm.name, d.name
    Column (xml) not found in any table in the query (or SLV is undefined).

    ReplyDelete
    Replies
    1. Please review the blog entry. UCM 9.1 schema does not store the XML string in the device table. See above for details on how to correct your query.

      HTH

      -Bill (@ucguerrilla)

      Delete