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

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 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

I do all stuff Webex and Cisco Collab at Frink Advanced Services GmbH

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store