Generate and Email Microsoft 365 User Last Logon Report

Understanding user login activity is essential for licensing optimization, security monitoring, compliance audits, and cleanup operations. Administrators frequently need to know which users have logged in recently and which accounts are active.

This script retrieves the last logon time for all Microsoft 365 users, automatically excludes users who have never logged in, generates a CSV report, and emails the results to the administrator for ongoing visibility.

i) The Script

$AdminUPN = "admin@yourtenant.onmicrosoft.com"
Connect-MgGraph -Scopes "User.Read.All","Mail.Send"
$AllUsers = Get-MgUser -All `
    -Property Id, DisplayName, UserPrincipalName, SignInActivity

$LoggedInUsers = $AllUsers | Where-Object {
    $_.SignInActivity -and
    $_.SignInActivity.lastSignInDateTime
}

$Report = $LoggedInUsers | Select-Object `
    @{n="DisplayName"; e={$_.DisplayName}},
    @{n="UserPrincipalName"; e={$_.UserPrincipalName}},
    @{n="LastLogonDateTime"; e={[datetime]$_.SignInActivity.lastSignInDateTime}}

$ReportPath = "$env:TEMP\M365_LastLogon_Report.csv"

$Report |
  Sort-Object LastLogonDateTime -Descending |
  Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8

$UserCount = @($Report).Count
$Subject = "M365 Last Logon Report — $(Get-Date -Format 'yyyy-MM-dd')"

$Body = @"
Hello Admin,<br><br>
Attached is the latest <b>Last Logon Report</b> for Microsoft 365 users.<br>
Only users who have logged in at least once are included.<br><br>
Total users in report: <b>$UserCount</b><br><br>
Regards,<br>
Graph PowerShell Script
"@

$AttachmentContent = [System.Convert]::ToBase64String([System.IO.File]::ReadAllBytes($ReportPath))

$Attachments = @(
    @{
        "@odata.type" = "#microsoft.graph.fileAttachment"
        Name          = [System.IO.Path]::GetFileName($ReportPath)
        ContentBytes  = $AttachmentContent
    }
)

$Message = @{
    Message = @{
        Subject = $Subject
        Body    = @{
            ContentType = "HTML"
            Content     = $Body
        }
        ToRecipients = @(
            @{ EmailAddress = @{ Address = $AdminUPN } }
        )
        Attachments = $Attachments
    }
    SaveToSentItems = "true"
}

Send-MgUserMail -UserId $AdminUPN -BodyParameter $Message

Write-Host "Last logon report emailed successfully to $AdminUPN" -ForegroundColor Green
                                

ii) How the Script Works

1. Connects to Microsoft Graph

The script requests two delegated permissions:

• User.Read.All → Required to read user sign-in activity
• Mail.Send → Required to send the email with the report

Both permissions must be granted with admin consent.

2. Retrieves All Users & Their Last Logon Data

Calling Get-MgUser with the SignInActivity property explicitly included allows Graph to return the last login timestamp for each user (when available).

3. Excludes Users Who Have Never Logged In

Users without:

• SignInActivity, or
• lastSignInDateTime

are removed automatically, ensuring the report contains only active login accounts.

4. Generates a Last Logon Report

The script outputs:

• Display Name
• UPN
• Last Logon Timestamp

It then sorts users by last logon date (newest first) and exports the report to a CSV file.

5. Emails the Report to the Administrator

The CSV is converted to a Base64 attachment and delivered using Send-MgUserMail, along with a clean, HTML-formatted summary.

iii) Further Enhancements

You can extend the script in several useful ways:

• Include account status

Add columns for:

• AccountEnabled
• UserType (Guest/Member)
• Add department or job title filters

Useful for HR and department-based reporting.

• Send the report to multiple recipients

Add shared mailboxes, Security teams, or SOC.

• Store the report in SharePoint or OneDrive

Automate archival and create a historical login trend.

• Visualize login activity

Use Power BI to analyze login frequency or detect anomalies.

iv) Possible Errors & Solutions

Error	Cause	Solution
SignInActivity always returns blank	Your tenant may lack the required Entra ID license tier.	Entra ID P1/P2 is required for detailed sign-in activity.
Authorization_RequestDenied	Missing Graph permissions.	Grant delegated permissions: User.Read.All Directory.Read.All Mail.Send
Email sending fails	The admin account may not have a mailbox.	Use a mailbox-enabled account as -UserId.
Empty CSV report	No users have logged in within the retention range.	Verify sign-in log retention (default: 30 days, extendable with add-ons).

v) Conclusion

This script provides administrators with a reliable way to track the last login date of all users in the tenant, ensuring visibility into active usage patterns and potential dormant accounts. With automatic email delivery, admins can stay informed without manually generating the report, making this an efficient and governance-friendly solution for ongoing tenant monitoring.