How to Automate Office 365 Tasks with Azure Automation?

Before the advent of cloud computing, you typically would need to provision a server to host and run your automation tasks. So, you not only manage the automation part, but you also have to manage the server resources to ensure that your automation tasks’ performance does not suffer along the way.

Various cloud-hosted solutions allow you to focus on managing your apps and tasks and forget about hardware scaling. One of these solutions is Azure Automation. With it, you can deploy your scripts in a serverless setup supporting common automation languages and workflows to fit your automation needs.

In this post, we’ll tackle a basic real-world example of using Azure Automation Runbooks to automate tasks in Office 365. Specifically, we’ll build an Azure Automation Runbook that generates an Office 365 tenant’s storage utilization for Exchange Online, SharePoint Online, and OneDrive for Business.

Requirements

  • An Azure tenant where you’ll create the automation runbook and account.
  • Access to an Office 365 Tenant with a global administrator role. This tenant will be the target of the automation task.

Step 1: Register the App in Azure Active Directory

Before we start working on Azure Automation, let’s create the application registration in Azure AD. This app is what we’ll use to access the storage report.

ADVERTISEMENT
  1. Log in to the Azure AD admin center.
  2. Go to the App registrations blade and click New registration.
    azure automation
  3. On the Register an application page:
    1. Type the application name StorageReportApp or whatever app name you wish to use.
    2. Select the option — Account in this organizational directory only.
    3. Select the Redirect URI — Web
    4. Enter the URL — http://localhost
    5. Click Register.
      azure automation office 365
    6. Once the application is created, copy the Application (client) ID and Directory (tenant) ID for later use.
      microsoft azure automation
  4. Next, let’s add the necessary permissions to the app to read the storage report. Click the API permissions blade and click Add a permission.
    azure process automation
  5. Click Microsoft Graph.
    azure ad automation
  6. Do the following:
    1. Click Application permissions.
    2. Enable Reports.Read.All.
    3. Enable Mail.Send.
    4. Click Add permissions.
      azure automation workflow
  7. After adding the permissions, you should see them on the list under the Microsoft Graph group. Click Grant consent for Yes.
    azure automatic
  8. The API permission status then changes to Granted for <tenant>.
    Untitled
  9. Next, click the Certificates & secrets blade → Client secretsNew client secret.
    Untitled
  10. Enter a description (optional), select the secret key expiration period, and click Add.
    Untitled
  11. Finally, copy the new secret value. Keep it secured and treat it like you would a password.
    Untitled

Step 2: Create the Azure Automation Account

You have the Azure AD app; now, let’s create the automation account that will host the Azure automation runbook.

  1. Login to your Azure tenant.
  2. Go to the Automation Accounts blade and click Create.
    Untitled
  3. On the Create an Automation Account page:
    • Select the Azure subscription.
    • Select the existing resource group (or create a new one) where you want to create the automation account.
    • Enter the automation account name. This name cannot be changed once it’s created.
    • Select the region.
    • Click Review + Create.
      Untitled
  4. Review the automation account summary and click Create.
    Untitled
  5. Once the automation account is created, click the Go to resource button.
    Untitled

Step 3: Store the Azure Automation Runbook Assets

Assets are the shared resources across all Azure runbooks in the same automation account. There are currently 7 types of assets:

  • Schedules
  • Modules
  • Python packages
  • Credentials
  • Connections
  • Certificates
  • Variables

In this step, we will create 5 assets using only 2 types:

  • ClientID and ClientSecret to hold the Application ID and Secret Key Value, respectively. Because of the confidential nature of the ClientID and ClientSecret, we’ll treat them as a credential pair (like username and password).
  • TenantID, EmailFrom, and EmailTo can be treated as regular string variables. These variables will hold the Tenant ID, sender email address, and recipient email address, respectively.

Let’s begin.

  1. Click Credentials Add a credential.
    Untitled
  2. On the New Credential fly-out:
    1. Enter the credential name. In this example, use the same name as the Azure AD app you registered. This way, you can quickly identify the account.
    2. Enter the credential description.
    3. In the User name box, enter the Client ID.
    4. Enter the Secret Key value in the Password box and Confirm password box.
    5. Click Create.
      Untitled
  3. Click the Variables blade and click Add variable.
    Untitled
    On the New Variable fly-out:

    1. Enter the variable name TenantID.
    2. Select the variable type String.
    3. Enter the Tenant ID value.
    4. Click Create.
      Untitled
  4. Repeat the same step to create two more variables called EmailFrom and EmailTo. In the end, you should have 3 variables, as shown below.
    Note. The EmailFrom sender email address can be any valid mailbox in your Exchange Online organization. This could be a user mailbox or a shared mailbox email address.
    Untitled

