Office 365 Report Using Excel COM Interface with PowerShell

Office 365 Report Using Excel COM Interface with PowerShell

I set out to explore a way to generate a nice, multi-sheet, Office 365 report that I could give to end-user’s or Office Manager’s using PowerShell. Unfortunately, I read about the Excel COM interface before reading about the Excel PowerShell module created by Doug Finke. If you are starting from scratch, I would recommend reading up on the PowerShell Module instead of going the COM route as it’s a easier to use that the Excel COM Interface. Regardless, I finished my PowerShell script and wanted to share it for others to use.

Pre-requisites

Not a lot of pre-reqs for this script and I designed it that way. At first it was reliant on an Excel Workbook that was pre-formatted how I liked, and it would populate the data specifically for that workbook only. However, I decided to have PowerShell format the Excel Workbook from scratch as it ran. So for this script to run you only need 1 thing: (I think it goes without saying, but you will need PowerShell as well as an Office 365 account as well)

  • Excel

Report Goals

For my report, I wanted to make sure it could do at least the following:

  1. Make a separate WorkSheet for each report
  2. Each WorkSheet will have a nice, clean heading
  3. Report the following:
    1. Licensed Mailboxes
      1. Users with licenses
      2. Display Name
      3. Friendly License Name
        1. If no friendly name found, just use the AccountSkuID
      4. Primary E-Mail Addresses
      5. Alias E-Mail Addresses
    2. Groups
      1. Group Name
      2. Group Type
        1. Distribution
        2. Office 365 Group
        3. Security Group
        4. etc…
      3. Group E-Mail Address (if its got one!)
    3. Shared Mailboxes
      1. Shared Mailbox Name
      2. E-Mail Address
    4. Contacts
      1. Name
      2. E-Mail Address
    5. Public Folders
      1. Name
      2. If It’s Mail-Enabled
      3. E-Mail Address
    6. Domains
      1. Domain Name
      2. If its fully verified
      3. If its default
        1. If it’s default, highlight it
  4. Make the report good looking, and easy to read
  5. Show the data in alphabetical order
  6. Auto-Format Excel rows and columns

Report Formatting

Below you can see some of the different tabs of the report from when I ran it on my test Office 365 tenant.

Report on all the Groups in my tenant
Public Folder report, shows Name, E-Mail and if it’s Mail-Enabled
Licensed Users, Friendly License name, primary E-mail and all alias addresses

Each report will be contained in it’s own Excel WorkSheet. Cells A1:C1 will contain the report name, and row 2 will contain the report headers. As we see pictured, the header cells and the number cells are formatted with a blue background and a white lettering to let it stand out from the report results. For the Licensed Users report it will take the license SKU (or SKU’s if you have more than 1 license) and convert the SKU to a friendly license name using a Hash Table. If it cannot find a match it will fail back on the AccountSkuID. So if it could not find the Enterprise Plan E1 license in the Hash Table it will list “EXCHANGESTANDARD”.

PowerShell will create the entire Excel Workbook, all report WorkSheets and worksheet formatting from scratch as it goes from report to report. The video below shows me running the script. PowerShell calls the Excel COM object by running:

 

And then it will show the Excel program by running:

 

As data starts filling into the report it will continuously auto-size the rows and columns so all the data fits nicely into each cell. The report data background color will alternate from white to light grey. As it finished up with the report it will automatically switch to the next worksheet. This will help you watch what it’s working on if you choose to have the Excel application open while it runs.

Pictured below we can see it changing to the Licensed Mailbox worksheet. Cell 1,1 becomes the report title where it then will change to font size 26, font changes to Cambria, changing font colors and alignment and then merging cells a1,c1.

Once it’s finished with the report title it will begin with the report headers. The first one it makes is the “#” which will label the rows in the report. The headers will all be formatted the same way.

Report Data

Licensed Mailboxes

The “Licensed Mailboxes” report will show you the following information

  • User Account Name
  • Friendly Office 365 license name
  • Primary E-mail address
  • All alias E-Mail addresses

The license mailbox report will look up each users license which is stored in a format like “{bwya77:FLOW_FREE, bwya77:EXCHANGESTANDARD}”. It will go through each license and look up the friendly name and display that.

For the users Alias E-Mail Addresses it will display all of them if there are multiple entries and also remove the primary from the object by looking for the value that matches “SMTP:” which is used only for the primary address.

Groups

The “Groups” report will show the following information

  • Group Name
  • Group Type
  • E-Mail Address

The Group report will report on all groups. You can distinguish the groups by their type. In my report I have mostly distribution groups, but I also have one mail-enabled security group.

Shared Mailboxes

The “Shared Mailbox” report will show the following information

  • Shared Mailbox Name
  • Primary E-Mail Address

Contacts

The Contacts report will show the following information

  • Contact Name
  • Primary E-Mail Address

Public Folders

The “Public Folders” report will show you the following information

  • Public Folder Name
  • Primary E-Mail Address
  • Mail-Enabled

The only report that takes a second to run is Public Folders. This is because it’s running 2 commands in the background. Get-PublicFolder and Get-MailPublicFolder. Get-PublicFolder will list ALL Public Folders, mail-enabled or not. So to list Mail-Enabled Public Folders and regular Public Folders I am comparing both objects and removing the Mail-Enabled Public Folders from the regular Public Folder object.

Domains

The “Domains” report will show you the following information

  • Domain Name
  • Default status
  • Verification status

The Script

The script is listed below and is copy-ready. There are no variables or anything you need to change or tweak (unless you want to tweak the reports look or data). As long as you have met the pre-requisites you can just copy this and run it immediately.

I do plan and learning more into the Excel PowerShell module and hopefully will be able to generate multi-tenant reports, and even charts based on each data set. If you are looking for more great information on the Excel COM interface I suggest taking a taking a look at this write-up.

 

 

 

2 thoughts on “Office 365 Report Using Excel COM Interface with PowerShell

  1. This is awesome. But just wanted to check is it possible to have one row for each license assigned to the user, instead of merging.

Leave a Reply

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