Using PowerShell to Export Office 365 User Details

If you’re managing a Microsoft 365 environment, you might need to export user information from Azure Active Directory to a CSV file for reporting, auditing, or administrative purposes.

So lets see how you can extract the user’s name, email, account status (locked or unlocked), and license assignment status.

Install and Import the Microsoft Graph Module

The first step is to install the Microsoft.Graph PowerShell module, which allows you to interact with the Microsoft Graph API.

Install-Module -Name Microsoft.Graph -Force

Once installed, import the Microsoft.Graph.Users module, which contains cmdlets for managing user data:

Import-Module Microsoft.Graph.Users

Connect to Microsoft Graph

Next, connect to Microsoft Graph using the Connect-MgGraph cmdlet. The -Scopes parameter specifies the permissions required to access user data. In this case, we’re requesting the User.Read.All scope to read all user profiles:

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

You’ll be prompted to sign in with your Office 365 credentials. After successful authentication, you’ll be connected to Microsoft Graph.

Retrieve User Details

Now that you’re connected, you can retrieve user details using the Get-MgUser cmdlet. The -All parameter ensures that all users are retrieved, and the -Property parameter specifies the properties to include in the results:

$users = Get-MgUser -All -Property Id, DisplayName, UserPrincipalName, AccountEnabled, AssignedLicenses

Process and Store User Details

We’ll loop through each user and extract the required information, such as email, name, login status, and license assignment. The results will be stored in an array of custom objects:

$userDetails = @()

foreach ($user in $users) {
    $userInfo = [PSCustomObject]@{
        Email            = $user.UserPrincipalName
        Name             = $user.DisplayName
        LoginStatus      = if ($user.AccountEnabled -eq $true) { "Unlocked" } else { "Locked" }
        HasLicense       = if ($user.AssignedLicenses.Count -gt 0) { "Yes" } else { "No" }
    }
    $userDetails += $userInfo
}

Export to CSV

Finally, we’ll convert the array of user details to CSV format and save it to a file. The Out-File cmdlet is used to write the CSV content to a file with UTF-8 encoding:

$csvContent = $userDetails | ConvertTo-Csv -NoTypeInformation
$csvContent | Out-File -FilePath "C:\Office365Users.csv" -Encoding UTF8

Full Script

Here’s the complete script for your reference:

# Install and import the Microsoft.Graph module
Install-Module -Name Microsoft.Graph -Force
Import-Module Microsoft.Graph.Users

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

# Get all users
$users = Get-MgUser -All -Property Id, DisplayName, UserPrincipalName, AccountEnabled, AssignedLicenses

# Initialize an array to store user details
$userDetails = @()

# Loop through each user and extract the required information
foreach ($user in $users) {
    $userInfo = [PSCustomObject]@{
        Email            = $user.UserPrincipalName
        Name             = $user.DisplayName
        LoginStatus      = if ($user.AccountEnabled -eq $true) { "Unlocked" } else { "Locked" }
        HasLicense       = if ($user.AssignedLicenses.Count -gt 0) { "Yes" } else { "No" }
    }
    $userDetails += $userInfo
}

# Convert to CSV and save to file
$csvContent = $userDetails | ConvertTo-Csv -NoTypeInformation
$csvContent | Out-File -FilePath "C:\Office365Users.csv" -Encoding UTF8

Write-Host "User details exported to C:\Office365Users.csv with UTF-8 encoding"

Use Cases for This Script

Auditing User Accounts: Quickly identify enabled or disabled accounts.
License Management: Check which users have licenses assigned.
Reporting: Generate user reports for compliance or administrative purposes.
Automation: Schedule the script to run periodically and keep user data up to date.

Feel free to customize the script to suit your specific needs, and explore other capabilities of Microsoft Graph to unlock even more possibilities for automation and integration. Happy scripting!

What’s your Reaction?