Solved

Powershell to perform Excel vLookup

Posted on 2013-12-09
17
2,418 Views
Last Modified: 2013-12-20
I have a CSV extract from our Oracle HR database that updates the information on telephone, office name, mobile, job title etc. for Active Directory users via Powershell. This works great and version 1 is delivering exactly what the the users wanted, the issues is however, the goal posts have now shifted a little.

Now they have decided that they want the users 'Manager' field populating from the Oracle HR database/CSV extract. It's simple in their mind, it's just name however, as we all know the Active Directory is not just a name, it is actually the distinguished name for the users manager (cn=managername,ou=manager dept,ou=users accounts,dc=company,dc=com) and obviously the Oracle HR database does not contain this information.

I am using the following to perform the dump of the live AD and create a CSV to be used for the vLookup;

# Powershell Script to Dump ALL domain users and associated attributes to CSV
# Use Quest ActiveRoles Active Directory Powershell CMDLETs
Add-PSSnapin Quest.ActiveRoles.ADManagement
# Quest AD commandlet command
# Dump All Users to CSV
write-Host "Active Directory User Dump Script" -foregroundcolor "yellow"
write-Host "Writing users to 'D:\Oracle\Backup\DumpAD.CSV'" -foregroundcolor "yellow"
write-Host "Please wait...." -foregroundcolor "yellow"
GET-QADUSER -sizelimit 0 | select-object dn,employeeNumber,sn,givenName,title,telephoneNumber,mobile,department,location,postalAddress,postalCode,mail,comment,manager | export-CSV "D:\Oracle\Backup\DumpAD.CSV" -noType
cls
write-Host "Active Directory User Dump Script Finished" -foregroundcolor "yellow"
write-Host "Please review 'D:\Oracle\Backup\DumpAD.CSV' for full information..." -foregroundcolor "yellow"
write-HOst "### END ###" -foregroundcolor "yellow"
#
#
#
#Email notification of run
send-mailMessage -To me@mycompany.com  -From auditreports@mycompany.com -subject 'Active Directory User Dump Complete' -body 'Please note the Active Directory user dump has finished successfully . See attached copy of D:\Oracle\Backup\DumpAD.CSV for full information of dumped accounts' -attachments 'D:\Oracle\Backup\DUmpAD.CSV' -smtp smtpserver.dc.dc
#
#End of Powershell Script

Open in new window


I need a powershell script to perform a vLookup so I can take the DN from the dump created above and add it as a column to the HR Oracle CSV file so I can populate the 'manager' field with the managers DN.

Any and all advice would be most welcome.
0
Comment
Question by:Darren Reevell
  • 9
  • 6
17 Comments
 
LVL 9

Expert Comment

by:VirastaR
ID: 39708867
Hi,

Check this

EE Internal Reference:
How do I perform a VLOOKUP in Excel VBA ?

Hope that helps :)
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39713700
OK...I'll take a look at that link and come back if(when) I have more questions...thanks
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39716700
How about reading in the AD user list into a hash table (keyed by manager name or whatever you have in HR DB), and merging that into the HR CSV when you create that one?
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39720058
How about reading in the AD user list into a hash table (keyed by manager name or whatever you have in HR DB), and merging that into the HR CSV when you create that one?

How would I do that?
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39720426
Could you show us the HR CSV code, at least a skeleton, so we can see what to match against AD?
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39721623
This is a sample of the CSV extract from Active Directory:

"DN","employeeNumber","sn","givenName","Title","telephoneNumber","mobile","Department","location","postalAddress","PostalCode","mail","comment","Manager"
"CN=Bob Day,OU=Expired Accounts,DC=company,DC=co,DC=uk",,"Day","Bob","Network & Telephony Support Manager","0209 111 1299",,"ISD",,,"W1 1BR","bob.Day@company.co.uk",,

Open in new window


This is a sample of the CSV extract from the Oracle HR CSV"

