Send a weekly summary report

Email a short weekly report to a manager or customer contact in your organisation without anyone remembering to run it. This example reports Microsoft 365 licence use and disabled accounts that still hold licences, but the pattern (gather, summarise, send, on a schedule) works for any figures an action can reach. For MSPs and IT teams that want regular reporting without a reporting tool.

Why use an action instead of asking SuperIT

SuperIT answers licence questions in chat whenever someone asks, through its read-only Microsoft 365 connection. It can't send an email, and it can't do anything on a timer. This action gathers the figures, emails them and runs on a schedule, so the report reaches a manager or customer every week without anyone asking. A scheduled run's output otherwise just sits in run history.

For a one-off answer, such as "how many Business Premium licences are free?", just ask SuperIT in chat.

When to use it

• "Email Priya a licence summary every Monday."
• "Send the customer a weekly report of unused licences."
• "Run the licence report now and send it to me."

What the action does

1. Validates the recipients input as a list of email addresses, and refuses any address outside the tenant's verified domains. The report names accounts, so it never leaves your organisation.
2. Reads the tenant's licence subscriptions from Microsoft Graph and works out seats used and free per licence.
3. Finds disabled user accounts that still have licences assigned, which usually means money being wasted.
4. Builds a short HTML email and sends it with Microsoft Graph sendMail.
5. Prints the same summary, so it also appears in the run's output.

What it doesn't do:

• It doesn't remove licences or change accounts. Make that a separate action with an approval.
• It doesn't send from a shared or no-reply mailbox. The Microsoft Graph credential is delegated, so the email comes from the mailbox of the admin who registered the credential.
• It doesn't include prices. Microsoft Graph doesn't return what you pay per licence.

Before you begin

• A Microsoft Graph credential just for report emails. Under Workflows → Credentials, select New, name it M365 Mail Reports, choose Microsoft Graph as the Auth scheme, enter the scopes Organization.Read.All User.Read.All Mail.Send, and select Register with Microsoft Graph. Sign in as the account the email should come from. The credential is delegated: the action acts as that account, limited to the scopes you chose. Don't add Mail.Send to the M365 Graph user-administration credential or any other credential: every action linked to it could then send email as that account. The script reads the token as CREDENTIAL_M365_MAIL_REPORTS_TOKEN. See Credentials.
• These scopes come from Microsoft's documentation and haven't been tested with SuperIT. If the run fails with Authorization_RequestDenied or 403, see If something goes wrong.
• A mailbox for that account. Use a dedicated admin or service account with a mailbox, so reports don't come from a person who might leave.
• An authorization policy with one rule: Specific groups superit:engineers, superit:admins → Require approval from superit:admins. Sending email from an admin's mailbox is a change, so an admin approves every run, including each scheduled run. Don't add an Anyone rule.
• Your role. Creating an action in chat needs the Admin role. Engineers can build it by hand under Workflows → Actions and create schedules in chat.

Build it with SuperIT

Create an action called "Weekly licence report".
Purpose: summarise Microsoft 365 licence use (seats used and free per SKU) and list disabled users who still have licences, then email the summary.
Runtime: PowerShell in the cloud sandbox. Call Microsoft Graph with Invoke-RestMethod and the credential's token (no Graph SDK).
Input: recipients (string[], required) - email addresses.
Credential: "M365 Mail Reports" (Microsoft Graph, delegated: Organization.Read.All, User.Read.All, Mail.Send).
It refuses recipients outside the tenant's verified domains (GET /organization).
It writes: sends one email with /me/sendMail. Nothing else changes.
Authorization policy: engineers and admins, every run approved by superit:admins.

SuperIT checks the credential, drafts the script, validates it and creates the action as a draft. You review it, test it and publish it under Workflows → Actions.

Or build it yourself

Set Runtime to PowerShell:

# Goal: summarise Microsoft 365 licence use and disabled-but-licensed accounts, then email it to
# recipients in the tenant's own domains. Only sends one email.
$token = $env:CREDENTIAL_M365_MAIL_REPORTS_TOKEN
$headers = @{ Authorization = "Bearer $token" }
$baseUrl = "https://graph.microsoft.com/v1.0"

