Solved

Automate CSV Comparison and Email Deltas Powershell Script

Posted on 2013-12-18
15
1,427 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
0
Comment
Question by:fireguy1125
  • 8
  • 7
15 Comments
 
LVL 1

Author Comment

by:fireguy1125
ID: 39727206
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39727303
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39727388
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39727452
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39727485
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39727795
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39728084
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Author Comment

by:fireguy1125
ID: 39729754
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39729768
One issue is "$_.AccessRights" instead of "AccessRights", but I'll check that ...
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 39729791
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39732441
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 39733573
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
 
LVL 1

Author Comment

by:fireguy1125
ID: 39736033
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 500 total points
ID: 39736058
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
 
LVL 1

Author Closing Comment

by:fireguy1125
ID: 39736412
Thank you so much for your patience and assistance!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This is a PowerShell web interface I use to manage some task as a network administrator. Clicking an action button on the left frame will display a form in the middle frame to input some data in textboxes, process this data in PowerShell and display…
A procedure for exporting installed hotfix details of remote computers using powershell
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 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

18 Experts available now in Live!

Get 1:1 Help Now