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 locationget the locations matrix
run sql select * from locationmatrixSet 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 regionget the region matrix
run sql select * from regionmatrixSet 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