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!

Getting Installed Chrome Extensions Remotely

I was looking into whitelisting Chrome extensions via the Google Chrome Group Policy, and I needed to find out which extensions my employees were using. So, I made the following function to help with that. You need to run the function as an account with local admin group membership, since it will use the administrative shares to find the installed extensions. Also worth noting is that all of those "Apps" icons count as extension from Google perspective. Comments in the code, as usual:

################ BEGIN SCRIPT ################

#Example: Get-ChromeExtensions -Computername $Computername -Username $Username

#Define the function with computername and username
function Get-ChromeExtensions {
[CmdletBinding()]            
 Param             
   (                       
    [Parameter(Mandatory=$true,
               Position=0,                          
               ValueFromPipeline=$true,            
               ValueFromPipelineByPropertyName=$true)]            
    [String[]]$ComputerName,
    [Parameter(Mandatory=$true,
               Position=1,                          
               ValueFromPipeline=$true,            
               ValueFromPipelineByPropertyName=$true)]            
    [String[]]$UserName
   )#End Param

Process
{
    #Get Webpage Title
    #I ripped off this function from https://gallery.technet.microsoft.com/scriptcenter/e76a4213-cd05-4735-bf80-d5903171ae11 -Thanks Mike Pfeiffer!
    Function Get-Title { 
    param([string] $url) 
    $wc = New-Object System.Net.WebClient 
    $data = $wc.downloadstring($url) 
    $title = [regex] '(?<=<title>)([\S\s]*?)(?=</title>)' 
    write-output $title.Match($data).value.trim() 
    } #End Function Get-Title

    #Build the path to the remote Chrome Extension folder
    $GoogleExtensionPath = "\\" + $ComputerName + "\C$\Users\" + $Username + "\AppData\Local\Google\Chrome\User Data\Default\Extensions"
    
    #Check that the computer is reachable
    If ((Test-Connection $Computername -Quiet -Count 1) -eq $False){
        Write-Host -foregroundcolor Red "$ComputerName is not online"
        return
    } #End If

    #Check that the path exists
    If ((Test-Path $GoogleExtensionPath) -eq $False){
        Write-Host -foregroundcolor Red "Path not Found: $GoogleExtensionPath"
        Write-Host -foregroundcolor Red "Chrome is probably not installed OR the username has no profile/is wrong" 
        return
    } #End If

    #Get the foldernames, which are the Google Play Store ID #s
    $ExtensionIDNumbers = Get-Childitem $GoogleExtensionPath *. | select name

    #Build a name for the output file
    $OutputFileName = "C:\Temp\GoogleExtensionList_" + $Computername + "_" + $Username + ".csv"

    #Create an array
    $GoogleExtensionsArray = @()

    #Cycle through each Google ID, and look up the Google Play Store Title, which is the extension name
    Foreach ($GoogleID in $ExtensionIDNumbers){
        $GoogleExtensionsArrayItem = New-Object system.object
        $ExtensionSite = "https://chrome.google.com/webstore/detail/adblock-plus/" + $GoogleID.Name
        $Title = ((Get-Title $ExtensionSite).split("-"))[0]
        $GoogleExtensionsArrayItem | Add-Member -MemberType NoteProperty -Name AppName -Value $Title
        $GoogleExtensionsArrayItem | Add-Member -MemberType NoteProperty -Name ExtensionID -Value ($GoogleID.Name)
        $GoogleExtensionsArray += $GoogleExtensionsArrayItem
    } #End Foreach

    #Export the list of extensions
    $GoogleExtensionsArray | export-csv $OutputFileName -NoTypeInformation
    

}#Process

}#Get-ChromeExtensions

################ END SCRIPT ################

What struck me is that we really need to think of the Chrome browser as almost its own OS. You've removed the ability for your users to install applications on their computer (they don't have local admin privs, right?), but what's to stop them from installing Chrome Extensions. Solitaire and Minesweeper are bad, but Bejeweled is ok?

My Neverending Ping Script

It happened that I needed to remote into every one of our computers to do some work. Of course, some systems were offline, go figure. After working through my computer list and removing the ones I was able to access and fix, I wrote a script to ping the rest of them and email me when they were found to be online.

The way the script works, is that you feed it a list of computers, and it goes through trying to ping them. If the ping is successful, it send me an email, then removes that system from the list. The script keeps running until the amount of systems in the list reaches 0.

########################### BEGIN SCRIPT ###########################

#Variables
$ComputerListPath = "C:\Temp\NeverendingPingList.txt"
$To = "me@contoso.com"
$From = "help@contoso.com"
$Body = "Responding to pings!"
$SMTPServer = "mailserver.contoso.com"

