sql: unity message counts

by ⋅ 2 Comments

Cisco has a good writeup on CUC SQL queries, which are very useful to determine which voicemail accounts are active and which are just taking up a license, and perhaps could be skipped over during migrations. To get everything you need, use the query below:

lists all message counts, durations, size, and dates per alias:

run cuc dbquery unitymbxdb1 select alias as UserID, count (*) as TotalMessages, sum(case when deleted=’0′ then 1 else 0 end) as Inbox, sum(case when deleted=’1′ then 1 else 0 end) as Deleted,  min (arrivaltime) as OldestMessageTime, vw_mailbox.bytesize, sum(duration/1000) as TotalDuration_In_sec from vw_message,vw_mailbox, unitydirdb:vw_mailbox, unitydirdb:vw_user where vw_message.mailboxobjectid=vw_mailbox.mailboxobjectid and vw_mailbox.mailboxobjectid in (select mailboxid from vw_mailbox where unitydirdb:vw_user.objectid = unitydirdb:vw_mailbox.userobjectid) group by alias, vw_mailbox.bytesize order by TotalMessages desc

sql: jabber last login time

by ⋅ 3 Comments

For a list of all jabber users and their last login time, you can run the following query:


run sql select e.userid, cd.timelastaccessed from enduser as e, credentialdynamic as cd, credential as cr where e.pkid=cr.fkenduser and e.tkuserprofile=1 and e.primarynodeid is not null and cr.tkcredential=3 and cr.pkid=cd.fkcredential order by cd.timelastaccessed

This date is in something called “epoch” format. To convert to something that makes sense, you’ll need to use the following formula:

=(((B2/60)/60)/24)+DATE(1970;1;1)+(-5/24)

This is for Eastern Standard Time where -5 = UTC -5, you can change this accordingly.

sql: update call forwarding rules on directory numbers

by ⋅ Leave a Comment

I’m not sure why Cisco didn’t include “custom file” option to update lines like you can phones, but if you have the list of numbers to change call forwarding rules, you can use the following queries which are a part of the numplan table. Call Forward All rules are in another table callforwarddynamic which i’ll cover later:

cfbvoicemailenabled = call forward busy external set to voicemail (boolean)
cfbdestination = call forward busy external destination
cfbintvoicemailenabled = call forward busy internal set to voicemail (boolean)
cfbintdestination = call forward busy internal destination
cfnavoicemailenabled = call forward no answer external set to voicemail (boolean)
cfnadestination = call forward no answer external destination
cfnaintvoicemailenabled = call forward no answer internal set to voicemail (boolean)
cfnaintdestination = call forward no answer internal destination

An example query below:

run sql update numplan set cfbvoicemailenabled = (‘f’), cfbintvoicemailenabled = (‘f’), cfnavoicemailenabled = (‘f’), cfnaintvoicemailenabled = (‘f’) where dnorpattern = “1111111”

sql: update line text label

by ⋅ 2 Comments

Making bulk changes in CUCM can be done in 3 ways:

  1. bulk operations built into cucm – great but very limited
  2. import/export of tar file – all details – concise but requires change freeze
  3. sql operations – sometimes the best option

The following example I needed to update each line text label to add “Agent” to the beginning. Built in operations would only allow me to change the label all together, not append text. Import / Export would work fine, but for 10,000 phones this would take a full day to process. In this case SQL was my best option. The query below matches the line and updates the label. I used excel and the concatenate function to pump out all of the required operations.

run sql update devicenumplanmap set (label) = (‘Agent – 1112001’) where fknumplan = (select pkid from numplan where dnorpattern = “1112001”)

SQL queries in CUCM

by ⋅ Leave a Comment

Sometimes working with a large amount of phones (10,000+) it can take a whole day to export into a csv, and makes more sense to view and manipulate data in the live database. Caution should be taken when handling the live database, so if you are not a programmer, you may want to stick to the canned scripts below as an overly broad query could lock up your server!

I was recently tasked with running firmware updates on a large amount of phones, various models for over 100 device pools. Anything that can help organize and divide workload and cut down on unnecessary bulk jobs is a huge help!

UC Guerilla has a good blog on this here
Cisco’s documentation you can find here, but a tough read for a non-programmer

Count the number of devices per type, total:

run sql select count(Device.name) Device_count, typemodel.name Device_Type from Device inner join typemodel on device.tkmodel=typemodel.enum group by typemodel.name

Count the number of phones in each device pool, sorted by device pool name:

run sql select count(d.name), dp.name as DevicePool, tc.name as DeviceType from Device as d inner join DevicePool as dp on d.fkDevicePool=dp.pkid inner join typeClass as tc on tc.enum=d.tkClass group by dp.name, tc.name order by dp.name

Count the number of each type of phone per device pool, sorted by device pool name:

run sql select count(Device.name) Device_count, DevicePool.name Device_Pool, typemodel.name Device_Type from Device inner join DevicePool on Device.fkDevicePool=DevicePool.pkid inner join typemodel on device.tkmodel=typemodel.enum group by DevicePool.name,typemodel.name order by DevicePool.name