AD User Attributes Bulk Modification

[pcstru]

Well ... unfortunately it seems I assumed too much. I dug out the script and thought I'd better test it first. It deals fine with common items and even anything in the Attributes list. Environment and Session attributes however are coded (along with god knows what else) into a binary blob known as 'userParameters'. I can't find anything to easily read and write the information in that blob, the best I found was thisblock of code, which would be a good start but is obviously not going to be trivial and unfortunately I don't have the time right now to tackle it.

[ranj]

Bulk AD Users

 

 

 

Hi All

 

I have a question on something I need to tackle within my enterprise. Just wondering if the tools mentioned on here would assist me.

 

We about to purchase a solution which integrates our smart cards with Active Directory to facilitate room bookings in our building.

 

For this to work I need to add a specific number as a Active Directory attribute or custom attribute.

 

Doing this for every user will be a major pain so I am looking at a solution to do this at a bulk level.

 

I hear products built into Windows like 'csvde' may be able to help or other 3rd party paid tools which have a GUI interface.

 

 

I am thinking of using either the 'pager' attribute under AD, telephones tab or using a custom attribute field. However I will need to use Exchange Administrator tools to view this field as I believe its not possible to see this under AD users and computers?

 

As the users are already created, I just need to update the 'pager' or 'custom attribute 1' field without amending any other details. Would this be possible?

 

I have a test AD environment which a copy of the live AD environment. I am planning on testing this before I roll out to live production. I have details of users and their card number but I also need to extract of copy of the usernames in AD so I can marry the two data sets together.

 

Appreciate any advice anyone can give me with this.

[pcstru]

@ranj. In AD management console, make sure View|Advanced Features is checked. Now, when you bring up a user there should be a tab called "Attribute Editor" which if you scroll down will show pager and many other fields.

 

For the task you are doing, I'd first extract info from AD using powershell's Get-AdUser into a CSV. I'd then use VLookup in excel to match with info from the system maintaining the smart card data (basically adding an extra column for the smart card ID). I'd probably handle any non matches at that point. I'd then use poswershell's Set-ADUser to reimport that data into the pager field (match on the info you extracted from AD so the match is exact).

 

Hope that is some help.

[ranj]

@ranj. In AD management console, make sure View|Advanced Features is checked. Now, when you bring up a user there should be a tab called "Attribute Editor" which if you scroll down will show pager and many other fields.

 

For the task you are doing, I'd first extract info from AD using powershell's Get-AdUser into a CSV. I'd then use VLookup in excel to match with info from the system maintaining the smart card data (basically adding an extra column for the smart card ID). I'd probably handle any non matches at that point. I'd then use poswershell's Set-ADUser to reimport that data into the pager field (match on the info you extracted from AD so the match is exact).

 

Hope that is some help.

 

My thoughts exactly! glad I am on the same page

[ranj]

I found a script which allows me to extract from AD all usernames and pager details. As expected they are blank.

 

Get-ADUser -Filter * -Properties sAMAccountName, pager | Select sAMAccountName, pager | Export-Csv .\UserPagers.csv -NoTypeInformation

 

So I made a couple of amendments to the pager attribute on a few test users on the CSV file and then imported the file back into AD.

 

Import-Csv -Path .\UserPagers.csv | ForEach {Set-ADUser $_.sAMAccountName -Replace @{pager="$_.pager"}}

 

However I found that now each user in the 'pager' attribute now has the following set (using the user ORSTestUser3 as example):

 

@{sAMAccountName=ORSTestUser3; pager=}.pager

 

What have I done wrong?

[adamholt1]

Can anyone recommend a script or utility to batch change all students passwords?

[pcstru]

I can't check this easily, I'd try :

 

Import-Csv -Path .\UserPagers.csv | ForEach {Set-ADUser $_.sAMAccountName -pager $_.pager}

[pcstru]

Can anyone recommend a script or utility to batch change all students passwords?

 

Powershell. Same thing : export the users you need, set the passwords you want and reimport. The import code I have from last time I did similar :

 

Import-Module ActiveDirectory
$AccList=Import-CSV c:\tmp\2015\yr7.csv

foreach ($acc in $accList) {
   Get-ADUser $($acc.UserName) | Set-ADAccountPassword -Reset -NewPassword (ConvertTo-SecureString -AsPlainText $($acc.Password) -Force)                      
}

[Davit2005]

Can anyone recommend a script or utility to batch change all students passwords?

 

How about this, you may want to test it first though

 

dsquery user "OU=Test,OU=Student User Accounts,OU=User Accounts,OU=Test,DC=domain,DC=local" -limit 0 | DSMOD user -pwd splinter -mustchpwd no -canchpwd no -pwdneverexpires yes -disabled no

