Reporting mailbox migration progress in Power BI


I’m currently working on a global Active Directory consolidation and Exchange e-mail migration at a client and as with every project is nice/required to report about how the migrations are progressing. At first I was using a PowerShell script that exported all the details that I needed to a comma separated file. The content of the CSV was copied into Excel where I used some Pivot tables to get the actual numbers that I could use in my report.

As you can imagine this took a while to get everything ready (somewhere between one and two hours), so I was eager to find something new where my report was looking better and especially where the whole process was automated. The answer? Power BI!

In the end the basics that I used before are reused. PowerShell will query both Active Directory and Exchange to get the latest information available and stores this information in 3 different CSV files. The automation part is the where these 3 CSV files are stored on SharePoint Online and Power BI will refresh this data automatically and update the dashboard and reports.

Generating the CSV files

The code is probably a bit longer than you expected, but it needs to correct a lot of information that’s configured wrong in Active Directory.

First we need to import the Active Directory module so we can use the right cmdlets to query Active Directory:

Import-Module ActiveDirectory

Next, the header of the CSV file that contains all account and mailbox information will be generated and Active Directory is queried for all AD Users under contoso.com/Production.

("userPrincipalName,BusinessUnit,Country,Enabled,Migrated,LoggedIn,Mailbox,ServerName,LitigationHoldEnabled,SingleItemRecoveryEnabled") | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReport.csv")
$AllADAccounts = Get-ADUser -Filter * -SearchBase "OU=Production,DC=contoso,DC=com" -Properties UserPrincipalName,SamAccountName,physicalDeliveryOfficeName,co,Enabled,sIDHistory,lastLogonTimestamp

We are using Active Directory Migration Tool to migrate accounts to the new domain and the old security identifier (SID) is added to the account in the new AD. This is also known as sIDHistory.

The script will check a couple of things for every AD User that comes back from the query above. First, it will check if the account is enabled, if it has sIDHistory added and if the account has a lastLogonTimestamp.

If ($Account.Enabled -eq $true) { $Enabled = "Yes"} Else { $Enabled = "No" }
If ($Account.SIDHistory -like "S-*") { $SIDHistory = "Yes"} Else { $SIDHistory = "No" }
If ($Account.lastLogonTimestamp -gt 1) { $LoggedIn = "Yes" } Else { $LoggedIn = "No" }

Next, the script checks if a mailbox exists for this user and it will gather some important settings like if litigation hold is enabled. First at the on premises Exchange servers. If there isn’t one (so the result is $null), it will check Exchange Online. If there’s also no mailbox in Exchange Online it will set $MailboxExists to No for this specific user.

$OnPremMailbox = Get-Mailbox -Identity $Account.SamAccountName -ErrorAction SilentlyContinue | Select LitigationHoldEnabled,SingleItemRecoveryEnabled,ServerName
If($OnPremMailbox -ne $null){
 $CheckOnDuplicate = $OnPremMailbox | measure
 If($CheckOnDuplicate.Count -eq 1){
 $MailboxExists = "Yes"
 $LitigationHoldEnabled = $OnPremMailbox.LitigationHoldEnabled
 $SingleItemRecoveryEnabled = $OnPremMailbox.SingleItemRecoveryEnabled
 $AuditEnabled = $OnPremMailbox.AuditEnabled
 $ServerName = $OnPremMailbox.ServerName
 }
 Else { }
}
Else {
$CloudMailbox = Get-RemoteMailbox -Identity $Account.SamAccountName -ErrorAction SilentlyContinue | Select LitigationHoldEnabled,SingleItemRecoveryEnabled
 If($CloudMailbox -ne $null){
 $MailboxExists = "Yes"
 $LitigationHoldEnabled = $CloudMailbox.LitigationHoldEnabled
 $SingleItemRecoveryEnabled = $CloudMailbox.SingleItemRecoveryEnabled
 $AuditEnabled = $CloudMailbox.AuditEnabled
 $ServerName = "Office 365"
 }
 Else {
 $MailboxExists = "No"
 $LitigationHoldEnabled = ""
 $SingleItemRecoveryEnabled = ""
 $AuditEnabled = ""
 $ServerName = ""
 }
}
Unfortunately not all information is entered correctly in Active Directory. The script will correct a couple of things in the Office-attribute, where the name of the business unit is stored, to make sure users are grouped at the right business unit and to avoid duplicates. For example: both NL1 and NL2 are part of NL, so if the Office field contains NL1 or NL2 it will be replaced by NL.
If ($Account.physicalDeliveryOfficeName -eq "NL1" -or $Account.physicalDeliveryOfficeName -eq "NL2") { $BusinessUnit = "NL" }
Else { $BusinessUnit = $Account.physicalDeliveryOfficeName }
Same goes for the Country-attribute. Some business units have entered the country in their local language, so these values will be replaced by the English description.
If ($Account.co -eq "Nederland") { $Country = "Netherlands" }
ElseIf ($Account.co -eq "Albanien") { $Country = "Albania" }
ElseIf ($Account.co -eq "Deutschland") { $Country = "Germany" }
ElseIf ($Account.co -eq "Litauen") { $Country = "Lithuania" }
ElseIf ($Account.co -eq "Österreich") { $Country = "Austria" }
Else { $Country = $Account.co }

