How to Export Unlicensed Microsoft 365 Users with ImmutableID to CSV
Question details
The user needs to identify and export internal Microsoft 365 users who are unlicensed but still have a populated ImmutableID to a CSV file, ensuring guest and cloud-only accounts are excluded.
- Product
- Microsoft 365
- Device & OS
- not provided
- Scenario
- Administrating Microsoft 365 tenant users and auditing unlicensed accounts synchronized from an on-premises Active Directory.
- Observed behavior
- The administrator must filter directory results specifically for an IsLicensed status of false, a populated ImmutableID, and a user type other than Guest.
Ensure you have the Azure Active Directory PowerShell for Graph module (MSOnline) installed and possess the necessary global admin or user admin privileges in your Microsoft 365 tenant.
Use PowerShell to Filter and Export Users to CSV
Run a specific PowerShell script using the Get-MsolUser cmdlet to filter your directory by license status, ImmutableID presence, and UserType, then output the data to a CSV.
This PowerShell command efficiently isolates internal users synced from on-premises servers (identified by the ImmutableID) who currently lack a license. It safely ignores external guests and cloud-only accounts.
Search for PowerShell in your Windows Start menu, right-click the application, and select 'Run as administrator'.
Type the command Connect-MsolService and press Enter. Sign in with your Microsoft 365 admin credentials in the popup window.
Execute the following command: Get-MsolUser | Where-Object { $_.ImmutableId -and -not $_.IsLicensed -and $_.UserType -ne 'Guest' } | Select-Object UserPrincipalName,DisplayName,ImmutableId,UserType | Export-Csv .\UnlicensedUsers.csv -NoTypeInformation
Navigate to the directory from which you ran the script (usually C:\Windows\System32 if run as Admin) to find the UnlicensedUsers.csv file containing your filtered results.
Open and Analyze Your Exported CSV Files with WPS Office
While PowerShell handles the administrative task of extracting Microsoft 365 data, viewing and formatting the resulting CSV files requires a reliable spreadsheet application. WPS Office provides a free, lightweight, and highly compatible alternative to Microsoft Excel for effortlessly auditing your user reports.
Frequently Asked Questions
What does an ImmutableID indicate in Microsoft 365?
An ImmutableID typically indicates that a user account was synchronized from an on-premises Active Directory environment to Microsoft 365 using Azure AD Connect. Native cloud-only accounts generally do not have this attribute populated.
Why did my PowerShell script return empty results?
Empty results mean no users currently match all of your filter criteria. Verify your connection to the MSOnline service and ensure that there are actually unlicensed, non-guest users with a populated ImmutableID in your tenant.
Can I use the AzureAD module instead of the MSOnline module?
Yes, but the cmdlets differ. You would need to use Get-AzureADUser and filter properties accordingly. However, the MSOnline module (Get-MsolUser) remains heavily documented and highly effective for this specific legacy filtering task.
How do I filter out specific domains from the export?
You can add additional filters within the Where-Object block using operators like -notmatch. For example, adding '-and $_.UserPrincipalName -notmatch "domain.com"' will exclude any users whose principal name matches that domain.
