Tuesday, January 7, 2014

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


13 comments:

Unknown said...

Hi Danny,

Thanks, that can come in handy.

Just to let you know there is an even easier way if you are using extension mobility. If so you can use the following SQL statement from CLI to set the owner ID to correspond to the user ID that is logged in at the phone.

run sql update device set fkenduser=(select fkenduser from extensionmobilitydynamic where fkdevice=device.pkid) where name like 'SEP%' and fkenduser IS NULL

Kind Regards,
Kevin

Unknown said...

Hi Danny,

You can ignore my last reaction, I just scrolled down your blog and obviously I missed the previous post you made about using the SQL statement with extension mobility. My bad!!

Kevin

Danny Wong said...

Thanks Kevin, useful one.

Unknown said...

Just ran a test and this looks like it works great. Will be running it to update ~3500 users tonight. Thanks, you saved me a ton of time!!!

Danny Wong said...

Good luck man

Tim T said...

Tim Bassett, how did you go with running this on all the phones? We're in a similar situation but have 7000 phones to update. I gave this a test on our test/dev CUCM with only a few phones and it worked great. I wanted to know if you ran into any issues when scaled up to the thousands.

Danny Wong said...

I only tested it in lab and my friend did run it in his production environment with a couple thousands of phone and it works OK. Good luck

Tim T said...

I'm very fresh to AXL but I saw over at the Cisco Dev community someone was trying to accomplish the same thing:

https://communities.cisco.com/docs/DOC-49071

It looks like he did it through a more complicated method and ran into some issues. Are you able to comment on this (I apologise for my ignorance).

Danny Wong said...

Instead of using the UpdatePhone object, my way is to use SQL statement directly. I can't compare them as I haven't tried his way before, but for me I am more comfortable to use SQL statement directly as I know better what the end result should be, comparing with using a AXL method provided which abstract what it will do. My 2 cents.

Anonymous said...

I was unable to get the script working. I am new to shell scripts. I received an alert the a few issues occurred.

\r was missing
EOF was missing

Please help

Jon said...

I use a very similar approach. Unless you want to keep the xml files for reference you can try running the XML as a variable. I know its saved me a lot of trouble with tmp files.

XML="





update Device set fkenduser = ( select pkid from EndUser where userid = \"$username\") where name = \"$devicename\"




"

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


For Device Association you can also run the other way to associate Devices with Enduser for Jabber

Unknown said...

So would there be a way to associate the device with the user and line to user this way also?

Thanks,

Unknown said...

So would there be a way to associate the device with the user and line to user this way also?

Thanks,