Step 4: Create the Azure Automation Runbook

Let’s now create the runbook to define the automation task.

  1. Click RunbooksCreate a runbook.
    Untitled
  2. On the Create a runbook form:
    1. Enter the Azure runbook name. In this example, let’s name it Office365StorageReport.
    2. Choose PowerShell as the runbook type.
    3. Select 5.1 as the runtime version. You may also select 7.1 or 7.2, but in this example, there’s no significant benefit to choosing either.
    4. Click Create.
      Untitled
  3. Once the Azure runbook is created, click the new runbook to open it.
    Untitled
  4. Click Edit.
    Untitled
  5. The editor opens, where we can write our automation script.
    Untitled
  6. Copy the code below, paste it into your Azure runbook, and click Save.
    This script retrieves the Exchange Online, SharePoint Online, and OneDrive for Business storage usage statics for the last 7 days.An HTML report is then sent to the recipients via email. At this point, you don’t need to make changes to the script. Just copy and paste it as-is.
    Tip. The Get-AutomationPSCredential cmdlet retrieves the credential asset, while the Get-AutomationVariable retrieves the variable assets.

    
    <# 
    
    - This script retrieves the Exchange Online, SharePoint Online, and OneDrive for Business storage usage statics for the last 7 days. 
    
    - An HTML report is then sent to the recipients via email. 
    
    #> 
    
    # Import automation variables (DO NOT CHANGE) 
    
    $Credential = Get-AutomationPSCredential -Name 'StorageReportApp' 
    
    $ClientID = $Credential.UserName 
    
    $ClientSecret = $Credential.GetNetworkCredential().Password 
    
    $TenantID = Get-AutomationVariable TenantID 
    
    $EmailFrom = Get-AutomationVariable EmailFrom 
    
    $EmailTo = Get-AutomationVariable EmailTo 
    
    # Create the request splat 
    
    $request = @{ 
    
    Body = @{ 
    
    grant_type = 'client_credentials' 
    
    scope = 'https://graph.microsoft.com/.default' 
    
    client_id = $ClientID 
    
    client_secret = $ClientSecret 
    
    } 
    
    ContentType = 'application/x-www-form-urlencoded' 
    
    Method = 'POST' 
    
    Uri = "https://login.microsoftonline.com/$($TenantID)/oauth2/v2.0/token" 
    
    } 
    
    # Get token 
    
    $oauth = Invoke-RestMethod @request 
    
    # Get SharePoint Online Storage Usage 
    
    $spoStorageUsage = (Invoke-RestMethod ` 
    
    -Method Get ` 
    
    -Uri "https://graph.microsoft.com/beta/reports/getSharePointSiteUsageStorage(period='D7')?`$format=application/json" ` 
    
    -Headers @{Authorization = "Bearer $($oauth.access_token)" } ` 
    
    -ContentType' application/json').Value 
    
    # Get Exchange Online Storage Usage 
    
    $exoStorageUsage = (Invoke-RestMethod ` 
    
    -Method Get ` 
    
    -Uri "https://graph.microsoft.com/beta/reports/getMailboxUsageStorage(period='D7')?`$format=application/json" ` 
    
    -Headers @{Authorization = "Bearer $($oauth.access_token)" } ` 
    
    -ContentType' application/json').Value 
    
    # Get OneDrive for Business Storage Usage 
    
    $odbStorageUsage = (Invoke-RestMethod ` 
    
    -Method Get ` 
    
    -Uri "https://graph.microsoft.com/beta/reports/getOneDriveUsageStorage(period='D7')?`$format=application/json" ` 
    
    -Headers @{Authorization = "Bearer $($oauth.access_token)" } ` 
    
    -ContentType' application/json').Value | Where-Object { $_.SiteType -eq 'OneDrive' } 
    
    # Build the report 
    
    $html = 
    
    '<html><head><title>EXO and SPO Storage Usage Report</title><http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" /> 
    
    <style type=" text/css"> 
    
    table { 
    
    font-family: "Calibri", "Tahoma"; 
    
    width: auto; 
    
    background-color: #FFFFFF; 
    
    border-collapse: collapse; 
    
    border-width: 1px; 
    
    border-color: #052561; 
    
    border-style: solid; 
    
    color: #000000; 
    
    } 
    
    table td, table th { 
    
    border-width: 1px; 
    
    border-color: #052561; 
    
    border-style: dotted; 
    
    padding: 5px; 
    
    } 
    
    table th { 
    
    background-color: #7EA8F8; 
    
    } 
    
    </style></head><body><table> 
    
    <tr><th>Date+Size(TB)</th><th>Exchange Online</th><th>SharePoint Online</th><th>OneDrive for Business</th></tr>' 
    
    for ($i = 0 ; $i -lt ($exoStorageUsage.Count); $i++) { 
    
    $exoSize = "{0:N2}" -f $([System.Math]::Round(($exoStorageUsage[$i].storageUsedInBytes / 1TB), 2)) 
    
    $spoSize = "{0:N2}" -f $([System.Math]::Round(($spoStorageUsage[$i].storageUsedInBytes / 1TB), 2)) 
    
    $odbSize = "{0:N2}" -f $([System.Math]::Round(($odbStorageUsage[$i].storageUsedInBytes / 1TB), 2)) 
    
    $html += "<tr><th>$($exoStorageUsage[$i].reportDate)</th><td>$($exoSize)</td><td>$($spoSize)</td><td>$($odbSize)</td></tr>" 
    
    } 
    
    $html += '</table></body></html>' 
    
    # Send email 
    
    $mailBody = @{ 
    
    message = @{ 
    
    subject = 'Office 365 Storage Usage Report' 
    
    body = @{ 
    
    content = $($html -join "`n") 
    
    contentType = "HTML" 
    
    } 
    
    internetMessageHeaders = @( 
    
    @{ 
    
    name = "X-Mailer" 
    
    value = "Office 365 Storage Usage Report" 
    
    } 
    
    ) 
    
    ToRecipients = @() 
    
    } 
    
    } 
    
    # Add recipients 
    
    $EmailTo | ForEach-Object { 
    
    $mailBody.message.ToRecipients += @{ 
    
    EmailAddress = @{ 
    
    Address = $_ 
    
    } 
    
    } 
    
    } 
    
    $emailReportSplat = @{ 
    
    Method = 'Post' 
    
    Uri = "https://graph.microsoft.com/beta/users/$($EmailFrom)/sendmail" 
    
    Body = $($mailBody | ConvertTo-Json -Depth 4) 
    
    Headers = @{Authorization = "Bearer $($oauth.access_token)" } 
    
    ContentType = 'application/json' 
    
    } 
    
    Invoke-RestMethod @emailReportSplat

    Untitled

