Scheduling a task against Exchange Online

  • Article

A request that customers ask us with some regularity in Exchange Online support is how do I automate tasks against Exchange Online.  Customer are used to their on premise environment where they can regularly run reports on all sorts of information and they want at least some of that in Exchange Online.

The method I am going to highlight today is using a local server (any server) to run a Scheduled Task that will connect to Exchange Online and retrieve some mailbox statistics information.  This is a nice basic implementation that can easily be expanded on.

What we will need:

  • Server that is always online
  • Set of logon credentials for that server
  • Set of EXO credentials that can run our script (Recommend different from your main admin account)
  • Scripts listed below

Implementation time:

30 Minutes

Directions:

For this set of directions I am going to walk you thru setting up and running the script using Start-AutomatedBasicScript.ps1 this is just a simple easy to understand example that will establish a session to O365 and get the Mailbox Statistics for all users listed in recipients.csv.

Prepare the needed inputs:

  1. Log into a server
  2. Create a directory to store the scripts and output ( C:\Scripts )
  3. Copy and save off the three scripts below into your path
  4. Connect to Exchange Online PowerShell
  5. Get the recipients we want to get a report on
    Get-Mailbox -resultsize 100| select-object -property PrimarySMTPAddress | export-csv c:\scripts\recipients.csv
  6. Run the New-StoredCredential.ps1 script
    1. Start Powershell
    2. Change to your directory
    3. Run .\New-StoredCredential.ps1 -Path c:\scripts\O365Account.txt -Password "<your password>"

Create the scheduled task:

  1. Start Task Scheduler
  2. Create a new Basic Task
  3. Name it
  4. Schedule it for One Time (I usually do 10 min in the past)
  5. Choose to have it start a program
  6. Put powershell.exe as the program to run
  7. The arguments should be -noprofile -file c:\scripts\Start-AutomatedBasicScript.ps1
  8. Make sure to choose "Open the properties dialog for this task when I click Finish" then finish creating the task
  9. On the properties page we want to change two options
    1. Select "Run whether user is logged on or not"
    2. Select "Do not store password"

Testing:

To run the script for testing simply right click your task and choose "Run Now".  You won't see any thing pop up but if you refresh the Task Scheduler it will show when the task has completed.

If you have the expected output in c:\scripts\outputXXX.csv then everything worked.  If you don't then we can make some simple changes to try and figure out what is happening.

Option 1: Watch the script

  1. Bring up the properties of the scheduled task.
  2. Change from "Run whether user is logged on or not" to "Run only when user is logged on"

Now when you click "Run Now" the PowerShell window will appear and you can watch the script execute.

Option 2: Get a transcript

Add the following line at the top of your script. Start-Transcript -path c:\scripts

Review the transcript that is created to determine what happened during the script run.

Additional Resources:

Running PowerShell cmdlets for large numbers of users in Office 365 - Very useful script for operating on thousands of Objects in Exchange Online in a robust manner.

Scripts:

*** All scripts/code shown here are covered by the standard disclaimer listed here. ***

New-StoredCredential.ps1
Copy the below script into a notepad document.
Save the document as New-StoredCredential.ps1

 param(
    [Parameter(Mandatory=$true)]
    [string]$Path,
    [Parameter(Mandatory=$true)]
    [string]$password
    )

    # Convert the password to a secure string
    $SecurePassword = $password | ConvertTo-SecureString -AsPlainText -Force
    
    # Store the credential in the path
    $SecurePassword | ConvertFrom-SecureString | Out-File $Path
    
    # Write What we did
    Write-Host "Wrote password to $path"    
    
<# 
.SYNOPSIS
Stores a password in a file on the local computer for retrevial by scripts.

.DESCRIPTION
Used for securely storing a password on a machine for use with automated scripts.

Takes a password and encrypts it using the local account, then stores that password in a file you specify.
Only the account that creates the output file can decrypt the password stored in the file.

