Exchange Migrations with Power BI Help

When migrated users in Exchange it is imperative to ensure that shared mailboxes are migrated along with its assigned users or the permissions will not work. When migrating, it is always good to get a grasp on how many Shared mailboxes are in the organization. What a better way than by using Power BI Visualization to help in understanding the magnitude of the task at hand. The results are simple and intuitive.

I like to use the Bubble Visualization created by Dharminder Kumar Dhanda located here. This visualization encodes data in circles. In this application the bigger the bubble the more users have access to the shared mailbox. Thank you Mr. Dhanda for sharing your custom visualization with the Power BI Community.

Now to the guide!

First we need the CSV, from the exchange management shell we run the following command.

Get-Mailbox -resultsize unlimited | Get-MailboxPermission | where {$_.user.tostring() -ne "NT AUTHORITY\SELF" -and $_.IsInherited -eq $false} | Select Identity,User,@{Name='Access Rights';Expression={[string]::join(', ', $_.AccessRights)}} | Export-Csv mailboxpermissions.csv -NoTypeInformation

Next, we need to load the CSV as a data set.

Get Source Data

2

Once you have chosen the file you will notice that the column names do not match. We will fix this issue on the next step.

Select Edit.  3

Now we will need to select Transform and select Use First Row As Headers.

4

Below the headers have been corrected but our Access Rights column has unwanted data. In this case its not much but in some cases its messy.

5

We will cleanse our data by using the Split Column feature.

6

We will use the Comma as the delimiter and split using the “At the left most delimiter” option.

7

After the split occurs you will notice two things.

  • There is an additional column that contains the additional data.
  • The original Access Rights column has been renamed.

We do not need the new column thus it can be removed. Ensure you review the column carefully, sometimes the first feature is not “FullAccess” but “DeleteItem”, this seems to happen randomly and it not immediate noticeable. This can be corrected by filtering and using the “Does not Equal” filter on the “DeleteItem”.

8

The next step is to close and apply the changes.

9

The data is now fit for our reporting. The Query window will close and the Reporting window becomes the active window. While in the reporting window please import the visualization we downloaded at the beginning of this guide.

10 11 12 13

Once the new visualization has been imported, select it and it will appear on the reporting window.

14

From the reporting window chose the Mailbox column as the first feature then the users as the values feature.

15

Lets change the Title!

Change Title

We should see some progress now! At this point we can see the mailbox name and if we select it the number of users will access is displayed.

That is pretty exciting but we want to know who these users with access are. We can accomplish this by using the built in Matrix Visualization.

17

For the Matrix Visualization we must ensure that Mailbox feature is first then the User feature 2nd in the Rows section.

 

18

Now we can see the users that pertain to the Mailbox select or we can view them all at once.

19

Another option is to add a slicer which would allow the selection of more than one Mailbox at once for those who like to select multiple mailboxes at once.

This guide can be adapted for Public Folders, etc.

I hope this guide is useful and aid in communicating to peers, layman and of course the corporate folks.

 

Author: J P

Azure, Azure AD, Office 365, Exchange, Exchange Online, Power BI, Data Science, Cloud in general and Power Shell are my passions. I love learning and staying current with technology.

Leave a Reply

Your email address will not be published. Required fields are marked *