PowerShell script to update Active Directory

PowerShell Help
1

Hi all, I’m trying to write a script for PowerShell, the script should read from a .csv sheet and update Active Directory if there are any changes. Somehow I don’t get what’s failing, I’m not an expert to be honest.

# Import the CSV file
$csvData = Import-Csv -Path 'C:\mydata.csv'

# Loop through each row in the CSV
foreach ($row in $csvData) {
    # Retrieve user information from the CSV
    $vorname = $row.'Vorname (bürgerlich)'
    $nachname = $row.'Nachname (bürgerlich)'
    $abteilung = $row.'Abteilung'
	$position = $row.'Position'
	$mobil = $row.'Telefonnummer (geschäftlich)'
	
    # Construct the SamAccountName from Vorname and Nachname
    $samAccountName = $vorname + ' ' + $nachname
	
	Write-Host "SamAccountName: '$samAccountName'"

    # Query Active Directory to retrieve the user object
    $user = Get-ADUser -Identity $SamAccountName -Properties Vorname, Nachname, Abteilung, Position, Mobil

    # Compare user attributes and update AD if necessary
if ($user) {
    if ($user.Vorname -ne $vorname) {
        Set-ADUser -Identity $samAccountName -Vorname $vorname -WhatIf
    }
    if ($user.Nachname -ne $nachname) {
        Set-ADUser -Identity $samAccountName -Nachname $nachname -WhatIf
    }
    if ($user.Abteilung -ne $abteilung) {
        Set-ADUser -Identity $samAccountName -Abteilung $abteilung -WhatIf
    }
    if ($user.Position -ne $position) {
        Set-ADUser -Identity $samAccountName -Position $position -WhatIf
    }
    if ($user.Mobil -ne $mobil) {
        Set-ADUser -Identity $samAccountName -Mobil $mobil -WhatIf
    }
}
    else {
        Write-Host "User with SamAccountName '$samAccountName' not found in Active Directory."
    }
}

#Press any key before closing the PowerShell window
Write-Host "Press any key to close this window..."
$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown')

Right now I have the following error:

User with SamAccountName ' ' not found in Active Directory.

2

magarpol,
Welcome to the forum. :wave:t3:

Do the sAMAccountNames of your users in your AD really have spaces in it? … and consist of the complete firstname AND lastname?

Do you get this error only once or as often as you have data sets in your input CSV file?

3

Hi, thanks for your reply. The account name is somehow a combination of both name and surname, but on the csv file that nomenclature doesn´t appear at all, only name and surname appear. I can take a look and see if I can export the .csv with a proper username on it.

4

Ok I changed the script as I found another filed for complete name (which is name and surname) in the .csv export. It looks like this now:

# Import the CSV file
$csvData = Import-Csv -Path 'C:\sheet.csv'

# Loop through each row in the CSV
foreach ($row in $csvData) {
    # Retrieve user information from the CSV
    $vorname = $row.'Vorname (bürgerlich)'
    $nachname = $row.'Nachname (bürgerlich)'
	$name = $row.'Name (bevorzugt)'
    $abteilung = $row.'Abteilung'
	$position = $row.'Position'
	$mobil = $row.'Telefonnummer (geschäftlich)'
	
    # Construct the SamAccountName from Vorname and Nachname
    $samAccountName = $name
	
	Write-Host "SamAccountName: '$samAccountName'"

    # Query Active Directory to retrieve the user object
    $user = Get-ADUser -Identity $SamAccountName -Properties Vorname, Nachname, Name, Abteilung, Position, Mobil

    # Compare user attributes and update AD if necessary
if ($user) {
    if ($user.Vorname -ne $vorname) {
        Set-ADUser -Identity $samAccountName -Vorname $vorname -WhatIf
    }
    if ($user.Nachname -ne $nachname) {
        Set-ADUser -Identity $samAccountName -Nachname $nachname -WhatIf
    }
	if ($user.Nname -ne $name) {
        Set-ADUser -Identity $samAccountName -Name $name -WhatIf
    }
    if ($user.Abteilung -ne $abteilung) {
        Set-ADUser -Identity $samAccountName -Abteilung $abteilung -WhatIf
    }
    if ($user.Position -ne $position) {
        Set-ADUser -Identity $samAccountName -Position $position -WhatIf
    }
    if ($user.Mobil -ne $mobil) {
        Set-ADUser -Identity $samAccountName -Mobil $mobil -WhatIf
    }
}
    else {
        Write-Host "User with SamAccountName '$samAccountName' not found in Active Directory."
    }
}