.PARAMETER Path
Path and file name for the password file that will be created.

.PARAMETER Password
Plain text version of password.

.OUTPUTS
File Specified in Path variable will contain an encrypted version of the password.

.EXAMPLE
.\New-StoredCredential.ps1 -Path c:\scripts\O365Account.txt -Password "Password123"

Puts the encrypted version of Password123 into the c:\scripts\O365Account.txt file
#>

Start-AutomatedBasicScript.ps1
Copy the below script into a notepad document.
Save the document as Start-AutomatedBasicScript.ps1

 # Constants
[string]$FileAppend = (Get-Date -Format mmddyyyy_) + (Get-Random -Maximum 9999)
$OutputFile = "C:\scripts\output" + $FileAppend + ".csv"
$Username = "admin@company.onmicrosoft.com"
$RecipientCSV = "c:\scripts\recipients.csv"
$PasswordPath = "c:\scripts\password.txt"

# Read the password from the file and convert to SecureString
Write-Host "Getting password from $passwordpath"
$SecurePassword = Get-Content $PasswordPath | ConvertTo-SecureString

# Build a Credential Object from the password file and the $username constant
$Credential = New-Object System.Management.Automation.PSCredential -ArgumentList $Username, $SecurePassword

# Open a session to O365
$O365Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection
import-pssession $O365session

# Get the Recipients to work with
$Recipients = Import-Csv $RecipientCSV

# Get the Mailbox Statistics
$Recipients | Foreach {
    Get-Mailboxstatistics -Identity $_.primarysmtpaddress.tostring() | Select-Object Identity,DeletedItemCount,TotalDeletedItemSize,TotalItemSize | Export-Csv $OutputFile -Append 
}

<# 
.SYNOPSIS
Simple Example Script that can be run in an automated manner against Exchange Online

.DESCRIPTION
Simple Example script for runing an automated task against Exchange Online.
     * All output/input files are defined in the #Constatns section
     * Connects to O365 using password stored on the local machine
     * Imports list of recipients from a CSV file
     * Get Mailbox Statistics information and exports to CSV

.INPUTS
recipients.csv - Csv of recipients to gather data on expected to include PrimarySMTPAddress property
password.txt   - Encrypted password for O365 account you can use New-StoredCredentials.ps1 to create this file

.OUTPUTS
A CSV file with the requested mailbox statistics information.  Will be stamped with the date and a random number to make it unique. 

.EXAMPLE
.\Start-AutomatedBasicScript.ps1

#>

Start-AutomatedScript.ps1
Copy the below script into a notepad document.
Save the document as Start-AutomatedScript.ps1

 # Constants
[string]$FileAppend = (Get-Date -Format mmddyyyy_) + (Get-Random -Maximum 9999)
$OutputFile = "C:\scripts\output" + $FileAppend + ".csv"
$Username = "admin@contoso.onmicrosoft.com"
$LogFile = "C:\scripts\testing.log"
$ExportRecipients = "c:\scripts\recipients.csv"
$PasswordPath = "c:\scripts\password.txt"


# Writes output to a log file with a time date stamp
Function Write-Log {
    Param ([string]$string)
    
    # Get the current date
    [string]$date = Get-Date -Format G
        
    # Write everything to our log file
    ( "[" + $date + "] - " + $string) | Out-File -FilePath $LogFile -Append
    
    # If NonInteractive true then supress host output
    if (!($NonInteractive)){
        ( "[" + $date + "] - " + $string) | Write-Host
    }
}

