Powershell Needed to Handle/Report Errors etc.

Right...I've managed to locate some info on creating a change log file to show what objects were updated by the script (see below) however, I can't seem to get a report working for any errors and/or objects that we NOT updated (i.e. wrong or missing email address).

If anyone knows how I can do that I would be VERY grateful...

#
#Oracle Data Import Powershell Script
#
#Delete old change log
Remove-Item D:\Oracle\ChangeLog\*.log
Remove-Item D:\Oracle\Zip\*.zip
#
#Get Date
$now = [datetime]::now.ToString('yyyy-MM-dd')
#
# Use Quest ActiveRoles Active Directory Powershell CMDLETs
Add-PSSnapin Quest.ActiveRoles.ADManagement
#
#Import from CSV & email changes

Import-CSV D:\Oracle\Scripts\TestSource.CSV | Foreach-Object {
            Get-QADUser $_."mail" | Set-QADUser -description $_."description" -sn $_."sn" -givenName $_."givenName" -telephoneNumber $_."telephoneNumber" -mobile $_."mobile" -title $_."title" -Department $_."Department" -l $_."l" -streetAddress $_."streetAddress" -st $_."st" -ObjectAttributes @{employeeNumber=$_.employeeNumber;postalOfficeBox=$_.postalOfficeBox;comment=$_.comment}} -ErrorAction Stop |
Out-File -FilePath ("d:\oracle\changelog\changelog_$now.log") -encoding ASCII -append -width 1000

#
#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 name@email.com -From reports@company.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp exchangeserver.dc.dc
#
#End of Powershell Script

There are two scenarios you may want to account for:

1. The account does not exist (the modification below does this)
2. The update failed (not implemented yet)

In addition to that, consistent formatting may be helpful. Last time I wrote a script like this I captured the before and after values in the log, but only changed users where the values differed (between the data-source and AD).

Anyway, to start the conversation off, this shows how you might capture users it cannot find.

#
#Oracle Data Import Powershell Script
#
#Delete old change log
Remove-Item D:\Oracle\ChangeLog\*.log
Remove-Item D:\Oracle\Zip\*.zip
#
#Get Date
$now = [datetime]::now.ToString('yyyy-MM-dd')
#
# Use Quest ActiveRoles Active Directory Powershell CMDLETs
Add-PSSnapin Quest.ActiveRoles.ADManagement
#
#Import from CSV & email changes

Import-CSV D:\Oracle\Scripts\TestSource.CSV | Foreach-Object {
  $Line = $_

  # Attempt to get the user account
  $User = Get-QADUser $Line.mail

  if ($User) {

    # This is a modification of the original, but you're not changing any of the attributes with this because $_ becomes the output from Get-QADUser, not the line from Import-CSV.
    # $_ is the current object in the *current* pipeline.
    # $User | Set-QADUser -description $_."description" -sn $_."sn" -givenName $_."givenName" -telephoneNumber $_."telephoneNumber" -mobile $_."mobile" `
    #     -title $_."title" -Department $_."Department" -l $_."l" -streetAddress $_."streetAddress" -st $_."st" `
    #    -ObjectAttributes @{employeeNumber=$_.employeeNumber;postalOfficeBox=$_.postalOfficeBox;comment=$_.comment}} -ErrorAction Stop |
    #  Out-File -FilePath ("d:\oracle\changelog\changelog_$now.log") -encoding ASCII -append -width 1000 

    # Modified version which uses the $Line variable, I made this hold the current line from the CSV file.
    #
    # The ErrorVariable parameter can be used to help trap errors thrown while making the update.
    $User | Set-QADUser -description $Line.description -sn $Line.sn -givenName $Line.givenName -telephoneNumber $Line.telephoneNumber -mobile $Line.mobile `
        -title $Line.title -Department $Line.Department -l $Line.l -streetAddress $Line.streetAddress -st $Line.st `
        -ObjectAttributes @{employeeNumber=$Line.employeeNumber;postalOfficeBox=$Line.postalOfficeBox;comment=$Line.comment}} -ErrorAction Stop -ErrorVariable QADError |
      Out-File -FilePath ("d:\oracle\changelog\changelog_$now.log") -encoding ASCII -append -width 1000 

  } else {

      # You may want to play with the formatting here.
      "$($Line.mail): User does not exist" | Out-File -FilePath ("d:\oracle\changelog\changelog_$now.log") -encoding ASCII -append -width 1000

  }
}

