topic Re: Bulk modification of Identity information Cisco ISE in Network Access Control

Bulk modification of Identity information Cisco ISE

GabsC2 — Wed, 26 Apr 2023 15:47:30 GMT

Good day,

I'm trying to do a bulk modification of users data in a Cisco ISE. We're migrating from an ACS to and ISE and I've migrated all the TACACS users from one to another. All the users are disabled because of the password policies and have the passwords outdated because the backup is from a couple of months back so I need to activate all the users, change the passwords of all the users to a generic password and activate the option to ask to change the password in the next login. The issue is that we have more than 500 users and do this manually is very time consuming. I've exported the user identity data from the Identities, opened in excel, changed text to columns, change all the data I needed and when I try to import the information it doesn't accept it. I need your help to see if there's a way I can change the data in excel and import it to the ISE once I change text to columns because I see that the ISE is waiting for the data to be comma separated and after I change it it doesn't accept it

Re: Bulk modification of Identity information Cisco ISE

Nancy Saini — Wed, 26 Apr 2023 17:51:07 GMT

If you have successfully imported the users to ISE and want to enable it then on ISE 3.1 use option "Change Status of All".

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Wed, 26 Apr 2023 22:02:59 GMT

That options helps with enabling all or the selected the users at the same time but the real issue is changing the password and activating to change the password in the next connect.

Re: Bulk modification of Identity information Cisco ISE

Greg Gibbs — Wed, 26 Apr 2023 22:45:31 GMT

What version of ISE are you using? What specific error(s) are you getting?

I tested on a small scale with ISE 3.1p6 by exporting 2 disabled users via CSV, opened in Excel, changed the 'Is Password Encrypted', 'Enable User', and 'Change Password on Next Login' fields, saved as CSV, and imported back to ISE.
I did not get any errors and the changes were applied to the users.

One of the biggest problems I've seen in the past with CSV imports is that either Excel is saving the file in xls/xlsx format instead of CSV or it is adding whitespace in some of the fields. You might want to confirm these are not happening.
In order to check for whitespace, you might need to open the CSV file in another tool like Notepad++.

Re: Bulk modification of Identity information Cisco ISE

Greg Gibbs — Wed, 26 Apr 2023 22:49:49 GMT

Another option would be to use the ERS API to modify the user attributes programatically.

https://developer.cisco.com/docs/identity-services-engine/latest/#!internaluser

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Thu, 27 Apr 2023 13:55:03 GMT

I'm using ISE 2.7

I'm receiving this error

The archive is CSV

I opened the csv file modified in Notepad++ and doesn't seem to be anything amiss.

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Thu, 27 Apr 2023 13:59:22 GMT

I exported the users again, changed 2 passwords without changing it from text to colums, save it at CSV and the import worked. It seems that the issue is when I change text to columns but that's what I need because is the easiest way to bulk change the data in the CSV file. Is there a way to make it work when I change the text into columns?

Re: Bulk modification of Identity information Cisco ISE

poongarg — Tue, 02 May 2023 05:36:12 GMT

I just tested on my LAB ISE 2.7 node with 4 users present in disabled state. Exported the identities and opened the CSV file and made modifications like assigned default password to all users, encryption FALSE, Change Password at next login as Yes. Also added a new user in the same CSV file. Saved it and imported successfully on the ISE node. Later enabled all users. No issue observed.

Re: Bulk modification of Identity information Cisco ISE

ahollifield — Tue, 02 May 2023 11:04:57 GMT

Just an FYI to help you prepare for a future upgrade from 2.7: https://www.cisco.com/c/en/us/products/collateral/security/identity-services-engine/bulletin-c25-2943876.html

Re: Bulk modification of Identity information Cisco ISE

Charlie Moreton — Tue, 02 May 2023 14:15:27 GMT

I see from above that you have saved the file as 'Microsoft Excel Comma Separated Values File (.csv)'. I have seen that as an issue before. You should save it as 'CSV UTF-8' as shown below:

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Tue, 02 May 2023 15:21:07 GMT

Good day @Charlie Moreton ,

