Power BI for Exchange Migrations

Are you planning or in the beginning stages of an Exchange migration?

Exchange to Exchange Online migrations are very complex. There are many factors that need to be considered, users, technical details, VIPs and project champions., etc..

My goal is to help in some areas of migrations that are constant across migrations to Exchange Online using Power BI.

How can you utilize Power BI with Exchange Migrations? First we need to start with Power BI.

Power BI is not just an application but a cloud analytics service. It allows users to visualize and analyze data quickly. It has a easy and user friendly interface that lowers the bar for entry for new users. Where things get a bit complex is when trying to sort out licensing. I have a blog post that explains at length licensing and the different versions of Power BI.

Power BI Key Features

The ability to import data from a plethora of sources, especially other non-Microsoft services, including but not limited to the following.

    • Azure Data Lake
    • Azure Cosmos
    • Azure Blob
    • Salesforce
    • Facebook
    • Dynamics
    • QuickBooks Online
    • Zendesk
    • Google Analytics
    • Excel
    • Access Databases
    • SQL/MySQL
    • PostgreSQL
    • GitHub
    • JSON
    • Folders
    • SharePoint
    • XML
    • Teradata
    • Oracle
    • IBM DB2 Database
    • IBM Informix (Beta)
    • IBM Netezza
    • Amazon Redshift
    • Google Big Query
    • OData Feed
    • R Script

 

 

 

 

 

Now to the fun stuff!

The list below contains some of the primary areas that are difficult to extrapolate but are crucial to migrations. Gaining visibility adds a level of clarity certain to ensure your migration runs smoothly. The following list is are the areas that I will be focusing on.

    • Mailbox Delegations
    • Mailbox Sizes
    • Mailbox Item Counts
    • Migration Waves

We can create basic reports that can accomplish the following.

First, we will need to export data.

Export all of the mailbox permissions with the following Exchange PowerShell code.

Get-Mailbox -ResultSize unlimited | Get-MailboxPermission | Select Identity,User,@{Name='Access Rights';Expression={[string]::join(', ', $_.AccessRights)}} | Export-Csv MailboxPermissions.csv –NoTypeInformation

Next, we export all of the mailbox sizes.

Get-Mailbox -ResultSize unlimited | Get-MailboxStatistics | Select-object DisplayName, ItemCount, @{expression={$_.TotalItemSize.Value.ToMB()};Label="SizeInMB"} | export-csv MailboxSizes.csv -Notypeinformation

Now, that we have the data we need to import it in to Power BI Desktop. There is a lot of data wrangling required. The data wrangling will vary depending on naming schemes, etc., but this Power BI template can serve as a guide. Simply download the file and change the source location of the data to where you stored the files that were created.

Prior to creating reports, you will need to download the visualizations from the Microsoft Marketplace.  I used the Bubble Chart by Akvelon and the Network Navigator Chart which is from Microsoft.

The video below will show the basic reports that you can create.

This methodology can be applied to shared mailboxes, which is what let me to developing these reports. I have an old post here. Hopefully the creativity juices are flowing and you will apply Power BI visualizations to many other areas.

Where to start with Power BI and Licensing?

It is very easy to become overwhelmed with Power BI as a beginner. There are so many Power BI designations going around. There are seven Power BI names sailing the internet. While Microsoft states that there are only two licensing models for Power BI, Pro and Premium. The nuances of it all is were some potential adopters get lost. I hear it all the time. Which Power BI do I need? Do I need a server in Azure for this? Is it a service in Azure or in Office 365?

It can be very confusing as an example here is a list of names associated with Power BI.

    • Power BI Free
    • Power BI Pro
    • Power BI Desktop
    • Power BI Desktop “optimized” for Report Server
    • Power BI on Premises Data Gateway
    • Power BI on Premises Personal Data Gateway
    • Power BI Premium
      • Shared Capacity
      • Premium Capacity
      • Power BI Report Server

To provide some insight as to why are there so many names floating the web and help guide the decision process. We need to understand some history.

Microsoft has adopted an “evergreen” strategy. This allows them to introduce new features remove old ones, change direction on how they monetize, etc. It’s brilliant, no longer are do they need to slowly become stale before adding new features to either stay ahead or play catchup with the competition. Microsoft now leverages the “User Voice” to listen to its customers, make the necessary changes, add popular requested features, alter existing features, etc. While we have grown custom to the “evergreen” strategy over the last couple of years with the explosion of Office 365, Azure Infrastructure as a Service (IaaS), Azure Software as a Services (SaaS) and Microsoft Cognitive Services to name a few. One caveat inherent of this strategy for us as users, administrators, engineers, is the constant changes to our products. It is very difficult to stay current in this environment but once you adjust to the rapid pace of things its manageable.

