Azure Function - Email Activity Report

This Azure function generates and saves the email activity report that is found in the M365 Admin Center under 'Reports > Usage > Exchange > Email Activity' into SharePoint based on a timer trigger using PowerShell on Linux.

Required Resources in Azure

Function App

The function app runs our PowerShell script based on a timer. In my use case, it runs every Monday at 12am UTC. Use a cron expression calculator to help modify this for your use case. I used VS Code with the Azure Function extension installed to create & upload my function.

Settings:

• Plan Type: Flex Consumption
• Instance Memory: 512MB
• OS: Linux
• PowerShell 7.4

Environment variables:

• APPID: Application (Client) ID from our App Registration, which provides API access to the tenant.
• TENANT_NAME: Name of the Entra ID tenant (domain name).
• TENANTID: Entra ID tenant ID.
• REPORT_DAYS: Number of days for which the report will pull data. The valid values are 7, 30, 90, and 180. This doesn't affect the frequency of when the report runs.
• KEYVAULT_CERT_PASSNAME: Name of the certificate password inside of Key Vault.
• KEYVAULT_CERTNAME: Name of the certificate inside of Key Vault.
• KEYVAULT_NAME: Name of the Key Vault.
• KEYVAULT_SECRETNAME: Name of the secret inside of Key Vault.
• SP_EXPORTFOLDER: SharePoint folder path where the report will be saved. Example: 'Documents/Reports/Weekly Email Activity Reports'
• SP_SITE: SharePoint site URL where the exported report will be saved. Example: https://contoso.sharepoint.com/sites/IT
• FILTER_FOR_MAILBOXES: (Optional) List of mailboxes to filter our report for. If this value doesn't exist, a report for all active mailboxes in the tenant will be generated. Example: 'it@contoso.com,hr@contoso.com,info@contoso.com,support@contoso.com'

Key Vault

Here we securely store our authentication tokens, including a certificate for Exchange and PnP PowerShell and a secret for MS Graph. To be able to use these in the script, we need to grant our function app the following IAM roles to our function:

• Key Vault Certificate User
• Key Vault Secrets User
• Key Vault Reader

Storage Account

Azure Functions require a storage account to store an application package. This is required when creating a new function app. The function will create these under blob storage and its access tier should be 'hot.'

App Registration

The app registration will give us API access to the tenant's data. Once we grant API access, we need to generate a certificate and secret to be used by the azure function for authentication to pull the requested data. We'll use the certificate to authenticate to Exchange Online and PnP PowerShell and the secret for MS Graph.

The following application API access is required:

• Office 365 Exchange Online
• Exchange.ManageAsAppV2
• Microsoft Graph
• Reports.ReadAll
• SharePoint
• Sites.FullControl.All

PowerShell Script

There were some gotchas I ran into when setting this up.

One question you might have at this point is "Why are we connecting to both Exchange and Graph?" This is because the report we generate comes from Graph, but it has a bug which causes shared mailboxes to show a send count of '0' even if they have sent emails within the timeframe. We use a command within Exchange PowerShell to grab the correct send count then update that column in the report.

We use a certificate, stored in Key Vault, for authenticating to Exchange and PnP PowerShell. To grab this, there is a command Get-AzKeyVaultCertificate, however, this command actually grabs the metadata of that certificate and not the actual value. To get the value, we use Get-AzKeyVaultSecret (works for both secrets and certificates), then convert it from base64 to binary, which gets passed to the x509 certificate constructor.

The Graph command that pulls the report has a required parameter '-OutFile,' which meant that the initial report needs to be stored somewhere (I chose the temp folder for the function). We then re-imported the report so we can modify the send count column.

I mentioned that we connect to PnP PowerShell, which is used to upload the report directly to SharePoint. You may be asking "we already connect to Microsoft Graph which has a 'sites' module within it so why not save a step here?" I did try to utilize this module, but I ran into an issue where the function would freeze then timeout when attempting to import it. PnP had issues loading when I tried through profile.ps1, so I ended up importing it directly in run.ps1 when I needed it. I'm unsure what the exact issue was with Graph.Sites but maybe Windows based functions or one with better resources wouldn't have this issue.

Improvements

As previously mentioned, switching from PnP PowerShell to Graph.Sites for the upload portion would simplify the script slightly. This could have been a resource limitation with my function or I need to play around with how I import it.

Having both a secret and a cerificate is not ideal. Being able to utilize just one for authentication would make the setup easier and be one less thing to remember to renew.

In Azure, the key vault is being accessed over the public Internet which is great for cost but not good for security. Spending extra money to create a virtual network and attaching private endpoints to both the key vault and azure function would help prevent leaking the authentication tokens.

The API permissions, specifically for the Exchange Online and SharePoint, are too permissive. Finding what the least required permissions are would increase the security of this app if the authentication tokens were ever leaked.

Cost

As configured above, the Azure resources costs roughly $.10 - $.60 cents per month. This depends on how frequent your function runs, with 90% of the cost coming from the storage account. If you decide to create a virtual network to keep the key vault access off the public Internet, the cost would be closer to $10 due to the required private endpoints.