Useful CUCM SQL Queries

Just dumping some Cisco UCM SQL queries that might help you do stuff faster.

List all numbers with the call forward destinations that they have. Helps to check, for example, if someone has non-CFA forwarding to a pilot number you want to delete

run sql select limit 10 dnorpattern, cfhrdn, cfhrintdn, cfbdestination, cfbintdestination, cfnadestination, cfnaintdestination, cfurdestination, cfurintdestination, devicefailuredn, pffdestination, pffintdestination from numplan

List all numbers with active call forward all destination to 911200. Helps to check, for example, if someone has forwarded their number to a pilot number you want to delete

run sql select cfddyn.cfadestination, n.dnorpattern, pt.name from callforwarddynamic as cfddyn join numplan as n on cfddyn.fknumplan=n.pkid join routepartition as pt on n.fkroutepartition=pt.pkid where cfddyn.cfadestination='911200'

Change call forward Busy External for all numbers that have 911200 to 2000

run sql update numplan set cfbdestination='2000' where cfbdestination='911200'

List all calling/ called party transformation masks and prefix digits — helps to find if something is being translated to a number you want to delete.

run sql select limit 10 dnorpattern, calledpartytransformationmask, callingpartytransformationmask, callingpartyprefixdigits, prefixdigitsout from numplan

Set owner based on Extension Mobility logged in user

run sql update device set fkenduser=(select fkenduser from extensionmobilitydynamic where fkdevice=device.pkid) where name like 'SEP%' and fkenduser IS NULL

UDPs without users — i.e. safe to delete

run sql select d.name from device d where (0=(select count(edm.pkid) from enduserdevicemap edm where edm.fkdevice=d.pkid)) and d.tkclass=254 and name not like 'ModelProfile%' order by d.name

Jabbers without users — might be OK to delete

run sql select name from device where fkenduser is NULL and tkmodel=503 and name not like 'ModelProfile%' order by name

Packet loss from CAR for the last 5 minutes

run sql car select count(*) as total, sum(CASE WHEN destnumberpacketslost between 0 and 50 then 1 ELSE 0 END ) as cat1, sum(CASE WHEN destnumberpacketslost between 50 and 200 then 1 ELSE 0 END ) as cat2, sum(CASE WHEN destnumberpacketslost between 200 and 500 then 1 ELSE 0 END ) as cat3, sum(CASE WHEN destnumberpacketslost > 500 then 1 ELSE 0 END ) as cat4 from tbl_billing_data where destdevicename like 'SEP%' and datetimestamporigination>(CURRENT-5 UNITS MINUTE)

Set video and immersive bandwidth to unlimited for all locations to Hub_None

get the names of all locations
run sql select * from location
get the locations matrix
run sql select * from locationmatrix
Set video and immersive bandwidth to unlimited for all locations to/from Hub_None - ID below is of hub_none
run sql update locationmatrix set videokbits=0, immersivekbits=0 where fklocation_a like '%29c5c1c4-8871-4d1e-8394-0b9181e8c54d%'
run sql update locationmatrix set videokbits=0, immersivekbits=0 where fklocation_b like '%29c5c1c4-8871-4d1e-8394-0b9181e8c54d%'

Set relations to a certain region from all other regions to System Default — repeat for each region you want to start using System Default

get the names of all regions
run sql select * from region
get the region matrix
run sql select * from regionmatrix
Set relations to a certain regions to/from all other regions to System Default - repeat for each region you want to start using System Default
run sql delete from regionmatrix where fkregion_a like '%43b635d2-3ed4-3ab3-42b9-5069c9ce4281%'
run sql delete from regionmatrix where fkregion_b like '%43b635d2-3ed4-3ab3-42b9-5069c9ce4281%'

Get all unassigned DNs with the descriptions and all CF settings

Thanks to Herr Bernhard Albler for this one.

SELECT dnorpattern             AS NUMBER, 
       description             AS DESCRIPTION, 
       cfd.cfavoicemailenabled AS CALL_FORWARD_ALL_VOICEMAIL_ENABLED, 
       cfd.cfadestination      AS CALL_FORWARD_ALL_DESTINATION, 
       cfbvoicemailenabled     AS BUSY_VOICEMAIL_ENABLED, 
       cfbdestination          AS BUSY_EXTERNAL_DESTINTATION, 
       cfbintvoicemailenabled  AS BUSY_INTERNAL_VOICEMAIL_ENABLED, 
       cfbintdestination       AS BUSY_INTERNAL_DESTINATION, 
       cfnavoicemailenabled    AS NOANSWER_VOICEMAIL_ENABLED, 
       cfnadestination         AS NO_ANSWERD_ESTINATION, 
       cfnaintvoicemailenabled AS NO_ANSWER_INTERNAL_VOICEMAIL_ENABLED, 
       cfnaintdestination      AS NO_ANSWER_INTERNAL_DESTINATION,
       rpt.name                AS PARTITION 
FROM   numplan np 
       INNER JOIN typepatternusage tpu 
               ON np.tkpatternusage = tpu.enum 
       LEFT OUTER JOIN callforwarddynamic AS cfd 
                    ON cfd.fknumplan = np.pkid 
       LEFT OUTER JOIN devicenumplanmap dnmp 
                    ON dnmp.fknumplan = np.pkid
       LEFT OUTER JOIN routepartition rpt
                    ON rpt.pkid = np.fkroutepartition 
WHERE  tpu.NAME = 'Device' 
       AND dnmp.pkid IS NULL 
ORDER  BY dnorpattern ASC