#Press any key before closing the PowerShell window
Write-Host "Press any key to close this window..."
$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown')

Now at least read from the file, good, but I see some errors:

SamAccountName: 'John Lennon'
Get-ADUser : Mindestens eine Eigenschaft ist ungültig.
Parametername: Vorname
In C:\script.ps1:20 Zeichen:13
+     $user = Get-ADUser -Identity $SamAccountName -Properties Vorname, ...
+             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (John Lennon:ADUser) [Get-ADUser], ArgumentException
    + FullyQualifiedErrorId : ActiveDirectoryCmdlet:System.ArgumentException,Microsoft.ActiveDirectory.Management.Comm
   ands.GetADUser

User with SamAccountName 'John Lennon' not found in Active Directory.
5

The sAMAccountName of an Active Directory account is a very specific and across the domain a unique attribute. The same goes for the DistinguishedName. I’d recommend using one of them to be able to clearly identify the desired accounts. :point_up:t3: :point_up:t3:

The name attribute can exist multiple times in a domain. It only has to be unique across the OU. Using this attribute can lead to errors that are difficult to identify.

Where do you get the input data from? If you get it from someone else you should insist of getting at least one unique attribute. If you create it yourself you should read the sAMAccountName from the AD.

6

Check your CSV.
There are several fields in AD related to name.
GivenName (First name)
Surname (Last name)
Name (Surname, GivenName)
Samaccountname (Can vary, but generally is a shortened Username)

Eg.,
GivenName - John
Surname - Doe
Name - Doe, John
Samaccountname - Doej, jdoe (really just depends on your naming convenention)

The first 3 names in this scenario are NOT names you can use to find with get-aduser -identity.

So what Olaf said, ask for samaccountname, or if you’re doing it yourself, pull the samaccountname when populating the CSV.

7

Hi, thanks for your reply.

I think the problem is that the .csv doesn´t have Samaccountname field at all. The .csv is exported from our Human Resources portal and though there are a lot of fields to be selected for the export, Samaccountname is none one of them. I tried to do it via email address as is unique as well, but it doesn´t work.

I guess is not other way to do it? :frowning:

8

It may have another name. Do they have “logon name” or “windows user name” or something like this?

It does actually. You just have to use the parameter -Filter instead of -Identity.

Depending on your configuration you could use the UserPrincipalName with the parameter -Identity. In most companies this unique AD attribute is set equal to the email address.

9

Cool, many thanks for your reply. I have changed the line and now it looks like:

$samAccountName = $mail

$user = Get-ADUser -Filter {samAccountName -eq $samAccountName} -Properties givenName, sn, displayName, department, title, mobile, mail

Now at least I don´t see any errors at all, but

User with samAccountName 'email@host.de' not found in Active Directory.

10

This is unnecessary.

Again … The sAMAccountName is a specific attribute in AD and is usually NOT equal to the email address. In most environments I know of it is set equal to the UserPrincipalName.

But since the email address is usually unique in an AD as well you can use a filter like this:

$user = Get-ADUser -Filter "mail -eq '$mail'" -Properties givenName, sn, displayName, department, title, mobile, mail

I did a mistake … I have to correct myself:

That is wrong. For the parameter -Identity you only can use:

  • A distinguished name
  • A GUID (objectGUID)
  • A security identifier (objectSid)
  • A SAM account name (sAMAccountName)

Here you can read more about:

1 Like
11

testing several things right now. Main problem is that the .csv file doesn´t have a SamAccountName field at all, as it comes from a Human Resources portal. I tried your both suggestion and they throw the same errors as before. With the one before, I can´t see errors at least, just that “not found in Active Directory” which is funny because is the email address and it´s on AD 100%.

Maybe the problems are in my “if”?

12

What “if”? Please share the code you’re using.

1 Like
13 
# Import the CSV file
$csvData = Import-Csv -Path 'C:\mydata.csv'

