Skip to main content

You want to find all the inactive Microsoft 365 users. There is no benefit to keeping user accounts active when there is no activity. Inactive user accounts become easy targets for hackers, and you should disable these accounts. In this article, you will learn how to export Microsoft 365 inactive users report.

What are Microsoft 365 inactive users

An inactive Microsoft 365 user is when the user hasn’t signed in for the last set of days. This can be different for every organization. Some find that an inactive account is valid when there is no sign-in within the last 60 or 90 days. Some find it’s a 30-day period.

The Center for Internet Security (CIS) recommends to delete or disable any dormant accounts after a period of 45 days of inactivity, where supported.

See 5.3: Disable Dormant Accounts.

Export Microsoft 365 inactive users CIS

Important: You need a Microsoft Entra ID P1 or P2 edition license to view the last sign-in date.

Check user last sign-in date in Microsoft 365 admin center

To check the Microsoft 365 user last sign-in date in Microsoft 365 admin center, follow these steps:

  1. Sign in to Microsoft 365 admin center
  2. Expand Users
  3. Click on Active Users
  4. Select the User
Export Microsoft 365 inactive users admin center
  1. Click on Account > View last 30 days
Export Microsoft 365 inactive users last 30 days
  1. The last sign-in date, status, and failure reason (if available) appear.
Export Microsoft 365 inactive users sign in activity

What if you want to export all the users last sign-in status? PowerShell to the rescue.

Export Microsoft 365 inactive users PowerShell script

The  Export-M365InactiveUsers.ps1 PowerShell script will get all the Microsoft 365/Microsoft Entra ID users and export a report to CSV file that shows how many days have elapsed since their last sign-in.

Note: The LastSuccessfulSignInDate value will start to appear in the report within 6 hours. The data for this property isn’t backfilled and starts recording sign-ins after December 1, 2023.

For every user, it gathers the following information:

  1. Id
  2. UserPrincipalName
  3. DisplayName
  4. Email
  5. UserType
  6. AccountEnabled
  7. LastSuccessfulSignInDate
  8. DaysSinceLastSignIn
  9. CreatedDateTime
  10. IsLicensed

Prepare Export-M365InactiveUsers PowerShell script

Create two folders on the (C:) drive:

  • Temp
  • Scripts

Download the Export-M365InactiveUsers.ps1 PowerShell script and place it in C:\scripts folder. The script will export the CSV file to the C:\temp folder.

Ensure the file is unblocked to prevent errors when running the script.

Another option is to copy and paste the below code into Notepad. Give it the name Export-M365InactiveUsers.ps1 and place it in the C:\scripts folder.

<#
    .SYNOPSIS
    Export-M365InactiveUsers.ps1

    .DESCRIPTION
    Export Microsoft 365/Microsoft Entra ID inactive users report.

    .LINK
    www.traseroute.net/export-microsoft-365-inactive-users/

    .NOTES
    Written by: Traseroute
    Website:    www.traseroute.net
    LinkedIn:   linkedin.com/in/traseroute

    .CHANGELOG
    V1.00, 12/13/2023 - Initial version
    V1.10, 02/26/2024 - Added IsLicensed column
#>

# Export path for CSV file
$CSVPath = "C:\Temp\InactiveUsers.csv"

# Parameters
$InactiveUsers = @()

# Connect to Microsoft Graph API
Connect-MgGraph -Scopes "User.Read.All", "AuditLog.Read.All"

# Get properties
$Properties = @(
    'Id',
    'DisplayName',
    'Mail',
    'UserPrincipalName',
    'UserType',
    'AccountEnabled',
    'SignInActivity',
    'CreatedDateTime',
    'AssignedLicenses'
)

# Get all users along with the properties
$AllUsers = Get-MgBetaUser -All -Property $Properties | Select-Object $Properties

foreach ($User in $AllUsers) {
    $LastSuccessfulSignInDate = if ($User.SignInActivity.LastSuccessfulSignInDateTime) {
        $User.SignInActivity.LastSuccessfulSignInDateTime
    }
    else {
        "Never Signed-in."
    }

    $DaysSinceLastSignIn = if ($User.SignInActivity.LastSuccessfulSignInDateTime) {
        (New-TimeSpan -Start $User.SignInActivity.LastSignInDateTime -End (Get-Date)).Days
    }
    else {
        "N/A"
    }

    # Check if the user is licensed
    $IsLicensed = if ($User.AssignedLicenses) {
        "Yes"
    }
    else {
        "No"
    }

    # Collect data
    if (!$User.SignInActivity.LastSuccessfulSignInDateTime -or (Get-Date $User.SignInActivity.LastSuccessfulSignInDateTime)) {
        $InactiveUsers += [PSCustomObject]@{
            Id                       = $User.Id
            UserPrincipalName        = $User.UserPrincipalName
            DisplayName              = $User.DisplayName
            Email                    = $User.Mail
            UserType                 = $User.UserType
            AccountEnabled           = $User.AccountEnabled
            LastSuccessfulSignInDate = $LastSuccessfulSignInDate
            DaysSinceLastSignIn      = $DaysSinceLastSignIn
            CreatedDateTime          = $User.CreatedDateTime
            IsLicensed               = $IsLicensed
        }
    }
}

# Display data using Out-GridView
$InactiveUsers | Out-GridView -Title "Inactive Users"

# Export data to CSV file
try {
    $InactiveUsers | Export-Csv -Path $CSVPath -NoTypeInformation -Encoding UTF8
    Write-Host "Script completed. Results exported to $CSVPath." -ForegroundColor Cyan
}
catch {
    Write-Host "Error occurred while exporting to CSV: $_" -ForegroundColor Red
}
  • Line 22: Edit CSV file path

Connect to Microsoft Graph PowerShell

Before we can proceed further and get the inactive status for all the users, we need to Install and connect to Microsoft Graph PowerShell.

Start Windows PowerShell as administrator and run the below commands.

Install-Module Microsoft.Graph -Force
Install-Module Microsoft.Graph.Beta -AllowClobber -Force

Important: Always install the Microsoft Graph PowerShell and Microsoft Graph Beta PowerShell modules. That’s because some cmdlets are not yet available in the final version, and they will not work. Update both modules to the latest version before you run a cmdlet or script to prevent errors and incorrect results.

Run the Connect-MgGraph cmdlet.

Connect-MgGraph -Scopes "User.Read.All", "AuditLog.Read.All"

Run Export-M365InactiveUsers PowerShell script

Get all the inactive users with PowerShell. Run the below command to run the script Export-M365InactiveUsers.ps1.

c:\scripts\.\Export-M365InactiveUsers.ps1

Out-GridView

An Out-GridView will show columns with all the inactive users and much more information.

Export Microsoft 365 inactive users Out-GridView

Open Microsoft 365 inactive users report CSV file

The Export-M365InactiveUsers.ps1 PowerShell script will export Microsft 365 users inactivity to CSV file. Find the file InactiveUsers.csv in the path C:\temp.

Temp folder with CSV file output

Open the CSV file with your favorite application. In our example, it’s Microsoft Excel.

CSV file

Did this help you to export Microsoft 365 inactive users to CSV file?

Leave a Reply