Comparing CSV to Active Directory, then output fields to a table

I updated my code suggestion above. Take look to see what I mean.

Aha, my apologies. “2 differently named columns” It’s so crazy how words mean things…

1 Like

Holy moly we might be on to something now. I’m seeing how the order of what is reference versus Difference matters too in what is stored in the variable. Turned those around and i think i’m starting to get what i need. YOU GUYS ARE AWESOME!!

This was a great lesson today and i owe you guys. First 3 rounds of your favorite pints on me!

1 Like

Unfortunately, i need to do a bit more with this script :frowning:

As it works now, it compares the csv and active directory and exports only what matches. I need to have it only put in the array those that have email addresses different from the csv and active directory. I have tried to do. I also added some items to the array.
The If statement doesn’t seem to do anything and it ends up giving me everything.
I also might need to get the FirstName and Lastname from the csv file into the array.

$csvfile = import-csv -path C:\file.csv
$activedirectory = get-aduser -filter -properties samaccountname, mail, surname, givenname

$exist = Compare-Object -ReferenceObject $activedirectory -DifferenceObject $csvfile -Property 'SamAccountName' -IncludeEqual -ExcludeDifferent -passthru

If ($csvfile.mail -ne $activedirectory.mail) {

#Specifies the Array Output
$OutArray =
Foreach ($Person in $Exist){
 
  $TodayDate = (Get-Date).AddDays(-1).ToString('MM/dd/yyy')
 
  [PSCustomObject]@{
        XRefCode       = $Person.SamAccountName
        EmployeeNumber = $Person.SamAccountName
        ContactInformationTypeXrefCode = "BusinessEmail"
        EffectiveStart = "$TodayDate"
        IsForSystemCommunication = "0"
        ElectionicAddress  = $Person.mail
        
  }
}
}

Color me stupid, but how is this working without a parameter for -Filter ? Tried this on my Win10 box using RSAT and no joy for me.

That’s the first time you mention this requirement. :confused:

Except of “mail” are all these properties already included in the output set Get-ADUser returns by default. :wink:
Regardless of that - if I understand your code right you actually just need “samaccountname” and “mail” for your final output, isn’t it?
And you may consider using a “-SearchBase” to reduce the stress you put on your AD for this query.

This code line does not make that much sense. You try to check if ALL mail addresses from the CSV file are equal to ALL mail addresses from the AD.
You will have to do 2 separate comparisons in this case.

If you create a variable containing the date of yesterday you should not name it “Today”. :wink:

If I got it right the following should be pretty close to what you want:

$YesterDay = (Get-Date).AddDays(-1).ToString('MM/dd/yyy')
$SearchBase = 'OU=Berlin,OU=Germany,OU=Europe,DC=Contoso,DC=com'
$UserListAD = Get-ADUser -Filter * -SearchBase $SearchBase -Properties mail | Select-Object -Property sAMAccountName, mail
$UserListCSV = Import-Csv -Path C:\file.csv

$UserListSamAccountNameEqual = Compare-Object  -ReferenceObject $UserListAD -DifferenceObject $UserListCSV -Property sAMAccountName -IncludeEqual -ExcludeDifferent -PassThru
$UserListMailDifferent = Compare-Object -ReferenceObject $UserListCSV -DifferenceObject $UserListSamAccountNameEqual -Property mail -PassThru | Where-Object -Property SideIndicator -EQ -Value '=>'
$OutArray =
$UserListMailDifferent |
ForEach-Object {
    [PSCustomObject]@{
        XRefCode                       = $_.SamAccountName
        EmployeeNumber                 = $_.SamAccountName
        ContactInformationTypeXrefCode = 'BusinessEmail'
        EffectiveStart                 = $YesterDay
        IsForSystemCommunication       = 0
        ElectionicAddress              = $_.mail
    }
}
$OutArray

Depending what mail addresses you want to have in your final result you can exchange the values for -ReferenceObject and -DifferenceObject in the second comparison. Play a little bit with it and you’ll see what I mean. :wink:

Yeah, i see how the logic is off. If it even satisfied 1 IF, then it would still run the foreach.
I did manually type this for confidentiality so i might have missed a * in the -Filter section.

Thanks Olaf, i’ll look into this today. Fun how criteria can just suddenly change. I’m just a worker monkey so when the uppers say they want something different, nothing i can do is say “Yes Sir”.

I do not agree about that at all … .if you are the expert it’s the complete opposite. :wink: … it just needs a little bit courage.

Quick question, if for some reason (as i could see this come up) they need me to include the FirstName and LastName not from AD, but from the CSV into the array. How can I pipe that in from the variable from the CSV?

I already have mentioned that right after my last code suggestion. :wink: With the parameter -PassThru you include the -ReferenceObject in the output. You really should start to play with the code and try and error just like we all do. That’s by far the best way to figure things out. AND … you should start to read the help for the cmdlets you’re about to use … all of it … including the examples.

1 Like