Yes, I modified it and save it in the same format I downloaded it, I didn't change the format. I'll try to see if that helps. This is the 3rd time I tried with different devices (ISE and computers) and the error is always the same.

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Tue, 02 May 2023 15:22:32 GMT

Good day,

I don't have that option

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Tue, 02 May 2023 15:24:08 GMT

Good day @poongarg ,

I tried with less users and received the same error

Re: Bulk modification of Identity information Cisco ISE

poongarg — Tue, 02 May 2023 16:47:54 GMT

As per the attached file, the users mod.csv is not exactly in the same format as users (1).csv file.

This is what I am seeing when I opened your users mod.csv file. If you are just changing the values and saving the same file or making any other modifications.

In my case, I did changes in the values of columns and then saved the same file and imported on ISE. The format was not changed.

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Tue, 02 May 2023 17:59:00 GMT

Good day @poongarg ,

When I export the data from the ISE I receive the information in the format of users (1). Since I need to change a lot of information of a lot of users that is not the same I need to change the format from comma delimited to columns. After I do that I change all the information I need but the device doesn't accept the users mod format. Excel doesn't have an easy way to return the information to comma delimited so my question is if there's a way the ISE could accept the data formatted as users mod

Re: Bulk modification of Identity information Cisco ISE

poongarg — Fri, 05 May 2023 06:14:33 GMT

We can only use a comma-delimited text file to import the user identity. You can also you template and copy paste information if possible and import the file. These are the only options I can see to bulk import the user data on ISE.

Re: Bulk modification of Identity information Cisco ISE

thomas — Sun, 07 May 2023 17:00:36 GMT

Export and import of ISE internal users via CSV is very straightforward.

I do not understand why you "changed text to columns". CSV is a column format so there should be no conversions. Just update your data. You say

I need to change the format from comma delimited to columns. After I do that I change all the information I need but the device doesn't accept the users mod format. Excel doesn't have an easy way to return the information to comma delimited so my question is if there's a way the ISE could accept the data formatted as users mod

I don't understand what you are doing here but this is clearly the problem! There have been 15 replies over more than 1 week and it works for everyone else so you must be doing something that you are not telling us. Tell us the exact change(s). See . You must show us the exact error from Excel. Do not tell us "it doesn't accept it". That is not a real error that can pinpoint the problem.

In my experience, you must save the CSV file in Excel as Comma Separated Values (.csv) :

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Mon, 08 May 2023 15:24:25 GMT

Good day,

Dear @thomas , I'll explain the step by step I'm using for the procedure.

1 - Enter the ISE and export the user data

2 - I get the CSV uploaded called users export

3 - I open the CSV and get this format

As you can see the file is CSV

4- Since I need to change data in bulk I need to change the format of the file so I can read it in columns. I do it in the following steps

After that I get a format in excel that I can easily work with

The after I do the changes I need I try to upload it to the ISE and receive the error I showed before. The only way I could solve the issue is using some formulas and some manual change in the excel document so I can return the data to the format it was in the image showed in step 3

I hope this helps to show what I do in order to export and import the data. Please let me know if I'm doing this incorrectly and what I can do to solve my issue. Thank you in advance

Re: Bulk modification of Identity information Cisco ISE

thomas — Mon, 08 May 2023 15:41:28 GMT

Thank you, very helpful!

I do not know why you are going through the Convert Text to Columns Wizard... Excel opens it perfectly for me when I open your file:

When you do the Convert Text to Columns Wizard, I see you are choosing both Commas and Tabs. There should only be Commas. Perhaps the Tabs option is adding an extra column or something which is why you get the 'Invalid number of fields' error???

Your `users export.csv` file has 2 additional columns added at the end:

Gerencia_Compa_ia

sandvine

When I try to import it, it complains that I do not have these defined as Custom Attributes. You will need to delete those columns if you do not have those as Custom Attributes.

Re: Bulk modification of Identity information Cisco ISE

GabsC2 — Mon, 08 May 2023 17:07:27 GMT

Thanks for the input @thomas

I don't know why Excel opens the CSV file as showed, that's why I have to convert it. Let me try the 2 suggestions you made to see if it works