# Setup a new O365 Powershell Session
Function New-CleanO365Session {
    
    # If we don't have a credential then prompt for it
    $i = 0
    while (($Credential -eq $Null) -and ($i -lt 5)){
        $script:Credential = Get-Credential -Message "Please provide your Exchange Online Credentials"
        $i++
    }
    
    # If we still don't have a credentail object then abort
    if ($Credential -eq $null){
        Write-log "[Error] - Failed to get credentials"
        Write-Error -Message "Failed to get credentials" -ErrorAction Stop
    }

    Write-Log "Removing all PS Sessions"

    # Destroy any outstanding PS Session
    Get-PSSession | Remove-PSSession -Confirm:$false
    
    # Force Garbage collection just to try and keep things more agressively cleaned up due to some issue with large memory footprints
    [System.GC]::Collect()
    
    # Sleep 15s to allow the sessions to tear down fully
    Write-Log ("Sleeping 15 seconds for Session Tear Down")
    Start-sleep -seconds 15

    # Clear out all errors
    $Error.Clear()
    
    # Create the session
    Write-Log "Creating new PS Session"
    
    $session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri "https://outlook.office365.com/powershell-liveid/" -Credential $Credential -Authentication Basic -AllowRedirection
        
    # Check for an error while creating the session
    if ($Error.Count -gt 0){
    
        Write-Log "[ERROR] - Error while setting up session"
        Write-log $Error
        
        # Increment our error count so we abort after so many attempts to set up the session
        $ErrorCount++
        
        # if we have failed to setup the session > 3 times then we need to abort because we are in a failure state
        if ($ErrorCount -gt 3){
        
            Write-log "[ERROR] - Failed to setup session after multiple tries"
            Write-log "[ERROR] - Aborting Script"
            exit
        
        }
        
        # If we are not aborting then sleep 60s in the hope that the issue is transient
        Write-Log "Sleeping 60s so that issue can potentially be resolved"
        Start-sleep -seconds 60
        
        # Attempt to set up the sesion again
        New-CleanO365Session
    }
    
    # If the session setup worked then we need to set $errorcount to 0
    else {
        $ErrorCount = 0
    }
    
    # Import the PS session
    $null = Import-PSSession $session -AllowClobber
    
    # Set the Start time for the current session
    Set-Variable -Scope script -Name SessionStartTime -Value (Get-Date)
}

##### Main #####

# Read the password from the file and convert to SecureString
Write-log "Getting password from $passwordpath"
$SecurePassword = Get-Content $PasswordPath | ConvertTo-SecureString

# Build a Credential Object from the password file and the $username constant
$Credential = New-Object System.Management.Automation.PSCredential -ArgumentList $Username, $SecurePassword

# Connect to O365
New-CleanO365Session

# Get the Recipients to work with
Write-Log "Getting Recipients"
$Recipients = invoke-command -session (get-pssession) -scriptblock {get-mailbox -resultsize unlimited | select-object -property DisplayName,Identity,PrimarySMTPAddress}

# Export them to CSV for documentation
Write-Log "Exporting " + $Recipients.count + " to csv " + $ExportRecipients
$Recipients | Export-Csv $ExportRecipients

# Call Start-RobustCloudCommand to gather the data
# https://blogs.technet.microsoft.com/exchange/2015/11/02/running-powershell-cmdlets-for-large-numbers-of-users-in-office-365/
C:\temp\Start-RobustCloudCommand.ps1 -Agree -LogFile $LogFile -Recipients $Recipients -ScriptBlock { Get-Mailboxstatistics -Identity $_.primarysmtpaddress.tostring() | Select-Object Identity,DeletedItemCount,TotalDeletedItemSize,TotalItemSize | Export-Csv $OutputFile -Append } -Credential $Credential

<# 
.SYNOPSIS
Example Script that can be run in an automated manner against Exchange Online

.DESCRIPTION
Example script for runing an automated task against Exchange Online.
     * All output/input files are defined in the #Constatns section
     * Connects to O365 using password stored on the local machine
     * Gets list of recipients from O365
     * Get Mailbox Statistics information and exports to CSV using a robust connection method

.OUTPUTS
A CSV file with the requested mailbox statistics information.  Will be stamped with the date and a random number to make it unique. 

.EXAMPLE
.\Start-AutomatedScript.ps1

#>