Find and replace multiple Windows user attribute values at once
By now you probably know how to replace a single value in the Active Directory; first use Data.Get.AD to extract account names and the attribute of interest, then loop through all extracted values and search for values to replace with AD.Account.SetProperty. Pseudo code:
Data.Get.AD Path§ColX§Attribute§ColY Data.Loop If %colY%=OldValue Then AD.Account.SetProperty %ColX%, Attribute, NewValue
EndIf Data.EndLoop
But what if you need to replace multiple values at once? Well, you could use multiple if statements
Data.Get.AD Path§ColX§Attribute§ColY Data.Loop If %colY%=OldValue Then AD.Account.SetProperty %ColX%, Attribute, NewValue
EndIf
If %colY%=OldValue2 Then AD.Account.SetProperty %ColX%, Attribute, NewValue2
EndIf
If %colY%=OldValue3 Then AD.Account.SetProperty %ColX%, Attribute, NewValue3
EndIf Data.EndLoop
However, this is a rather clumpsy solution, and what if there are twenty or hundred of values to replace? Fortunately, there is a better way, have a look at this pseudo code:
Data.Get.AD Path§ColX§Attribute§ColY Data.Loop
xData.Loop Replace If %colY%=%Replace1% Then AD.Account.SetProperty %ColX%, Attribute, %Replace2%
EndIf
xData.EndLoop
Data.EndLoop
[xData_Replace_Begin]
OldValue,NewValue
OldValue2,NewValue2
OldValue3,NewValue3
[xData_Replace_End]
By using an [xData] section and a corresponding loop, the code gets much more compact and you get a nice list of things to replace that easily can be extended.
We are now going to explore this technique in two complete scripts, the first one, script 1, includes a preview dialog that lets you to see the changes before they are carried out, the second one, script 2, uses wildcards to replace parts of text and a secondary [xData] section with a list of specific organizational units to manage accounts in.
Script 1 - replace five different zip codes throughout the entire directory
SET ADDomainPath=dc=com/dc=acme/dc=D31
SET ADOUPath=%ADDomainPath%/ou=Employees
[Settings_End]
[Batch_Begin]
Data.Get.AD Container, %ADOUPath%, User, Yes, ADpath§1§postalCode§2§
Data.Copy.xData 2§,3§,col2=replace1
SET DataShowLabels=Account§Replace§With§0§
Data.Show.Filter Replace zip code for listed accounts?,col3<>
Data.Loop If %col3%<> Then
// LogWindow.Write %col1% %col2% %col3%
AD.Account.SetProperty %col1%,PostalCode,%col3%
EndIf Data.EndLoop
[Batch_End]
[xData_Replace_Begin]
90509;90059
91342;90048
93306;90024
92422;90025
96322;90026
[xData_Replace_End]
About script 1
The [xData] section named "Replace" (xData sections can be named anyway you want) contains zip codes to replace in the first column and what to replace them with in the second column.
Data.Get.AD fills the [Data] section with all accounts in the container (and all subcontainers if fourth parameter is "Yes") specified in the ADOUPath variable, full AD-path for each account is written to [Data] column 1 and postal (zip) code to column 2.
This is how the data section looks after Data.Get.AD:
The line "Data.Copy.xData 2§,3§,col2=replace1" copies data from [xData] to [Data] if there is a match between [xData] and [Data] - in this case - if the postal code in [xData_Replace] column 1 matches the existing postal code in [Data] column 2, then copy the new postal code from [xData_Replace] column 2 to [Data] column 3. If no match then [Data] column 3 is cleared.
This is how the data section looks after Data.Copy.xData:
[Data_Begin]
DC=com/DC=acme/DC=d31/OU=Employees/CN=adal;96322;90026;
DC=com/DC=acme/DC=d31/OU=Employees/CN=adbl;92422;90025;
DC=com/DC=acme/DC=d31/OU=Employees/CN=alda;91342;90048;
DC=com/DC=acme/DC=d31/OU=Employees/CN=alde;90129;;
.
.
[Data_End]
Notice line four, "90129" does not exist in [xData_Replace] - the third column is empty.
SET DataShowLabels sets labels for the below Data.Show.Filter command.
Data.Show.Filter shows the preview dialog, see below screen shot. Notice "col3<>", it means that rows are shown only if the third column in the [Data] section is not empty - only accounts that will be modified are displayed.
Click abort to stop the script or continue to make modifications.
The commands inside Data.Loop..Data.EndLoop are executed once for each row in the [Data] section, %col1% refers to column 1 on current row, %col2% to column 2 on current row etc.
AD.Account.SetProperty modifies the account.
Tip: remove the comment "//" before LogWindow.Write and put it in front of AD.Account.SetProperty to write to the log window instead of making actual changes.
Script 2 - Use wildcards to replace five different building numbers for accounts in three specific organizational units
This time things gets a bit more complicated since we are not going to replace the entire attribute value, only a part of it. The attribute in question looks like this: "internal mail code, building, floor', example: "5033, Bldg 34, 8th Flr", we will replace the building part "Bldg XX" with "Bldg YY". Also, we do not want to search through all objects in the entire directory, just the ones in "OU1", "OU2" and "OU5".
(See above "about script 1" for basic details about Data.Get.AD, Data.Show.Filter, Data.Loop..Data.EndLoop and AD.Account.SetProperty.)
The first [xData] section named "Containers" contains a list of the containers from which to extract accounts.
In this script the Data.Get.AD command is placed inside an xData.Loop..xData.EndLoop loop; Data.Get.AD is executed once for each row in the [xData] section "Containers", the %Containers1% variable contains the value of the first column on current row in the [xData] section.
Notice the last parameter for Data.Get.AD,"add" , without it Data.Get.AD replaces everything in the [Data] section, with it, data is added at the end.
Data.Clear is used to remove old data from the [Data] section in case the script is executed more than once.
This is how the data section looks after Data.Get.AD:
The second [xData] section named "Replace" contains building numbers to replace in the first column and what to replace them with in the second.
Data.Connect.xDatabasically connects an [xData] section with the [Data] section by a row number (much like "pointers" if you are familiar with C programming or foreign keys if you are into databases). Remember Data.Copy.xData in the previous script? Data.Connect.xData works almost the same way, but instead of copying actual data from [xData] to [Data], the row number of the [xData] section row that matches the criteria is written to [Data]. This is useful in two cases (1) if there are many items on each row in [xData] - leave them there and use the referring row number in [Data] to access them when needed, or in this case, (2) when data is to be manipulated in any way before copied to [Data].
OK let's get back to the actual script.
Data.Connect.xData 3; col2=*replace1* connects [xData] to [Data] if there is a match between [xData] and [Data] - in this case - if the building number in [xData_Replace] column 1 matches the existing building number in [Data] column 2, then write the row number of the matching [xData_Replace] row in [Data] column 3. If no match write 0. Notice the wildcards "*" before and after "replace1", they mean that it does not matter what is before and after "Bldg XX".
This is how the data section looks after Data.Connect.xData:
[Data_Begin]
DC=com/DC=acme/DC=d31/OU=OU1/CN=depa2;5035, Bldg 36, 5th Flr;0
DC=com/DC=acme/DC=d31/OU=OU1/CN=fael;5046, Bldg 22, 7th Flr;1
DC=com/DC=acme/DC=d31/OU=OU1/CN=frco;5046, Bldg 22, 1th Flr;1
DC=com/DC=acme/DC=d31/OU=OU1/CN=havi;5033, Bldg 34, 2nd Flr;0
.
.
[Data_End]
The third (rightmost) column in the [Data] section now contains pointers to the [xData_Replace] section. For example, have a look at the third column of the second row in the [Data] section, it reads "1". Now have a look at the first row in the [xData_Replace] section, it reads "Bldg 22;Bldg 62" - "Bldg 22" was found in the [Data] section and will soon be replaced by "Bldg 62".
The if-statement and the Data.Write command inside the Data.Loop..Data.EndLoop structure replaces the building number part of the string. Example: "5046, Bldg 22, 7th Flr" replace "Bldg 22" with "Bldg 62" -
CopyWord(%col2%;,;1;0) reads the first "5046" part from [Data] section column 2, then xDataValue(replace;2;%col3%) reads the replacement value "Bldg 62" from the row in the [xData] section that the second column in [Data] refers to and finally the last CopyWord (%col2%;,;3;0) reads third word "7th Flr" from [Data].
This is how the data section looks after the if-statement. [Data_Begin]
DC=com/DC=acme/DC=d31/OU=OU1/CN=depa2;5035, Bldg 36, 5th Flr;0;;
DC=com/DC=acme/DC=d31/OU=OU1/CN=fael;5046, Bldg 22, 7th Flr;1;5046, Bldg 62, 7th Flr;
DC=com/DC=acme/DC=d31/OU=OU1/CN=frco;5046, Bldg 22, 1th Flr;1;5046, Bldg 62, 1th Flr;
DC=com/DC=acme/DC=d31/OU=OU1/CN=havi;5033, Bldg 34, 2nd Flr;0;;
.
.
[Data_End]
The last part of the script is identical to the last part of the first script.