function Invoke-WeeklyLicenceReport {
    param([string]$Recipients, [string]$TeamName)

    Assert-Credential
    $to = @(ConvertTo-RecipientList -Recipients $Recipients)
    Assert-OwnDomains -Recipients $to
    $skus = @(Get-EnabledSku)
    $disabled = @(Get-DisabledLicensedUser)
    $date = (Get-Date).ToString('d MMMM yyyy')
    $html = New-ReportHtml -Skus $skus -DisabledUsers $disabled -TeamName $TeamName -Date $date
    Send-ReportMail -Recipients $to -Subject "Weekly licence summary - $date" -Html $html
    Write-ReportSummary -Recipients $to -SkuCount $skus.Count -DisabledCount $disabled.Count
    # $script: so the runner can still read $result after this function returns.
    $script:result = @{ recipients = $to; licence_types = $skus.Count; disabled_licensed = $disabled.Count }
}

function ConvertTo-RecipientList {
    param([string]$Recipients)
    $list = @($Recipients -split ',' | ForEach-Object { $_.Trim() } | Where-Object { $_ })
    if ($list.Count -eq 0) { throw "Input 'recipients' needs at least one email address." }
    foreach ($r in $list) { if ($r -notmatch '^[^@\s]+@[^@\s]+\.[^@\s]+$') { throw "Not an email address: $r" } }
    return $list
}

function Assert-Credential {
    if (-not $token) { throw "The 'M365 Mail Reports' credential is not linked to this action." }
}

function Assert-OwnDomains {
    param([string[]]$Recipients)
    # The report lists account names, so it may only go to the tenant's own verified domains.
    try {
        $org = Invoke-RestMethod -Uri "$baseUrl/organization?`$select=verifiedDomains" -Headers $headers
    } catch {
        throw "Failed to read the tenant's verified domains from Microsoft Graph: $($_.Exception.Message)"
    }
    $domains = @($org.value.verifiedDomains | ForEach-Object { "$($_.name)" })
    $outside = @($Recipients | Where-Object { $domains -notcontains $_.Split('@')[-1] })
    if ($outside.Count -gt 0) {
        throw "Not in this tenant's domains ($($domains -join ', ')): $($outside -join ', '). Nothing was sent."
    }
}

function Get-GraphPage {
    param([string]$Uri)
    $items = @()
    while ($Uri) {
        $page = Invoke-RestMethod -Uri $Uri -Headers $headers
        $items += $page.value
        $Uri = $page.'@odata.nextLink'
    }
    return $items
}

function Get-EnabledSku {
    try {
        return @(Get-GraphPage "$baseUrl/subscribedSkus" | Where-Object { $_.capabilityStatus -eq 'Enabled' })
    } catch {
        throw "Failed to read licence subscriptions from Microsoft Graph: $($_.Exception.Message)"
    }
}

function Get-DisabledLicensedUser {
    try {
        return @(Get-GraphPage "$baseUrl/users?`$filter=accountEnabled eq false&`$select=displayName,userPrincipalName,assignedLicenses" |
            Where-Object { $_.assignedLicenses.Count -gt 0 })
    } catch {
        throw "Failed to read disabled users from Microsoft Graph: $($_.Exception.Message)"
    }
}

