Shell script to update device user association in batch

Referring to my previous post:
http://pandaeatsbamboo.blogspot.hk/2014/01/associate-existing-phones-to-users-with.html

I have created a shell script that allow us to update the owner ID to phone in batch.

bulk-update.sh, change with your UCM ip address and credential in the curl statement.

#!/bin/sh

for i in `less user-phone-list.txt`

        do 

                #echo $i

                username=`echo $i | cut -d ',' -f1`

                devicename=`echo $i | cut -d ',' -f2`

                 

cat <<EOF1 >$username.xml

<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 sequence="?">

EOF1

                echo "<sql>update Device set fkenduser = ( select pkid from EndUser where userid = '$username')  where name = '$devicename'</sql>" >>$username.xml

cat <<EOF2 >>$username.xml

        </ns:executeSQLUpdate>

    </soapenv:Body>

</soapenv:Envelope>

EOF2

        curl -k -u administrator:ccievoice -H "Content-type: text/xml;" -H "SOAPAction: CUCM:DB ver=8.5" -d @$username.xml https://10.1.90.11:8443/axl/

        

        done

user-phone-list.txt, file contains username and device name pair with comma as delimiter, put them in the same directory as the script.

dannywon,SEPAABBAABBAABB

briacho,SEP00077DDFFB7E

Associate Existing Phones to Users with SQL statement

I have come across a case which is a licensing issue for the new 9.1.2.  The customer is using desk phone for extension mobility, while they have users using CSF device as the main phone.  All those desk phone are not associated with user because it doesn't make sense to do it, however all the EM desk phone and CSF devices will count as Enhanced UCL instead of CUWL, as there are no multiple device associate to single users and thus double counting the license, as those Enhanced UCL are "borrowed" from CUWL standard.

As an interim solution we need to update the phones to associate it with an "owner", although BAT can do the job, it imposes risk in overriding phone settings.

http://www.cisco.com/en/US/products/sw/voicesw/ps556/products_tech_note09186a0080affd4d.shtml

An alternative is using cURL with AXL.  So the first thing I need to do is to dig out what SQL statement I need to issue before writing the batch job.

Firstly I need to find out the PKID of a particular user.

admin:run sql select pkid from EndUser where userid = 'dannywon'

pkid                                 

==================================== 

61b76ad9-7a1b-405c-bb70-b9850397635d 

Then I can check out how many and what devices is associating to that particular user.

admin:run sql select name from Device where fkenduser = '61b76ad9-7a1b-405c-bb70-b9850397635d'

name            

=============== 

CSFDANNYWON     

TABDANNYWON     

TCTDANNYWON     

ECPDANNYWON     

SEP503DE57D87F8 

EMDANNY1        

EMDANNY2        

SEPF84F5794013A 

I also want to know all the unassociated devices.

admin:run sql select name from Device where fkenduser is NULL

name                                               

================================================== 

MTP_2                                              

CFB_2                                              

ANN_2                                              

MOH_2                                              

MTP_3                                              

CFB_3                                              

ANN_3                                              

MOH_3                                              

SEPAABBAABBAABB                                    

VCB0003D6012D4C 

OK, next thing I want to do is to update the Device table, so that the device SEPAABBAABBAABB is associated to the desired user dannywon.

admin:run sql update Device set fkenduser = '61b76ad9-7a1b-405c-bb70-b9850397635d' where name = 'SEPAABBAABBAABB'

Before the UPDATE statement it shows Anonymous under the device settings:

After the UPDATE statement it shows that this device is associated to an user:

But it will not shown in the End User page as "Controlled Device", we are just changing the Owner ID of that particular device to trick the ELM.  With all these SQL statement you should be able to develop your XML file and post the SOAP request via cURL.

Note:  An optimized SQL statement with sub-query:

admin:run sql update Device set fkenduser = ( select pkid from EndUser where userid = 'dannywon' ) where name = 'SEPAABBAABBAABB'

My next post is going to create a shell script to do it in batch:
http://pandaeatsbamboo.blogspot.hk/2014/01/shell-script-to-update-device-user.html