When all If, ElseIf and Else statements are processed, the output will be written to a CSV file. The -Append parameter at the end makes sure the line is added to the file, instead of replacing it.

($Account.UserPrincipalName + "," + $BusinessUnit + "," + $Country + "," + $Enabled + "," + $SIDHistory + "," + $LoggedIn + "," + $MailboxExists + "," + $ServerName + "," + $LitigationHoldEnabled + "," + $SingleItemRecoveryEnabled) | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReport.csv") -Append

After all AD Users are processed the historical data will be gathered and written to a seperate CSV file. First we need to get the current date and import the previously generated CSV file.

$Timestamp = Get-Date -Format d
$Report = Import-Csv "C:\scripts\MigrationReport\Output\MigrationReport.csv"
Several variables are defined to measure the report in a different way and the last two variables will get all computer accounts under the contoso.com/Production OU and measure the total amount of data in all Exchange databases.
$MigratedAccounts = $Report | ?{$_.Migrated -eq "Yes"} | measure
$MigratedMailboxes = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "Yes"} | measure
$MigratedEmployees = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "Yes" -and $_.LoggedIn -eq "Yes"} | measure
$MailboxesOnPrem = $Report | ?{$_.ServerName -ne "Office 365" -and $_.ServerName -ne ""} | measure
$MailboxesCloud = $Report | ?{$_.ServerName -eq "Office 365"} | measure
$AccountsReady = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "No"} | measure
$NewMailboxes = $Report | ?{$_.Migrated -eq "No" -and $_.Mailbox -eq "Yes"} | measure
$NewUsers = $Report | ?{$_.Migrated -eq "No" -and $_.Mailbox -eq "Yes" -and $_.LoggedIn -eq "Yes"} | measure
$TotalAccounts = $Report | measure
$TotalMailboxes = $Report | ?{$_.ServerName -ne ""} | measure
$TotalComputers = Get-ADComputer -Filter * -SearchBase "OU=Production,DC=contoso,DC=com" | measure
$TotalOnPremMbxSize = Get-MailboxDatabase -Status | sort name | select name,@{Name='DB Size (Gb)';Expression={$_.DatabaseSize.ToGb()}} | measure "DB Size (Gb)" -sum
Most of the different results of these variables are counted and written to a CSV file that will keep track of the historical trend. Every time the script runs a new line will be added.
($Timestamp + "," + $MigratedAccounts.Count + "," + $MigratedMailboxes.Count + "," + $MigratedEmployees.Count + "," + $MailboxesOnPrem.Count + "," + + $MailboxesCloud.Count + "," + $AccountsReady.Count + "," + $NewMailboxes.Count + "," + $NewUsers.Count + "," + $TotalAccounts.Count + "," + $TotalMailboxes.Count + "," + $TotalOnPremMbxSize.Sum/1000 + "," + $TotalComputers.Count) | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReportTrends.csv") -Append

The totals are written to a new file every time for 4 specific tiles in the Power BI dashboard.

("TotalAccounts,TotalMailboxes,TotalOnPremMbxSize,TotalComputers") | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationTotals.csv")
([string]$TotalAccounts.Count + "," + [string]$TotalMailboxes.Count + "," + [string]$TotalOnPremMbxSize.Sum/1000 + "," + [string]$TotalComputers.Count) | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationTotals.csv") -Append

Uploading the CSV files to SharePoint Online

Now the CSV files are generated, the script will continue to upload them to SharePoint Online. It will upload all files in the specified folder. There are two prerequisites installed on the Exchange server to enable this feature in PowerShell.

  • SharePoint Server 2013 Client Components SDK (link)
  • SharePoint Online Management Shell (link)

First, the script will import two DLL-files from the SharePoint components:

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

After defining the functions that are necessary to upload files, the credentials are specified and the actual upload will take place.

