From basics, we know how to:

• read CSV file
• write CSV file with new columns
• test email format from one column

Now let's discover how to query one Active Directory. First a new PowerShell module is required to deal with AD.

# once per server
Install-Module ActiveDirectory

Let's start from the same input we managed into basics part.

input.csv

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

Previously 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.

csvLevel2.ps1

Import-Module ActiveDirectory
 
Import-Csv -Path input.csv | foreach {
 
    # save current object into a local variable
    $currentLine = $_
 
    # retrieve 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 we are querying AD (UTF-8)
Export-Csv -Path preCheck.csv -NoTypeInformation -Encoding UTF-8

This script will output CSV file with two new columns.

preCheck.csv

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