Avatar of fireguy1125
fireguy1125 asked on

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
PowershellMicrosoft ExcelScripting Languages

Avatar of undefined
Last Comment
fireguy1125

8/22/2022 - Mon
ASKER
fireguy1125

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

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

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

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
fireguy1125

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

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

ASKER
fireguy1125

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Qlemo

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

ASKER
fireguy1125

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

One issue is "$_.AccessRights" instead of "AccessRights", but I'll check that ...
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Qlemo

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.
ASKER
fireguy1125

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

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*.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
fireguy1125

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.
ASKER CERTIFIED SOLUTION
Qlemo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
fireguy1125

Thank you so much for your patience and assistance!