M365: Export All Active/Blocked Users Using Powershell

When a user leaves an organization, the first step is to block the user’s sign-in from the Microsoft 365 admin center. This prevents the user from logging in and restricts access to Microsoft 365 services, including Outlook, signing in to their MDM device, and accessing any documents stored in OneDrive or SharePoint Online.

When you block a user’s sign-in on Microsoft 365, they will automatically be signed out of all Microsoft services within 60 minutes. It is a best practice to regularly monitor active users in Microsoft 365, including those for whom sign-in has been blocked.

In this blog post, we will explore how to identify all active and blocked users and export this information into a CSV file. Additionally, you can use PowerShell to export a list of users and their assigned licenses from Microsoft 365.

1. Install Entra ID Powershell Module (MSOnline)

To establish a connection with Entra ID, you need to install the Microsoft Azure Active Directory Powershell Module. Follow the steps below to install this module on your device:

1. Open the PowerShell console as an administrator.
2. Run the following PowerShell command: Install-Module -Name MSOnline.
3. Press ‘Y‘ or ‘A‘ when prompted to proceed with the installation.

Install Microsoft Azure Active Directory Module

Install-Module -Name MSOnline

2. Connect to Entra ID Using Powershell

After installing the MSOnline PowerShell module, use Connect-MsolService cmdlet to connect to Entra ID. Note that this command will only work if the MSOnline (as shown in Step 1) has been installed. Otherwise, you will encounter the following error:

connect-msolservice : The term ‘connect-msolservice’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. At line:1 char:1 connect-msolservice ~~~~~~~ CategoryInfo : ObjectNotFound: (connect-msolservice:String) [], CommandNotFoundException FullyQualifiedErrorId : CommandNotFoundException

1. Use Connect-MsolService to connect to Entra ID.
2. A pop-up will appear for authentication to Entra ID.
3. Provide the sign-in email address and password.

Connect to Entra ID

Connect-MsolService

3. List All Active Users in Microsoft 365

The key to finding all active users is to use the Get-MsolUser cmdlet and filter the list using the BlockCredential property. The BlockCredential property indicates the user’s sign-in status, represented in the boolean form of true/false. If BlockCredential is true, the user’s sign-in is blocked; if it is false, the sign-in for the user is active.

• After successfully connecting to Entra ID using the Connect-MsolService cmdlet, you can run the following command to find all active users in Office 365

Command to list Active users in Microsoft 365

Get-MsolUser -All | Where {$_.BlockCredential -eq $false} | Select DisplayName,UserPrincipalName, blockcredential

4. Export All Active Users from Microsoft 365 into a CSV file

To export all active users from Microsoft 365 into a CSV file, run the same command as above, but pipe it to the Export-CSV cmdlet and provide a path for the CSV file. Here is the command:

Command to Export All Active users into CSV file

Get-MsolUser -All | Where {$_.BlockCredential -eq $false} | Select DisplayName,UserPrincipalName, blockcredential | Export-CSV c:\temp\ActiveUsersO365.csv -NoTypeInformation

5. List All Microsoft 365 Users Where Sign-in Status is Blocked

To find all users with the BlockCredential status set to true, use the following command. This will list all users where the sign-in status is set to blocked:

Command to List All M365 Users where Sign-in Status is Blocked

Get-MsolUser -All | Where {$_.BlockCredential -eq $True} | Select DisplayName,UserPrincipalName, Country, City

6. Export All Microsoft 365 Users Where Sign-in Status is Blocked into a CSV file

To export all users in Microsoft 365 where sign-in status is blocked into a CSV file, use the same command as above, but pipe it to the Export-CSV command. Provide a path to store the report. Here is the command:

Sign-in blocked users (Export to CSV)

Get-MsolUser -All | Where {$_.BlockCredential -eq $True} | Select DisplayName,UserPrincipalName, Country, City | Export-CSV c:\temp\O365_blocked_Users.csv -NoTypeInformation

7. Export All Licensed Users into a CSV file using Powershell

To export all licensed users into a CSV file, we will use the following command:

Command to Export All Licensed Users into CSV File

Get-MsolUser -All | Where {$_.IsLicensed -eq $true} | Select DisplayName, UserPrincipalName, Country, City | Export-CSV c:\temp\LicensedUsersExport.csv

8. Export All Active Users from Microsoft 365 Admin Center

Powershell is much quicker and faster when you need to export specific information that may not be available from a Graphical User Interface (GUI) portal. The report we exported using PowerShell can also be easily exported directly from the Microsoft 365 admin center.

Let’s export the list of active users from Office 365 where the ‘Sign-in Allowed’ status is true:

1. Log in to the Microsoft 365 admin center.
2. Navigate to Users > Active Users.
3. On the right-hand side, next to the search box, click on Filter and select “Sign-in allowed“.
4. This will filter the list of users and only display those where Sign-in is allowed.

• After you select Sign-in Allowed from the Filter, Click on three dots and then select Export users. There may be a pop-up to provide information that “Export could take some time depending on the number of users in the tenant“. Click on Continue to start the Export process.

9. Export All Blocked users from Microsoft 365 Admin Center

To export a list of users where the sign-in status is blocked, please follow the steps below:

1. Log in to the Microsoft 365 admin center.
2. Navigate to Users > Active Users.
3. On the right-hand side, next to the search box, click on Filter and select “Sign-in blocked“.
4. This will filter the list of users and only display those where Sign-in is blocked.
5. Click on the three dots and then select “Export users“.

Conclusion

There is a cost associated with a Microsoft 365 license when it’s assigned to a user. Therefore, when a user leaves an organization, it’s best to remove/deallocate the license and block the user’s sign-in.

This not only saves costs but also enhances security for the tenant. Reports can be exported from the Microsoft 365 admin center or using PowerShell. In this blog post, we have explored both methods of exporting the reports.

In addition to the Sign-in Blocked, Sign-in Allowed, and Licensed User reports, you can apply filters to view specific groups such as Global admins, Unlicensed users, Users with errors, or Guest users, and export the information to a CSV file if needed.