====== PowerShell ======

===== Advanced =====

From [[en:powershell:start|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.

<code powershell>
# once per server
Install-Module ActiveDirectory
</code>

Let's start from the same input we managed into [[en:powershell:start|basics part]].

<csv>
Samaccountname,Mail
abc123,abc123@domain.com
def123,def123@domain.com
ghi123,ghi123@test.com
</csv>

<file csv input.csv>
Samaccountname,Mail
abc123,abc123@domain.com
def123,def123@domain.com
ghi123,ghi123@test.com
</file>

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

<file powershell 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
</file>

This script will output CSV file with two new columns.

<file csv preCheck.csv>
"Samaccountname","Mail","AdActiveUser","MailMatch"
"abc123","abc123@domain.com","yes","yes"
"def123","def123@domain.com","yes","no"
"ghi123","ghi123@test.com","no","no"
</file>

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