#Get Computer List
$Computers = Get-Content $ComputerListPath

#Count the number of computers
$Count = ($Computers | Measure-Object).count

If ($Count -le 0)
{
Write-Host "No computers to ping, exiting"
exit
}

#Create a "matchlist" - if a computer responds, it is added to this list, then when the script iterates through the foreach loop, it skips the computers that exist in this list
[System.Collections.ArrayList]$Matchlist = @()

#Clear the screen
Clear-host

#For each computer in the list, ping it, while the count is greater than 0
Do
{
foreach ($Computer in $Computers)
{
#If the computer matches a member of the matchlist, skip to the next iteration of the foreach loop
If ($Matchlist -contains $Computer) { Continue }

#Test the connection to the computer, using 1 ping packet
$Result = (Test-Connection -ComputerName $Computer -Count 1 -Quiet)

#If no ping response, write to host
If ($Result -eq $false) { Write-Host "No ping received from $Computer - will pass again" }

#If it responds, email me AND remove it from the list of computers, then recount computers
If ($Result -eq $true)
{
$Subject = "$Computer is on the network!!!"
Send-MailMessage -To $To -From $From -SmtpServer $SMTPServer -Body $Body -Subject $Subject
$Matchlist.Add($Computer)
} #End If
} #End Foreach

Write-Host "`r`n========================================================`r`n========================================================`r`n"

#Sleep for 30 seconds
Start-Sleep -Seconds 30

} while ($Count -gt 0)

########################### END SCRIPT ###########################

Getting the Windows Install Date from ALL of Your Computers

This script is inspired by a post I ran across on Windows Networking showing how to extract and format the Windows installation date via Powershell. This data would be useful to me for planning hardware refreshes.

On to the script!

################ BEGIN SCRIPT ################

#Purpose: To scan computers for Windows Install Date

#Choose whether this is an initial scan or a rescan
$InitialChoice = Read-Host "Enter 'I' for initial scan, or 'R' for rescan"

#Output file variables
$MissedComputersFile = "C:\Temp\Script - InstallDatesMissed.txt"
$OutputFile = "C:\Temp\Script - InstallDates.csv"

#Get the computer list, depending on initial choice
If ($InitialChoice -like "I"){
    $Computers = get-adcomputer -filter * | select name | sort name
} #End If

If ($InitialChoice -like "R"){
    $Computers = Get-Content $MissedComputersFile
    Remove-Item $MissedComputersFile -Force
} #End If

#Build an empty array for the data
$Results = @()

#Foreach computer in the list
Foreach ($Computer in $Computers){
    
    #Here I need to pick a naming method but it's dependent on where the data came from (the initial choice and subsequent computer name import)
    #For the initial scan, where data comes from AD:
    If ($InitialChoice -like "I"){$TestSystem = ($Computer.Name)}

    #For the rescan, where data comes from the text file:
    If ($InitialChoice -like "R"){$TestSystem = $Computer}
    
    #Test the connection
    If (Test-Connection -ComputerName $TestSystem -count 1 -quiet){
        
        #Create a new object to populate
        $ResultsEntry = New-Object System.Object
        
        #Get the computer's install date from WMI
        $InstallDate = (Get-WmiObject -ComputerName $TestSystem win32_operatingsystem | select @{Name="InstallDate"; Expression={$_.ConvertToDateTime($_.InstallDate)}}).InstallDate
        
        #Reformat the install date
        $InstallDateRefined = ($InstallDate.Year).ToString() + "-" + ($InstallDate.Month).ToString() + "-" + ($InstallDate.Day).ToString()
        
        #Add the name of the computer to the object
        $ResultsEntry | Add-Member -type NoteProperty -name Name -value $TestSystem
        
        #Add the install date to the object
        $ResultsEntry | Add-Member -type NoteProperty -name OSInstallDate -value $InstallDateRefined
        
        #Add the object to the array
        $Results += $ResultsEntry

        #Print a status message to the screen
        Write-Host -ForegroundColor Green "$TestSystem - Windows Install Date is $InstallDateRefined"
    } #End If
    
    #This runs if the test-connection is no good
    Else {
        
        #Adds the name of the system to the "Missed" file
        $TestSystem | Add-Content $MissedComputersFile

        #Print a status message to the screen
        Write-Host -ForegroundColor Red "$TestSystem - Not Online"
    } #End If
} #End Foreach

#Export the array to CSV
$Results | sort name | export-csv $OutputFile -NoTypeInformation

################ END SCRIPT ################

So after the initial script, you just feed it the "Missed" file over and over until you get everything.