Automate CSV Comparison and Email Deltas Powershell Script

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
LVL 1
fireguy1125Asked:
Who is Participating?
 
QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Line 26:
} {$obj} | select mailbox, oldUser, OldAccessRights, OldIsValid, newUser, newAccessRights, newIsValid | Export-Csv -NoTypeInformation Difference.csv

Open in new window

Not really difficult, is it?
0
 
fireguy1125Author 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

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

0
 
fireguy1125Author 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
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

0
 
fireguy1125Author 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.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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

0
 
fireguy1125Author 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"
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
One issue is "$_.AccessRights" instead of "AccessRights", but I'll check that ...
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
 
fireguy1125Author 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

0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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*.
0
 
fireguy1125Author 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.
0
 
fireguy1125Author Commented:
Thank you so much for your patience and assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.