Unity Connection extension in use

[AskQ2Forum_2]

                   hi, we have Connection v 8.62a, fully populated , working fine. Only issue is we need to create a user with a particular extension yet the system reports extension is in use. We cannot find the extension anywhere, users, call handlers, pdl's - none have that extension in use, we only have the single partition  - any advice appreciated, TIA....Jag

[Jagpreet Barmi]

Hi,

You can query the database with the following query.

run cuc dbquery unitydirdb select * from tbl_DtmfAccessId where DtmfaccessId = 'extension_number'

HTH,

Jagpreet Singh Barmi

[AskQ2Forum_2]

SSA Jagpreet Singh,

thanks, will check that command - what if it finds the user in the DB (which i'm sure it will) - what is command to delete it from there ?...Regards, Jag

[Jagpreet Barmi]

SSA,

You can delete the user using the following command.

run cuc dbquery unitydirdb delete from tbl_DtmfAccessId WHERE DtmfAccessId ='extension_number'

As the extension could be added as an Alternate extension, it may not show with the user directly, but would show up in its alternate extension.

HTH,

Jagpreet Singh Barmi

[lindborg · Accepted solution]

that's not how you delete a user - thats a direct table delete of an extension number (which could be a primary or an alternate extension for a user or it may not be).  Blowing away (or trying - I suspect the FK constraint on that row will cause it to fail) a row in a table with delete commands like that is an amazingly bad idea and should never be done unless there is no other choice available.

in general direct table edits in a database as large and complex as Connection is never a good idea.  A user has data strung out over 30 tables - trying to just blow one of them away is not going to work and if it did it would damage your database.

Always use stored procedures for adding/deleting data in Connection - there is a huge number of interdependencies on tables (FK constraints on most) and a lot of the business logic is in the stored procedures.

In this case you can find the owner (user) of the extension you're looking for by doing a "select parent_subscriberobjectid from vw_DTMFAccessId where dtfaccessid='1234'"

armed with the owner objectid you can call the stored procedure to safely remove that user like this:

"execute procedure csp_userdelete (pObjectId='58b18eb6-6af3-4bfb-a2d7-c822e323635a')"

and you can even get fancy and pass in a replacement user - this is handy since if the user you are trying to delete is referenced _anywhere_ in the database the delete will not be allowed (more of those FK constraints).  So you can replace a user's references and point them to another (i.e. like the operator user or the like).  this is done by passing in the "pReplacementUserObjectId" parameter.

As a rule if you're going to be doing direct database editing you should get yourself CUDLI (the database explorer and editor for Connection): http://www.ciscounitytools.com/Applications/CxN/CUDLI/CUDLI.html

it has a complete data dictionary, a query builder, table/view explorer and help for finding and constructing stored procedures - if you want to do it right, it's good to use the tools the database developers provided for such things.

[Jagpreet Barmi · Accepted solution]

Thanks for the detailed post Jeff.

I agree that this should not be used as a direct approach to delete the user and should be tried as the last resort. If we are deleting a user from the database or anything else for that matter, we need to check the dependencies and delete only the required entry/association.

As mentioned above, we may not even need to delete the user from the CLI beucase "the extension could be added as an Alternate extension and would not show  with the user directly". Simply removing the entry from the alternate extensions (from GUI) would allow us to add the extension to another user and the database.

[AskQ2Forum_2]

Thanks for your input guys. I ran a user data dump that included alternate extensions and found the user with the alternate extension configured. I have deleted that and now can create the user with that extension....regards, Jaginder

[Vaijanath Sonvane]

Hello All,

There are multiple ways to find Alternate extension in Unity Connection.

Following is the one using Unity Connection CLI:

Command #1: (52327 is the Alternate Extension associated with user which is unknown)

run cuc dbquery unitydirdb select * from tbl_DtmfAccessId where DtmfaccessId = '52327'

The result of this command would be:

objectid                              dtmfaccessid  idindex  displayname  parent_globaluserobjectid             parent_distributionlistobjectid  parent_handlerobjectid  parent_locationobjectid  locationobjectid                      callpickuptimeout  transmitforcedauthorizationcode  clientmattercode  parent_contactobjectid  partitionobjectid                     userdefined

------------------------------------  ------------  -------  -----------  ------------------------------------  -------------------------------  ----------------------  -----------------------  ------------------------------------  -----------------  ----

4193548d-ef81-4800-b5bf-050d5890d493  52327         1        52327        1bc1f6a6-2361-4791-ba51-837e9d3ea600  null                             null                    null                     7de855c4-6f74-4fc7-9d8e-805b1dc5ad37  4                  0                                null              null                    ce2ddfb5-b1cd-48b1-9a85-bc08181bb116  0

Command #2: (Use parent_globaluserobjectid in the following command)

run cuc dbquery unitydirdb select * from vw_subscriberreference where objectid='1bc1f6a6-2361-4791-ba51-837e9d3ea600'

The result of this command would be:

objectid	displayname	referencetype	referrerobjectid	referrerobjecttype
162677bb-a84d-41d5-89ab-571ead7c5828	dfeghij	Recipient	1894de90-0c6f-4cfd-acbf-020709158040	3

Now, you can search with alias "defghij" in Unity Connection and remove/edit the Alternate Extension.

Thanks,

Vaijanath S.

[Corey P]

Thanks for this! I was trying to figure out a good way to do this via CLI but I didn't see alternate extension as a column in vw_subscriber when I ran this command "show cuc dbview schema unitydirdb vw_subscriber" (unless I just overlooked it). I also didn't realize the table was tbl_DtmfAccessId instead of just DtmfAccessId. I ended up finding it quickly via educated guesses but I was about to do a full user export.