topic Re: How to associate an AppUser with thousands of devices in Management

How to associate an AppUser with thousands of devices

chazhang — Fri, 20 Nov 2015 13:43:34 GMT

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

Re: How to associate thousands of devices with an appUser

Aaron Harrison — Fri, 20 Nov 2015 14:41:42 GMT

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

Aaron

Re: How to associate thousands of devices with an appUser

dstaudt — Fri, 20 Nov 2015 15:10:33 GMT

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.

Re: How to associate thousands of devices with an appUser

chazhang — Tue, 24 Nov 2015 12:57:40 GMT

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

Re: How to associate thousands of devices with an appUser

npetrele — Tue, 24 Nov 2015 19:30:13 GMT

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?

Re: How to associate thousands of devices with an appUser

chazhang — Tue, 24 Nov 2015 19:40:52 GMT

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'

Re: How to associate an AppUser with thousands of devices

npetrele — Tue, 24 Nov 2015 20:09:17 GMT

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

Re: How to associate an AppUser with thousands of devices

dstaudt — Tue, 01 Dec 2015 23:59:48 GMT

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>

Re: How to associate an AppUser with thousands of devices

chazhang — Wed, 02 Dec 2015 09:20:01 GMT

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

Re: How to associate an AppUser with thousands of devices

dstaudt — Wed, 02 Dec 2015 15:54:56 GMT

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.

Re: How to associate an AppUser with thousands of devices

igormo — Sat, 14 Oct 2017 15:32:40 GMT

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

Re: How to associate an AppUser with thousands of devices

rchaseling — Mon, 03 Sep 2018 09:58:26 GMT

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