# Loop through each row in the CSV
foreach ($row in $csvData) {
    # Retrieve user information from the CSV
    $vorname = $row.'Vorname (bürgerlich)'
    $nachname = $row.'Nachname (bürgerlich)'
	$name = $row.'Name (bevorzugt)'
    $abteilung = $row.'Abteilung'
	$position = $row.'Position'
	$mobil = $row.'Telefonnummer (geschäftlich)'
	$mail = $row.'E-Mail'
	
    # Construct the SamAccountName from user´s email
    $samAccountName = $mail
	
	Write-Host "SamAccountName: '$samAccountName'"

    # Query Active Directory to retrieve the user object
    #$user = Get-ADUser -Identity $samAccountName -Properties givenName, sn, displayName, department, title, mobile, mail
	$user = Get-ADUser -Filter {samAccountName -eq $samAccountName} -Properties givenName, sn, displayName, department, title, mobile, mail

    # Compare user attributes and update AD if necessary
if ($user) {
    if ($user.givenName -ne $vorname) {
        Set-ADUser -Identity $samAccountName -givenName $vorname -WhatIf
    }
    if ($user.sn -ne $nachname) {
        Set-ADUser -Identity $samAccountName -sn $nachname -WhatIf
    }
	if ($user.displayName -ne $name) {
        Set-ADUser -Identity $samAccountName -displayName $name -WhatIf
    }
    if ($user.department -ne $abteilung) {
        Set-ADUser -Identity $samAccountName -department $abteilung -WhatIf
    }
    if ($user.title -ne $position) {
        Set-ADUser -Identity $samAccountName -Position $position -WhatIf
    }
    if ($user.mobile -ne $mobil) {
        Set-ADUser -Identity $samAccountName -mobile $mobil -WhatIf
    }
	if ($user.mail -ne $mail) {
        Set-ADUser -Identity $samAccountName -mail $mail -WhatIf
    }
}
    else {
        Write-Host "User with samAccountName '$samAccountName' not found in Active Directory."
    }
}

#Press any key before closing the PowerShell window
Write-Host "Press any key to close this window..."
$null = $Host.UI.RawUI.ReadKey('NoEcho,IncludeKeyDown')
14

I thought we already agreed on dropping the use of the sAMAccountName!? :smirk:

You’re using the wrong filter and your code is quite inefficient. In the worst case you’re calling the cmdlet Set-ADUser 6 times for each single user account you have in your input CSV. :point_up:t3:
Instead you should use a hashtable and splatting. This way you can assemble the desired attribute depending on the existing values in the AD and call Set-ADUser only once.

$NotFoundList = @()

Foreach($row in $CSVData){

    $User = Get-ADUser -Filter "mail -eq '$($row.'E-mail')'" -Properties DisplayName, Department, Title, Mobile, Mail

    $SetADUserSplatHash = @{
        Identity = $User.sAMAccountName
    }

    if ($user) {
        if ($user.GivenName -ne $($row.'Vorname (bürgerlich)')) {
            $SetADUserSplatHash.Add('GivenName',$($row.'Vorname (bürgerlich)'))
        }
        if ($user.Surname -ne $($row.'Nachname (bürgerlich)')) {
            $SetADUserSplatHash.Add('Surname',$($row.'Nachname (bürgerlich)'))
        }
	    if ($user.DisplayName -ne $($row.'Name (bevorzugt)')) {
            $SetADUserSplatHash.Add('DisplayName',$($row.'Name (bevorzugt)'))
        }
        if ($user.Department -ne $($row.Abteilung)) {
            $SetADUserSplatHash.Add('Department',$($row.Abteilung))
        }
        if ($user.Title -ne $($row.Position)) {
            $SetADUserSplatHash.Add('Position',$($row.Position))
        }
        if ($user.Mobile -ne $($row.'Telefonnummer (geschäftlich)')) {
            $SetADUserSplatHash.Add('Mobile',$($row.'Telefonnummer (geschäftlich)'))
        }
        Set-ADUser @SetADUserSplatHash  -WhatIf
    }
    else {
        $NotFoundList += $row
    }
}

$NotFoundList

Outputting verbose information during the runtime with Write-Host slows your code down as well. Either you should use Write-Verbose wich you only enable on demand. Or you simply collect the unavailable accounts in a list and output this list later on or use it to create a response for the department provided that input data.

Here you can read more about hastables and splatting:

15

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.