question

RenuMurali-7202 avatar image
0 Votes"
RenuMurali-7202 asked RichMatheisen-8856 answered

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

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

windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RenuMurali-7202 edited

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."
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TThank you Rich.
The script works perfectly.

Regards,
Renu

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered RenuMurali-7202 commented

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."
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RenuMurali-7202 avatar image
0 Votes"
RenuMurali-7202 answered RenuMurali-7202 edited

Hi Rich,

The script works fine but it gives me wrong values.
Say Mr X@example.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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The script will only give you the outcome you asked for! If the data you're given is incorrect then the answers you get will only be as good as your data! In other words, the old adage "Garbage in, garbage out" proves true once again!

So, are you now asking to validate the contents of the MasterList.csv file, or the ALLUserMain30082021.csv file? Either way the script would just import one of the CSVs and then do a Get-ADUser for each row. Select the properties you want (including the "enabled" property) and export the results to another CSV. Just be sure the CSV has a column that holds a unique ID for the users -- the samAccountName is one of those.

0 Votes 0 ·

Hi Rich,

I wanted to know if i can compare my Masterlist.csv(provided by HR) directly with the users in the AD to find active and inactive users.So there is no discrepency in the data exported.

I have tried checking with the csv which has samAccountName header and it works however in the Masterlist.csv we dont have samAccountName column.

The headers of the Master list.csv contains
emailaddress,LastName,Legal First Name,Anglicized Western First Name,Last/First Legal,Job Title,Job Location

So the question of comparing this with samAccountName is doubtful.

ii am now confused.

0 Votes 0 ·
RichMatheisen-8856 avatar image
0 Votes"
RichMatheisen-8856 answered

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.