Using Powershell to Sift Through My Email

Every morning I have over 100 new emails. Most of these I glance at and archive, because I only need to know that the processes ran. After thinking about how to better optimize this, it occured to me that I wasn't getting the information I needed.

For example, I get over 20 emails from Veeam about Backup Jobs and BackupCopy Jobs. They're successful (if they aren't there's a rule that forwards the offending email to my normal email address), so what's the problem? Well, the problem is that I see that they're successful, grab the whole chunk, and mark as read/archive. Was there 20? Or only 19? Now, in this example I would know if one of the jobs was hung or something because I'm running another script to check for snapshots before the workday, but what about other things like MySQL backups, or Backup Exec jobs (yes, shudder)?

So, here's what I'm doing. These emails are all sent to a reporting mailbox, that forwards any emails with issues to the appropriate personnel. I will use Veeam email as an example.

An email with a subject of "Veeam Job [Success] Daily-Job" comes in. A rule on my reporting mailbox marks it as read and throws it into an "Archive" subfolder.

At 7:15AM, a scheduled task runs on my computer at work, with Outlook open and the reporting mailbox loaded. I'm going to do this script in pieces, explaining each part in between.

#Mailbox Name
$account_address = "reporting"

#Folder in that Mailbox
$mails_folders = "archive"

#Email Variables
$To = "me@contoso.com"
$From = "reporting@contoso.com"
$SMTPServer = "mail.contoso.com"
$Subject = "Reporting Mailbox Summary"

##########################################################################
#          Date/Time Variables                                           #
##########################################################################

#Begin: 5pm yesterday 
$BeginningDateTimeString = (((Get-Date).AddDays(-1)).ToString("yyyy-MM-dd") + " 17:00:00")
[datetime]$BeginningDateTime = $BeginningDateTimeString

#End 7am today
$EndDateTimeString = ((Get-Date).ToString("yyyy-MM-dd")) + " 07:00:00"
[datetime]$EndDateTime = $EndDateTimeString

##########################################################################
#          Stuff with Outlook                                            #
##########################################################################

#Create outlook.application object
$outlook = new-object -com outlook.application
$MailNameSpace = $outlook.GetNameSpace("MAPI")
$MailFolders = $MailNameSpace.Folders |? {$_.Name -eq $account_address}

#Getting main inbox folder
$inbox = $MailFolders.Folders |? {$_.Name -eq "Inbox"}

#Specify folder of mails to calculate
$folder_to_calculate = $inbox.Folders |? {$_.Name -eq "$mails_folders"}

#Get the mail
$Emails = $folder_to_calculate.Items


At this point, $Emails has all of my mail in it (I have autoarchive enabled on the mailbox to delete after 30 days). Now, I'm only interested in a subset of this data. Searching through a months worth of email would take awhile, so I constrain the dataset with the following line, so I only get email received last night after 5pm and before this morning at 7am (see date/time variables above):

$TimePeriodMails = $Emails | Where-Object {$_.ReceivedTime -gt $BeginningDateTime -and $_.ReceivedTime -lt $EndDateTime}

Now I filter what I'm interested in by subject. Here's 4 lines:

$ApplicableMails = $TimePeriodMails | where-object {
    $_.TaskSubject -like 'Backup Exec Alert: Job Success *' -or `
    $_.TaskSubject -like "PS Report - GPO Backup Report" -or `
    $_.TaskSubject -like "PS Report - MySQL Backup Status - SUCCESS - *" -or `
    $_.TaskSubject -like "Veeam Job ``[Success``] Daily-*"}

One interesting tidbit I discovered, through much gnashing of teeth and Googling, is that when you do a string comparison, and the string includes square brackets you have to double-escape them (using the backtick)!

Once I have my emails, I just need to gather a count of the data, which I did like so:

#Create an array to hold the data
$ResultArray = @()

#Look for the Backup Exec Emails and count them

$ArrayItem = New-Object psobject

$ArrayItem | Add-Member -MemberType NoteProperty -Name Name -Value "BE Backup Successful"

$BESuccessMails = $ApplicableMails | where-object {$_.TaskSubject -like 'Backup Exec Alert: Job Success (Server: *'}

#Count those, and convert that number to a string

$BESuccessMailsCount = (($BESuccessMails | measure-Object).count).ToString()

#This next line is for my reference, once I get the process down, I'll put in here how many emails I should see. 

#You'll see why this is important (to me) later.

$BESuccessMailsCountShouldBe = "777"

#Make an array item and add the data I want to the result array

$ArrayItem | Add-Member -MemberType NoteProperty -Name ShouldBe -Value $BESuccessMailsCountShouldBe

$ArrayItem | Add-Member -MemberType NoteProperty -Name Is -Value $BESuccessMailsCount

$ResultArray += $ArrayItem

I'll spare you the other 4 search blocks; they're the same format, just with different names and data to look for.

The last step is to add some formatting, because who doesn't like a nice table to look at? You'll see here that I've included the number of emails I SHOULD see, so that with minimal effort I can deduce that all of my stuff ran.

##########################################################################

#          Format and Send                                                                                                           #

##########################################################################

#HTML Style Formatting

$style = "<style>BODY{font-family: Arial; font-size: 10pt;}"

$style = $style + "TABLE{border: 2px solid black; border-collapse: collapse;}"

$style = $style + "TH{border: 2px solid black; background: #dddddd; padding: 5px; }"

$style = $style + "TD{border: 2px solid black; padding: 5px; }"

$style = $style + "</style>"

#Export the array, with the style, to HTML

$Body = $ResultArray | ConvertTo-Html -Head $style | out-string

Send-Mailmessage -To $To -From $From -SMTPServer $SMTPServer -Subject $Subject -Body $Body -BodyAsHTML

It looks like this , which is much abbreviated, and not using the same fields as above (sorry):

You'll notice here that I haven't received the number I expected, which I've since fixed (this was due to that double-escaping of square brackets!).

Thanks for reading, and Happy Thanksgiving!