Modifying AD Atributes

Modifying Multiple User Attributes in Active Directory

I had a challenge to modify user phone number extensions in Active Directory. I had to export

user phone extensions from Cisco Callmanager’s database.

You can use this “Article” to modify any user attribute, not just phone extension.

To perform the following tasks you will need:

• CSVDE command line tool (You will need to download RSAT for Win 7 with SP1, this tool will have all the command tools necessary)
• LDIFDE command line tool (You will need to download RSAT for Win 7 with SP1, this tool will have all the command tools necessary)
• Log Parser 2.0 (you can download it here)
• MS Excel and
• Notepad

Installing RSAT for Windows 7

Once you have downloaded and installed the update from MS website:

1. Open "Control Panel" --> "Programs" --> "Turn Windows features on or off"
2. Then you need to tick the “AD DS Snap-ins and Command-line Tools” check box.
   Note: You can also install other RSAT tools if you want.
   
3. Click OK and it will install this feature.

Exporting Data from Active Directory

1. Manually:

To export the data from the AD:

1. Open “Command Line” Tool with Administrative Privileges
2. Navigate to C:\Windows\System32
3. And run this command:
   
   csvde -f “C:\\<path_to_the_output_file>\\output.csv” -d "OU=<the_OU_you_are_interested_in>,DC=<the_domain_name>,DC=<the_second_part_of_the_domain>" -r "(objectclass=<object_type_[user_or_group]>)"
   -l <atributes_[displayName,telephoneNumber,department,l,givenName,sn]>

Where:

• csvde - AD Tool for importing and exporting AD object data and creating AD ojects
• -f “C:\\<path_to_the_output_file>\\output.csv”- specifies an exported file output location and has to be .csv file
• -d "OU=<...>,DC=<...>,DC=<...>" - Specifies a full DN for the OU that you are interested in
• -r “(objectclass=<object_type>)” - specify the type of the object you are looking for. It can be:
• User
• Computer
• Contact
• Printer
• -l <attributes> - Specifies attributes that you want CSVDE Tool to retrieve from AD. You can get a list of attributes from this website. You can search for other versions of MS Active Directory on the same website.

2. By script:

•  I have created a script. You can access it at this link

Note: You just need to tweak it so it fits your needs like location of the output file and
attributes.

By completing this step you should have an CSV file “output.csv” in the folder that you have specified in the previous step.

Open the folder to see and modify the data as required. 
In my scenario I will be modifying the "telephoneNumber" attribute field.

This is how it should look like this:

Exporting data from Cisco Callmanager

I will be exporting phone extension number data from "Cisco Callmanager". If this section is irrelevant to you please skip it.

To export the data from "Cisco Callmanager" follow these steps:

1. Go to the "Cisco Callmanager Web Based Console"
2. Click on “Bulk Administration”
3. Point over “Users”
4. And Click on “Export Users”
5. In the “Find Users” field you can specify a name for the person you are looking. You can also choose the criteria to search for:
   
          
   You can also leave the field blank to see all the users in the system.
6. Once the list is populated click on “Next”
7. In the “File Name” field enter the name of the file that will be created
8. In the “File Format” drop-down list you can choose from these options:


1. All User Format. It will have this type of the information:
   
   
2. Default User Format
   
   
9. In the “Job Description” write the name of the job or leave the default value and specify when to run the job. The options are:
1. Run Immediately
2. Run Later (To schedule and activate this job, use JobScheduler page.)
10. Once you have made your choice click “Submit”. In this scenario I chose “Run Immediately”. After clicking the “Submit” button the console will take you back in the “Find and List Users” field. Just notice the information in the “Status” field:
    
    
11. Click on the “Bulk Administration” → “Job Scheduler”
    
    
12. Here you can search for the job that was scheduled. To my mind the easiest way is to click “Find” and sort the files by date.
    
    
13. See if your job status is shown as “Completed” if so then proceed to download, if not then wait until the job is finished
14. Once your job has finished then click on “Bulk Administration” and click on “Upload/Download Files”
    
    
15. Type the name of the file that you entered in the 7th step and click “Find”
    
    
16. Then tick the box for the file that you want to download and click “Download Selected”. Once prompted by the web browser save the file on the computer
    
    
17. Since the file has been converted as ".txt" file you need to convert it to ".csv" file. Easyest way to do it is to open the file using "Notepad" and save it as “filename.csv”. Don’t forget the “ ” so the file will be saved as the "csv" file.

Merging data

Now I am going to update the phone extensions from the "Cisco Callmanager" in output document that was exported from "Active Directory":

1. Open the file that was exported and converted from "Cisco Callmanager"
2. Copy the necessary field to the document that was exported from "Active Directory"
   
   
   
   
   
   
   As you can see the A field is data from AD and the B field is data copied from Cisco Callmanager. The telephone field in the field A is empty.
