Automatically Generating Email Counts with PowerShell and Orchestrator

Recently, a manager at our company was wondering if we can get monthly email counts for a few specific mailboxes and if we could possibly get an automatic email sent every day for the count of the previous day's email.

Certainly! Time to pull out PowerShell and Orchestrator!


So, here is the basic workflow I'm going to be shooting for: 

Okay! Time to start up Runbook Designer.

First thing's first - let's generate our recurring schedule. I want this job to run every day at 12:01 AM. I find a Monitor Date/Time object and add it to the beginning of my Runbook.

Now, I'll need to call PowerShell.exe on my Orchestrator server (ORCH12) and have it call my saved .ps1 file.

I know it is possible to run PowerShell directly through a .NET Script object in Orchestrator, but I prefer this way as I am able to generate my .ps1 files remotely, test them, and simply sign and copy them to my ORCH12 server's script repository.

Configure the Details for the Program Execution command to reflect the following:
  • Computers: The Runbook Server you wish to perform the task
  • Program Path: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
  • Parameters: -c "[The UNC path to your saved .ps1 script ]"
    • In my environment, this field is:
      • -c "\\orch12\c$\POSH_Scripts\Exchange\EmailCounts\EmailCounts.ps1"
  • Working Folder: The local path (in reference to the 'Computer' setting above), that you wish this PowerShell script to be run from.
    • In my environment, this field is: 
      • C:\POSH_Scripts\Exchange\EmailCounts\
Continue through the Advanced/Security sections and configure authentication for this command to run through. 

Now, we simply need the actual PowerShell script that will generate our information and send our email. I'm choosing to send emails directly out of my PowerShell script as, again, I simply find it easier to test the entire thing on my computer before deploying out to a Runbook Server.

The script is suspiciously simple. The example below is actually hardcoded for a single day previous as well as the email address as the subject of the email count. Modifying these is quite trivial, and I may cover it at a further date.

#Import the Exchange PowerShell Module
if (Test-Path 'C:\Program Files\Microsoft\Exchange Server\V14\Bin\RemoteExchange.ps1') {
     Import-Module 'C:\Program Files\Microsoft\Exchange Server\V14\bin\RemoteExchange.ps1';
     Write-Host "Type in the command Connect-ExchangeServer -auto; to connect to Exchange Online" -BackgroundColor Black -ForegroundColor Green
     } else {
          Write-Host "Exchange Management Shell Not Installed"
#Connect to my On Prem Exchange Server
Connect-ExchangeServer -Auto
#Store the 'running time' into a variable
$runningtime = (Get-Date -DisplayHint DateTime)
# This following section will generate my email body as well as actually find the count of all emails sent from in the last 1 day.
[String]$results = "Your email account has sent "
[String]$results += (Get-MessageTrackingLog -Recipients -EventId "RECEIVE" -Start (get-date).AddDays(-1) ).count
[String]$results += " emails in the last 24 hours. This report was generated on "
[String]$results += $runningtime
[String]$results += "."
# Now, send our email output
Send-MailMessage -SmtpServer email.internal.domain -From -To -Subject "Your Daily Email Counts for" -Body $results

Once you have saved your .ps1, you should be able to simply start the Runbook and have your email counts be sent out automatically every day!

Another win for automation!


Popular Posts