# Should onyl need to do this bit once (rather than once per user)

#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 name@email.com -From reports@company.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp exchangeserver.dc.dc
#
#End of Powershell Script

Select allOpen in new window

Cheers,

Chris

OK...having seen the quality of the source data I'm looking at a slight 'tweak' in my requirements.

I'm using 'mail' to identify the users which works fine for the most part however, for some of the users the source doesn't have a email/mail address recorded. I need the script to create a log of the accounts that don't have an email address and, a separate log listing all the accounts that were changed and the associated values.

I've tried tweaking and playing a little but only seem to be able to generate blank files with no information. If anyone can provide me with the sample Powershell I need to achieve this I will be forever in your debt.

Please can you post a sample import line along with the CSV header?

It doesn't need to be real data, but it does need to show the problems you're trying to work-around.

Chris

Sample Oracle Source CSV extract as follows:

employeeNumber,givenName,sn,title,mail,telephoneNumber,mobile,comment,Department,PostalAddress,PostalOfficeBox,description
E12345,Kashif,Abbasi,Desktop Engineer,Kashif.Abbasi@domain.com,0203 666 1234,07801 111 111,Anne Other,IT Services,1 High Street, My Town ,MY12 1MY,

This is the script I am currently using:

#
#Oracle Data Import Powershell Script
#
#Delete old change log
Remove-Item D:\Oracle\ChangeLog\*.log
Remove-Item D:\Oracle\Zip\*.zip
#
#Get Date
$now = [datetime]::now.ToString('yyyy-MM-dd')
#
# Use Quest ActiveRoles Active Directory Powershell CMDLETs
Add-PSSnapin Quest.ActiveRoles.ADManagement
#
#Import from CSV & email changes

Import-CSV D:\Oracle\Scripts\TestSource-2411.CSV | Foreach-Object { 
		Get-QADUser $_."mail" | Set-QADUser -description $_."description" -sn $_."sn" -givenName $_."givenName" -telephoneNumber $_."telephoneNumber" -mobile $_."mobile" -title $_."title" -Department $_."Department" -l $_."l" -info $_."info" -streetAddress $_."streetAddress" -st $_."st" -ObjectAttributes @{employeeNumber=$_.employeeNumber;postalOfficeBox=$_.postalOfficeBox;comment=$_.comment}} -ErrorAction Stop | 
Out-File -FilePath ("d:\oracle\changelog\changelog_$now.log") -encoding ASCII -append -width 1000 

#
#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 me@mymail.com -From reports@company-domain.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp smtpservername.dc.dc
#
#End of Powershell Script

Select allOpen in new window

Okay, here's a new version for you. I think it works well logically, however I haven't tested it at all (not even syntax).

I've tried to comment it throughout, please let me know if anything is unclear.

#
#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

#
# 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.
#

$WriteableProperties = "employeeNumber", "givenName", "sn", "title", "telephoneNumber", "mobile", "comment", "Department", "PostalAddress", "PostalOfficeBox", "description"

#
# Begin processing import
#

Import-CSV D:\Oracle\Scripts\TestSource.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 abandonned and a status message recorded.
  #
  # If the search returns no result the update process is abandonned 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)"

      # Set the attributes, then retrieve the new version of the user for the report
      $SuccessfulChanges += Set-QADUser -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 name@email.com -From reports@company.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp exchangeserver.dc.dc
#
#End of Powershell Script

Select allOpen in new window

Chris