A prime example of rapid changes is made very evident in the Power BI Gateway. Once upon a time it was one product, now there are two variations. The Power BI Team added a plethora of data source options and features that elevated the product. If a user did not need these features and simply wanted to use it as initially intended. Why force them to adopt the new requirements? Naturally the on premises data gateway forked. Now we have Power BI on Premises Personal Gateway for single users and the “normal” on premises data gateway that can connect to Microsoft Flow, Power Apps and Logic Apps and be shared with many users.

June of 2017, Power BI Premium was released as part of the General Availability (GA). This was a great attempt in removing the license limitations in the previous model thus removing a barrier of adoption. Until that point, both content generators and report consumers needed to have Power BI Pro licenses. This was making adoption for some organizations very costly. With the new Power BI Premium model, changes were introduced to accelerate adoption. Users who were report consumers and not content creators could consume these reports without the need for an individual license. The Power BI Premium model provides shared capacity and dedicated capacity. Power BI Premium also introduced the Power BI Report Server which allowed an on premises deployment of the service, also lowering the cost to consume reports. In general, I like to view Power BI Pro licenses for content creators and for smaller companies. Power BI Premium for enterprise companies or companies that will share reports with many consumers but have fewer Pro users. Microsoft provides a nifty calculator to help in the Premium model decision making.

Changes to Power BI happened very quickly and while I admit it is a challenge to keep current. I feel it is our responsibility to help guide beginners and potential Power BI users in to the Microsoft Data Science ecosystem. Power BI when used properly becomes a gateway in to the realm of possibilities that reside in the data that already exist for many customers. It provides the ability to tell a story visually that would otherwise have been lost in an email, spread sheet or simply not understood. If you combine Power BI with Microsoft Cognitive services and Azure Machine learning the possibilities are infinite.

Which Power BI is right for you?

    • Power BI Free – Exploration phase or for report consumers.
    • Power BI Pro – Need to share what I have learned with my immediate circle or small company.
    • Power BI Desktop – Everyone should use this tool. **There are two versions one of which is optimized for the on premises report server.
    • Power BI Data Gateway – Perhaps as proof of concept or personal use.
    • Power BI Premium Shared Capacity – Small to midsize companies because reports refresh no more than 8 times a day.
    • Power BI Premium/Premium Capacity – Large enterprises were reports need to be refreshed as close to live as possible.
    • Power BI Report Server (On Premises) – Need to keep everything on premises or already own the necessary hardware. Facilitate a least expensive Premium deployment.

Are you ready to learn some Power BI? Below are some useful links.

Microsoft Office 365 Group Based Licensing

In February Microsoft announced the public preview of Azure AD group based licenses. The caveat it was only available for tenants with Azure AD basic and above not only that the dynamic group feature was available for tenants with Azure AD Premium subscriptions.  That being said once it goes GA it will be available to E3 and equivalent licensed tenants. I am a half full kinda guy!

While working on a large Exchange Migration I decided to test it out. The issue, well I did not have any AD groups that I could honestly rely on for the several type of Office 365 licenses that I was issuing.

Prior to this feature I was populating a custom attribute in AD, cutting over mailboxes and then licensing based on the custom attribute via Power Shell. This method worked but wasn’t as efficient as I would have liked it to be.

The solution was to create the unique groups and add the appropriate users in these groups. I did some searching on the web and came up line below thanks to TechNet.

Get-ADUser -SearchBase ‘OU=Sales,OU=EasternUS,DC=TechKrag,DC=com’ -Filter * | ForEach-Object {Add-ADGroupMember -Identity ‘O365-SalesLicenses’ -Members $_ }

I did this for each license type included the proper restrictions on the O365 License side such as removed Yammer, etc., following the Azure instructions here.

Once AD synced to Azure AD the users were now licensed! Group based licensing not only saved time it makes the future management of licenses and features simple and easy.  If you need to add or remove a product from a group simply make a change in one location and all members of the group will update with the change, etc..

This was a long time coming and some may say perhaps overdue! Either way I am glad its here and we can take advantage of it.

If you are passionate about O365 let your voice be heard by Microsoft visit the user voice page and get to voting or adding suggestions. 

 

How to add VLANS to Service Profiles in UCS Manager via PowerShell

We have had some unique cases where we need to add VLAN’s to a service profile’s vNic’s in the new Cisco UCS Manager. With the new HTML 5 Manager 3.1(2b) I am unable to make this change to all vNic’s templates. I have it push the change to all service profiles.

The solution was to write quick script that sets some variables, and adds the vlans to each vNic (in our case 2) for each service profile. I googled but was unable to find a quick and easy script. I decided to write my own and share it with you.

