13 Apr

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.

Also Read

Create Office 365 Group Reports with PowerShell Script (Export CSV)

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.

Also Read

Office 365 Reports Tool – Run Over 200+ Office 365 / Azure AD Reports – InfraSOS

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:

Also Read

Top 10 Best Free Office 365 Reporting Tools (Pros and Cons)

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

After the report gets generated as a csv file, we will view it using Microsoft Excel and make modifications to achieve a perfect view.

Also Read

How to Sync Active Directory with Office 365 with Azure AD Connect

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.

The above steps will give you a detailed view of the reports in Microsoft Excel as displayed below:

Also Read

Top 20 Best FREE Active Directory Reporting Tools (AD Tools in 2022)