Step 5: Test the Azure Runbook

This time, let’s run a test to determine if the runbook will work as intended.

  1. Click the Test pane button.
    azure automation schedule
  2. Click Start to test the Azure runbook.
    create azure automation account powershell
    The status changes to Queued.
    azure ad automation
    To Running.
    azure automation in office 365
    And finally, to Completed.
    azure automation powershell
  3. How do you know it worked? Open the recipient’s mailbox, and you should see the email report sent from the Azure runbook, similar to the one below.
    microsoft azure ad automation
  4. Close the test pane.
    azure process automation office 365
  5. Since you’ve confirmed that the Azure runbook works, we can now publish it. To do so, click the Publish button and click Yes to confirm.
    azure automation examples
    The Azure runbook status now changes to Published.
    azure automation example

Step 6: Add a Schedule

Automating a task means should involve no manual intervention in running it. In this case, you must add a schedule (or multiple schedules) to execute the runbook unattended.

To accomplish this, let’s create a new schedule asset.

  1. On the runbook page, click SchedulesAdd a schedule.
    azure ad automation runbook schedule
  2. Next, click Link a schedule to your runbook.
    schedule runbook azure ad
  3. Since we have yet to create a schedule asset, there is no schedule we can link to the runbook. So let’s create one. Click the Add a schedule button.
    azureautomationtutorial
  4. On the New Schedule fly-out, fill out the form using the below details as an example.
    1. NameDaily@0600.
    2. DescriptionDaily at 6AM.
    3. Starts at — set the start date and the time at 6:00 AM.
    4. Time zone — select the appropriate timezone.
    5. RecurrentRecurring.
    6. Recur every1 Day.
    7. Set expirationNo.
      Once you’ve completed the schedule form, click Create.
      create a runbook in azure
  5. Finally, click OK.
    create runbook
    And the Azure runbook is now scheduled to run daily at 6:00 AM.
    create azure runbook

Conclusion

Automation with Azure runbooks is a quick and convenient solution to deploying your automation tasks in the cloud. Moreover, each automation account has a free 500 minutes of runtime per month.

And according to the Azure automation pricing table, the excess is at $0.002 per minute or $1 for another 500 minutes. Isn’t that just great?

I enjoy technology and developing websites. Since 2012 I'm running a few of my own websites, and share useful content on gadgets, PC administration and website promotion.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.