Bulk method of converting LDAP users into local users

[Michael Gerrard]

Hi there,

We were wondering if anyone using CCM 9.1 had a way of bulk editing users to convert them from LDAP users to local? Unfortunately there is no BAT option to do this, even in 9.1.2, so we're a bit stuffed, as there are over 3000 users to do manually!

Things we've tried so far:

- Seeing if there was a standard BAT way of changing this value

- Exporting All Users into a CSV, to see if there is a difference between LDAP and local users that we could use to perform a bulk update.

Data dictionary: http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/datadict/9_1_1/datadictionary_911.pdf

- Started going through the data dictionary to see if we could find the key that indicates whether a user in the database is a local/LDAP user or not. We're still digging on this. NOTE that "Local User" seems to be set to true for all users in the database, probably indicating that they are an EMCC Local User, rather than LDAP.

- Ran a few SQL commands to experiment. What's interesting is that if you try the following:

run sql update enduser set status=2 where userid = "mgerrard"

...you can see in End User pages the "User Status" of that user changes to "Inactive LDAP Synchronised User". We assumed therefore that the status key was probably the one to edit, because the "User Status" of a local user already in the system is in the same column. Not so, unfortunately. Changing the status to values other than 1 or 2 seems to break things. Just to point out that if you run:

run sql select status from enduser where userid = "SIPspeaker"

....where the SIPspeaker is already a local user, rather than LDAP synchronised, the status is still 1.

So does anyone have any fresh ideas? There are so many tables, that we can't really tell where in the database the system says whether a user is LDAP Sync'd or not.

Any help appreciated!

Mike.

[Michael Gerrard]

Any ideas anyone?

[Ayodeji Okanlawon]

The easiest way I can think of is to disable LDAP intergation and check the button that says convert ldap users to local users. There is no BAT way to convert ldap users. You have to disable LDAP first

Please rate all useful posts

"opportunity is a haughty goddess who waste no time with those who are unprepared"

[m.gravel · Accepted solution]

Hi Michael,

Export the users using BAT Import/Export option and select only End Users from the User Data section.  Once you have the exported TAR file extract it and remove the rows leaving only the rows with the users you want to change to local account.  (This is faster than having to import all the rows again with unchanged data)

Find the column "DIRECTORY PLUGIN CONFIG".  This column will show the LDAP Directory name you created in CUCM.  Set the value to "NULL" without the quotes for all the users.

Repackage your tar file and upload and import using the BAT Import/Export option.  Make sure you choose overwrite the existing information.  When I tested it I only selected the End User data when importing as the export automatically adds in Fall Back Feature Configuration.

Marc

[Jose Ignacio Leite Neto]

thanks for this!

adding NULL to DIRECTORY PLUGIN CONFIG on the export/import .csv is the best way to remove LDAP active users.

[Robert Thomas]

Don't quote me on this, but I remember seen an SQL way of doing this. I'll have to check back tomorrow through my notes once I'm in the office. Please remind me during tomorrow so I can check for those SQL.

[Jon Snipes · Accepted solution]

run sql update enduser set fkdirectorypluginconfig=NULL where userid='<userid>'

[zandokan1978 · Accepted solution]

Do you know if there is a way to do all users?

[vaibsha2]

The method refered above by Abhay Reyal would do it for all the users.

Regards

Vaibhav

[zandokan1978]

Yeah, I didn't want to do it that way but I found the SQL query.

run sql update enduser set fkdirectorypluginconfig=NULL where fkdirectorypluginconfig IS NOT NULL

[Mark Lyman]

run sql update enduser set fkdirectorypluginconfig=NULL where fkdirectorypluginconfig IS NOT NULL

[Mohammed al Baqari]

Its not recommended to update cucm using sql commands. It rather preferred
to use axl in order to avoid breaking the database

[vaibsha2]

The best solution is to use the sql command,however with just a slight modification,you can do this for a lot of users.

run sql update enduser set fkdirectorypluginconfig=NULL where userid IN ('userid1', 'userid2', ‘userid3’, 'userid4', 'userid5')

[Abhay Singh Reyal]

You can use below steps

*Delete the LDAP directory from call manager.

*disable the LDAP synchronisation from call manager.

*Deactivate the "DIR sync" service from the call manager serviceability.

*On the CLI of the CUCM use the following command to make the end users active and normal standard end users :
"run sql update enduser set status=1"

*Verifie using following command:
"run sql select * from enduser where status=0 "

Regards

Abhay Reyal

[vaibsha2]

Hi Abhay,

That is also the way to go however,that procedure will change the status for every user.I have mostly seen customer wanting to make amendments to a bulk of users out of the whole group.

Regards

Vaibhav Sharma