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.

[jimbodeano33]

You can also use:

run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'a%'

'a%' being the first letter of the userid.

[Ken Beck]

if anyone wants to use wildcards this worked for me

run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'a%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'b%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'c%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'd%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'e%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'f%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'g%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'h%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'i%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'j%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'k%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'l%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'm%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'n%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'o%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'p%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'q%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'r%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 's%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 't%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'u%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'v%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'w%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'x%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'y%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'z%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'A%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'B%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'C%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'D%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'E%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'F%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'G%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'H%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'I%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'J%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'K%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'L%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'M%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'N%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'O%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'P%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'Q%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'R%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'S%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'T%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'U%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'V%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'W%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'X%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'Y%'
run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'Z%'

with the above I used secureCRT to wait (at least 1000ms) for the admin: prompt otherwise every other line would fail.  Alternatively, you can paste in a few at a time.

you can also combine what you need into one line by using the or command

run sql update enduser set fkdirectorypluginconfig=NULL where userid like 'a%' OR userid like 'b%' OR userid like 'c%'

it certainly depends on your need.