Export all active and blocked users from office 365 using powershell

by Leave a comment

When a user leaves a organization, first step is to block user’s sign-in from Microsoft 365 admin center. This way user will not be able to login and blocked from accessing any Microsoft 365 services like accessing outlook, sign in to their MDM device, accessing any documents stored in Onedrive or sharepoint online.

When you block a user’s sign-in on Microsoft 365, They’ll automatically be signed out of all Microsoft services within 60 minutes. Its a best practice to regularly monitor Office 365 active users and also the users where sign-in has been blocked. In this blog post, we will see how to find all active users and blocked users and Export it in to a CSV file.

You can export a list of users and their assigned licensed from office 365 using powershell from here.

Install Microsoft Azure Active Directory Module (MSOnline)

For creating a connection to Microsoft 365/Azure AD, we need to Install the Microsoft Azure Active Directory Module for Windows PowerShell. Please follow below steps to Install this module on your device:

  • Open Powershell console as an administrator.
  • Run Install-Module -Name MSOnline powershell command.
  • Press Y or A when prompted to proceed with Installation.

Install Microsoft Azure Active Directory Module

Install-Module -Name MSOnline
Install Microsoft Azure Active Directory Module (MSOnline)

Connect to Azure Active Directory using Powershell

After you have Installed MSOnline powershell module, you can use Connect-MsolService to connect to Azure active directory. This command will only work if MSOnline (Azure active directory module) has been already installed, else you will get below 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
  • Use Connect-MsolService to connect to Azure Active Directory.
  • There will be a pop-up to authenticate to Azure Active Directory.
  • Provide Sign-in email address and password.

Connect to Azure Active Directory

Connect-MsolService
Connect to Azure Active Directory using Powershell

Find all active users on Office 365

The key to find all active users is to using Get-Msoluser cmdlet and filter the list using Blockcredential property. blockcredential shows that if the user’s Sign-in status is blocked or active. This is in the boolean form of true / false. If blockcredential is true then Sign-in for that user is blocked and if blockcredential is false then Sign-in for the user is Active.

  • After you have successfully connected to Azure Active directory using connect-msolservice cmdlet. You can run below command to find all active users in office 365.

Active users on Office 365

Get-MsolUser -All | Where {$_.BlockCredential -eq $false} | Select DisplayName,UserPrincipalName, blockcredential
Find all active users on Office 365

Export all active users on Office 365 to a CSV file

To Export all active users on Office 365 in a CSV file, we will run the same command as above but pipe it to Export-CSV cmdlet and provide a path for the CSV file. Let’s check the command:

Active users report (Export to CSV)

Get-MsolUser -All | Where {$_.BlockCredential -eq $false} | Select DisplayName,UserPrincipalName, blockcredential | Export-CSV c:\temp\ActiveUsersO365.csv -NoTypeInformation
Export all active users on Office 365 to a CSV file

Find all users on Office 365 where Sign-in Status is Blocked

We will use below command to find all users where blockcredential status is set to true. This will list all users where Sign-in status is set to blocked.

Sign-in blocked users

Get-MsolUser -All | Where {$_.BlockCredential -eq $True} | Select DisplayName,UserPrincipalName, Country, City
Find all users on Office 365 where Sign-in Status is Blocked

Export all users on Office 365 where Sign-in Status is Blocked to CSV

To Export all users on Office 365 where sign-in status is blocked into a CSV file, we will use the same command as used above and pipe it to Export-CSV command. We will also provide a path to store the report. Please use below command for exporting the required data:

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

Export all Licensed Users in to a CSV file using powershell

To Export all licensed users into a CSV file, we will use below command:

Export all Licensed Users to CSV

Get-MsolUser -All | Where {$_.IsLicensed -eq $true} | Select DisplayName, UserPrincipalName, Country, City | Export-CSV c:\temp\LicensedUsersExport.csv
Export all Licensed Users in to a CSV file using powershell

Export all Active users from Office 365 using Microsoft 365 admin center

Powershell is much quicker and faster when you need specific information to be exported which may not be available from a Graphical user interface (GUI) portal. The reported as we exported using powershell, can easily be exported directly from Microsoft 365 admin center as well.

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

  • Login on Microsoft 365 admin center.
  • Go to Users -> Active Users.
  • On the right hand side, next to search box. Click on Filter and select Sign-in allowed.
  • This will filter the list of users and only show users where Sign-in is allowed.
Export all Active users from Office 365 using Microsoft 365 admin center

  • 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.
Export all Active users from Office 365 using Microsoft 365 admin center

Export all blocked users from Office 365 using Microsoft 365 admin center

For Exporting a list of users where Sign-in status is blocked. Please follow below steps:

  • Login on Microsoft 365 admin center.
  • Go to Users -> Active Users.
  • On the right hand side, next to search box. Click on Filter and select Sign-in blocked.
  • This will filter the list of users and only show users where Sign-in is blocked.
  • Click on three dots and then select Export users.

Conclusion

There is a cost associated with a Microsoft 365 license when its assigned to a user. Therefore, when a user leaves an organization its best to remove / deallocate the license and block user’s Sign-in. This will save cost and also increases security for the tenant. You can export reports from Microsoft 365 admin center or using Powershell. In this blog post, we have seen both the ways of exporting the reports.

Apart from Sign-in Blocked, Sign-in Allowed, Licensed User report. You can apply a filter to see the Global admins, Unlicensed users, Users with errors or Guest users etc. and export it to a CSV file if required.

Leave a Comment