Grâce aux bases, nous savons désormais:

• lire des fichiers CSV
• écrire des fichiers CSV avec de nouvelles colonnes
• effectuer un test sur une colonne comme le courriel

Maintenant, découvrons comment adresser des requêtes AD. Tout d'abord il faut installer un module requis.

# once per server
Install-Module ActiveDirectory

Repartons du même fichier d'entrée que celui des bases.

input.csv

Samaccountname,Mail
abc123,abc123@domain.com
def123,def123@domain.com
ghi123,ghi123@test.com

Before we tested mail addresses in an easy way : does it end with “domain.com” ?. New tests with AD queries, Samaccountname must belong to an active user from AD and AD user mail must be equal to mail from CSV file.

Dans un premier temps, on faisait un simple test : est-ce que le courriel se termine par “domain.com” ?. Maintenant, testons en se reposant sur l'AD, on doit trouver un utilisateur AD actif avec ce Samaccountname et dont le courriel correspond à l'adresse fournie dans le CSV.

csvLevel2.ps1

Import-Module ActiveDirectory
 
Import-Csv -Path input.csv | foreach {
 
    # save current object into different variable
    $currentLine = $_
 
    # deal with columns
    $mail = $currentLine.Mail
    $sam = $currentLine.Samaccountname
 
    # let's query AD with SAM
    try {
        # we are using Get-AdUser with parameter -Identity
        # in this case, expected result is either one AdUser or nothing
        # if no result is found then Exception is thrown
        # that's why we are in a try -- catch block
        $user = Get-AdUser -Identity $sam -Properties mail
 
        # if the script goes here
        # then it means that one AD active user has been found
        Write-Verbose ("AD active user found with SAM={0}" -f $sam)
        $currentLine | Add-Member -MemberType NoteProperty -Name "AdActiveUser" -Value "yes"
 
        # second test
        # Do mails from AD and from CSV match ?
        if ($user.mail -eq $mail) {
            Write-Verbose ("{0}(AD) is equal to {1}(CSV): perfect match" -f $user.mail, $mail)
            $currentLine | Add-Member -MemberType NoteProperty -Name "MailMatch" -Value "yes"
        } else {
            Write-Verbose ("{0}(AD) is NOT equal to {1}(CSV)" -f $user.mail, $mail)
            $currentLine | Add-Member -MemberType NoteProperty -Name "MailMatch" -Value "no"
        }
    } catch {
        # if the script goes here
        # then it means that no AD active user has been found
        Write-Verbose ("No AD active user found with SAM={0}" -f $sam)
        $currentLine | Add-Member -MemberType NoteProperty -Name "AdActiveUser" -Value "no"
        $currentLine | Add-Member -MemberType NoteProperty -Name "MailMatch" -Value "no"        
    }
 
    # return updated currentLine with new column
    $currentLine
} |
# export result line by line to CSV
# -NoTypeInformation prevents metadata from being exported
# -Encoding is specified because of the fact we are querying AD (UTF-8)
Export-Csv -Path preCheck.csv -NoTypeInformation -Encoding UTF-8

L'exécution du script va produire un fichier CSV avec deux nouvelles colonnes.

preCheck.csv

"Samaccountname","Mail","AdActiveUser","MailMatch"
"abc123","abc123@domain.com","yes","yes"
"def123","def123@domain.com","yes","no"
"ghi123","ghi123@test.com","no","no"