function New-ReportHtml {
    param([object[]]$Skus, [object[]]$DisabledUsers, [string]$TeamName, [string]$Date)
    # Names come from the directory, so encode them before they go into HTML.
    $enc = { param($text) [System.Net.WebUtility]::HtmlEncode("$text") }
    $skuNames = @{}
    $rows = foreach ($s in $Skus | Sort-Object skuPartNumber) {
        $skuNames[$s.skuId] = $s.skuPartNumber
        $total = $s.prepaidUnits.enabled
        "<tr><td>$(& $enc $s.skuPartNumber)</td><td>$($s.consumedUnits)</td><td>$total</td><td>$($total - $s.consumedUnits)</td></tr>"
    }
    $wasted = foreach ($u in $DisabledUsers) {
        $names = ($u.assignedLicenses | ForEach-Object { $skuNames[$_.skuId] }) -join ', '
        "<li>$(& $enc $u.displayName) ($(& $enc $u.userPrincipalName)): $(& $enc $names)</li>"
    }
    return @"
<p>Microsoft 365 licence summary for $(& $enc $TeamName), $Date.</p>
<table border="1" cellpadding="4"><tr><th>Licence</th><th>Used</th><th>Total</th><th>Free</th></tr>$($rows -join '')</table>
<p>Disabled accounts that still have licences: $($DisabledUsers.Count)</p><ul>$($wasted -join '')</ul>
"@
}

function Send-ReportMail {
    param([string[]]$Recipients, [string]$Subject, [string]$Html)
    $mail = @{
        message = @{
            subject      = $Subject
            body         = @{ contentType = "HTML"; content = $Html }
            toRecipients = @($Recipients | ForEach-Object { @{ emailAddress = @{ address = $_ } } })
        }
        saveToSentItems = $true
    } | ConvertTo-Json -Depth 6
    try {
        Invoke-RestMethod -Method Post -Uri "$baseUrl/me/sendMail" -Headers $headers -ContentType "application/json" -Body $mail
    } catch {
        throw "Failed to send the licence summary to '$($Recipients -join ', ')': $($_.Exception.Message)"
    }
}

function Write-ReportSummary {
    param([string[]]$Recipients, [int]$SkuCount, [int]$DisabledCount)
    Write-Output "=== LICENCE SUMMARY SENT ==="
    Write-Output "Recipients        : $($Recipients -join ', ')"
    Write-Output "Licence types     : $SkuCount"
    Write-Output "Disabled licensed : $DisabledCount"
}

Invoke-WeeklyLicenceReport -Recipients $ctx.inputs.recipients -TeamName $ctx.team.name

$ctx.team.name is the SuperIT team's name, which SuperIT provides to every PowerShell action.

Test it

The script is untested, so treat your first test runs as debugging, and don't publish the action until a test run does exactly what you expect.

1. Save the draft and select Test.
2. Test is not a dry run: it skips authorization and approvals and uses the real Microsoft Graph credential, so the email really sends. Test runs aren't billed.
3. Set recipients to your own address only.
4. Check the email arrives from the credential's account and the numbers match the Microsoft 365 admin centre.
5. Test again with an address outside your organisation, and check the run is refused and nothing is sent.
6. Select Publish.

Use it

• Schedule it in chat: "Schedule Weekly licence report every 7 days, first run next Monday at 08:00 UK time, recipients [email protected]." SuperIT confirms the details and replies with a View schedule link. Schedules are created in chat only, repeat every N elapsed days (not a calendar or cron schedule), and run as the engineer who created or last changed them. Each scheduled run waits for an admin to approve it, and the next run is skipped while a previous one is still waiting or in progress. A run nobody approves within 7 days expires.
• Run it now: "Run the weekly licence report and send it to me."
• Pause or change it: "Pause the weekly licence report schedule" or "Add [email protected] to the licence report schedule".
• For each customer: use Publish to teams to give managed teams a read-only copy. Each copy arrives with a blank M365 Mail Reports credential, which that team's admin registers against their own tenant with Register with Microsoft Graph. Then create a schedule in each team. Recipients must be in that customer's own domains.

Variations

• Other figures: swap the Graph calls for any data an action can reach, such as backup results (Check backup job status) or certificate expiry (Check website and SSL certificate expiry).
• Post instead of email: send the summary to a channel with Post a message to a Teams or Slack channel, and drop the Mail.Send scope.
• A fixed recipient list: replace the recipients input with a list in the script, so nobody can change who gets the report without editing the action.
• Monthly: schedule every 30 days. It drifts against calendar months, so put the date range in the email.

Related

• Connect Microsoft 365
• Credentials
• Post a message to a Teams or Slack channel
• Check backup job status