Differences

This shows you the differences between two versions of the page.

--- en:powershell:advanced [2021/03/15 20:18] – lonclegr
+++ en:powershell:advanced [2021/03/16 21:51] (current) – lonclegr
@@ Line 9: / Line 9: @@
-Now let's discover how to query one Active Directory. First one new PowerShell module is required to deal with AD.
+Now let's discover how to query one Active Directory. First a new PowerShell module is required to deal with AD.
 <code powershell>
@@ Line 32: / Line 32: @@
 </file>
-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//.
+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>