Create Office 365 User Reports with PowerShell Script (Export CSV)
Create Office 365 User Reports with PowerShell. In this post, we will be discussing how to create an Office 365 Users’ report using a PowerShell script.
What is Office 365?
Office 365 is a software as a service offered by Microsoft, which is a subscription-based service that gives access to Microsoft’s productivity applications.
Below are some of the traditional and new Microsoft productivity applications we are familiar with:
- Microsoft Word
- Microsoft Excel
- Microsoft PowerPoint
- Microsoft Outlook
- Microsoft One Note
- Microsoft Publisher
- Microsoft Exchange
- Microsoft SharePoint
- Microsoft Teams
- Microsoft OneDrive
- Skype for Business
- Yammer
These services’ availability is dependent on the subscription/license been purchased and assigned to a user, we will not be discussing subscriptions or licenses in this article.
What is a User Object in Office 365?
For users to have licenses or subscriptions assigned to them they need to have a user object exiting in an Azure Active Directory. We would describe a user object as an object in Azure Active Directory with a userType attribute value defined as Member.
When a user is created in a Tenant, it creates the user object in Azure Active Directory. The user object allows Microsoft services via licenses assigned to it to be available to the user.
Types of Office 365 User Reports
We can not overemphasize the importance of auditing in Information Technology as a whole and this article concerning Office 365 Administrators.
Auditing these Office 365 users will assist administrators to know information about the users existing on the Tenant that could help during troubleshooting, onboarding, and separation of users to update internal documentation, and many more.
Below are some of the different types of Office 365 User Reports that administrators run:
- A report showing the Creation Date and Time for Office 365 Users
- A report showing Users that are Enabled/Disabled in an Office 365 Tenant
- A report showing Synchronized Office Users from on-premise Active Directory
- A report showing the Last Synchronization Time for Users in an Office 365 Tenant
- A report showing Users’ Multifactor Authentication Status
- A report showing Office 365 Users’ Role
- A report showing Office 365 Users’ last password change timestamp
These reports are mostly generated in CSV or HTML format by the administrators. A CSV report will be of preference in this article.
Generating Office 365 Users Reports using PowerShell
The Office 365 Administration Portal gives us the ability to generate this report, however, the report generated from the admin portal does not hold some attributes that would be included in our report.
In this article, we will be making use of Windows Microsoft PowerShell and the MSOnline module which is known as the MSOnline V1 PowerShell module for Azure Active Directory.
This module can be found in the PowerShell Gallery and this gives us access to create a more detailed report using PowerShell CMDLETs.
Installing the MSOnline Module
To gain access to this module, first, we would have to install the module on the PC we are going to use to generate the report.
The first script will be setting the execution policy of the PowerShell Process to Unrestricted. This is necessary to avoid any restrictions.
# set the execution policy of the process
Set-ExecutionPolicy -Scope Process Unrestricted -Force
The next script will be to install the MSOnline Module.
# Install MSOnline Module
Install-Module -Name 'MSOnline' -Force
Importing the MSOnline Module
Next, we will be importing the installed MSOnline Module into the current PowerShell session using the cmdlet below:
# Import MSOnline Module
Import-Module -Name 'MSOnline'-Force
We have listed some Office 365 User reports in this article and we are going to generate a single report that would have all the attributes of those reports and more.
For us to achieve this we will be making use of these two CMDLETS imported from the MSOnline module:
- Get-MsolUser: This cmdlet gets the users from Azure Active Directory
- Get-MsolUserRole: This cmdlet gets the administrator roles to which an Office 365 User belongs.
Connecting to MsolService
We need to connect to Azure Active Directory before we can query it using the CMDLETS listed above.
The PowerShell script below allows this to be possible:
# Connect to MsolService
Connect-MsolService
Next, there will be a pop-up dialogue box to enter the global administrator credentials:
Exporting Office 365 Users’ Details to csv using PowerShell
Next, we will be creating an Office 365 Users Report which will have a combination of the attributes and more of the previously listed examples of user reports generated by Office 365 Administrators.
The report will have the following parameters in a CSV output file:
- Display Name
- UserPrincipalName
- First Name
- Last Name
- When Created
- Mobile Number
- Department
- City
- Usage Location
- Proxy Addresses
- MFA Status
- Last Password Change Timestamp
- Block Sign-In Status
- User Role Details
- Last DirSync Time
- Synchronization Status
Below is the PowerShell script that generates this Office 365 Users CSV report:
Note: Change the $ReportName variable to suit your desired output file path and file name. Also, the “.\” represents the present working directory (you can get this information by running the following cmdlets in your PowerShell session: PWD or Get-Location).
“.\” can be changed to any desired directory on the machine (E.g C:\Windows\)
# set the name of the report alongside the path ".\ relative path"
$ReportName = ".\Office365UsersReport.csv"
# get all office Users
$UsersDetails = Get-MsolUser -All
# set export array
$ExportArray = @()
# loop through each office 365 user
Foreach ($UserDetail in $UsersDetails) {
# set the office 365 user's UserPrincipalName
$UserUpn = $UserDetail.UserPrincipalName.ToString()
# get the office 365 user's role(s)
$UserRoleDetails = Get-MsolUserRole -UserPrincipalName $UserUpn
# set the value of the office 365 user's immutable id
$ImmutableId = $UserDetail.ImmutableId
#region check for the office 365 user's synchronization status
if ($null -eq ($UserDetail.ImmutableId)) {
# set the office 365 user's Synchronization Status
$SynchronizationStatus = "Cloud Only"
}
elseif ($null -ne ($UserDetail.ImmutableId)) {
# get the office 365 user's Synchronization Status
$SynchronizationStatus = "Should Be On-Premise ($ImmutableId)"
}
#endregion check for the office 365 user's synchronization status
# add values to array
$ExportArray += [PSCustomObject][Ordered]@{
# get the office 365 user's display name
"Display Name" = $UserDetail.DisplayName
# get the office 365 user's userprincipalname
"UserPrincipalName" = $UserUpn
# get the office 365 user's First Name
"First Name" = $UserDetail.FirstName
# get the office 365 user's Last Name
"Last Name" = $UserDetail.LastName
# get the office 365 user's creation date
"When Created" = $UserDetail.WhenCreated
# get the office 365 user's mobile number
"Mobile Number" = $UserDetail.MobilePhone
# get the office 365 user's department
"Department" = $UserDetail.Department
# get the office 365 user's city
"City" = $UserDetail.City
# get the office 365 user's usage location
"Usage Location" = $UserDetail.UsageLocation
# get the office 365 user's proxy addresses
"Proxy Addresses" = ($UserDetail.ProxyAddresses | Out-String).Trim()
# get the office 365 user's MFA status
"MFA Status" = $UserDetail.StrongAuthenticationRequirements.state
# get the office 365 user's last password change timestamp
"Last Password Change Timestamp" = $UserDetail.LastPasswordChangeTimestamp
# get the office 365 user's block sign-in status
"Block Sign-In Status" = $UserDetail.BlockCredential
# get the office 365 user's role details
"User Role Details" = ($UserRoleDetails.Name | Out-String).Trim()
# get the office 365 user's Last Directory Sync Time
"Last DirSync Time" = $UserDetail.LastDirSyncTime
# get the office 365 user's Synchronization Status
"Synchronization Status" = $SynchronizationStatus
}
}
# export to csv file
$ExportArray | Export-Csv -Path $ReportName -Delimiter "," -NoTypeInformation
Modifications to Excel Office 365 Users’ Details CSV Export Report
Once the report is open in Excel, right-click on any column heading and select the Format Cells… option.
Next, click on the Alignment tab, then click on the drop-down menu below the Vertical: option and select Top, then click on the OK button to save.
Create Office 365 User Reports with PowerShell Script (Export CSV) Conclustion
In this article we created a Powershell script that allowed you to create Office 365 user reports using Powershell and export to a CSV file. Check out our Office 365 reporting tool that allows you to easily create Office 365 reports and automate these steps easily.
