Solved

Powershell Needed to Handle/Report Errors etc.

Posted on 2013-11-25
21
628 Views
Last Modified: 2013-11-28
Good afternoon...I'm a little new to Powershell so need a little advice from those more experienced than me.

I have written a simple import Powershell script to update User objects with information from an extracted CSV taken from our Oracle HR database:

Import-CSV "D:\Oracle\Scripts\TestSource.CSV" -Delimiter "," | 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}}

This works great, updates all the fields that I need..I would like to add something for error handling/reporting and perhaps a log file of all the objects that have been updated to.

Any/all advice and assistance is most welcome.
0
Comment
Question by:Darren Reevell
  • 11
  • 10
21 Comments
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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

Open in new window

Cheers,

Chris
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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.
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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,
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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

Open in new window

0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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

Open in new window


Chris
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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: _
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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

Open in new window

0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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?
0
Do email signature updates give you a headache?

Do you feel like all of your time is spent managing email signatures? Too busy to visit every user’s desk to make updates? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today!

 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
Is it populating either the Skipped or Successful file?

One or the other should be filling.

Chris
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
No...just the failed (which works perfectly)
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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

Open in new window

Chris
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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

Open in new window

Without that it'll throw errors.

Chris
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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

Open in new window


This repeats for all the accounts in the import file...
0
 
LVL 70

Accepted Solution

by:
Chris Dent earned 500 total points
Comment Utility
Awesome, thank's for that. Fixed below.

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?

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

Open in new window

Chris
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
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?
0
 
LVL 1

Author Closing Comment

by:Darren Reevell
Comment Utility
Thanks for all your efforts Chris...you are top of my Christmas Card List. =D
0
 
LVL 70

Expert Comment

by:Chris Dent
Comment Utility
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
0
 
LVL 1

Author Comment

by:Darren Reevell
Comment Utility
Brilliant, thanks for all your help.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This is a based on my experience in IT role and also my role before that, a builder. These 1st contact rules have helped me and more importantly help past, present and hopefully future client/people I get to work/assist. Via Telephone: 1.      Answ…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …

763 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

10 Experts available now in Live!

Get 1:1 Help Now