power shell script to compare 2 csv and give only inactive users

Question

I have 2 csv files.1st contains all the user info from AD.2nd once contains selected users from AD. I would want to compare these 2 files. I have the script which gives me matching users but i want the non-matching csv as output. Masterlist.csv contains 250 users .I want the inactive users only in mastelist.csv.I have attached my script below any help would be greatly apprectiated.
cls
$OrdersA = Import-CSV -Path C:\Users\renu\Desktop\renu\Masterlist.csv
$OrdersB = Import-CSV -Path C:\Users\renu\Desktop\renu\ALLUserMain30082021.csv
$matchcounter = 0
$start = [system.datetime]::Now
foreach ($order1 in $OrdersA){
$matched = $false
foreach ($order2 in $OrdersB){
$obj = "" | select "e-mailaddress","Last Name","Legal First Name","Anglicized Western First Name","Last/First Legal","Job Title", "Job Location"
if(($order1.'e-mailaddress') -eq $order2.'EmailAddress' ){
$matchCounter++
$matched = $true
$obj.'e-mailaddress' = $order1.'e-mailaddress'
$obj.'Last Name' = $order1.'Last Name'
$obj.'Legal First Name' = $order1.'Legal First Name'
$obj."Anglicized Western First Name" = $order1."Anglicized Western First Name"
$obj.'Last/First Legal' = $order1.'Last/First Legal'
$obj.'Job Title' = $order1.'Job Title'
$obj.'Job Location' = $order1.'Job Location'
Write-Host "Match Found Orders " "$matchCounter"
$obj | Export-Csv -Path C:\Users\renu\Desktop\renu\Report_Orders.csv -Append -NoTypeInformation
}
}
}
$end = [system.datetime]::Now
$resultTime = $end - $start
Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."

Answer 1

Make two passes over the $OrdersA hash:

# find matched & unmatched users in this list (i.e. difference)
$OrdersA = @{ }
Import-Csv -Path C:\Junk\Masterlist.csv |
    ForEach-Object {
        $OrdersA[$_.'e-mailaddress'] = $_ |
            Select-Object "e-mailaddress", "Last Name", "Legal First Name", "Anglicized Western First Name", "Last/First Legal", "Job Title", "Job Location", @{n = 'Matched'; e = { $false } }
    }
$start = [system.datetime]::Now
Import-Csv -Path C:\Junk\ALLUserMain30082021.csv | # using this list as the reference
ForEach-Object {
    if ($OrdersA.ContainsKey($_.EmailAddress)) {
        $OrdersA[$_.EmailAddress].Matched = $true
    }
}
$OrdersA.GetEnumerator() |
    ForEach-Object {
        if ($_.Value.Matched){
            $_.Value
        }
    } | Export-Csv -Path C:\Junk\Matched_Report_Orders.csv -NoTypeInformation
$OrdersA.GetEnumerator() |
    ForEach-Object {
        if (-not $_.Value.Matched){
            $_.Value
        }
    } | Export-Csv -Path C:\Junk\Not_Matched_Report_Orders.csv -NoTypeInformation

$end = [system.datetime]::Now
$resultTime = $end - $start
Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."

Answer 2

Is it okay to place matched and unmatched in the same CSV? If not you can do the enumeration of the hash twice and select only the true/false values of each item in the hash, exporting them into separate CSVs.

# find matched & unmatched users in this list (i.e. difference)
$OrdersA = @{}
Import-Csv -Path C:\Junk\Masterlist.csv |
    ForEach-Object{
        $OrdersA[$_.'e-mailaddress'] = $_ |
            Select-Object "e-mailaddress", "Last Name", "Legal First Name", "Anglicized Western First Name", "Last/First Legal", "Job Title", "Job Location", @{n='Matched';e={$false}}
    }
$start = [system.datetime]::Now
Import-Csv -Path C:\Junk\ALLUserMain30082021.csv |   # using this list as the reference
    ForEach-Object{
        if ($OrdersA.ContainsKey($_.EmailAddress)){
            $OrdersA[$_.EmailAddress].Matched = $true
        }
    }
$OrdersA.GetEnumerator() |
    ForEach-Object{
        $_.Value
    } | Export-Csv -Path C:\Junk\Report_Orders.csv -NoTypeInformation

$end = [system.datetime]::Now
$resultTime = $end - $start
Write-Host "Execution took : $($resultTime.TotalSeconds) seconds."

Answer 3

Hi Rich,

The script works fine but it gives me wrong values.
Say Mr X@ssss .com is a active user in AD but this script adds him into the list of inactive users.
So when i compare the AD with the list of active and inactive users there are so many users who are interchanged.
Is there a way to check this?

or is there a way to compare the csv directly with AD and export active and inactive.csv files?
Could you guide?

Regards,
Renu.

Answer 4

If you only want to verify that the e-mail address exists in the ad and if the associated user is enabled, then this might be all you need:

Import-Csv Import-Csv -Path C:\Junk\Masterlist.csv |
    ForEach-Object{
        $u = Get-ADUser -Filter "mail -eq '$($_.emailaddress)'"
        if ($u){
            Select-Object @{n='emailaddress'; v=$u.mail}, enabled, @{n='emailaddress found in AD';v=$true}
        }
        else{
            "" | Select-Object @{n='emailaddress'; v=$u.mail}, @{n='enabled';v='unknown'}, @{n='emailaddress found in AD';v=$false}
        } Export-CSV c:\junk\IserMatch.csv -NoTypeInfo