Fun Problem. I took over an install that included Emergency Responder and when we started testing, the email alert was missing the Display Name of the calling user. The customer of course thought that would be a good idea to have because, well it is. CER pulls that field from Display Ascii on the line. Not Display. The bulk loader that was used for user imports does not bother with silly things like making sure ascii displays work and was missing that field. Updating that particular field is not as easy as you think. Display is functions on a line on a device. In order to correct this through BAT you would need to export all phones, update the ascii display and then import and overwrite, which is a bit more than anybody likes doing. Especlly when you have side cars in the network and Excel tends to not like that man rows.
To do this through SQL is also a bit strange because the display is stored in a mapping table that relates the device to the line because display can be different per phone and not per line. Now we could have written an awesome script that used the axl interface to pull phones and then get/store and update the device back, but for the sake of time I did it a bit different.
First we need to create a big update file with the SQL statements and then use a curl script to push the updates to CM. Pasting a bunch of SQL statements in CLI in bulk tends to fail pretty bad. So first we get the info we need from CLI with the statement – run sql select pkid,display from devicenumplanmap. This will give us the unique line on the device line map so we can update. Because we do have the Display info we can just duplicate that over. If we did not have that or needed to update display too, we could join in the extension and grab alerting name or description and adjust as needed. So one we have a full list of stuff that needs changed – devicenumplanmap.pkid and display name we just use the dirty ol find replace in notepad++ or sublime to get what we need – or sed. Something like – sed ‘s/^\([^ ]*\) *\(.*\)$/update devicenumplamap set displayascii=\’\’ where pkid=\’\’/g’ displayMapFile.txt – Then you might want to strip duplicate spaces at the end of the display.
Once we have that file we want a dirty little curl/bash script to run them:
#! /bin/bash
AXL="
<?xml version=\"1.0\" encoding=\"UTF-8\"?>
<soapenv:Envelope xmlns:soapenv=\"http://schemas.xmlsoap.org/soap/envelope/\" xmlns:ns=\"http://www.cisco.com/AXL/API/8.5\">
<soapenv:Header/>
<soapenv:Body>
<ns:executeSQLUpdate>
<sql>${3}</sql>
</ns:executeSQLUpdate>
</soapenv:Body>
</soapenv:Envelope>
"
echo > /tmp/axl.xml
#echo
curl -k -u ${1} -H "Content-type: text/xml;" -H "SOAPAction: CUCM:DB ver=8.5" -d @/tmp/axl.xml https://${2}:8443/axl/
Our curl script take three options – credentials in the form of ‘<username>:<password>’ the CM IP address for the API interface just as the IP and then the update command quoted. Then all we need is a command that loops through the update script file using the curl script to send to CM and update.:
cat displayasciiupdate.txt | while read line ; do axlsql '<username>:<password>' <CM_IP> "" ; done