locked
Powershell script to compare .CSV file to AD. Export field names that match & do not match into separate .csv file. RRS feed

  • Question

  • Hello,

    I have a .csv file with the column names "Employee Number, Employee Name, Job, Family, Email Address and SameAccountName". I want to compare this .csv file to active directory. So essentially, if any account names under "SameAccountName" from the .csv file does not match the "SameAccountName" from AD, I want to export the names that does not match into a separate .csv file named "notmatchusers.csv" and for account names that do match, export to a separate .csv named "match.csv". Below is the current script, it exports the data to a .csv file but the data is not correct. I viewed several account names from the .csv file (notmatchusers.csv) and found the names still active in AD so something is not right with my script.  I would like to continue to use this script with slight modifications if possible so that it works. Please help!

    Note: OU names were changed to example

    Thanks!

    # Import CSV from HR, headers should be "employee number", "employee name", "job family", "networkid"
    $actives = import-csv -path "C:\Users\hello\Desktop\HR_Data\actives.csv"
    # pull all users in the OU's
    $adUsers = get-aduser -SearchBase "ou=example,ou=example,dc=example,dc=example" -LDAPfilter cn=${enabled -eq $true} -Properties samaccountname
    # array of users that match our known good input
    $matchUsers = @()
    # array of users NOT matching our known good input
    $notMatchUsers = @()

    foreach ($user in $adUsers){

        #first try networkID (Which should match exactly)
        if ($actives.samaccountname -eq $user.samaccountname) {
            $matchUsers += $user
        } 
        else {

        $notMatchUsers += $user
        }


    }

    $matchUsers | export-csv  -NoTypeInformation -Path ".\match.csv"
    $notmatchusers | export-csv   -NoTypeInformation -path ".\notmatch.csv"


    Thursday, January 11, 2018 11:27 PM

Answers

  • Hi,

    The "notmatch.csv" line above actually compares all the difference between the CSV file and AD.
    Based on your description, if you only need to compare users who do not exist in AD, please have a try with the following command:
    Compare-Object -ReferenceObject $csv.samAccountName -DifferenceObject $ad.samAccountName | 
    Where-Object {$_.SideIndicator -eq '<='} | Select-Object -Property InputObject | 
    Export-Csv -Path .\notmatchusers.csv -NoTypeInformation

    In addition, the following demo script may help you understand Compare-Object better:
    $a = 'a', 'b', 'c'
    $b = 'a', 'd', 'e'
    Compare-Object -ReferenceObject $a -DifferenceObject $b

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, January 17, 2018 4:11 AM
  • If you dont want to help, please just keep to yourself.
    I love to help. Otherwise I wouldn't be here. But I expect some colaboration. If you had posted some code you tried and got errors I would be the last not trying to help but if I have the impression someone is just lazy I'll point that out.  ;-)

    I usually try to figure out myself but I am stuck.

    Where do you stuck? You just have to import the CSV file with Import-CSV, pipe it to a Foreach-Object loop and use the SamAccountName property fire a Get-ADUser to get the desired properties.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    • Marked as answer by H3LP_M3 Friday, January 19, 2018 5:50 PM
    Friday, January 19, 2018 4:30 PM

