====== 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.
# once per server
Install-Module ActiveDirectory
Let's start from the same input we managed into [[en:powershell:start|basics part]].
Samaccountname,Mail
abc123,abc123@domain.com
def123,def123@domain.com
ghi123,ghi123@test.com
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//.
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.
"Samaccountname","Mail","AdActiveUser","MailMatch"
"abc123","abc123@domain.com","yes","yes"
"def123","def123@domain.com","yes","no"
"ghi123","ghi123@test.com","no","no"
"Samaccountname","Mail","AdActiveUser","MailMatch"
"abc123","abc123@domain.com","yes","yes"
"def123","def123@domain.com","yes","no"
"ghi123","ghi123@test.com","no","no"