#Usage
$Url = "https://yourdomain.sharepoint.com/team/TeamSite/"
$UserName = "serviceaccount@contoso.com"
$Password = "password"
$TargetListTitle = "Site Pages" #Target Library
$SourceFolderPath = "C:\scripts\MigrationReport\Output" #Source Physical Path 

#Upload files
Upload-Files -Url $Url -UserName $UserName -Password $Password -TargetListTitle $TargetListTitle -SourceFolderPath $SourceFolderPath

Importing the CSV files in Power BI

The 3 CSV files are currently placed in SharePoint Online, but it’s also possible to use OneDrive for Business. To import the data you’ll need to go to https://app.powerbi.com,click Get Data and click Get under Files.

Choose the source where your files are located. In this particular example that would be SharePoint Online. Browse to the folder where your files are located, select the file you want to build your report on and click Connect.

As soon as the dataset is imported you can click View dataset and start building reports.

Creating reports

Every dataset contains at least one report and specific filtering options. Since the filtering applies to the whole dataset, we need 3 different CSV files to get the right information available in the reports and the dashboard.

MigrationReport.csv contains a dump of all AD Users under contoso.com/Production with information like;

  • Does the user have a mailbox?
  • Is litigation hold enabled?
  • Where is the mailbox located?
  • Is it a migrated user, e.g. does it have sIDHistory?
  • Did the user ever login to the new domain?

MigrationReportTrends.csv is the second file and it holds all historical data about;

  • Migrated accounts (accounts with sIDHistory)
  • Migrated mailboxes (accounts with sIDHistory that have a mailbox)
  • Migrated employees (accounts with sIDHistory that have a mailbox and have logged in to the new domain)
  • Number of mailboxes on-prem (count of all mailboxes on-premises)
  • Number of mailboxes in Office 365 (count of all mailboxes in Office 365)
  • Accounts ready for migration (accounts with sIDHistory without a mailbox and not logged in yet)
  • Newly created mailboxes (mailboxes not linked to a migrated user account)
  • Newly created users accounts (accounts without sIDHistory)
  • The total number of accounts under contoso.com/Production
  • The total number of mailboxes
  • Total size of all Exchange databases on premises
  • The total number of computer accounts under contoso.com/Production

MigrationTotals.csv contains the last known totals that are used for only 4 tiles in the Migration Report dashboard:

  • Total number of accounts
  • Total number mailboxes
  • Total size of all Exchange databases on premises
  • Total number of computers

Automatic refresh

By default files located on OneDrive, OneDrive for Business and SharePoint Online are refreshed every hour.

Conclusion

The script is scheduled to run every day at 11:00 PM GMT+1 on one of the Exchange servers and it takes about 30 minutes to complete. It uses Active Directory and Exchange cmdlets. To use all available cmdlets the following path is used in the scheduled task:

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe -psconsolefile "D:\Program Files\Microsoft\Exchange Server\V15\bin\exshell.psc1" -file "C:\scripts\MigrationReport\MigrationReport.ps1"

All stakeholders are informed about how they can use this report so they are always up to date. All feedback has been very positive and the reports are heavily used. And last but not least: It saved me a lot of time 🙂

CSV files are probably not the most efficient and it may be better to choose Azure SQL Server for example. However, in this specific scenario it would take a lot of effort to get the data into a SQL database because sqlcmd and bcp couldn’t handle the generated CSV files and it would take a huge effort to get this fixed.

Click “expand source” to view the complete source code.

Import-Module ActiveDirectory

