Determine On-Premise and Cloud Mailbox Users

During an Exchange online management activity for one of our enterprise clients, I had to determine which mailboxes are on cloud and which ones still resides on-premise. After going through several PS commands, I had to come up with a customized basic script to get this done neatly. This script will run against Office 365 and check for the following.

Get licensed users, fetch Display Name, UPN and IsLicensed properties and format the output then export the result to an CSV file.

Connect to Exchange online PowerShell using Connect-ExchangeOnline and then run the following  after customizing the output path to suit your environment.

Get-MsolUser -MaxResults 50000 |
Where-Object isLicensed -eq $true |
Select-Object -Property DisplayName, UserPrincipalName, isLicensed,
                        @{label='MailboxLocation';expression={
                            switch ($_.MSExchRecipientTypeDetails) {
                                      1 {'Onprem'; break}
                                      2147483648 {'Office365'; break}
                                      default {'Unknown'}
                                  }
                        }} | Export-Csv c:\Official\Tools\mailusers.csv

You can run it directly from PowerShell which would result as below if everything went well.

Or use PowerShell ISE

Result would look something similar to this.

Get-MsolUser -MaxResults 50000 |
Where-Object isLicensed -eq $true |
Select-Object -Property DisplayName, UserPrincipalName, isLicensed,
                        @{label='MailboxLocation';expression={
                            switch ($_.MSExchRecipientTypeDetails) {
                                      1 {'Onprem'; break}
                                      2147483648 {'Office365'; break}
                                      default {'Unknown'}
                                  }
                        }} | Export-Csv c:\Official\Tools\mailusers.csv

Advertisement

Export All Sites, libraries and lists of a SharePoint Online Site Collection

If you remember classic SharePoint, it had that nice looking (and yet unreliable sometimes) feature called “SharePoint Site Structure” which was eventually deprecated as move & copy functions were introduced. This was very insightful to understand the site and content hierarchy across the entire SharePoint farm.

However, let’s assume you want to review your modern day SharePoint Online hierarchy every once in a while, and make sure your sites, libraries and lists are aligning to best practices as far as the depth of the site levels? Or, you just want to know what sort of sites exist in your site collection, we still have a manual way of getting those information out using a simple PowerShell script. This may not be the best sophisticated way of getting a handy report which can probably be obtained using a 3rd party tool.

Unless its a test environment, we rarely notice any Office 365 tenant without MFA enabled, so this script is Modern-Auth friendly and supports MFA. You can generate a basic report of all sites, libraries and lists in a specific site collection by defining the site collection name and CSV path to save it.

###Function to Get Lists and Libraries of a web
Function Get-SPOSiteInventory([Microsoft.SharePoint.Client.Web]$Web)
{
    Write-host -f Yellow "Getting Lists and Libraries from site:" $Web.URL
 
    ###Get all lists and libraries
    $SiteInventory= @()
    $Lists= Get-PnPList -Web $Web
    foreach ($List in $Lists)
    {
        $Data = new-object PSObject
        $Data | Add-member NoteProperty -Name "Site Name" -Value $Web.Title
        $Data | Add-member NoteProperty -Name "Site URL" -Value $Web.Url
        $Data | Add-member NoteProperty -Name "List Title" -Value $List.Title
        $Data | Add-member NoteProperty -Name "List URL" -Value $List.RootFolder.ServerRelativeUrl
        $Data | Add-member NoteProperty -Name "List Item Count" -Value $List.ItemCount
        $Data | Add-member NoteProperty -Name "Last Modified" -Value $List.LastItemModifiedDate
        $SiteInventory += $Data
    }
 
    ###Get All Subwebs
    $SubWebs = Get-PnPSubWebs -Web $Web
    Foreach ($Web in $SubWebs)
    {
        $SiteInventory+= Get-SPOSiteInventory -Web $Web
    }
    Return $SiteInventory
}
 
###Config Variables
$SiteURL = "https://sitename.sharepoint.com/sites/PWA"
$CSVFile = "C:\temp\filename.csv"
 
###Get Credentials to connect
 
Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $SiteURL -UseWebLogin
 
    ###Get the Root Web
    $Web = Get-PnPWeb
 
    ###Call the function and export results to CSV file
    Get-SPOSiteInventory -Web $Web | Export-CSV $CSVFile -NoTypeInformation
}
Catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red

As you execute it, you’ll be prompted for credentials and the report will be generated (duration might depend on the number of site collections and the weight of each)

Original script used in this scenario was published in this article of SharePoint Diary

Microsoft Ignite 2020 will be an online only (digital) event

In light of global health concerns due to the COVID-19 pandemic, Microsoft has decided to make Ignite (one of the most sought after tech event out there) an online only digital event.

This would have both pros and cons but, looking at the way Microsoft managed the global MVP summit 2020 via Teams, this can only be a better opportunity opening doors for many of those who won’t be able to make it to USA to attend in person. You surely gonna miss that networking opportunity with a beer and good food but let’s look at the positive side. This surely will be an epic event ! fingers crossed.

You can now pre-register here – https://www.microsoft.com/en-us/ignite