Check against HR CSV List of Users against AD and create output csv files. RRS feed

  • Question

  • Hi Everyone,

    I am trying check users from a HR 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'm trying to check all the four columns please see my script below:

    Issues: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=testgroup1,ou=testgroup2,dc=test1.com,dc=test2.com" -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.employee_email -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"


    Monday, November 12, 2018 10:48 AM

All replies

  • You need to fix the obvious errors in your script.  Run the script and fix the errors that occur.

    Please take the time to learn how to format code and how to write a basic script.  Your script is just a run-on of different things with no structure.


    the following line is impossible:

    $adUsers = get-aduser -SearchBase "ou=example,ou=example,dc=example,dc=example" -LDAPfiltercn=${enabled -eq $true} -PropertiesSamAccountName

    It will never return anything.  Your filter syntax is wrong..  Use help to learn how to use the command.


    Monday, November 12, 2018 10:58 AM
  • Your code is still wrong.  Please carefully read the help.  You filter is impossible.

    Please take the time to format your code correctly. It is unreadable as code.

    PowerShell Style Guidelines


    Monday, November 12, 2018 11:55 AM
  • That is why I posted the link.  Read the guide carefully.  It will help you to write code correctly.


    Monday, November 12, 2018 12:03 PM
  • Here is what I come up with ...

    Now i got the variables/filters, 
    $FirstFilter =$Name.first_name 
    $SecondFilter =$Name.last_name 
    $thirdfilter =$name.employee_number 
    $Fourthfilter =$name.employee_email 
    In this example if any of the above filter matches
    set STATUS = PossibleMatch
    and when all the filter matches
    set STATUS = FullMatch
    and when non of the filter matches I would like to
    set STATUS = No Match.
    So, that all the outcome appears in one csv file or multiple.
    Any suggestion is highly appreciated.
    Once again many many thanks for the help.

    here is the final script.

    $names = Import-Csv C:\data.csv
    ForEach ($Name in $Names)
       $FirstFilter   =$Name.first_name
       $SecondFilter  =$Name.last_name
       $thirdfilter   =$name.employee_number
       $Fourthfilter  =$name.employee_email
          $user=Get-ADUser -Filter {GivenName -eq $FirstFilter -and Surname -eq $SecondFilter -and Employeeid -eq $thirdfilter -and emailaddress -eq $Fourthfilter} -Properties EmailAddress,Employeeid 
           if( $User )
            $User |Select-Object -Property Givenname, Surname, Samaccountname, EmailAddress,Employeeid 
        New-Object PSObject -Property @{
                GivenName      = $FirstFilter
                Surname        = $SecondFilter
                STATUS         = 'MISSING ACCOUNT/Or Missing an Attribute'
                EmailAddress   = $Fourthfilter
                EmployeeNumber = $thirdfilter

    Monday, November 12, 2018 1:35 PM
  • What is the issue.  We still need a specific question.

    Here is a better way to format this. You also need to use the code posting tool to post code or the site will break your post in many browsers.

    This is how to post code and this is a better way to format code. Yours is not consistent and very hard to follow and match braces with the eye.

    Your code will not output the results anywhere but the console. 

    Your code will create inconsistent objects which may cause issues if the output is to be used in other places.

    Import-Csv C:\data.csv |
            $FirstFilter   = $_.first_name
            $SecondFilter  = $_.last_name
            $thirdfilter   = $_.employee_number
            $Fourthfilter  = $_.employee_email
            $filter = "GivenName -eq '$FirstFilter' -and Surname -eq '$SecondFilter' -and Employeeid -eq '$thirdfilter' -and emailaddress -eq '$Fourthfilter'" 
            if($user = Get-ADUser -Filter $filter -Properties EmailAddress,Employeeid){
                $user |Select-Object -Property Givenname, Surname, Samaccountname, EmailAddress,Employeeid
                        GivenName      = $FirstFilter
                        Surname        = $SecondFilter
                        STATUS         = 'MISSING ACCOUNT/Or Missing an Attribute'
                        EmailAddress   = $Fourthfilter
                        EmployeeNumber = $thirdfilter


    Monday, November 12, 2018 2:00 PM
  • Many thanks,

    my specific questions are

    1. save the result output to csv file

    2. each row should display a status column in which 

    a) if any of the filter matches, set status column value = "Possible Match"

    b) If all the filter matches, set statsu column value = "All Match"

    c) If non of the filter matches set status column value = "No Match"


    Monday, November 12, 2018 2:12 PM
  • As I noted above.  The objects all have to be identical to save to a CSV.

    Here is where you need to start: Microsoft Virtual Academy - Getting Started with Microsoft PowerShell

    Once you actually learn PowerShell the questions you ask will be obvious.  This forum is not for custom re-writing of scripts.

    This project will be a good one to help you learn PowerShell.


    Monday, November 12, 2018 2:20 PM
  • We will not write your code for you.   If you write the code you are asking for and you have issues we will help but it is entirely tour responsibility to write the code.


    Monday, November 12, 2018 3:29 PM
  • Please I have a script question and I stumble,  are you not able to see two of my scripts above, which has issues? You are wasting time, others will be able to help or correct.  Please no dis-respect to you JRV, but please if you can help answer.  I had scripts both are failing or lack the punch it require.  Please help or go away.


    Monday, November 12, 2018 3:31 PM
  • This will add a new column "Result" into the original array with a "Matched" value if ALL attributes match or "NotMatched" for all other conditions (0 attributes match, 1 match, 2 match, or 3 match).

    $HRDATA =Import-CSV .\input.csv
    ForEach ($item in $HRDATA) {
            $FirstFilter   = $item.first_name
            $SecondFilter  = $item.last_name
            $thirdfilter   = $item.employee_number
            $Fourthfilter  = $item.employee_email
            $filter = "GivenName -eq '$FirstFilter' -and Surname -eq '$SecondFilter' -and Employeeid -eq '$thirdfilter' -and emailaddress -eq '$Fourthfilter'" 
            If (Get-ADUser -Filter $filter -Properties EmailAddress,Employeeid,givenname,surname -server yourdomain.local)
            {$item | Add-Member -MemberType NoteProperty -Name Result -Value 'Matched'}
            Else {$item | Add-Member -MemberType NoteProperty -Name Result -Value 'NotMatched'}
    $HRDATA | Export-CSV .\HR_AD_COMP_RESULT.CSV -NoTypeInformation

    As the code and approach is structured, it is not suitable for "partial" match results.  Suppose you have a "Jane Doe" in the HR feed.  And you have "Jane Smith" and "John Doe" in AD - which AD object are you going to compare the HR feed line to?  You really need to establish a one to one mapping from HR feed to AD object to perform the comparison in a reliable manner.
    Monday, November 12, 2018 6:30 PM
  • Hi Both tickermcse76, LeeSeenLi

    Million thanks, there is no better cleaner and friendly code i ever saw in my life.

    Is it possible to include Partial Match if exact match of email exists in AD? will it be possible to apply this somewhere in the above script.

    $eFilter = "emailaddress - eq '$Fourthfilter'"

    if (Get-AdUser - Filter $eFilter - Properties EmailAddress - Server yourdomain.local)

    #Partial Match Employee_email if ($item | Add-Member -MemberType NoteProperty -Name Result -Value 'Partial Match'}


    Tuesday, November 13, 2018 9:28 AM
  • This is an exact example of what is called a "help vampire".  A person who asks a question then keeps adding questions to get other to write a script for free.  It is not only childish but it is unprofessional.

    See: Help Vampires: A Spotter's Guide

    It is grossly unfair of people who have no intention of learning the technology to demand that others write code for them. 


    Tuesday, November 13, 2018 9:33 AM
  • Hi JRV, How many time I have to say to keep away from my post.  please if you can help answer otherwise mind your business please.

    What a childish and unprofessional behaviour and now you are crossing your limits, keep your thoughts to yourself and burn in hell.


    Tuesday, November 13, 2018 9:54 AM