The requirements for this script to run properly are Cisco PowerTools and ensure that PowerShell remote execution is enabled.

Enjoy!

################################
# Author: Jeff Patton #
# #
# Date: 3/17/2017 #
################################

#Variables to be set by you.
$ucsserver = "172.0.0.20"
$hostsp = Import-CSV C:\Scripts\hosts.csv
$vlantoadd = "New-VLAN"
$identpoolA = "Pool-A"
$identpoolB = "Pool-B"

#Import Modules
Import-Module Cisco.UCSManager

You can create the VLAN’s via Gui or by using this script as well. If you want to create them in this script, remove the commented out sections called “VLAN Creation Section 1 and 2” in this script.

#VLAN Creation Section 1 - Connect to UCS Server
#Connect-Ucs $ucsserver -Credential (Get-Credential)

#VLAN Creation Section 2 - Optional Script to Add VLAN via CLI to Global LANCloud
#Get-UCSLanCloud | Add-VLAN -Name "DeleteMe" -Id "999"
#$vlantoadd = "DeleteMe"

#Script to Run
foreach ($part in $hostsp)
{
$sp = Get-UcsServiceProfile -Name $part.Name
$ethA = $sp | Get-UcsVnic -Name eth0 -IdentPoolName $identpoolA
$ethB = $sp | Get-UcsVnic -Name eth1 -IdentPoolName $identpoolB
$ethA | Add-UcsVnicInterface -Name $vlantoadd -Verbose
$ethB | Add-UcsVnicInterface -Name $vlantoadd -Verbose
}

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.

 

Windows 10 Anniversary Update Slow Logon Fix

Yesterday, I  finally decided to install the Windows 10 “Anniversary” update. It took a while but everything worked, of course I had a full back up prior to installation.

The next morning my logon screen was there and when I hit control alt delete, a the spinning circle began. It stayed that way for over an hour (Yes, I waited while I worked on my Laptop.), until I decided it was enough. I hit control alt delete again and it allowed me to enter my password and logged me in.

Everything post logon was normal. I rebooted and the same thing, this time about 10 minutes, a third time, again, another 10 minutes. I had enough! Tonight, I decided to look in to some forums to find a solution. Suggestions cropped up from basic OS troubleshooting to suggestion to reinstall! One forum mentioned video drives as a potential issue. I did a bit of research and sure enough there was an Anniversary update driver. I installed the driver, rebooted and like magic I was back to normal.

I lost the link to the forum post reboot, I should have saved it and added it to this post. My apologies to whom ever ran the site out there in the almost infinite internet!

I hope this helps someone.

 

For Microsoft Partners Only

Information Technology departments across the globe have started are in the initial stages of moving any one part of their infrastructure services to the cloud. Microsoft has taken the world by storm with Office 365. I must admit at first, like many IT professionals, I was hesitant to move to Office 365.  After learning more about I decided to get O365 certified. This brought a set of challenges, I realized I do not have a test environment! At first it wasn’t much of an issue. I would test things out with trial accounts, not a bad idea 25 users, etc. Not a bad idea but it was getting old quickly!

The solution

My employer was a Microsoft Partner and as a partner there are benefits. One of these not so well known benefits is the Office 365 Demo Site! This site allows the creation of up to 6 tenants. It has changes over time, they use to allow the license category selections and the term for a tenant was 6 months before it was automatically removed. The new terms is 3 months for automatic deletion of tenants and one license scheme. Either way it is a great place to for testing.

Below is a quick guide on how to get started.

Step 1 – Sign In

Visit the Demo Site and sign in.

Microsoft Demos

Microsoft Demo

Step 2 – Create Tenant

Once signed in you will be taken to the dashboard, in Home and under My Demo Environments you will have the option to “Create a New Demo Environment”, click on the + sign.

Microsoft Partner Demo

Step 3 – Chose Tenant type and/or Industry

You will see the options for tenant creations, sometimes there are many options. I think it depends on what Microsoft is pushing for the year. Not long ago it they had all Enterprise Tiers even the E5.

When I took this screen shot I only had the one shown below.

Microsoft Partner Demo

Select the tenant type.

Microsoft Partner Demo

Under Industry you have more options.

Microsoft Partner Demo

Once you select one select finish at this prompt.

Microsoft Partner Demo

Step 4 Take note of you tenant name

After it completes you will be taken to the Dashboard were you will be provided with the tenant domain name, administrator account and password. The tenant domain name is where the green dot is located in the screen shot below.

Microsoft Partner Demo

That is all there is to it. Now you can test, demo, etc with a real live Office 365 Tenant without the need of 30 trials.

Build Your Own IT Lab

Lab