("userPrincipalName,BusinessUnit,Country,Enabled,Migrated,LoggedIn,Mailbox,ServerName,LitigationHoldEnabled,SingleItemRecoveryEnabled")  | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReport.csv")
$AllADAccounts = Get-ADUser -Filter * -SearchBase "OU=Production,DC=contoso,DC=com" -Properties UserPrincipalName,SamAccountName,physicalDeliveryOfficeName,co,Enabled,sIDHistory,lastLogonTimestamp
ForEach ($Account in $AllADAccounts){
If ($Account.Enabled -eq $true) { $Enabled = "Yes"} Else { $Enabled = "No" }
If ($Account.SIDHistory -like "S-*") { $SIDHistory = "Yes"} Else { $SIDHistory = "No" }
If ($Account.lastLogonTimestamp -gt 1) { $LoggedIn = "Yes" } Else { $LoggedIn = "No" }

$OnPremMailbox = Get-Mailbox -Identity $Account.SamAccountName -ErrorAction SilentlyContinue | Select LitigationHoldEnabled,SingleItemRecoveryEnabled,ServerName
If($OnPremMailbox -ne $null){
    $CheckOnDuplicate = $OnPremMailbox | measure
    If($CheckOnDuplicate.Count -eq 1){
        $MailboxExists = "Yes"
        $LitigationHoldEnabled = $OnPremMailbox.LitigationHoldEnabled
        $SingleItemRecoveryEnabled = $OnPremMailbox.SingleItemRecoveryEnabled
        $AuditEnabled = $OnPremMailbox.AuditEnabled
        $ServerName = $OnPremMailbox.ServerName
    }
    Else { }
}
Else {
$CloudMailbox = Get-RemoteMailbox -Identity $Account.SamAccountName -ErrorAction SilentlyContinue | Select LitigationHoldEnabled,SingleItemRecoveryEnabled
    If($CloudMailbox -ne $null){
    $MailboxExists = "Yes"
    $LitigationHoldEnabled = $CloudMailbox.LitigationHoldEnabled
    $SingleItemRecoveryEnabled = $CloudMailbox.SingleItemRecoveryEnabled
    $AuditEnabled = $CloudMailbox.AuditEnabled
    $ServerName = "Office 365"
    }
    Else { 
    $MailboxExists = "No"
    $LitigationHoldEnabled = ""
    $SingleItemRecoveryEnabled = ""
    $AuditEnabled = ""
    $ServerName = ""
    }
}

# Corrections in the OPCO field 
If ($Account.physicalDeliveryOfficeName -eq "NL1" -or $Account.physicalDeliveryOfficeName -eq "NL2") { $BusinessUnit = "NL" } 
Else { $OPCO = $Account.physicalDeliveryOfficeName }

# Corrections in the country field
If ($Account.co -eq "Nederland") { $Country = "Netherlands" }
ElseIf ($Account.co -eq "Albanien") { $Country = "Albania" }
ElseIf ($Account.co -eq "Deutschland") { $Country = "Germany" }
ElseIf ($Account.co -eq "Litauen") { $Country = "Lithuania" }
ElseIf ($Account.co -eq "Österreich") { $Country = "Austria" }
Else { $Country = $Account.co }

    ($Account.UserPrincipalName + "," + $BusinessUnit + "," + $Country + "," + $Enabled + "," + $SIDHistory + "," + $LoggedIn + "," + $MailboxExists + "," + $ServerName + "," + $LitigationHoldEnabled + "," + $SingleItemRecoveryEnabled)  | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReport.csv") -Append
}

# Getting history trend data
$Timestamp = Get-Date -Format d
$Report = Import-Csv "C:\scripts\MigrationReport\Output\MigrationReport.csv"

$MigratedAccounts = $Report | ?{$_.Migrated -eq "Yes"} | measure
$MigratedMailboxes = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "Yes"} | measure
$MigratedEmployees = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "Yes" -and $_.LoggedIn -eq "Yes"} | measure
$MailboxesOnPrem = $Report | ?{$_.ServerName -ne "Office 365" -and $_.ServerName -ne ""} | measure
$MailboxesCloud = $Report | ?{$_.ServerName -eq "Office 365"} | measure
$AccountsReady = $Report | ?{$_.Migrated -eq "Yes" -and $_.Mailbox -eq "No"} | measure
$NewMailboxes = $Report | ?{$_.Migrated -eq "No" -and $_.Mailbox -eq "Yes"} | measure
$NewUsers = $Report | ?{$_.Migrated -eq "No" -and $_.Mailbox -eq "Yes" -and $_.LoggedIn -eq "Yes"} | measure
$TotalAccounts = $Report | measure
$TotalMailboxes = $Report | ?{$_.ServerName -ne ""} | measure
$TotalComputers = Get-ADComputer -Filter * -SearchBase "OU=Production,DC=contoso,DC=com" | measure

# Get total amount of mailbox data
$TotalOnPremMbxSize = Get-MailboxDatabase -Status | sort name | select name,@{Name='DB Size (Gb)';Expression={$_.DatabaseSize.ToGb()}} | measure "DB Size (Gb)" -sum

# Write values to CSV
($Timestamp + "," + $MigratedAccounts.Count + "," + $MigratedMailboxes.Count + "," + $MigratedEmployees.Count + "," + $MailboxesOnPrem.Count + "," +  + $MailboxesCloud.Count + "," + $AccountsReady.Count + "," + $NewMailboxes.Count + "," + $NewUsers.Count + "," + $TotalAccounts.Count + "," + $TotalMailboxes.Count + "," + $TotalOnPremMbxSize.Sum/1000 + "," + $TotalComputers.Count) | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationReportTrends.csv") -Append

