We help IT Professionals succeed at work.

Automate CSV Comparison and Email Deltas Powershell Script

1,837 Views
Last Modified: 2013-12-23
I have a powershell script that runs once per day that generates a csv file with a list of all mailbox users, and calendar access rights for thier mailbox.  This csv contains 4 columns
A=Mailbox B=User C=$_.AccessRights D=IsValid

I need another automated script that will run a delta between the previous days csv file and current, and create another csv with the differences between the two csv files, which would then be emailed to specific email addresses.

My 2 big issues/questions are:
-My powershell script creates the same name csv file (export.csv) which ultimately overwrites it, so I would need to either have it save to a different name or prevent the previous day from being overwritten
-Running the delta compare between the csvs and creating a new one
Comment
Watch Question

Author

Commented:
Here is the powershell script i am using:

$Mailboxes = Get-Mailbox -Filter {RecipientTypeDetails -eq "UserMailbox"} -ResultSize Unlimited
$Output = @()
ForEach ($Mailbox in $Mailboxes) {
$Calendar = (($Mailbox.PrimarySmtpAddress.ToString())+ ":\" + (Get-MailboxFolderStatistics -Identity $Mailbox.DistinguishedName -FolderScope Calendar | Select-Object -First 1).Name)
$Permissions = Get-MailboxFolderPermission -Identity $Calendar
ForEach ($Permission in $Permissions) {
  $Permission | Add-Member -MemberType NoteProperty -Name "Mailbox" -value $Mailbox.DisplayName
  $Output = $Output + $Permission
 }
}
$Output | Select-Object Mailbox, User, {$_.AccessRights}, IsValid | Export-Csv -Path C:\CalendarPermissions.csv -NoTypeInformation

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
My recommendation, to keep it simple, is to integrate that all into a single script.

1. Rename the prior CSV to e.g. "export.old.csv". If the file exists, the rename fails, and the difference process will use the older file, which is ok.

2. Create the new CSV as "export.csv"

3. Run compare-object on both files. That is the tricky part, however.

How should the difference CSV look like? E.g. we could just display all changed lines in the new CSV (only), or try to match the mailbox field of both, and display something like
"mailbox", "old User", "new User", "old AccessRights", "new AccessRights", "old IsValid", "new IsValid"

Open in new window

with non-existent records not having data in the respective fields.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
This in example how this could be done:
compare-object (import-csv Export.CSV | sort) (import-csv Export.Old.CSV | sort) -passthru -property Mailbox,User,AccessRight,IsValid |
  % { $obj = $null } {
  if ($obj -and $obj.mailbox -eq $_.mailbox)
  {
    if ($_.SideIndicator -eq '<=') {
      $obj.NewUser         = $_.User
      $obj.NewAccessRights = $_.AccessRights
      $obj.NewIsValid      = $_.IsValid
    } else {
      $obj.OldUser         = $_.User
      $obj.OldAccessRights = $_.AccessRights
      $obj.OldIsValid      = $_.IsValid
    }
  } else {
    $obj
    $obj = New-Object PSObject -Property @{
      mailbox = $_.Mailbox
      NewUser         = $_.User         * ($_.SideIndicator -eq '<=')
      NewAccessRights = $_.AccessRights * ($_.SideIndicator -eq '<=')
      NewIsValid      = $_.IsValid      * ($_.SideIndicator -eq '<=')
      OldUser         = $_.User         * ($_.SideIndicator -eq '=>')
      OldAccessRights = $_.AccessRights * ($_.SideIndicator -eq '=>')
      OldIsValid      = $_.IsValid      * ($_.SideIndicator -eq '=>')
    }
  }
} {$obj} | Export-Csv Difference.csv
Remove-Item Export.Old.CSV
# use Send-MailMessage for sending the CSV file here.

Open in new window

Author

Commented:
I'm getting the following error when I run the script:

Export-Csv : Cannot bind argument to parameter 'InputObject' because it is null.
At C:\ee\csvcalmodify.ps1:27 char:22
+ } {$obj} | Export-Csv <<<<  Difference.csv
    + CategoryInfo          : InvalidData: (:) [Export-Csv], ParameterBindingValidationException
    + FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.ExportCsvCo
   mmand
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
There has been no difference, or the script is not working to find any. Let's put a check into the code:
$diff = `
compare-object (import-csv Export.CSV | sort) (import-csv Export.Old.CSV | sort) -passthru -property Mailbox,User,AccessRight,IsValid |
  % { $obj = $null } {
  if ($obj -and $obj.mailbox -eq $_.mailbox)
  {
    if ($_.SideIndicator -eq '<=') {
      $obj.NewUser         = $_.User
      $obj.NewAccessRights = $_.AccessRights
      $obj.NewIsValid      = $_.IsValid
    } else {
      $obj.OldUser         = $_.User
      $obj.OldAccessRights = $_.AccessRights
      $obj.OldIsValid      = $_.IsValid
    }
  } else {
    $obj
    $obj = New-Object PSObject -Property @{
      mailbox = $_.Mailbox
      NewUser         = $_.User         * ($_.SideIndicator -eq '<=')
      NewAccessRights = $_.AccessRights * ($_.SideIndicator -eq '<=')
      NewIsValid      = $_.IsValid      * ($_.SideIndicator -eq '<=')
      OldUser         = $_.User         * ($_.SideIndicator -eq '=>')
      OldAccessRights = $_.AccessRights * ($_.SideIndicator -eq '=>')
      OldIsValid      = $_.IsValid      * ($_.SideIndicator -eq '=>')
    }
  }
} {$obj} 

if ($diff)
{ 
  Export-Csv -InputObject $diff Difference.csv
  # use Send-MailMessage for sending the CSV file here.
} else {
  Remove-Item Difference.csv
}
Remove-Item Export.Old.CSV

Open in new window

Author

Commented:
I've manually created an Export.Old.csv document and a blank difference.csv document.  I made a change in the export.csv document.  I ran through the new script, it completed without error.  However, it deleted the Export.old.csv document, and the difference.csv document, and did not provide me with a new difference.csv document.
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
I see what went wrong. The code is pushing a $null object into the pipe, and that gets into the way and makes the CSV export void. Improved code, based on my first try:
compare-object (import-csv Export.CSV | sort) (import-csv Export.Old.CSV | sort) -passthru -property Mailbox,User,AccessRight,IsValid |
  % { $obj = $null } {
  if ($obj -and $obj.mailbox -eq $_.mailbox)
  {
    if ($_.SideIndicator -eq '<=') {
      $obj.NewUser         = $_.User
      $obj.NewAccessRights = $_.AccessRights
      $obj.NewIsValid      = $_.IsValid
    } else {
      $obj.OldUser         = $_.User
      $obj.OldAccessRights = $_.AccessRights
      $obj.OldIsValid      = $_.IsValid
    }
  } else {
    if ($obj) { Write-Output $obj }
    $obj = New-Object PSObject -Property @{
      mailbox = $_.Mailbox
      NewUser         = $_.User         * ($_.SideIndicator -eq '<=')
      NewAccessRights = $_.AccessRights * ($_.SideIndicator -eq '<=')
      NewIsValid      = $_.IsValid      * ($_.SideIndicator -eq '<=')
      OldUser         = $_.User         * ($_.SideIndicator -eq '=>')
      OldAccessRights = $_.AccessRights * ($_.SideIndicator -eq '=>')
      OldIsValid      = $_.IsValid      * ($_.SideIndicator -eq '=>')
    }
  }
} {$obj} | select mailbox, old*, new* | Export-Csv -NoType Difference.csv
Remove-Item Export.Old.CSV
# use Send-MailMessage for sending the CSV file here.

Open in new window

Author

Commented:
Thanks, this runs and does deketed the export.old.csv file and creates the Difference.csv file, however the file is blank. If it helps, here is the contents of each file>

export.old.csv


"Mailbox","User","$_.AccessRights","IsValid"
"TEST-USER","Default","AvailabilityOnly","True"

export.csv

"Mailbox","User","$_.AccessRights","IsValid"
"TEST-USER","Default","MASTERACCESS","True"
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
One issue is "$_.AccessRights" instead of "AccessRights", but I'll check that ...
Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
compare-object (import-csv Export.CSV | sort) (import-csv Export.Old.CSV | sort) -passthru -property Mailbox,User,'$_.AccessRights',IsValid |
  % { $obj = $null } {
  if ($obj -and $obj.mailbox -eq $_.mailbox)
  {
    if ($_.SideIndicator -eq '<=') {
      $obj.NewUser         = $_.User
      $obj.NewAccessRights = $_.'$_.AccessRights'
      $obj.NewIsValid      = $_.IsValid
    } else {
      $obj.OldUser         = $_.User
      $obj.OldAccessRights = $_.'$_.AccessRights'
      $obj.OldIsValid      = $_.IsValid
    }
  } else {
    if ($obj) { Write-Output $obj }
    $obj = New-Object PSObject -Property @{
      mailbox = $_.Mailbox
      NewUser         = $_.User              * ($_.SideIndicator -eq '<=')
      NewAccessRights = $_.'$_.AccessRights' * ($_.SideIndicator -eq '<=')
      NewIsValid      = $_.IsValid           * ($_.SideIndicator -eq '<=')
      OldUser         = $_.User              * ($_.SideIndicator -eq '=>')
      OldAccessRights = $_.'$_.AccessRights' * ($_.SideIndicator -eq '=>')
      OldIsValid      = $_.IsValid           * ($_.SideIndicator -eq '=>')
    }
  }
} {$obj} | select mailbox, old*, new* | Export-Csv -NoTypeInformation Difference.csv
# Remove-Item Export.Old.CSV

Open in new window

I've commented out the remove-item here, so you can test more easily.

Author

Commented:
This works great.  However I found that, when I have a New Mailbox in the export.csv report, it returns results improperly:

Exportold.csv

"Mailbox","User","$_.AccessRights","IsValid"
"CHANGED-USER","Default","AvailabilityOnly","True"
"NEW-USER","Default","AvailabilityOnly","True"

Open in new window



Export.csv

"Mailbox","User","$_.AccessRights","IsValid"
"CHANGED-USER","Default","MASTER ACCESS","True"

Open in new window


Difference.csv

"mailbox","OldIsValid","OldUser","OldAccessRights","NewIsValid","NewUser","NewAccessRights"
"NEW-USER","True","Default","AvailabilityOnly","","",""
"CHANGED-USER","True","Default","AvailabilityOnly","True","Default","MASTER ACCESS"

Open in new window

Qlemo"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015

Commented:
That's correct! NEW-USER is in the "old" file, and so the old* columns are filled. No NEW-USER in the "recent" file, so nothing in new*.

Author

Commented:
I see, i guess the confusion is around the column headings, where the location of them changes in the difference file.  Can you kindly modify the headings to match the source:
"Mailbox","User","$_.AccessRights","IsValid"

so it would be in this order

"Mailbox","OldUser","OldAccessRights","OldisValid","NewUser","NewAccessRights","NewIsValid"

It makes it easier to read.

Thanks so much again.
"Batchelor", Developer and EE Topic Advisor
CERTIFIED EXPERT
Top Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you so much for your patience and assistance!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.