Generate and Email Users-Never-Logged-In Report

Monitoring user login activity is essential for security, cleanup, onboarding validation, and identity governance. Users who have never logged in may represent inactive accounts, onboarding gaps, unnecessary license consumption, or potential identity risks.

This Graph PowerShell script retrieves all Microsoft 365 users who have never logged in, generates a detailed report, and emails it to the administrator.

i) The Script

$AdminUPN = "admin@yourtenant.onmicrosoft.com"
Connect-MgGraph -Scopes "User.Read.All","Mail.Send"

$AllUsers = Get-MgUser -All `
  -Property Id, DisplayName, UserPrincipalName, Mail, AccountEnabled, UserType, SignInActivity

$NeverLoggedInUsers = $AllUsers | Where-Object {
    -not $_.SignInActivity -or
    -not $_.SignInActivity.lastSignInDateTime
}

$ReportRows = $NeverLoggedInUsers | Select-Object `
  @{n='DisplayName';          e={$_.DisplayName}},
  @{n='UserPrincipalName';    e={$_.UserPrincipalName}},
  @{n='Mail';                 e={$_.Mail}},
  @{n='UserType';             e={$_.UserType}},
  @{n='AccountEnabled';       e={$_.AccountEnabled}},
  @{n='LastSignInDateTime';   e={ if ($_.SignInActivity.lastSignInDateTime) { [datetime]$_.SignInActivity.lastSignInDateTime } else { $null } }}

$ReportPath = "$env:TEMP\Users_NeverLoggedIn.csv"
$ReportRows |
  Sort-Object DisplayName |
  Export-Csv -Path $ReportPath -NoTypeInformation -Encoding UTF8

$userCount = @($ReportRows).Count
$Subject = "Users Who Have Never Logged In — $(Get-Date -Format 'yyyy-MM-dd')"
$Body = @"

Hello Admin,<br><br>
Attached is the report of users who have <b>never logged in</b> to Microsoft 365.<br>
Total users: <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 "Users who have never logged in report emailed successfully to $AdminUPN"
                                

ii) How the Script Works

1. Connects to Microsoft Graph

The script connects using the User.Read.All scope to read sign-in activity and Mail.Send to send the email from the admin’s mailbox.

2. Retrieves All Users With Sign-In Activity

The SignInActivity property is explicitly requested when calling Get-MgUser.

Users lacking this property or its lastSignInDateTime value are treated as never logged in.

3. Filters Users Who Have Never Logged In

The script checks for:

• No SignInActivity object
• No lastSignInDateTime value

This reliably identifies accounts that have never authenticated.

4. Formats the Output

The output includes:

• Display Name
• UPN
• Mail
• User Type
• Account Enabled
• Last Sign-In DateTime (blank = never logged in)
5. Exports a CSV Report

The script exports the final dataset to a temporary CSV file.

6. Emails the Report

The script:

• Builds an HTML email body
• Converts the CSV into Base64
• Sends the email using Send-MgUserMail from the administrator’s mailbox

iii) Further Enhancements

Here are ways to extend the script for even richer reporting:

• Add License Details

Identify whether the never-logged-in accounts are consuming paid licenses.

• Flag Guest vs Member Accounts

Categorize external and internal accounts separately.

• Auto-disable or notify managers

Automatically notify owners or disable accounts idle beyond a threshold.

• Store reports in SharePoint

Instead of email only, archive the CSV in a SharePoint document library.

• Log execution history

Maintain audit logs of script runs for compliance.

iv) Possible Errors & Solutions

Error	Cause	Solution
Authorization_RequestDenied	Missing permissions.	Use delegated scopes: User.Read.All Directory.Read.All Mail.Send Ensure consent is granted.
SignInActivity returns blank for all users	Your tenant may not have the necessary license (e.g., Microsoft Entra ID P1/P2) or reporting retention may have expired.	Verify audit log retention and licensing.
Send-MgUserMail fails	The admin account may not have a mailbox.	Use a licensed mailbox-enabled account for email sending.
CSV attachment appears empty	Script executed before the CSV file was generated or access issues.	Ensure the CSV path is valid and writable.

v) Conclusion

This Graph PowerShell automation helps administrators identify users who have never logged in, a critical aspect of identity hygiene, licensing cleanup, and security posture management. With automatic CSV export and email delivery, this script ensures that administrators stay informed without manual checks, improving operational efficiency and tenant governance.