How to associate an AppUser with thousands of devices

[chazhang]

Hi All,

I am using AXL addAppUser to add an AppUser for UCCE systems. This AppUser has over 5500 controlled devices + device profiles. From the AXL logs, I can see UCM tried to update this appUser account with each associated device one by one:

2015-11-19 11:56:23,632 DEBUG [http-bio-443-exec-180345] axlapiservice.AddAppUserHandler - select pkid from device where my_lower(name) = my_lower('SEP5067AE2505EB')

2015-11-19 11:56:23,634 DEBUG [http-bio-443-exec-180345] axlapiservice.Handler - insert into applicationuserdevicemap ( tkUserAssociation,fkDevice,pkid,fkApplicationUser ) values ( '1','ed3e2faf-27ef-c16e-454a-1a70fb1d1593','7c82905c-131e-35f5-dcab-79d31c0248f3','ca7f4383-9bc2-aaf5-ee0f-a77d3cd5c85c' )

The result is, tons of DBL Change Notification sitting in the queue. When there's other change happening con-currently, this AXL request will fail with the following error:

2015-11-19 11:47:57,177 DEBUG [http-bio-443-exec-180331] filters.ThrottlingFilter - DBLCNQueue Count: 3048

2015-11-19 11:47:57,177 WARN  [http-bio-443-exec-180331] filters.ThrottlingFilter - AXL Unavailable due to throttling

So wondering, is there other better way to get this done using AXL?

Thanks

Charlie

[Aaron Harrison]

Hi

Yes - just smash them in with a SQL query via AXL.

Aaron

[dstaudt]

The CUCM database includes triggers that will perform tasks, like sending change notifications to the runtime parts of CUCM, on certain operations, like associating users to devices, on any change (whether from the admin UI, AX, or direct SQL manipulation) so inserting 5500 rows into the appUser->device mapping table in one go would likely still trigger a large number of change notifications.

Are you performing an addAppUser with a giant associatedDevices list, or how are you attempting to get the 5500 devices associated?  If so, one possible workaround might be to do the addAppUser with no associated devices, and then use executeSqlUpdate (via the applicationuserdevicemap table) to add groups of devices over a period of time, say 100 devices every X seconds.

[chazhang]

Thanks guys. Not confident at using SQL insert statement to update DB directly. I guess the statement could be:

insert into applicationuserdevicemap (fkapplicationuser, fkdevice, tkuserassociation) select au.pkid, d.pkid, 1 from applicationuser au cross join device d where au.name = 'ipcc' and d.name in ('SEPXXXXXXX', 'SEPYYYYYY', 'SEPZZZZZZ', ...)

Please correct me, or let me know if there's some way to fine-tune this SQL statement to make it more efficient.

Thanks

Charlie

[npetrele]

What is your ultimate goal here?  Do you want the application users to be able to have CTI control over the 5500 devices? There's a much easier way to do that, if so. If not, can you share the goal?

[chazhang]

As this AppUser is for PG, it needs to be associated to all agent phones and device profiles. So yes, I 'want the application users to be able to have CTI control over the 5500 devices'

[npetrele]

You can add the AppUser to the group "Standard CTI Allow Control of All Devices" and that should do the trick. 

[dstaudt]

Note the 'CTI Allow Control of All Devices' will only work if the CTI application user the 'super provider' feature set to dynamically add devices to its control list (not sure if PG supports that.)

If this is a one time config, then the CUCM Bulk Administration Tool (BAT) may be an option - it allows you to configure bulk user/device associations by submitting a CSV/spreadsheet file.

To insert a row into the applicationuserdevicemap table to create an association, the basic form is like:

insert into applicationuserdevicemap (pkid,description,fkapplicationuser,fkdevice,tkuserassociation) values (newid(),'','02743a27-7f8e-8968-95bf-a26e39fbeda0','1de50417-2f0a-3ef0-8969-eb7b7aaadb58',1);

If you want to insert multiple rows with one request, you can submit multiple commands separated by semi-colons, e.g.:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/10.5">

   <soapenv:Header/>

   <soapenv:Body>

      <ns:executeSQLUpdate>

         <sql>insert into applicationuserdevicemap (pkid,description,fkapplicationuser,fkdevice,tkuserassociation) values (newid(),'','02743a27-7f8e-8968-95bf-a26e39fbeda0','1de50417-2f0a-3ef0-8969-eb7b7aaadb58',1);

insert into applicationuserdevicemap (pkid,description,fkapplicationuser,fkdevice,tkuserassociation) values (newid(),'','6a30e7ea-d30d-2956-7cfc-7b73499f18c7','7d65fcf4-678b-64e3-0c3b-d0c04cc1fc32',1);

</sql>

      </ns:executeSQLUpdate>

   </soapenv:Body>

</soapenv:Envelope>

[chazhang]

Hi David,

Much appreciated the reply. One more question if you don't mind, do we have to use 'newid()' when inserting the row? I checked on my lab serves, without newid(), Informix does generate the new pkid for each new row.. So newid() is just a best practice, or mandatory? Curious whether it will cause something wrong if we don't use newid().

Thanks

Charlie

[dstaudt]

Per the Data Dictionary 'newid()' is the default value for the field, so it looks like you can omit it and the database rules will make sure it is present.

[igormo]

Don't know if you solve this issue, but here is php script that i made for bulk adding or removing devices from  AppUser in CUCM. If anyone need it , here it is : GitHub - moki74/cucm-appuser-devices

[rchaseling]

Hi Igormo,

I am giving your script a test but I always get the error message below

"PHP Fatal error: Uncaught error: Class 'SoapClient' not found in C:\Users\test\cucm-appuser-devices\cucmappuser.php:25

Stack trave:

#0 {main}

  thrown in C:\Users\test\cucm-appuser-devices\cucmappuser.php on line 25

I have tried it against a 10.5 couster & 11.5 cluster by downloading AXL toolkit from 11.5 and creating new folder as per instructions

Would you know any rason I am getting this?

Many thanks in advance