mail,comment,sn,givenName,telephoneNumber,mobile,title,department,l,streetAddress,postalCode,st,info,employeeNumber,physicalDeliveryOfficeName,company
bob.day@company.co.uk,,Day,Bob,02031112233,09911 010101,Project Controller,Business Programme Office,London,High Street,N1 2BR,,Ivor Manager,E12345,The Office,Central Services

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39722812
Bob Day is a manager, so the HR CSV certainly should show something different?
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39723387
sorry...should have put some context to the HR extract. The manager name is currently being stored in the field/column named 'info'. This is so we can add this into AD, however the business have requested that the manager name is stored in the AD 'manager' field, so I need to pull the manager's FQDN from Active Directory, add this column to the Oracle HR extract (or a new workbook) and import this into AD.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39723664
So we would need to search for "Ivor Manager" in this case?
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39723671
yep...we need to bring in 'Ivor Manager' FQDN from the AD and add as a new column named 'manager' in the Oracle HR workbook.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39723767
$DN = import-csv "D:\Oracle\Backup\DumpAD.CSV" | select -Expand DN
import-csv "D:\Oracle\Backup\Oracle HR.csv" | % {
   $mgr=$_.Info
   $_ | Add-Member NoteProperty "Manager" ($DN | select-string -SimpleMatch "CN=$mgr")
} | export-csv "D:\Oracle\Backup\Oracle HR 2.csv" -notype

Open in new window

0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39723883
Thanks for the prompt response...

When I run the script above it creates a blank Oracle HR 2.CSV file (i.e. a ZERO kb file with no information in it). If I amend line 3 to $mgr=$_.manager I get the message:

Add-Member : Cannot add a member with the name "Manager" because a member with that name already exists. If you want to overwrite the member anyway, use the Force parameter to overwrite it.

When I added the Force command I now get the following message:

Select-String : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the input and its properties do not match any of the parameters that take pipeline input.
0
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39723925
You told us that the "Info" field of the HR CSV contains the manager name, so my line 3 is correct. Try this one:
$DN = import-csv "D:\Oracle\Backup\DumpAD.CSV" | select -Expand DN
import-csv "D:\Oracle\Backup\Oracle HR.csv" | % {
   $mgr=$_.Info
   $_ | Add-Member NoteProperty "Manager" ($DN | select-string -SimpleMatch "CN=$mgr") -PassThru
} | export-csv "D:\Oracle\Backup\Oracle HR 2.csv" -notype

Open in new window

0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39724017
Brilliant...that one works perfectly. I owe you several pints of beer or bottles of wine for that.

Cheers.
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39724730
OK, thanks to help from all you folks here I now have a working script that does the following:

Dumps the AD to a file (inc. users FQDN)
Retrieves the FQDN from above file, merges with Oracle HR extract and creates new import file
Imports Oracle HR information to AD (including Manager FQDN)
Removes OLD change log files
Creates NEW change log files
Creates a ZIP file with all change log files included
Emails the ZIP files out
Removes last import archive file
Creates New import archive file

I'll post full script on here in the next couple of days...thanks so much for all your help folks.
0
 
LVL 1

Author Comment

by:Darren Reevell
ID: 39731535
As promised, see the complete 'working' PowerShell for the Oracle to AD import script.

Thanks for all your assistance...

#
#Oracle Data Import Powershell Script
#

# WARNING: This script should not be executed on any system where RAM / memory consumption is critical.

# Use Quest ActiveRoles Active Directory Powershell CMDLETs

Add-PSSnapin Quest.ActiveRoles.ADManagement

# Dump All Users to CSV
cls
write-Host "Active Directory User Dump Script" -foregroundcolor "yellow"
write-Host "Writing users to 'D:\Oracle\Backup\DumpAD.CSV'" -foregroundcolor "yellow"
write-Host "Please wait...." -foregroundcolor "yellow"
GET-QADUSER -sizelimit 0 | select-object dn,employeeNumber,sn,givenName,title,telephoneNumber,mobile,department,location,postalAddress,postalCode,mail,comment,manager | export-CSV "D:\Oracle\Backup\DumpAD.CSV" -noType
cls
write-Host "Active Directory User Dump Script Finished" -foregroundcolor "yellow"
write-Host "Please review 'D:\Oracle\Backup\DumpAD.CSV' for full information..." -foregroundcolor "yellow"
write-HOst "### END ###" -foregroundcolor "yellow"
#
#
# Pull FQDN from AD and pump into Import File
#
cls
write-Host "Active Directory Import Starting..." -foregroundcolor "yellow"
write-Host "Pulling Manager FQDN from Active Directory" -foregroundcolor "yellow"
write-Host "Please wait..." -foregroundcolor "yellow"#
$DN = import-csv "D:\Oracle\Backup\DumpAD.CSV" | select -Expand DN
import-csv "D:\Oracle\SOurce\OracleADImport.csv" | % {
   $mgr=$_.info
   $_ | Add-Member NoteProperty "Manager" ($DN | select-string -SimpleMatch "CN=$mgr") -PassThru
} | export-csv "D:\Oracle\Source\OracleADImport1.csv" -notype