One thing that sets apart an amateur from a professional is experience. What a better way to gain experience than by building a lab and testing. From the basic AD installations, Exchange installations to the more complex hybrid O365 environments, labs are quintessential. When in doubt use a lab, when the change in question will have broad business effects, test in the lab, you get where I am going. If you are starting out in the IT industry the lab is your friend and teacher. Where else are you going to be able gain skills without any risk?

When I tried to build my lab I used Hyper-V as my hypervisor and ran into issues with network connectivity. This doesn’t mean that it can’t work for you. That being said, I decided to use VMWare Workstation which is the free version. Once I changed hypervisors the network issue experienced with Hyper V was resolved.

Pitfalls experienced during the process:

  • VM boot times where slow this grew exponentially when booting multiple VMS.
  • Issue number two, when running multiple VMs they were sluggish.

After combing the web and looking at countless benchmarks, I decided to purchase a Samsung 850 Pro. The new drive solved both problems. I took it a step further and decided to use it as my boot drive as well.

Below is the two step process to build a lab without the need of a dedicated server or desktop.

Step 1

Download and install VMware Workstation

Step 2

I like to download Microsoft Trial software from TechNet or Linux ISOs from DistroWatch.

Optional

One thing that you will encounter eventually is that you will want to open up a VM to the world. By default this is not possible because VMs are defaulted to use NAT behind the host’s NIC. This doesn’t allow a VM to see the rest of the LAN thus not allowing port forwarding or adding the VM to the DMZ. This can be resolved by making the changes below.

Provide LAN access

Navigate to the VMs settings shown below.

Network-Settings VMWare Workstation -1

Navigate to the Network Adapter option and choose Custom, choose a vnet. Don’t worry about the which vnet you use as long as you do not use the vnet labeled NAT.

Network-Settings VMWare Workstation.png

That is all I have on building a LAB.

 

Best PowerShell Exchange Health Report

Tools

There are many exchange tools on the web but this has been the most useful I have ever encountered. It provides a the bulk of what you need to know about an exchange organization. Every exchange admin should have this in their toolbox. Its called the Exchange Environment Report, it was developed by Steve Goodman Microsoft MVP.

This Report is like a Swiss Army Knife of reports.  It provides a comprehensive view on the exchange organization, from roles to database health, backup information, DAGS, mailbox counts, etc.. it includes this and more!

Enough bragging about it, you can get more information and a copy at Steve’s website or a direct download here. I would like to thank Steve for writing this awesome tool. Don’t forget to send him a message on Twitter!

 

Two step process to export a list of installed patches using PowerShell

desk-office-workspace-coworking

Building on the previous post Easy Server Inventory!

Not all businesses can afford expensive patch management solutions. Some organizations may have WSUS but not know how to use it properly or its outdated. Some of you may think SCCM can produce reports. SCCM is great when you have staff that can take advantage of it; for some its not a viable solution, because no one on staff has the skill set, price tag or a combination of time and/or money it takes to send someone to train on SCCM.

Scenario

The auditors, boss, corporate folks, etc. request a list or sample list of servers, they want to see  installed patches on each server with dates. A hefty proposition if you do not have one of these fancy applications that handle all of these things for you! No worries! Below is a quick way to keep things under control and provide the necessary information quickly and effectively.

Step 1

You can either gather a list of all servers in the domain via the AD Users and Computers, PowerShell or any other preferred method. You will use this to populate a txt file that will be used to feed the script.

Below is a script I like to use which can be useful for inventory, etc.

Export all servers to a friendly csv by running the following in PowerShell

Get-ADComputer -Filter {(OperatingSystem -Like "Windows *Server*")-and (Enabled -eq "True")} -Property * | Select Name,OperatingSystem,OperatingSystemServicePack,IPv4Address | export-csv Servers.csv -notypeinformation

*Added the (Enabled -eq "True") to filter only enabled Severs, thanks to the comment posted on Reddit by Master_apprentice .  -Thank you again

Step 2

Populate a file name servers.txt with the requested selection of servers.  Store this file in the same directory as the script below. The script below simply needs to be copied into either the PowerShell ISE or a notepad and saved as a ps1.

$ErrorActionPreference= 'silentlycontinue' # Allows the script to run in case of an error.

$servers = Get-Content -path Servers.txt  # Pulls the servers in question.

# The foreach below run the commands as the commands states for each server in the txt file.

# It then exports them to a useful csv.

foreach ($server in $servers)

{

(Get-Hotfix -ComputerName  $server | sort InstalledOn)| select CSName, Description, HotFixID, InstalledOn, RebootRequired | export-Csv -Path export.csv -append -notypeinformation

}

Once the report runs you will have a CSV with the information needed.