Click an Ad

If you find this blog helpful, please support me by clicking an ad!

Tuesday, November 24, 2015

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!

Thursday, November 19, 2015

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?