("TotalAccounts,TotalMailboxes,TotalOnPremMbxSize,TotalComputers") | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationTotals.csv")
([string]$TotalAccounts.Count + "," + [string]$TotalMailboxes.Count + "," + [string]$TotalOnPremMbxSize.Sum/1000 + "," + [string]$TotalComputers.Count) | Out-File -FilePath ("C:\scripts\MigrationReport\Output\MigrationTotals.csv") -Append

# Upload the files to SharePoint Online
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

Function Ensure-Folder()
{
Param(
  [Parameter(Mandatory=$True)]
  [Microsoft.SharePoint.Client.Web]$Web,

  [Parameter(Mandatory=$True)]
  [Microsoft.SharePoint.Client.Folder]$ParentFolder, 

  [Parameter(Mandatory=$True)]
  [String]$FolderUrl

)

    $folderNames = $FolderUrl.Trim().Split("/",[System.StringSplitOptions]::RemoveEmptyEntries)
    $folderName = $folderNames[0]
    Write-Host "Creating folder [$folderName] ..."
    $curFolder = $ParentFolder.Folders.Add($folderName)
    $Web.Context.Load($curFolder)
    $web.Context.ExecuteQuery()
    Write-Host "Folder [$folderName] has been created succesfully. Url: $($curFolder.ServerRelativeUrl)"

    if ($folderNames.Length -gt 1)
    {
        $curFolderUrl = [System.String]::Join("/", $folderNames, 1, $folderNames.Length - 1)
        Ensure-Folder -Web $Web -ParentFolder $curFolder -FolderUrl $curFolderUrl
    }
}

Function Upload-File() 
{
Param(
  [Parameter(Mandatory=$True)]
  [Microsoft.SharePoint.Client.Web]$Web,

  [Parameter(Mandatory=$True)]
  [String]$FolderRelativeUrl, 

  [Parameter(Mandatory=$True)]
  [System.IO.FileInfo]$LocalFile

)

    try {
       $fileUrl = $FolderRelativeUrl + "/" + $LocalFile.Name
       Write-Host "Uploading file [$($LocalFile.FullName)] ..."
       [Microsoft.SharePoint.Client.File]::SaveBinaryDirect($Web.Context, $fileUrl, $LocalFile.OpenRead(), $true)
       Write-Host "File [$($LocalFile.FullName)] has been uploaded succesfully. Url: $fileUrl"
    }
    catch {
       write-host "An error occured while uploading file [$($LocalFile.FullName)]"
    }
}

function Upload-Files()
{

Param(
  [Parameter(Mandatory=$True)]
  [String]$Url,

  [Parameter(Mandatory=$True)]
  [String]$UserName,

  [Parameter(Mandatory=$False)]
  [String]$Password, 

  [Parameter(Mandatory=$True)]
  [String]$TargetListTitle,

  [Parameter(Mandatory=$True)]
  [String]$SourceFolderPath

)

    if($Password) {
       $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force
    }
    else {
      $SecurePassword = Read-Host -Prompt "Enter the password" -AsSecureString
    }
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$SecurePassword)
    $Context.Credentials = $Credentials

    $web = $Context.Web 
    $Context.Load($web)
    $list = $web.Lists.GetByTitle($TargetListTitle);
    $Context.Load($list.RootFolder)
    $Context.ExecuteQuery()

    Get-ChildItem $SourceFolderPath -Recurse | % {
       if ($_.PSIsContainer -eq $True) {
          $folderUrl = $_.FullName.Replace($SourceFolderPath,"").Replace("\","/")   
          if($folderUrl) {
             Ensure-Folder -Web $web -ParentFolder $list.RootFolder -FolderUrl $folderUrl
          }  
       }
       else{
          $folderRelativeUrl = $list.RootFolder.ServerRelativeUrl + $_.DirectoryName.Replace($SourceFolderPath,"").Replace("\","/")  
          Upload-File -Web $web -FolderRelativeUrl $folderRelativeUrl -LocalFile $_ 
       }
    }
}

#Configure SharePoint Online
$Url = "https://yourdomain.sharepoint.com/team/TeamSite/"
$UserName = "serviceaccount@contoso.com"
$Password = "password"
$TargetListTitle = "Site Pages"   #Target Library
$SourceFolderPath = "C:\scripts\MigrationReport\Output"  #Source Physical Path 

#Upload files
Upload-Files -Url $Url -UserName $UserName -Password $Password -TargetListTitle $TargetListTitle -SourceFolderPath $SourceFolderPath

Leave a Reply