CUCM User Update

[mightyking]

Hello All,

We need to remove the value in the Name Dialing (CUCM User Page) for about 5000 users in our organization and keep that field empty. Unfortunately the Bulk Administarotion doesn't offer an option to do so. Is there a SQL query that we can use to update the Name Dialing with an empty value?

Thanks,

MK.

[Jonathan Unger]

What is the exact name of the field are you looking to clear on the user page? Additionally, can you clarify whether you are talking about the self care portal or the actual end user page in CUCM Administration?

[mightyking]

Hi Jonathan,

As I mentioned, the name of the field is Name Dialing in the end user page in CUCM Administaration.

CUCM version: 10.5

Thanks,

MK

[Vaijanath Sonvane]

Hi MK,

I do not have knowledge about the SQL method to resolve your issue. But here is what I did to resolve this issue. This may not be a full solution but it may be a workaround:

1. Used CUCM BAT to export all Users
2. Downloaded the exported file and opened it in Microsoft Excel.
3. Removed all unnecessary fields and kept only " USER ID" and "NAME DIALING" fields in Excel.
4. In the "NAME DIALING", I entered dash (-) for all users and saved this file in CSV format.
5. Imported this file into CUCM. Target "Users" and Transaction Type "Update Users - Custom File"
6. Used BAT to update the Users.

The end result is "Name Dialing" field under end user configuration changed to dash (-). I tried with the empty field but it didn't work.

I hope this will help.

Thanks,

Vaijanath S.

(Please rate if this is helpful)

[Jonathan Unger · Accepted solution]

It turns out that the "Name Dialing" field does not exist natively in CUCM. I had to integrate UCCX with my lab box in order for it to appear, this is why I had the initial confusion as to where the field was located.

I just tested the following SQL on my lab CUCM box which is running version 9.1.1.20000-5 with local CUCM users (although I don't think that having LDAP users would make any difference since this value is stored in a completely separate table from the end users).

**Disclaimer, always use SQL with caution in production (and off prod hours)! You can break a lot of things very quickly if you are not careful. If you do not understand a statement which is being used do not use it. Be sure to have a valid DRS backup. Preferably test statements in the lab first. #endrant

Steps:

1. Grab an output of the current table (in case something goes wrong you have the data to correct the issue)

admin:run sql SELECT * FROM crsapplication
pkid fkenduser aakeypadmapping aapromptname fnamealtpronun lnamealtpronun
==================================== ==================================== =============== ============ ============== ==============
04f85869-1ae4-4166-a8b0-1aaf85f8732e a746ace8-ed93-4691-58fa-d95cc48b4512 764845646 SmithJohn
8513414b-d708-4c03-ba84-1dff7b40f1eb e4767dc6-607a-4a89-cb4b-7e5f4b61ac93 566372539 JonesAlex
c418bbbf-7cbf-4554-9e90-fcc7143d0677 45bc6093-6331-8619-7a24-d557d2491bfe 37262652637 FrancoJames

2. Update ALL values in the aapromptname column to "blank" (this will clear every single "Name Dialing" field on the system:

admin:run sql UPDATE crsapplication SET aapromptname = ''

Rows: 3

3. Confirm that you have the desired result by using the original select from step 1

admin:run sql SELECT * FROM crsapplication
pkid fkenduser aakeypadmapping aapromptname fnamealtpronun lnamealtpronun
==================================== ==================================== =============== ============ ============== ==============
04f85869-1ae4-4166-a8b0-1aaf85f8732e a746ace8-ed93-4691-58fa-d95cc48b4512
8513414b-d708-4c03-ba84-1dff7b40f1eb e4767dc6-607a-4a89-cb4b-7e5f4b61ac93
c418bbbf-7cbf-4554-9e90-fcc7143d0677 45bc6093-6331-8619-7a24-d557d2491bfe

If you would like to test the statement with a single user first you can use the following syntax.

run sql UPDATE crsapplication SET aapromptname = '' WHERE aapromptname LIKE 'SmithJohn'

Please let us know if this works for you!

- Jon

[Vaijanath Sonvane]

Thank you for this valuable information.

Thanks,

Vaijanath S

[mightyking]

This is amazing Jon,

I really appreciate your time and effort.

We  have 7000 users in total but only 4860 of them are affected by this change. I guess the best option is to emply the filed for everybody using SQL and then use Vaijanath's method to update the users with a custom file. Is there any other way to proceed with SQL?

Thanks again,

MK

[Jonathan Unger]

Just to make sure I understand, you have 7000 users who all have the Name Dialing attribute filled out, but you would only like the value cleared out for a sub-set of those users (4860)?

What criteria are you using to identify those 4860 users? Is there anything about those users which we could use to identify them?

[mightyking]

Yes, that's exactely what I need to do.

Those users are agents and have a second UserID that starts from 1000 to 5900. They have a normal UserID wihich is the last name followed by frist name as well but we are not going to touch them. We are going to clear out the value of the Name Dialing for those UserID that starts with 1000 to 5900.

I hope it makes sens to you.

Thanks,

MK 

[Jonathan Unger]

I can take a look at this type of scenario in the lab later tonight and see if there is a semi-easy solution.

[mightyking]

Thank you Jon,

Let me ask you another question as I see you are very good with SQL.

Would you happen to know how can I delete a Subscriber server from a CUCM cluster using SQL. It looks like the delete from CUCM Admin page is not completely deleting the server from datebase.

CUCM version 10.5

Thanks,

MK

[Jonathan Unger]

No expert by any means, I just like to mess around :)

I gave it a shot, unfortunately it looks like CUCM does not support the type of operation we need to perform here which I think would be an update using a join.

The SQL to generate a list of the users and their corresponding Name Dialing value who have user IDs in the range you specified would be:

run sql SELECT userid,aapromptname FROM enduser INNER JOIN crsapplication ON enduser.pkid = crsapplication.fkenduser WHERE userid BETWEEN '1000' AND '5900'

I just couldn't make it work with an update statement...

As for the CUCM server lingering on in the database, I would rely on TAC to help you get rid of it. Simply deleting the value from the processnode table may not be enough. I recommend you take a look at the process noted in this thread https://supportforums.cisco.com/discussion/12956056/deleting-server-node-cucm-cluster 

If I ever do figure out the SQL update method for this type of request I will post back here.

EDIT: Read Next Post, I found a workaround!

[Jonathan Unger · Accepted solution]

I found a workaround to the syntax I was trying to use before by re-purposing a concept from the all powerful William Bell http://www.ucguerrilla.com/2014/06/using-sql-to-reconfigure-dial-plan.html .

You should be able to accomplish your goal by using the following syntax (I tested against my lab CUCM 9.1.1.20000-5 box):

run sql UPDATE crsapplication SET aapromptname = '' WHERE (fkenduser IN (SELECT pkid FROM enduser WHERE userid BETWEEN '1000' AND '5900'))

If you run into issues with SQL timeout messages, try removing the name dialing values in smaller blocks of 500.

Example:

1. run sql UPDATE crsapplication SET aapromptname = '' WHERE (fkenduser IN (SELECT pkid FROM enduser WHERE userid BETWEEN '1000' AND '1500'))

2. run sql UPDATE crsapplication SET aapromptname = '' WHERE (fkenduser IN (SELECT pkid FROM enduser WHERE userid BETWEEN '1500' AND '2000'))

Make sure you have a copy of the full table BEFORE you make any changes, you can use run sql SELECT * FROM crsapplication to grab the output.

Be cautious, and good luck!

Let us know how it goes!

[mightyking]

Thanks you very much Jon :-)

MK