It's best to use Conditional Access based MFA when you have Microsoft Entra ID P1…
Export Azure AD users to CSV with PowerShell
We want to export Azure AD users information to CSV with PowerShell. Why do we need to list the Azure AD users with PowerShell? For example, we want to know if every Azure AD user has the correct attributes in Azure Active Directory. That’s because the service desk needs this information. In this article, you will learn how to export Azure Active Directory users to CSV file with PowerShell.
Table of contents
Information export Azure AD users PowerShell script
The Export-AADUsers.ps1 PowerShell script will run against the Azure tenant. After that, it will export the report to CSV file. You can open the CSV file with Microsoft Excel or any other application that supports the CSV file extension.
The script will gather the following information per user:
- ID
- First name
- Last name
- Display name
- User principal name
- Domain name
- Email address
- Job Title
- Manager display name
- Manager user principal name
- Department
- Company
- Office
- Employee ID
- Mobile
- Phone
- Street
- City
- Postal code
- State
- Country
- User type
- On-Premises sync
- Account status
- Account created on
- Last successful sign in (requires an Azure AD P1/P2 license)
- Licensed
- MFA status (including authentication methods)
Export Azure Active Directory users to CSV with PowerShell
Let’s go through the steps and export Azure Active Directory users to CSV file with PowerShell.
Step 1. Install Microsoft Graph PowerShell
Run Windows PowerShell as administrator and Install Microsoft Graph PowerShell.
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.
Step 2. Connect to Microsoft Graph PowerShell
Connect to Azure Active Directory (AAD) with Microsoft Graph PowerShell.
Connect-MgGraph -Scopes "User.Read.All", "UserAuthenticationMethod.Read.All", "AuditLog.Read.All"
Enter your global administrator credentials and accept the Microsoft Graph permissions request.
Step 3. Prepare export Azure AD users PowerShell script
Create two folders on the (C:) drive:
- Temp
- Scripts
Download and place Export-AADUsers.ps1 PowerShell script 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. Read more in the article Not digitally signed error when running PowerShell script.
Another option is to copy and paste the below code into Notepad. Give it the name Export-AADUsers.ps1 and place it in the C:\scripts folder.
<#
.SYNOPSIS
Export-AADUsers.ps1
.DESCRIPTION
Export Azure Active Directory users to CSV file.
.LINK
www.alitajran.com/export-azure-ad-users-to-csv-powershell
.NOTES
Written by: ALI TAJRAN
Website: www.alitajran.com
LinkedIn: linkedin.com/in/alitajran
.CHANGELOG
V1.10, 06/20/2023 - Initial version
V1.10, 06/21/2023 - Added license status and MFA status including methods
V1.20, 06/22/2023 - Added progress bar and last sign in date
V1.30, 07/24/2023 - Update for Microsoft Graph PowerShell changes
V1.40, 04/07/2024 - Added domain name
#>
# Connect to Microsoft Graph API
Connect-MgGraph -Scopes "User.Read.All", "UserAuthenticationMethod.Read.All", "AuditLog.Read.All"
# Create variable for the date stamp
$LogDate = Get-Date -f yyyyMMddhhmm
# Define CSV file export location variable
$Csvfile = "C:\temp\AllAADUsers_$LogDate.csv"
# Retrieve users using the Microsoft Graph API with property
$propertyParams = @{
All = $true
# Uncomment below if you have Azure AD P1/P2 to get last sign in date
# Property = 'SignInActivity'
ExpandProperty = 'manager'
}
$users = Get-MgBetaUser @propertyParams
$totalUsers = $users.Count
# Initialize progress counter
$progress = 0
# Initialize an array to store user objects
$userObjects = @()
# Loop through all users and collect user objects
foreach ($index in 0..($totalUsers - 1)) {
$user = $users[$index]
# Update progress counter
$progress++
# Calculate percentage complete
$percentComplete = ($progress / $totalUsers) * 100
# Define progress bar parameters
$progressParams = @{
Activity = "Processing Users"
Status = "User $($index + 1) of $totalUsers - $($user.userPrincipalName) - $($percentComplete -as [int])% Complete"
PercentComplete = $percentComplete
}
# Display progress bar
Write-Progress @progressParams
# Get manager information
$managerDN = $user.Manager.AdditionalProperties.displayName
$managerUPN = $user.Manager.AdditionalProperties.userPrincipalName
# Create an object to store user properties
$userObject = [PSCustomObject]@{
"ID" = $user.id
"First name" = $user.givenName
"Last name" = $user.surname
"Display name" = $user.displayName
"User principal name" = $user.userPrincipalName
"Domain name" = $user.userPrincipalName.Split('@')[1]
"Email address" = $user.mail
"Job title" = $user.jobTitle
"Manager display name" = $managerDN
"Manager user principal name" = $managerUPN
"Department" = $user.department
"Company" = $user.companyName
"Office" = $user.officeLocation
"Employee ID" = $user.employeeID
"Mobile" = $user.mobilePhone
"Phone" = $user.businessPhones -join ','
"Street" = $user.streetAddress
"City" = $user.city
"Postal code" = $user.postalCode
"State" = $user.state
"Country" = $user.country
"User type" = $user.userType
"On-Premises sync" = if ($user.onPremisesSyncEnabled) { "enabled" } else { "disabled" }
"Account status" = if ($user.accountEnabled) { "enabled" } else { "disabled" }
"Account Created on" = $user.createdDateTime
# Uncomment below if you have Azure AD P1/P2 to get last succesful sign in date
# "Last sign in" = if ($user.SignInActivity.LastSuccessfulSignInDateTime) { $user.SignInActivity.LastSuccessfulSignInDateTime } else { "No sign in" }
"Licensed" = if ($user.assignedLicenses.Count -gt 0) { "Yes" } else { "No" }
"MFA status" = "-"
"Email authentication" = "-"
"FIDO2 authentication" = "-"
"Microsoft Authenticator App" = "-"
"Password authentication" = "-"
"Phone authentication" = "-"
"Software Oath" = "-"
"Temporary Access Pass" = "-"
"Windows Hello for Business" = "-"
}
$MFAData = Get-MgBetaUserAuthenticationMethod -UserId $user.userPrincipalName
# Check authentication methods for each user
foreach ($method in $MFAData) {
Switch ($method.AdditionalProperties["@odata.type"]) {
"#microsoft.graph.emailAuthenticationMethod" {
$userObject."Email authentication" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.fido2AuthenticationMethod" {
$userObject."FIDO2 authentication" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.microsoftAuthenticatorAuthenticationMethod" {
$userObject."Microsoft Authenticator App" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.passwordAuthenticationMethod" {
$userObject."Password authentication" = $true
# When only the password is set, then MFA is disabled.
if ($userObject."MFA status" -ne "Enabled") {
$userObject."MFA status" = "Disabled"
}
}
"#microsoft.graph.phoneAuthenticationMethod" {
$userObject."Phone authentication" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.softwareOathAuthenticationMethod" {
$userObject."Software Oath" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.temporaryAccessPassAuthenticationMethod" {
$userObject."Temporary Access Pass" = $true
$userObject."MFA status" = "Enabled"
}
"#microsoft.graph.windowsHelloForBusinessAuthenticationMethod" {
$userObject."Windows Hello for Business" = $true
$userObject."MFA status" = "Enabled"
}
}
}
# Add user object to the array
$userObjects += $userObject
}
# Complete the progress bar
Write-Progress -Activity "Processing Users" -Completed
# Export all user objects to CSV
$userObjects | Sort-Object "Display name" | Export-Csv -Path $Csvfile -NoTypeInformation -Encoding UTF8 #-Delimiter ";"
This is how it looks.
Step 4. Run export Azure AD users PowerShell script
Change the path to the scripts folder. Run the PowerShell script to export Azure AD users to CSV file. Wait till it completes.
PS C:\> cd c:\scripts
PS C:\scripts> .\Export-AADUsers.ps1
Step 5. Verify Azure AD users report CSV file
Go to the temp folder and verify that you see the AllAADUsers_ file.
Open the CSV file with your favorite application. In our example, it’s Microsoft Excel.
Everything looks fantastic!
Read more: Migrate Azure AD Connect to new server »
Conclusion
You learned how to Export Azure AD users to CSV with PowerShell. There is a lot of information in every user account. With PowerShell, you can have a custom report that will suit your needs.
Did you enjoy this article? You may also like Find Azure AD Connect accounts. Don’t forget to follow us and share this article.
Hi Ali,
Thanks for this magnificent scrip,
But can you please update it to work for specific domain, not for all the tenant?
I added “Domain name” to the script.
Once you have the exported CSV file, you can easily filter on the domain name you want.
Thanks a lot, Eng. ALI,
But that’s not what I meant, It takes forever to export all the users in the tenant especially if it has multiple domains, so I wanted the script to export users only from one of these domains, and I tried to add these:
Get-MgUser -All -Filter “endsWith(userprincipalname ,’exoip.com’)”
but it didn’t work.
Is it possible to retrieve the LastLogonTimeStamp, PasswordLastSet, and AccountExpirationDate attributes?
thank you!
hi Ali,
Seems like this is not possible to get the “sponsors” attribute which I think is neither among the default attributes or the extension attributes, any ideas?
Thanks.
Hi Ali,
Can you help me to get the Extension attributes values
Love the script, works a charm. I’m trying to add a column in the output for the user’s proxyaddresses. What would be the object to reference those? I tried adding this but returned a blank column. “Additional SMTP Addresses” = $user.ProxyAddresses
Hello, Good day!
I’m facing an challenges filtering cloud-only user accounts in Entra ID using PowerShell. The following commands are not producing accurate results:
Get-MgUser -Filter “userType eq ‘Member'” | Where-Object {$_.ImmutableId -eq $null}
Get-MgUser -Filter “userType eq ‘Member'” | Where-Object { $_.onPremisesImmutableId -eq $null}
Get-MgUser -Filter “userType eq ‘Member’ and onPremisesSyncEnabled eq false”
Discrepancies Observed:
Microsoft Entra admin center displays values for onPremisesSyncEnabled and related attributes for synced users, but these attributes appear empty in PowerShell.
Request for Assistance:
Have anyone encountered this issue with inconsistent attribute values?
Could you suggest approaches to filter cloud-only users effectively?
To filter all cloud users (including guests):
To filter all cloud users (excluding guests):
Read more in the article How to use Get-MgUser in PowerShell.
Hello!
Can you add last log in and mailbox type like user mail box or shared mailbox?
Thank you!
Hey Ali.
Something seems off regarding LastLogon – I uncommented it in the script and it was part of the CSV. Yet, it say “No log in” for all exported users”, which can’t be. I and all our users have an AAD P1 license.
What could be the reason?
I have a tenant with AAD P2, and it works (I tested it right now). It should work with AAD P1, but I can’t tell why you get this.
Hi Ali,
Thank you for this script.
I’d appreciate your guidance on how to re-import a file after modifying user information, ensuring no conflicts based on “ID” or “Email address”.
This is exactly what we needed. Now the question is, is there a script available to where we can update the data in the csv file and then import it via powershell to update AAD?
Thanks for the script. how can we export the members of a specific Azure AD group to a CSV file with all or some of those users’ objects?
Hello Ali.
Thank you for the great script.
However, I can’t find a way to log in as a Global Admin because the pop-up window only shows the user currently logged in to Windows / no option to log in to Azure as a different user (Global Admin).
thanks in advance
Run the below cmdlet in PowerShell to sign out the current admin from the session:
Disconnect-MgGraph
After that, rerun the script, and you should get a Windows credentials prompt where you can sign in with your admin credentials.
Hi Ali,
What is the specified parameter for the employee hire date?
Hey Ali
Thank you for compiling this script – very helpful!!!
Is it possible to include azure / o365 roles assigned to each user as well?
Hi Ali,
Can you add for last login information?
I tried from end not getting reported for lastlogin
I added it to the script. Note that it requires an Azure AD P1/P2 license.
Hi Ali,
What would need to be added in your script to also get the following details:
-the MFA status of each account.
-Authentication Methods configured/being used (for accounts that have MFA enabled)
I added it to the script.
Is there a way to add the last login of the user?
Thanks
I added it to the script. Note that it requires an Azure AD P1/P2 license.
Is there a way to modify this to only export records that have been modified or created within 7 days for example?
i have updated the with the employeeID and manager line but the csv file is empty for both of them, any advice here, thanks
I updated the script and added the following:
– Manager display name
– Manager user principal name
– Employee ID
Is there possible export users and groups with Azure AD assigned licenses ?
Is there a way to export extensionAttribute1, etc…?
how to import csv file to OnPrem ?
Hello, I love you blog post, over the years your website has being my go-to look for fun stuff. After checking this post, I have a question or a request, can you get a script to bulk change the UPN Suffix for users from this exported user csv? That’d be so helpful
Hi There,
I have the following question. I have a list of accounts that i need to export their manager list. Is this possible?
I updated the script and added the following:
– Manager display name
– Manager user principal name
– Employee ID