[ranj]

@ranj. I'd then use VLookup in excel to match with info from the system maintaining the smart card data (basically adding an extra column for the smart card ID). I'd probably handle any non matches at that point.

 

I hit a bit of a stumbling block using vlookup in Excel.

 

So I have compiled a spreadsheet with data from both systems, AD and door system.

 

I have 5 columns, first 2 (sAMAccount Name and Pager) are from AD and (Firstname Surname, Door ID and combinedNames) are from door access system

 

e.g

 

 

How would I use vlookup to match whats in sAMAccountName to ChubbNames as they are all different order but ordered by alaphabet? I also need to integrate door ID in this so SamAccountname corresponds to the correct DoorID which relates to chubbnames

Book1.xlsx

Edited August 5, 2016 by ranj

[pcstru]

Insert a new blank column before C, call it ExtKey and enter a formula to concatenate the firstname and surname attributes in what will now be columns D&E (so C2=D2&E2). Now in B2 you can use VLOOKUP(A2, C$2:C$1000, 4, false). Copy those down the columns and you should be good.

[ranj]

Insert a new blank column before C, call it ExtKey and enter a formula to concatenate the firstname and surname attributes in what will now be columns D&E (so C2=D2&E2). Now in B2 you can use VLOOKUP(A2, C$2:C$1000, 4, false). Copy those down the columns and you should be good.

 

I think the problem is though my AD users and names within the door access system are different, there seems to be more AD names possibly because users have left or are named differently. So when I try to run the formula you mention I don't get the results and also in the formula wouldnt I need to factor in the door ID so it can marry the SAM account name to the Door ID?

 

Thanks

[pcstru]

The technique I describe should result in the second column having the correct door ID ... IF the data on on name matches up. If you do not have data that allows that match between the records in the different systems, then you have no basis for the process to work and if you cannot find any commonality, then you will need to do the match as a manual exercise. You can and should restrict the export Get-ADUsers to enabled accounts although inevitably in these exercises, there need to be manual interventions to deal with typo's, poor quality data etc.

[ranj]

ok Thanks, the issue I think I had is because the data is coming from 2 separate systems, there are some discrepancies to be expected.

 

Managed to get it working though using the formula you mentioned and some help from an excel guide on vlookups, made some minor changes to it and and now when there is match it produces the number, anywhere where it can't find a number and name it just shows #N/A.

 

This has helped big time as it now means I have lot less users to work through and makes my data cleansing job a bit easier.

 

Thank you.

[pcstru]

Glad to be of help. A couple of thoughts :

 

It's generally easier in excel to start a new workbook and then import each CSV into separate sheets - so each system's data is kept separate.

 

You can sometimes make use of multiple matching criteria in different columns, so perhaps some of your AD accounts are A.Smith, but the lock system records it as Andrew Smith, so you would add another column and build a key using a substring on the full forename then do that as a lookup. You might end up with multiple lookups from one system to the other at which point it can be useful to translate the success of failure of the lookup as a value and then use weighting and a total to allow you to sort by the overall quality of the match. It can also be worth trying to match both ways - something which is not necessarily obvious as a possibility when the key you need suggests you must look up records in the lock system from the AD data. Warning : Much of that would make no sense to do on very small datasets!

[ranj]

If you need to clear AD attributes you could use this script as a starting point.

 

Import-Module ActiveDirectory

$users = Import-Csv -Path C:\Scripts\adtest.csv
                       
foreach ($user in $users) {
# Search in specified OU and Update existing attributes
Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "cn=Users,DC=manticore,DC=org" |
 Set-ADUser -Clear l, physicalDeliveryOfficeName, division
}

 

... or this one to modify values.

 

Import-Module ActiveDirectory
           
$users = Import-CSV -Path C:\Scripts\adtest.csv

foreach ($user in $users) {
# Search in specified OU and Update existing attributes
Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "cn=Users,DC=manticore,DC=org" |
 Set-ADUser -City $($user.City) -Office $($user.Office) -Division $($user.Division)
}

 

Bulk AD Users is also good.

 

Hi Arthur

 

Just got a question on this.

 

If I need to clear all pager information in AD for a particular OU, would this script work?

 

I used the script mentioned however replaced pager but it didnt work

 

Get-ADUser -Filter "SamAccountName -eq '$($user.samaccountname)'" -Properties * -SearchBase "OU=Media,OU=2012,OU=Windows Users,OU=Windows ,DC=Net,DC=tro,DC=uk" |

Set-ADUser -Clear l, physicalDeliveryOfficeName, pager