Tweaking the Teams and Groups Report Script

PowerShell Tricks Help Get Real Work Done

As dedicated readers of this blog might have noticed, I write quite a few PowerShell scripts. Notice that I use the word “write” instead of “develop.” That’s because my time of professional development lie in the days VAX BASIC and VAX COBOL in the 1980s. Today, I write PowerShell for fun to illustrate concepts and principals. I don’t write code that is bulletproof or a thing of beauty. In fact, I represent the archetype hacker (in its original meaning).

Upgrading Scripts

All of which means that I am constantly discovering techniques when I go looking for solutions to problems. Some would say that I am always on the lookout for new PowerShell tricks, but that stretches the point.

Take the Microsoft 365 Groups and Teams report script (Figure 1). I have worked on many iterations of this script since 2016 (the latest version is 5.10). The code evolved from using Exchange Online cmdlets to interrogate group mailboxes to the current version that’s mostly based on Graph API requests and is much faster. Some people have run the report for over 20,000 groups.

In some cases, the changes I make are driven by need. For example, the Get-ExoMailboxFolderStatistics and Get-MailboxFolderStatistics cmdlets from V3.01 of the Exchange Online management module have an annoying habit of returning system objects (arrays) for the date of last received email and the number of items in the inbox folder of group mailboxes. In some cases, the cmdlets report that a group mailbox has two dates for the last received email. This is crazy stuff because the Get-MailboxFolderStatistics cmdlet has been around since 2006 and Microsoft really shouldn’t be screwing it up at this point. The solution isn’t based on any PowerShell tricks. Instead, the script now includes code to handle the erratic behavior of Exchange Online cmdlets.

Outputting Email Addresses on Separate Lines

In other cases, I make changes in response to requests. One recent request was to include the email address of a group owner, which I did in V5.8. Then someone asked if they could have the email addresses of all group owners output in the report. Easy, I said, edit your copy of the script to get the owner display names for each group and join the names together into a string. But that didn’t work because they needed each email address to be on a separate line to import the data into Power BI.

Some search brought me to a May 2006 blog written by Jeffrey Snover, who was then the architect evangelizing PowerShell within Microsoft. Jeffrey subsequently became a Microsoft Fellow before leaving to go to Google in the Fall of 2022, just after he wrote the foreword for the 2023 edition of the Office 365 for IT Pros eBook.

Jeffrey’s blog covered what he called the “Ouptut Field Sperator,” or OFS, defined as a special variable containing the string used as a separator when PowerShell converts an array into a string. By default, OFS is a space, but you can change it. This knowledge and some searching brought me to another blog that explained how to use escape characters in the OFS. New line is an escape character, so presto, a solution appeared.

Take this example where we fetch the owners of a group and store the data in an array:

$GroupId = (Get-MgGroup -Filter "displayname eq 'Ultra Fans'").Id
[array]$GroupData = Get-MgGroupOwner -GroupId $GroupId
[array]$GroupOwners = $GroupData.AdditionalProperties.mail

The email addresses of the group owners are now in the array:

Tony.Redmond@office365itpros.com
Ben.James@Office365itpros.com
Chris.Bishop@office365itpros.com

If I convert the array to a string, the output is a line of names separated by spaces:

[string]$Owners = $GroupOwners
$Owners
Tony.Redmond@office365itpros.com Ben.James@Office365itpros.com Chris.Bishop@office365itpros.com

But if I define the special $OFS variable to be a new line character, I get this:

$OFS="`n`n"
[string]$ContactEmail = $GroupOwners
$ContactEmail
Tony.Redmond@office365itpros.com

Ben.James@Office365itpros.com

Chris.Bishop@office365itpros.com

Problem solved and the output has email addresses on separate lines. In some cases, a carriage return and new line might be better for the output. To do this, set $OFS to “`r`n” (see this post). Either way, being able to change the output space character is a nice example of the kind of PowerShell tricks and techniques that you can find on the internet.

Overcoming Export-CSV Limitations

I often use the Export-CSV cmdlet to export report data from PowerShell to a CSV file where people can open and work on the data using tools like Excel. Recently, a French MVP reported that the Teams and Groups Activity report script worked great, but the CSV output dropped the accented characters used in French (like é) from essential information like group names.

It’s not at all surprising that this should happen. CSV files are comma delimited plain-text ASCII files and by default, the cmdlet generates 7-bit ASCII output (other encoding schemes are available). If you want to more precise control over formatting and extended characters, you need something more sophisticated, which is where the ImportExcel module comes in. Exporting to an XLSX file preserves the formatting and group names appear in all their glory. The lesson learned here is that Export-CSV does a good but limited job. If you work with non-ASCII data, seek another solution.

Time to write some code and discover a few more PowerShell tricks to investigate!