Thanks...I get the following message when I run the script:

cmdlet Set-QADUser at comman pipeline position 1
Supply values for the following parameters:
Indentity: _

See, a prime example of testing failure :)

Fixed.

#
#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

#
# 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.
#

$WriteableProperties = "employeeNumber", "givenName", "sn", "title", "telephoneNumber", "mobile", "comment", "Department", "PostalAddress", "PostalOfficeBox", "description"

#
# Begin processing import
#

Import-CSV D:\Oracle\Scripts\TestSource.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 abandonned and a status message recorded.
  #
  # If the search returns no result the update process is abandonned 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)"

      # 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 name@email.com -From reports@company.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp exchangeserver.dc.dc
#
#End of Powershell Script

Select allOpen in new window

Right...the script runs through and produces an list of the failures for those that the email address is incorrect however, the script no longer actually updates any of the Active Directory attributes via the import.

Any ideas?

Is it populating either the Skipped or Successful file?

One or the other should be filling.

Chris

No...just the failed (which works perfectly)

Okay, need a debug line. I suspect it's attempting to set, but not doing very much.

This version has the debugging line added.

#
#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

#
# 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.
#

$WriteableProperties = "employeeNumber", "givenName", "sn", "title", "telephoneNumber", "mobile", "comment", "Department", "PostalAddress", "PostalOfficeBox", "description"

#
# Begin processing import
#

Import-CSV D:\Oracle\Scripts\TestSource.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 abandonned and a status message recorded.
  #
  # If the search returns no result the update process is abandonned 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 name@email.com -From reports@company.com -subject 'Oracle Data Sync Complete' -body 'Please note the WEEKLY Oracle data sync has finished successfully. See attached file for list of users that have been successfully updated.' -attachments 'd:\oracle\zip\ChangeLog.zip' -smtp exchangeserver.dc.dc
#
#End of Powershell Script

Select allOpen in new window

Chris

I noticed a bug as well. The account locator is missing a bracket in the LdapFilter used for EmployeeNumber:

$User = Get-QADUser -LdapFilter "(employeeNumber=$($_.employeeNumber))" -IncludedProperties $WriteableProperties

Select allOpen in new window

Without that it'll throw errors.

Chris

Write - updated the account locator as above...

The script runs through, produces no updates into success/skipped/failed and I get the following on-screen messages when the script runs:

Attempting to set values for me@myemail.com: PostalAddress PostalOfficeBox
Select-Object : Cannot convert System.Object[] to one of the following types {System.String, System.Management.Automation.ScriptBlock}.
At D:\oracle\scripts\2811-Import.PS1:132 char:22
+ Select-Object <<<< $WriteableProperties, mail, @{n='UpdatedAttributes';e={ $UpdatedAttributes }}
+ CategoryInfo: InvalidArgument: (:) [Select-Object], NotSupportedException
+ FullyQualifiedErrorId: DictionaryKeyUnknownType,Microsoft.Powershell.Commands.SelectObjectCommand

Select allOpen in new window

This repeats for all the accounts in the import file...

Actually, ignore this:

"Now given that's where it was throwing errors it should have managed to change the properties it listed. It didn't though? Any other error messages?"

The syntax error is so significant it simply didn't (apparently) execute that (extended) line at all. Let's see if the fixed version behaves itself.

Chris

OK..that looks like it's done the trick, thank you so much =D

One last question, what is the skipped_changelog file used for?

Thanks for all your efforts Chris...you are top of my Christmas Card List. =D

The script checks to see if values differ between your CSV file and AD for each account it's considering changing.

If no changes are required the CSV line will be logged to the Skipped file with a field indicating that no fields require updating (in AD).

This is why you had that debug entry stating this:

Attempting to set values for me@myemail.com: PostalAddress PostalOfficeBox

It left all the other values alone and only attempted to write the change for the two listed.

This way your change log should really be only changes. The other two files account for everything else. :)

Chris

Brilliant, thanks for all your help.