Mastering Microsoft 365 PowerShell Scripting

Do Three Fundamentals of Microsoft 365 PowerShell Well and Scripting is Much Easier

As some of you might know, I write a lot of PowerShell. I’m not a very good PowerShell developer. Most of my code ignores fundamentals like error handling because I write scripts to explore and explain principles of getting work done rather than to develop complete solutions. In any case, that’s my excuse.

At the TEC 2023 conference in Atlanta, I was quizzed about how to write PowerShell scripts for Microsoft 365. The more I use PowerShell to interact with Microsoft 365 workloads, the more I am certain that the essence of PowerShell knowledge for administrators boils down to three things:

• Get objects.
• Interpret objects.
• Report objects.

95% of scripts follow the pattern of get, interpret, and report. This applies to scripts that run interactively and those that run on a schedule, like Azure Automation runbooks. Sure, there might be some twiddling of bits to update things like mailbox settings, but generally, my scripts follow the pattern. It’s my belief that if administrators learn the basics of executing the pattern well, they’ll have sufficient mastery of PowerShell to handle even complex automation projects. Let me explain why I think this way.

Getting Objects

In many cases, the need for automation arises because something needs to be done to a set of objects, like mailboxes, sites, or teams. Scripts might begin by running cmdlets like Connect-MgGraph or Connect-ExchangeOnline to connect to different Microsoft 365 endpoints followed by initializing some variables, but the first real work is done when fetching objects for processing.

All the Microsoft 365 modules are well-equipped with cmdlets to fetch objects. Sometimes multiple cmdlets exist that can fetch the same objects, meaning that the trick is to make the right choice of which cmdlet to use. That decision depends on the context. We’ll get to that later.

Table 1 lists some of the basic objects used in Microsoft 365 and the cmdlets available to fetch these objects.

The thing that shines out from Table 1 is that Microsoft Graph PowerShell SDK cmdlets have access to most of the important Microsoft 365 objects. Mailboxes are the obvious exception. SDK cmdlets are available to interact with messages, but only through the Users endpoint. You can’t use SDK cmdlets to fetch shared mailboxes or room mailboxes, for instance.

Filtering and Limits

If you don’t hit a limit, getting every object of a certain type is relatively simple. Microsoft 365 applies throttles in different places to ensure that users can’t absorb more resources than they should. PowerShell is no different. You can certainly fetch many objects with a single command, but the general rule is to only retrieve what you intend to process.

For example, don’t run the Get-ExoMailbox cmdlet without a filter because you’ll end up with a collection of user, shared, and room mailboxes. Instead, specify the RecipientTypeDetails parameter to find the type of mailboxes required. Even better, add a filter to further focus on the precise set of objects needed. A well-formed query looks something like this:

[array]$Mailboxes = Get-ExoMailbox -Filter {Office -eq "Dublin"} -RecipientTypeDetails UserMailbox -ResultSize Unlimited

The command tells PowerShell to create an array of mailboxes that match two conditions: their Office property is set to Dublin and they are a user mailbox. The command is willing to accept all matching mailboxes. Exchange supports different filterable properties for different cmdlets, but you’ll find that the same properties are used often.

Similarly, a common requirement is to find the set of Entra ID user accounts that have licenses. Looking for licensed accounts removes the user accounts created for shared mailboxes, Microsoft Bookings, and similar purposes. This command uses the Get-MgUser cmdlet to do the job:

[array]$Users = Get-MgUser -Filter "assignedLicenses/`$count ne 0 and userType eq 'Member' and OfficeLocation eq 'Dublin'" -ConsistencyLevel eventual -CountVariable Records -All

The command creates an array of user accounts that have licenses and whose OfficeLocation property is Dublin. Because the filter queries a multi-value property, it’s called a complex query. In Entra ID terms, this means that the query must wait until all copies of the data are consistent (that’s why the Consistencylevel parameter is present).

It’s obvious that the Get-ExoMailbox and Get-MgUser cmdlets use different kinds of filters.  The Exchange cmdlets have their roots in the very first implementation of PowerShell in a major Microsoft server product (Exchange Server 2007) and they follow the rules of syntax laid down at that time. The Graph SDK cmdlets replicate Graph API query rules and that accounts for the different syntaxes seen in these examples.

Nevertheless, the point remains that the golden rule is to only fetch the data you need to process. Using good filters is the best way to accomplish this goal.

Interpreting Objects

Once you’ve found some objects, the next step is to make sense of the data. Most PowerShell cmdlets return easy-to-use properties, but there are a few things to watch out for with some Microsoft 365 cmdlets.

In some cases, the value returned for a property is an array and some work is needed to get the full information that you might want. Consider this example of finding who manages a distribution list:

Get-DistributionGroup -Identity VIPusers | Format-List ManagedBy

ManagedBy : {tony.redmond, Lotte.Vetler, d36b323a-32c3-4ca5-a4a5-2f7b4fbef31c}

The ManagedBy property holds the name of each owner account. To get more information about the owners, use the names with another cmdlet like Get-ExoMailbox. For example:

[array]$ManagedBy = Get-DistributionGroup -Identity VIPUsers | Select-Object -ExpandProperty ManagedBy

