powershell - Read from CSV and Compare info against AD -

i have csv file treating master file. not of headers csv part of user in ad.

the master csv has following fields:

• last name
• first name
• last 4 ssn
• month of birth
• day of birth
• company employee id
• titles
• department name
• phone number

i need compare company employee id field csv against employeenumber attribute ad. if match, following fields should written new csv:

• last name
• first name
• last 4 ssn
• month of birth
• day of birth
• company employee id
• titles
• department name
• phone number
• email address

last name , first name come ad, last 4 ssn, month of birth, day of birth , phone number come master csv file.

company employee id, titles, department name , email address in output csv should take values of ad attributes employeenumber, title, department , mail respectively.

if not match, record master csv needs part of new csv same headers mentioned above.

@kobhrl: not add whole code in comment. have far following:

function checkad {   $masterfile,$csv,$ad = @()   domaincontroller = "companydc02.companycolo.pvt"   $companyusersou = "ou=company,dc=companycolo,dc=pvt"   $masterfile = "c:\scripts\edir\edir_headers01.csv"   $csvadmismatch = "c:\scripts\edir\edir_headers01_1.csv"   $csv = import-csv $masterfile   foreach ($user in $csv)   {     $clocknumber = $user."company employee id"     get-aduser -searchbase $companyusersou -server $domaincontroller `         -filter '(employeenumber -eq $clocknumber)' | foreach-object {       if ($user.$clocknumber -eq $_.employeenumber) {         write-host "last name " $_.surname       }     }   } } 

latest code stuck:

function checkad {  $masterfile,$csv,$ad,$output = @()  $domaincontroller = "companydc02.companycolo.pvt"  $companyusersou = "ou=company,dc=companycolo,dc=pvt"  $masterfile = "c:\scripts\edir\edir_headers01.csv"  $csvadmismatch = "c:\scripts\edir\edir_headers01_1.csv"  $csv = import-csv $masterfile foreach ($user in $csv) {     $clocknumber = $user."company employee id"     #get-aduser -searchbase $companyusersou -server $domaincontroller -filter '(employeenumber -eq $clocknumber)' -properties * | `     #get-aduser -searchbase $companyusersou -server $domaincontroller -filter '(employeenumber -eq $clocknumber)' -properties * | select surname, givenname, employeenumber, department, mail     get-aduser -searchbase $companyusersou -server $domaincontroller -filter '(employeenumber -eq $clocknumber)' -properties surname,givenname,employeenumber,department,mail | `     # if ($ad)     # {             # $output += new-object psobject -property @{                 # "last name"=$_.surname                 # "first name"=$_.givenname                 # "last 4 ssn"=$user."last 4 ssn"             # }     # foreach-object {              # write-host "last name: " $_.surname             # write-host "first name: " $_.givenname             # write-host "last 4 ssn: " $user."last 4 ssn"             # write-host "month of birth: " $user."month of birth"             # write-host "day of birth: " $user."day of birth"             # write-host "company employee id: " $_.employeenumber             # write-host "titles: " $user."titles"             # write-host "department name: " $_.department             # write-host "phone number: " $user."phone number"             # write-host "email address: " $_.mail                           # write-host ""     # }     #$ad | sort-object -property sn, givenname | select samaccountname,surname,givenname,name,department,title,telephonenumber,employeenumber | export-csv "c:\scripts\ceridian\06-10-2013_exportedaduserswithclocknumber.csv" -notypeinformation     select @{n="last name";e={$_.surname}},@{n="first name";e={$_.givenname}},@{n="last 4 ssn";e={$user."last 4 ssn"}},@{a="month of birth";b={$user."month of birth"}},@{c="day of birth";d={$user."day of birth"}},@{f="company employee id";g={$_.employeenumber}},@{i="titles";j={$user."titles"}},@{k="department name";l={$_.department}},@{m="phone number";o={$user."phone number"}},@{p="email address";q={$_.mail}} | export-csv $csvadmismatch -notypeinformation     #} } #$output | sort "last name" | export-csv $csvadmismatch -notypeinformation 

}

i've done similar @ job , did you're doing , running loop each user. pull data ad object, , updating records in custom object. so, this:

$domaincontroller = "companydc02.companycolo.pvt" $companyusersou = "ou=company,dc=companycolo,dc=pvt" $masterfile = "c:\scripts\edir\edir_headers01.csv" $csvadmismatch = "c:\scripts\edir\edir_headers01_1.csv" $csv = import-csv $masterfile $output = @() foreach($user in $csv){     remove-variable current     $current = get-aduser -searchbase $companyusersou -server $domaincontroller -filter '(employeenumber -eq $clocknumber)'     if($current){ #if ad match found         $output += new-object psobject -property @{             "last name"=$current.surname             "first name"=$current.givenname             "last 4 ssn"=$user.'last 4 ssn'             "other fields"="other values"         }     }else{ #if no ad match         $output += new-object psobject -property @{             "last name"=$user.'last name'             "first name"=$user.'first name'             "last 4 ssn"=$user.'last 4 ssn'             "other fields"="other values"         }     } } $output|export-csv c:\somefile.csv -notypeinformation 

you need fill in various fields, imports master file, loops through each user , looks them in ad. if found creates new object both existing user , ad, if aren't found uses values existing user. adds object master listing. lastly had output new csv.

edit: so, didn't use code, saying getting errors code. have above doesn't resemble gave @ (except parts commented out). if want help, use given you. if won't use given don't ask help.

what have won't work because hashtables constructed wrong. parts @{n="name";e={expression}} have random letters thrown in there. n= short name= , e= short expression=, should saying things like: name of object = "last name" expression determine value = {$user.'last name'}

but have random letters thrown in there @{o='last name';p={$user.'last name'}} , powershell has no idea o= , p= supposed mean, throws errors.

now, have function, , i'm not sure why. aren't treating function really. in spirit of keeping function reason i've re-done have above keep one. sets function, , calls function, , outputs csv.

function checkad{     param($masterfile = "c:\scripts\edir\edir_headers01.csv",     $domaincontroller = "companydc02.companycolo.pvt",     $companyusersou = "ou=company,dc=companycolo,dc=pvt")     $csv = import-csv $masterfile     foreach($user in $csv)     {         $clocknumber = $user."company employee id"         remove-variable ad         $ad = get-aduser -searchbase $companyusersou -server $domaincontroller -filter '(employeenumber -eq $clocknumber)' -properties surname,givenname,employeenumber,department,mail         new-object psobject -property @{"last name"=if($ad){$ad.surname}else{$user.'last name'}             "first name"=if($ad){$ad.givenname}else{$user.'first name'}             "last 4 ssn"=$user."last 4 ssn"             "month of birth"=$user."month of birth"             "day of birth"=$user."day of birth"             "company employee id"=if($ad){$ad.employeenumber}else{$user.'company employee id'}             "titles"=$user."titles"             "department name"=if($ad){$ad.department}else{$user.department}             "phone number"=$user."phone number"             "email address"=if($ad){$ad.mail}else{$user.'email address'}         }     } } $csvadmismatch = "c:\scripts\edir\edir_headers01_1.csv" checkad | sort "last name" | export-csv $csvadmismatch -notypeinformation