• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1676
  • Last Modified:

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
0
fireguy1125
Asked:
fireguy1125
  • 8
  • 7
1 Solution
 
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

 
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
One issue is "$_.AccessRights" instead of "AccessRights", but I'll check that ...
0
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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
 
QlemoC++ DeveloperCommented:
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:
Thank you so much for your patience and assistance!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now