$ManagedBy | Get-ExoMailbox | Format-Table DisplayName, Alias, Name

DisplayName         Alias        Name
-----------         -----        ----
Tony Redmond        Tony.Redmond tony.redmond
Lotte Vetler        Lotte.Vetler Lotte.Vetler
Kim Akers (She/Her) Kim.Akers    d36b323a-32c3-4ca5-a4a5-2f7b4fbef31c

Some of the Microsoft Graph PowerShell SDK cmdlets obscure valuable information. For example, the Get-MgGroupMember cmdlet returns a list of account identifiers for group members:

Get-MgGroupMember -GroupId (Get-MgGroup -Filter "Displayname eq 'System Innovation'").Id

Id                                   DeletedDateTime
--                                   ---------------
eff4cd58-1bb8-4899-94de-795f656b4a18
a3eeaea5-409f-4b89-b039-1bb68276e97d
67105a51-e817-493e-8094-f600babf5f62
d36b323a-32c3-4ca5-a4a5-2f7b4fbef31c

If you want details of the group members, a different approach is necessary to retrieve the information from the additionalProperties property:

$GroupId = (Get-MgGroup -Filter "displayName eq 'System Innovation'").Id
Get-MgGroupMember -GroupId $GroupId | Select-Object -ExpandProperty additionalProperties

Key               Value
---               -----
@odata.type       #microsoft.graph.user
businessPhones    {+1 713 633-5141}
displayName       Kim Akers (She/Her)
givenName         Kim
jobTitle          VP Marketing
mail              Kim.Akers@office365itpros.com
mobilePhone       +1 761 504-0011
officeLocation    NYC
preferredLanguage en-US
surname           Akers
userPrincipalName Kim.Akers@office365itpros.com

Before you ask, it’s the Graph that returns data in this way.

The unified audit log is a great source of valuable information about who did what and when inside a Microsoft 365 tenant. Workloads capture audit events as people interact with applications and those events eventually end up in the audit log. The Search-UnifiedAuditLog cmdlet (which has had some recent problems) finds data from the log. A lot of interesting detail is contained in a JSON-format property called AuditData. Before you can extract information from that property, you must convert it from JSON.

For example, this command searches the audit log to find events related to file edits in SharePoint Online:

$Records = search-UnifiedAuditLog -StartDate 18-Oct-2023 -EndDate 19-Oct-2023 -Operations FileModified -Formatted -ResultSize 5000 -SessionCommand ReturnLargeSet

After finding the right event, we can convert its AuditData property:

$AuditData = $Records[0].AuditData | ConvertFrom-Json

And then all the fields in the property are available for use in a script. For instance, here’s the site URL for the modified document:

$AuditData.SiteUrl
https://office365itpros.sharepoint.com/sites/BlogsAndProjects/

At this point, you might wonder how to deal with all of the edge cases you might encounter. Trial and error and good internet searches are the normal response. Someone has probably met and overcome a problem you meet, and maybe there’s a Practical365.com article that helps. Just be persistent.

Reporting Objects

Eventually, a script extracts all the required data and it’s time to generate some form of report. Some of the common ways to make the output generated by a script available to people include:

• Creating an HTML format document and print it or store the file in SharePoint Online.
• Distributing the report via email, inserting the data in the body of a message, or adding it as an attachment.
• Posting the information to a Teams channel.
• Update a list in a SharePoint Online site.

These actions mark the final stage of a script. Before a script can post, send, or store anything, the data retrieved from objects must be held in a PowerShell object, usually an array or a list.

I use the same approach to record data in all scripts. First, I create a PowerShell list:

$Report = [System.Collections.Generic.List[Object]]::new()

Then, after the script processes an object, the data to capture is handled by populating the properties of a custom object and writing it to the list. In performance terms, this approach is better than adding items to an array because PowerShell recreates the array for each addition. That’s OK for a couple of items but not so good when dealing with thousands. Updating the list is simple. For instance:

$ReportLine = [PSCustomObject]@{
     TimeStamp       = $M.Received
     Sender          = $M.SenderAddress
     Recipient       = $M.RecipientAddress
     Subject         = $M.Subject
     Status          = $M.Status
     Direction       = $Direction
     SenderDomain    = $SenderDomain
     RecipientDomain = $RecipientDomain
    }
$Report.Add($ReportLine)

The output list is easy to work with and easy to transform into other forms. For instance, to include the data in a HTML file, the script can convert the list like this:

$HTMLData = $Report | ConvertTo-HTML -Fragment

Lots More to Learn about Microsoft 365 PowerShell

We’ve really only scratched the surface of learning how to work with Microsoft 365 data through PowerShell here. I hope that I’ve communicated my strong belief that writing successful PowerShell scripts is often done by following the formula of getting, processing, and reporting objects. After that, it’s a matter of finessing the basics. That, and reading about how people solve automation problems with PowerShell and understanding their code.

Hopefully, you’ll find that the many Practical365.com articles we publish about using PowerShell to get real work done help. And if you want to browse code, feel free to look through my GitHub repository. You might even find some working code there.