# Prepare reporting objects
#
#
$SkippedChanges = @()
$SuccessfulChanges = @()
$FailedChanges = @()

#
# Prepare a list of properties which can be changed by this script
#
# Mail will not be set by this script. It is treated as the primary key, and if Exchange is in use this is not the way to change the users
# primary e-mail address.
#
cls
write-Host "Oracle HR Data Import Running" -foregroundcolor "yellow"
write-Host "Please wait..." -foregroundcolor "yellow"
#

#
# Remove old import files from 'D:\Oracle\ChangeLog' folder
#

Remove-Item D:\Oracle\ChangeLog\*

$WriteableProperties = "employeeNumber", "givenName", "sn", "title", "telephoneNumber", "mobile", "comment", "Department", "streetAddress", "l", "st", "info", "PostalCode", "description", "physicalDeliveryOfficeName", "company", "manager"

#
# Begin processing import
#

Import-CSV D:\Oracle\Source\OracleADImport1.CSV | Foreach-Object {

  #
  # Account location algorithm
  #

  # The following attributes are checked in order:
  #  * mail
  #  * proxyAddresses
  #  * employeeNumber
  #
  # If any of the searches returns too many results the search is abandoned and a status message recorded.
  #
  # If the search returns no result the update process is abandoned for this line.
  #
  # The update will proceed only if a unique match is found.

  # Ensure nothing is cached from any previous iteration of this loop
  $User = $null; $SearchStatus = $null

  # If mail is set in the CSV file
  if ($_.mail) {

    # First attempt: Literal match using mail.
    $User = Get-QADUser -Email $_.mail -IncludedProperties $WriteableProperties
    # Check to ensure this has not returned more than one match.
    if ($User -is [Array]) {
      Write-Verbose "Too many matches for user using mail field ($($_.mail))"
      $SearchStatus = "Too many matches using mail"
    }

    # Second attempt: If the first search attempt failed, returning no matches. Match using proxyAddresses
    if (!$User -and !$SearchStatus) {
      Write-Verbose "Failed to find user using mail field ($($_.mail))"

      $User = Get-QADUser -ProxyAddress "smtp:$($_.mail)" -IncludedProperties $WriteableProperties
      # Again, check to ensure this has not returned too many matches.
      if ($User -is [Array]) {
        Write-Verbose "Too many matches for user using mail field ($($_.mail))"
        $SearchStatus = "Too many matches using proxyAddress"
      }
    }    
  }

  # Third attempt: Fall back to employeeNumber.
  if (!$User -and !$SearchStatus -and $_.employeeNumber) {
    $User = Get-QADUser -LdapFilter "(employeeNumber=$($_.employeeNumber))" -IncludedProperties $WriteableProperties
    # Once more, check to ensure this has not returned too many matches.
    if ($User -is [Array]) {
      Write-Verbose "Too many matches for user using mail field ($($_.mail))"
      $SearchStatus = "Too many matches using employeeNumber"
    }    
  }

  # Fourth attempt: The least definite, included and commented out as a possible method. Attempt to locate the user using
  # givenName and sn.
  # if (!$User -and !$SearchStatus -and $_.givenName -and $_.sn) {
  #  $User = Get-QADUser -GivenName $_.GivenName -SN $_.SN -IncludedProperties $WriteableProperties
  #  # Once more, check to ensure this has not returned too many matches.
  #  if ($User -is [Array]) {
  #    Write-Verbose "Too many matches for user using mail field ($($_.mail))"
  #    $SearchStatus = "Too many matches using givenName and sn"
  #  }
  # }

  # Failed to find the user account. Create a log line for this.
  if (!$User) {
  
    if (!$SearchStatus) {
      # If we just fail to match no status message is recorded. Adding one now to indicate the reason for failing
      # this entry.
      $SearchStatus = "Failed all search attempts"
    }
    # Add this to the failed changes report
    $FailedChanges += $_ | Select-Object *, @{n='SearchStatus';e={ $SearchStatus }}

  } else {

    # As the user is now in memory a check will be performed to see if there is any need to update the object
    $ObjectAttributes = @{}
    $CsvLine = $_
    $WriteableProperties | ForEach-Object {    
      if ($User.$_ -ne $CsvLine.$_) {
        # The two properties do not match, this must be updated. $CsvLine contains the new value to set.
        $ObjectAttributes.Add($_, $CsvLine.$_)
      }
    }

    # If we managed to find some properties to set it is time to do so
    if ($ObjectAttributes.Keys.Count -gt 0) {

      # The list of attributes which were updated
      $UpdatedAttributes = "$($ObjectAttributes.Keys)"

      # The debugging line
      Write-Host "Attempting to set values for $($_.mail): $UpdatedAttributes"
      
      # Set the attributes, then retrieve the new version of the user for the report
      $SuccessfulChanges += Set-QADUser $User.DN -ObjectAttributes $ObjectAttributes |
        Get-QADUser -IncludedProperties $WriteableProperties |
        Select-Object ($WriteableProperties + "mail" + @{n='UpdatedAttributes';e={ $UpdatedAttributes }})

    } else {

      # No changes were required. Recording this to a log file.
      $SkippedChanges += $_ | Select-Object *, @{n='UpdatedAttributes';e={ "None" }}

    }
  }
}

