- Advanced CUCM SQL queries for bulk update and delete operations
- Summary
- Prerequisite
- SQL Queries
- Update CSS
- Update Device Pool
- Update Partition for DN and URI
- Regex in SQL Queries
- Update description in device DN’s
- Update Line Text Label in DN’s
- Update alternate number mask
- Updating speeddial numbers
- Updating call forward destinations
- Delete Query
- Delete all DN’s
Advanced CUCM SQL queries for bulk update and delete operations
Summary
This document lists the CUCM SQL queries for updating various configuration elements like CSS, Device Pool, DN for users, devices and Route Points. Please use these queries with caution as these changes are irreversible. Kindly verify this in the test environment before applying in prod.
Prerequisite
These sql queries only updates the configuration from a to b, it will not create the configuration b in the CUCM. Kindly confirm the new configuration element exists in CUCM before applying these queries.
SQL Queries
Update CSS
Update callingsearchspace from css_a to css_b on only CTI Route Points:
run sql update device set fkcallingsearchspace=(select pkid from callingsearchspace where name='new_css') where fkcallingsearchspace=(select pkid from callingsearchspace where name='old_css') and tkmodel=73 Example: run sql update device set fkcallingsearchspace=(select pkid from callingsearchspace where name='css_b') where fkcallingsearchspace=(select pkid from callingsearchspace where name='css_a') and tkmodel=73
tkmodel=73 restricts this change to only happen on the model 73, which is CTI Route Points. To get the model number of different device types use run sql select enum,name from typemodel, where enum is the model number and name is the device type.
Update Device Pool
Update devicepool from dp_a to dp_b for only CSF devices
run sql update device set
fkdevicepool=(select pkid from devicepool where name='new_dp')
where
fkdevicepool=(select pkid from devicepool where name='old_dp')
and tkclass=1 and tkmodel=503
Example:
run sql update device set
fkdevicepool=(select pkid from devicepool where name='dp_b')
where
fkdevicepool=(select pkid from devicepool where name='dp_a')
and tkclass=1 and tkmodel=503tkclass=1 indicates the class type as phone and tkmodel=503 restricts the change to happen only on CSF devices. Use run sql select enum, name from typeclass to list all class types and the associated enum.
Update Partition for DN and URI
To update partition for directory uri associated with the directory number, use the table numplanuri. whereas to update the partition for directory number, numplantable is being used.
/*To update partition for Directory URI*/
run sql update numplanuri set
fkroutepartition=(select pkid from routepartition where name='new_PT')
where
fkroutepartition=(select pkid from routepartition where name='old_PT')
/*To update partition for Directory Numbers*/
run sql update numplan set
fkroutepartition=(select pkid from routepartition where name='new_PT')
where
fkroutepartition=(select pkid from routepartition where name='old_PT')
and tkpatternusage=2tkpatternusage defines the type of the associated element to the pattern such as call park, conference, device, etc. tkpatternusage=2 means all the patterns associated to type device. Hence this sql query updates the partition for only the directory numbers associated to the devices such as desk phones, CSF, TCT, TAB and other end user devices. Use run sql select enum,name from typepatternusage to get the complete list of pattern usage enum and associated types.
Regex in SQL Queries
CUCM Sql queries also supports regex. Here are few examples using regex operations.
run sql update numplan set
dnorpattern=replace(dnorpattern,110,1138) where dnorpattern like '11012%'This sql query replaces 110 with 1138 in all directory numbers that starts with 11012%. For example: It updates the DN 11012345 to be 113812345.
run sql update numplan set
dnorpattern=replace(dnorpattern,'21*','2111*') where dnorpattern like '21*4%'In this example, we are setting the DN as string by enclosing it in quotes, since DN contains a non numeric character *. This sql replaces 21* with 2111* for all DN that starts with 21*4. For example: 21*4987 becomes 2111*4987.
Update description in device DN’s
In this sql query, regex_replace is used to strip the numbers from the description field.
run sql update numplan set
description=replace(description,description,regex_replace(description, ' - [0-9]+$', ''))
where tkpatternusage=2 and dnorpattern like '9118%'This sql query updates the description from John Doe — 8198 to John Doe. Note that in this query, update applies only to the patterns starts with 9118.
Update Line Text Label in DN’s
Line Text Label in the directory number is bit trickier to change than changing description. The label is associated with each device,DN pair. For example: line text label for DN 1001 on CSF is John Doe — 8198, but the same DN 1001 on TCT could have description as Johnathon Doe. So, this change needs to happen in the devicenumplanmap table, where we can get all the DN’s and the associated devices.
run sql update devicenumplanmap set
label=replace(label,label,regex_replace(label, ' - [0-9]+$', ''))
where
fknumplan in (select pkid from numplan where dnorpattern like '9118____')This query removes numbers on line text label for all the devices associated with the number that starts with 9118 and contains exactly 4 digits after that.
Update alternate number mask
run sql update alternatenumber set
dnormask=replace(dnormask,'12XXXX','1411XXXX')
where
dnormask like '12XXXX' and
fknumplan in (select pkid from numplan where dnorpattern like '\\\+44%')This query updates alternate number mask from 12XXXX to 1411XXXX for mask that starts with 12 and contains 4 characters after that. It also further restricts this query to apply only for DN that starts with \+44.
Updating speeddial numbers
Updates speed dial number using concat operation.
run sql update speeddial set
speeddialnumber=concat('9110',speeddialnumber)
where speeddialnumber like '11__'This query updates speeddialnumber 11__ with 911011XX. For example:1134 becomes 91101134.
Updating call forward destinations
Updates call forward destinations in the directory number.
/* update call forward all destination */
run sql update callforwarddynamic set
cfadestination=replace(cfadestination,'old','new')
where cfadestination like 'old'
/* update call forward busy external destination */
run sql update numplan set
cfbdestination=replace(cfbdestination,'old','new')
where cfbdestination like 'old'
/* update call forward busy internal destination */
run sql update numplan set
cfbintdestination=replace(cfbintdestination,'old','new')
where cfbintdestination like 'old'
/* update call forward no answer external destination */
run sql update numplan set
cfnadestination=replace(cfnadestination,'old','new')
where cfnadestination like 'old'
/* update call forward no answer internal destination */
run sql update numplan set
cfnaintdestination=replace(cfnaintdestination,'old','new')
where cfnaintdestination like 'old'
/* update call forward no coverage external destination */
run sql update numplan set
pffdestination=replace(pffdestination,'old','new')
where pffdestination like 'old'
/* update call forward no coverage internal destination */
run sql update numplan set
pffintdestination=replace(pffintdestination,'old','new')
where pffintdestination like 'old'
/* update call forward on CTI failure destination */
run sql update numplan set
devicefailuredn=replace(devicefailuredn,'old','new')
where devicefailuredn like 'old'
/* update call forward on unregistered external destination */
run sql update numplan set
cfurdestination=replace(cfurdestination,'old','new')
where cfurdestination like 'old'
/* update call forward on unregistered internal destination */
run sql update numplan set
cfurintdestination=replace(cfurintdestination,'old','new')
where cfurintdestination like 'old'Delete Query
To remove the alternate number associated with DN.
run sql delete from alternatenumber
where
pkid in (select pkid from alternatenumber
where
fknumplan in (select pkid from numplan where dnorpattern like '9118____'))This query deletes alternate number associated with DN that starts with 9118 and contains exactly 4 digits after that.
run sql delete from numplan
where
pkid in (select pkid from numplan where dnorpattern like '44XX'
and tkpatternusage=3 and
fkroutepartition=(select pkid from routepartition where name='Internal_PT'))This query deletes translation pattern (tkpatternusage=3) 44XX which is in the partition Internal_PT .
Delete all DN’s
/* Deletes all at once */
run sql delete from numplan where pkid in (select pkid from numplan)
/* Deletes in batches */
run sql delete from numplan
where
pkid in (select pkid from (select first 3000 pkid from numplan))Hope this was useful, please let me know if you would like to know about any specific operation.