3. Using built-in formulas in MS Excel replicate and merge the data from Cisco Callmanager’s file with the AD data.This formula will match the first name and last name and if the match is positive it will copy the phone number and place it in the “telephoneNumber” field.
   The formula is:
   =INDEX($K$2:$K$3,MATCH(1,($I$2:$I$3=E2)*($J$2:$J$3=B2),0))
   
   where:
1. INDEX - Returns the value of an element in a table or an array, selected by the row and column number indexes
2. $K$2:$K$3 - Specifies the range of the data that you are looking for. It has to be with the $ signs because if you are going to copy the formula then you would like to source to stay fixed and not to come along and leave the previous data out. Even if you are looking for a single data you still have to specify a range otherwise the formula will not work.
3. MATCH - Finds the largest value that is less than or equal to value.
4. 1 - This is a logical value that states if the match is correct then TRUE or could be 0 that is FALSE. We need TRUE value
5. $I$2:$I$3 - The first range of data.
6. E2 - The first criteria that has to be meet
7. * - combining sign
8. $J$2:$J$3 - The second range of data
9. B2 - The second criteria that has to be meet
10. 0 - Specifies that the Match has to be “Exact” (there are other options:
1. 1 - Approximate Match
2. 0 - Exact Match
3. -1 - Greater than 
   After entering formula you need to press “Ctrl+Shift+Enter” instead of “Enter” as it will put { }. If you put the symbols manually or will press “Enter” the formula will not work. 
   If you don’t want to specify two search criteria you can use "vLookup" formula.
4. Modify file and prepare it so that you would be able to convert it to "LDF" file. You need to delete all unnecessary fields as the fields in this example are used only for ease and for formula purposes.
1. First you need to copy the data that you got in the previous field as once you will delete the field the formula will stop working. You need to create a extra column next to "DN" column and name it “telephoneNumbers” and copy all the data from the original “telephoneNumbers” column 
   
   Once copying the file use the “Values” option
   
2. After that delete all unnecessary data so it will fit the template we will use later. In my scenario the template uses only two fields:
1. "DN"
2. "telephoneNumber"
   
   
3. Click “Save” and close it. On the First Prompt click Yes and on the second prompt “Do you want to save the changes you made …” click “Don’t Save” 

Converting "CSV" to "LDF" file 

Before converting the file you need to create a template that the “Log Parser” will use. The template can look like this but you can modify it:

<LPBODY>
dn: %FIELD_3%
changetype: modify
replace: telephoneNumber
telephoneNumber: %FIELD_4%
-
</LPBODY>

Where:

• <LPBODY> and </LPBODY> - specifies scripting language
• dn: - full DN
• %FIELD_3% - Third field in the "CSV" source file
• changetype: modify - What type in the "AD" will be performed. It can be
• Add
• Modify
• Delete
• replace: - what action need to be done when specifying changetype:
• telephoneNumber - What needs to be replaced. This creates a variable
• telephoneNumber - specifies what is the variable telephoneNumber from the previous line
• %FIELD_4% - Forth field in the "CSV" source file

Here is the image to understand how he converter will read the source file:



You can also use my template that is saved at this link

You can choose one of the two options to convert the "CSV" to "LDF" file:

1. Manually:To do that follow these steps:
1. Open “Command Line” Tool with Administrative Privileges
2. Navigate to "C:\Windows\System32"
3. And run this command:type <file_path>\output.csv | logparser "SELECT * FROM STDIN" -i:CSV -o:tpl -tpl:<file_path>\template.tpl -q:on -stats:off > <file_path>\output.ldf
   where:
1. type - command for Log Parser
2. <file_path>\output.csv - Location of the CSV file
3. | - Piping command that enables to use multiple commands in one command
4. logparser - Command for the Log Parser
5. "SELECT * FROM STDIN" - command for the Log Parser
6. -i: - Input file
7. CSV - File type
8. -o: - Output file
9. tpl - Output File type
10. -tpl:- template
11. <file_path>\template.tpl- location of the template
12. -q: - Command for the Log Parser
13. on - Command for the Log Parser
14. -stats: - Command for the Log Parser
15. off- Command for the Log Parser
16. > - this is a command that the result has to be writen to an output file
17. <file_path>\output.ldf - location of the output file
2. By Script

• I have created a script. You can access it at this link, you just need to tweak it so it fits your needs like location of the output file and attributes.

Importing Data into Active Directory

You can choose one of the two options to import data into Active Directory:

1. Manually:To do that follow these steps:
1. Open “Command Line” Tool with Administrative Privileges
2. Navigate to C:\Windows\System32
3. And run this command:
   ldifde -i -f "<file_path>\output.ldf"
   where:
• ldifde - AD command line tool for modifying the data in AD
• -i - Import command
• -f - file
• "<file_path>\output.ldf" - file path
2. By Script:

• I have created a script. You can access it at this link, you just need to tweak it so it fits your needs like location of the output file and attributes.

Double check if everything is as it should be, if not please double check if you have done as written in this manual.