#Delete old change log
Remove-Item D:\Oracle\ChangeLog\*.log
Remove-Item D:\Oracle\Zip\*.zip

$Now = (Get-Date).ToString('yyyy-MM-dd')

# Write the report of successful changes
$File = "d:\oracle\changelog\success_changelog_$now.csv"
$SuccessfulChanges | Export-Csv $File -NoTypeInformation

# Write the report of failed changes
$File = "d:\oracle\changelog\failed_changelog_$now.csv"
$FailedChanges | Export-Csv $File -NoTypeInformation

# Write the report of skipped changes
$File = "d:\oracle\changelog\skipped_changelog_$now.csv"
$SkippedChanges | Export-Csv $File -NoTypeInformation

#Create archive file
$srcdir = "D:\Oracle\changelog\"
$zipFilename = "Changelog.zip"
$zipFilepath = "D:\Oracle\Zip\"
$zipFile = "$zipFilepath$zipFilename"

#Prepare zip file
if(-not (test-path($zipFile))) {
    set-content $zipFile ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
    (dir $zipFile).IsReadOnly = $false  
}

$shellApplication = new-object -com shell.application
$zipPackage = $shellApplication.NameSpace($zipFile)
$files = Get-ChildItem -Path $srcdir | where{! $_.PSIsContainer}

foreach($file in $files) { 
    $zipPackage.CopyHere($file.FullName)
    #using this method, sometimes files can be 'skipped'
    #this 'while' loop checks each file is added before moving to the next
    while($zipPackage.Items().Item($file.name) -eq $null){
        Start-sleep -seconds 1
    }
}
#
#
#Create a 30 second pause whilst ZIP archive is created
Start-Sleep -s 30
#
#
#Email notification of run
send-mailMessage -To email@company.com -From reports@company.com-subject 'Oracle Data Sync Complete' -body 'Please note the Oracle to Active Directory data sync has finisheds. Please review the change log files in the attachement for success, failed and/or skipped users.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp servername.company.com
#

#
# Remove old import source from 'D:\Oracle\Source' folder
#

Remove-Item D:\Oracle\Source\*.OLD

#
# Archive last import source from 'D:\Oracle\Source' folder
#

Rename-Item D:\Oracle\Source\OracleADImport.CSV D:\Oracle\Source\OracleADImport.OLD



write-Host "Import Script Complete...please exit window." -foregroundcolor "yellow"

#End of Powershell Script

Open in new window

0

Join & Write a Comment

Find out how to use Active Directory data for email signature management in Microsoft Exchange and Office 365.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now