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.

Flexible Jabber ID

by ⋅ 2 Comments

The default IM address scheme is to take the userid and append @defaultdomain to the end. This may work for smaller deployments, but caused a lot of confusion when multiple domains or subdomains were used. Flexible Jabber ID allows the IM address scheme to be mapped to Directory URI which is then mapped to either mail or msRTCSIP in LDAP.

jid

Changing the IM address scheme to Flexible JID is simple:

    • Stop the following services:
      Cisco Presence Engine
      Cisco SIP Proxy
      Cisco XCP Router
      Cisco Sync Agent
      Cisco Client Profile Agent
    • Presence > Settings > Advanced Configuration:
      Change IM Address Scheme to Directory URI and save.

imaddress

You can now browse to Presence > Domains to see a list of all domains in your presence environment.

You may find that some should not be there, as a lot of people like to add external contacts in their active directory with an ipPhone field populated. This will cause issues down the road if you deploy Mobile and Remote Access and want to do XMPP Federation. You’ll need to be able to sign for each domain. You’ll want to filter these out either by ldap filter into CUCM, or UC Service Profile. The Default Domain above also gets put into the CSR, so make sure to change that as well.

You can run the sql query below to clear the domains from the CSR once they are removed from the system:

run sql select * from impresencedomains

run sql delete from impresencedomains where pkid=’pkid_from_step_1′

To take advantage of Flexible Jabber ID, you must be running Jabber 10.6.6 or higher !

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