All replies

  • Hello and welcome.

    First: please format your code as code with the according option in the post editor. This way you prevent unwanted line breaks and the code is easier to read, to understand and to debug if needed.  ;-)

    For comparisons of objects Powershell has the cmdlet Compare-Object. It could make your live much easier and you code cleaner and more "elegant" or sophisticated.  ;-) :-D


    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Thursday, January 11, 2018 11:36 PM
  • Sorry about that.

    Please see below for the correct format.

    # Import CSV from HR, headers should be "employee number", "employee name", "job family", "networkid"
    $actives = import-csv -path "C:\Users\hello\Desktop\HR_Data\actives.csv"
    # pull all users in the OU's
    $adUsers = get-aduser -SearchBase "ou=example,ou=example,dc=example,dc=example" -LDAPfilter cn=${enabled -eq $true} -Properties samaccountname
    # array of users that match our known good input
    $matchUsers = @()
    # array of users NOT matching our known good input
    $notMatchUsers = @()
    
    foreach ($user in $adUsers){
    
        #first try networkID (Which should match exactly)
        if ($actives.samaccountname -eq $user.samaccountname) {
            $matchUsers += $user
        } 
        else {
    
        $notMatchUsers += $user
        }
    
    
    }
    
    $matchUsers | export-csv  -NoTypeInformation -Path ".\match.csv"
    $notmatchusers | export-csv   -NoTypeInformation -path ".\notmatch.csv"
    

    Thursday, January 11, 2018 11:49 PM
  • My recommendation remains the same. Please have a look at the help for Compare-Object, including the examples. IMHO it delivers exactly the functionality you're after.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Friday, January 12, 2018 12:01 AM
  • Thank you BOfH_666,

    Would you kindy format it for me =).

    I forgot to mention I am a newbie with Powershell and did not create this original script.

    Thanks

    Friday, January 12, 2018 12:44 AM
  • Thank you BOfH_666,

    Would you kindy format it for me =).

    I forgot to mention I am a newbie with Powershell and did not create this original script.

    Thanks

    You are expected to do your own code editing.  If you do not know basic PowerShell then you will need to take the time to learn it.

    The contributor's here are also busy and do not have time to write code for you.  That is not the purpose of ANY technical forum. 


    \_(ツ)_/

    Friday, January 12, 2018 1:05 AM
  • Hi,

    According to your description, I wrote this demo script for your reference, hope it is helpful to you:
    $csv = Import-Csv -Path C:\Users\hello\Desktop\HR_Data\actives.csv
    $ad = Get-ADUser -Filter "Enabled -eq 'True'" -SearchBase 'ou=example,ou=example,dc=example,dc=example'
    
    Compare-Object -ReferenceObject $csv.samAccountName -DifferenceObject $ad.samAccountName -IncludeEqual -ExcludeDifferent | Select-Object -Property InputObject | Export-Csv -Path .\match.csv -NoTypeInformation
    Compare-Object -ReferenceObject $csv.samAccountName -DifferenceObject $ad.samAccountName | Select-Object -Property InputObject | Export-Csv -Path .\notmatchusers.csv -NoTypeInformation

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Friday, January 12, 2018 6:33 AM
  • Hello,

    i was working on the same project - comparing HR with AD

    Had a different approach to do this.

    1. created hashtable from HR and AD

    $AD = ....  |group employeenumber -AsHashTable
    $HR = |group employeenumber -AsHashTable

    2. selected unique keys

    $employeenumbers = $($HR.keys) + $($AD.keys) | select -unique

    3. created custom table containing both AD and HR records

    write-host ".. creating custom hashtable, please wait ..." 
    $mycol = @()
     foreach ($number in $employeenumbers) {
      try   {
            $lastlogon = $ad[$number].lastlogondate.Split(" ")[0]
            }
      catch {
            $lastlogon = ""
            }
      $myobject = New-Object psobject -Property ([ordered]@{
       OsobniCislo = $hr[$number].employeenumber
       ADName = $hr[$number].name
       AD_Number = $ad[$number].employeenumber
       AD_Name = $ad[$number].name
       AD_Enabled =$ad[$number].enabled
       AD_LastLogon = $lastlogon
    ... few more entries here
    
        })
      $mycol += $myobject
      

    After i got my new table, i could easily query differencies

    ## Missing in HR, AD Enabled
        $Vemamissing = $mycol | ?{!$_.EmployeeNumber -and $_.AD_Enabled -eq $true}
        $body += "<br>" 
        $body += "<b><font color=red>Povolené účty bez záznamu ve VEMA exportu! </font></b><br>"
        $body += ($Vemamissing |ConvertTo-Html -head $header)
    
        
    ## Enabled accounts which should be disabled
        $VemaVystup = $mycol |?{$_.ad_enabled -eq $true -and $_.Vystup }| 
                            select *,@{n="DateTime";e={[datetime]$_.Vystup}} |?{$_.datetime -lt $(get-date)} |
                            select * -ExcludeProperty datetime
        $body += "<br>" 
        $body += "<b><font color=red>Ukončen pracovní poměr, v AD povoleno </font></b><br>"
        $body += ($VemaVystup |ConvertTo-Html)
    ...

    Then i created HTML file from the results

    ## output to file
        set-Content $htmlreport $body -Encoding UTF8
    .. and used send-mailmessage for sending scheduled reports
    Send-MailMessage @messageParameters -body $messagebody -subject "HR x AD" -Attachments $htmlreport -BodyAsHtml -Encoding UTF8


    • Edited by Mekac Friday, January 12, 2018 8:54 AM
    Friday, January 12, 2018 8:47 AM
  • Hi,

    Just checking in to see if the information provided was helpful. Does the script work?

    Please let us know if you would like further assistance.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Tuesday, January 16, 2018 2:32 AM
  • Hi Alert,


    Thank you so much for the code. It looks like it does do some comparison but the output .csv file (notmatch.csv) contains data/strings that are currently in AD.

    Should I be using a different cmdlet instead of "Compare-Object"? to compare the strings and ignore if the letters are upper/lowercase?

    Thanks

    Tuesday, January 16, 2018 8:39 PM
  • Hi,

    The "notmatch.csv" line above actually compares all the difference between the CSV file and AD.
    Based on your description, if you only need to compare users who do not exist in AD, please have a try with the following command:
    Compare-Object -ReferenceObject $csv.samAccountName -DifferenceObject $ad.samAccountName | 
    Where-Object {$_.SideIndicator -eq '<='} | Select-Object -Property InputObject | 
    Export-Csv -Path .\notmatchusers.csv -NoTypeInformation

    In addition, the following demo script may help you understand Compare-Object better:
    $a = 'a', 'b', 'c'
    $b = 'a', 'd', 'e'
    Compare-Object -ReferenceObject $a -DifferenceObject $b

    If you need further help, please feel free to let us know.

    Best Regards,
    Albert

    Please remember to mark the replies as an answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Wednesday, January 17, 2018 4:11 AM
  • Thank you Albert!

    This script works perfectly!!

    Wednesday, January 17, 2018 6:00 PM
  • Hi Albert,

    Thanks again for helping with the script (works excellent).

    I was wondering with the output .csv file (notmatchusers.csv), how can I take the samaccountname on this spreadsheet and run it against AD to get the "Full Name", "Last Logon" and "Account Creation".

    Thanks!

    Thursday, January 18, 2018 10:05 PM
  • I was wondering with the output .csv file (notmatchusers.csv), how can I take the samaccountname on this spreadsheet and run it against AD to get the "Full Name", "Last Logon" and "Account Creation".

    Don't you think it would be an appropriate behaviour when you spend a little effort by yourself? 

    And ... if you have another question you should create a new post with this new question and not extend your original question.

    What you're asking for has been asked thousand times here and everywhere else. It should be easy even for you to figure that out by yourself actually.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    Thursday, January 18, 2018 10:52 PM
  • I am new to scripting and I am in the process of learning PS.

    I usually try to figure out myself but I am stuck.

    His samples helped me out alot and I have learned from them.

    If you dont want to help, please just keep to yourself.


    Friday, January 19, 2018 4:16 PM
  • I am new to scripting and I am in the process of learning PS.

    I usually try to figure out myself but I am stuck.

    His samples helped me out alot and I have learned from them.

    If you dont want to help, please just keep to yourself.


    The issue is that you are asking for a free tutorial in basic PowerShell.  There are numerous sites that can give you this.  This is a technical forum for scripters and a question.  You have been doing this for over a week now.  That is not the purpose of technical forums.

    Start here: Learn PowerShell  

    Example scripts are here: Script Gallery.


    \_(ツ)_/

    Friday, January 19, 2018 4:23 PM
  • If you dont want to help, please just keep to yourself.
    I love to help. Otherwise I wouldn't be here. But I expect some colaboration. If you had posted some code you tried and got errors I would be the last not trying to help but if I have the impression someone is just lazy I'll point that out.  ;-)

    I usually try to figure out myself but I am stuck.

    Where do you stuck? You just have to import the CSV file with Import-CSV, pipe it to a Foreach-Object loop and use the SamAccountName property fire a Get-ADUser to get the desired properties.

    Best regards,

    (79,108,97,102|%{[char]$_})-join''

    • Marked as answer by H3LP_M3 Friday, January 19, 2018 5:50 PM
    Friday, January 19, 2018 4:30 PM
  • Hi Everyone,

    I really like the simplicity of the H3LP_M3 method, I am trying to modify the script, so i can check bunch of users from a CSV file to see if they exists in our AD.

    There are 4 columns in my csv, one is first_name, last_name, employee_number, employee_email

    I trying to check all the four columns please see my script below:

    ...I need to concate first_name and Last_name (Trim) etc.

    Can i improve this slightly better and any suggestion is highly appreciated.

    # Import CSV from HR, headers should be "employee_number", "first_name", "last_name","employee_email","job family"$actives = import-csv -path "C:\Users\hello\Desktop\HR_Data\actives.csv"
    
    # pull all users in the OU's
    
    $adUsers = get-aduser -SearchBase "ou=example,ou=example,dc=example,dc=example" -LDAPfilter cn=${enabled -eq $true} -Properties samaccountname
    
    # array of users that match our known good input
    
    $matchUsers = @()# array of users NOT matching our known good input
    
    $notMatchUsers = @()foreach ($user in $adUsers)
    {  
     
    #first try employee_number (Which should match exactly)    
    
    if ($actives.employee_number -eq $user.samaccountname) {        $matchUsers += $user    }	
    
    #Second try First Name (Which should match exactly)    
    
    if ($actives.first_name -eq $user.samaccountname) {        $matchUsers += $user    }		
    
    #Third try Last Name (Which should match exactly)    
    
    if ($actives.last_name -eq $user.samaccountname) {        $matchUsers += $user    }		
    
    #Fourth try empoyee_email (Which should match exactly)    
    
    if ($actives.last_name -eq $user.samaccountname) {        $matchUsers += $user    }		    
    
    else {    $notMatchUsers += $user    }}   
    
    ##End of Loop
    
    $matchUsers | export-csv  -NoTypeInformation -Path ".\match.csv"
    $notmatchusers | export-csv   -NoTypeInformation -path ".\notmatch.csv"

    # Import CSV from HR, headers should be "employee_number", "first_name", "last_name","employee_email","job family"
    $actives = import-csv -path "C:\Users\hello\Desktop\HR_Data\actives.csv"
    # pull all users in the OU's
    $adUsers = get-aduser -SearchBase "ou=example,ou=example,dc=example,dc=example" -LDAPfilter cn=${enabled -eq $true} -Properties samaccountname
    # array of users that match our known good input
    $matchUsers = @()
    # array of users NOT matching our known good input
    $notMatchUsers = @()

    foreach ($user in $adUsers){

        #first try employee_number (Which should match exactly)
        if ($actives.employee_number -eq $user.samaccountname) {
            $matchUsers += $user
        }
    #Second try First Name (Which should match exactly)
        if ($actives.first_name -eq $user.samaccountname) {
            $matchUsers += $user
        }

    #Third try Last Name (Which should match exactly)
        if ($actives.last_name -eq $user.samaccountname) {
            $matchUsers += $user
        }

    #Fourth try empoyee_email (Which should match exactly)
        if ($actives.last_name -eq $user.samaccountname) {
            $matchUsers += $user
        }


        else {

        $notMatchUsers += $user
        }


    }   ##End of Loop




    Monday, November 12, 2018 10:43 AM
  • Hi Everyone,

    I really like the simplicity of the H3LP_M3 method, I am trying to modify the script, so i can check bunch of users from a CSV file to see if they exists in our AD.




    Please do NOT add a new question to another users closed and answered post. You must start your own question. This forum is nto for fixing or modifying scripts you have found. You must as a specific question about your script.


    \_(ツ)_/

    Monday, November 12, 2018 10:51 AM