Purview and Exchange Online Disagree about Scoped Audit Log Searches

Like many Purview solutions, audit log searches support scoping using Entra administrative units. In other words, an account holding the Audit Manager Purview role scoped for a specific administrative unit is only able to find audit records linked to the administrative unit. An account can be scoped to manage a single or multiple administrative units. Alternatively, the scope assigned to an account can be “Organization,” meaning that the role applies to all audit events created in the tenant. Figure 1 shows that two accounts hold organization scopes for the Audit Manager role while another is scoped for a single administrative unit.

Administrative unit support for Purview scoped audit log searches has been available since November 2023.

Audit Records and Administrative Units

Each audit record is tagged with the user account or service principal responsible for the logged action. If a user account belongs to an administrative unit, the audit event captures the identifier of the administrative unit in an array called AssociatedAdminUnits in the audit payload. If the account belongs to multiple administrative units, the audit record captures the identifiers of all the administrative units. Capturing administrative unit details in audit records is what makes scoping possible.

For example, this code fetches the audit payload from an audit record and converts it from JSON before looping through the administrative unit identifiers to return the display name for each administrative unit:

$AuditData = $Records[0].Auditdata | ConvertFrom-JSON

ForEach ($AU in $Auditdata.AssociatedAdminUnits) {
  $AUName = Get-MgDirectoryAdministrativeUnit -AdministrativeUnitId $AU.toString() | Select-Object -ExpandProperty DisplayName
  Write-Host ("Found administrative unit {0} ({1})" -f $AUName, $AU)
}

Found administrative unit Ireland (112f5e71-b430-4c83-945b-8b665c14ff25)

Limiting Audit Log Searches with Administrative Units

When a user with a scoped Audit Manager role signs into the Purview Compliance portal to run an audit log search, they can select one or multiple of the administrative units they are scoped to manage for the search (Figure 2).

Purview audit log searches only return audit records matching the selected administrative units. It’s easy to validate that this is so by checking that audit records returned by the search have the identifiers for the selected administrative unit(s) in their properties (Figure 3).

Inconsistent Scoping

Administrative unit scoping works for audit log searches performed through the Purview compliance portal and with the AuditLog Query Graph API. However, despite almost a year lapsing since the introduction of scoping for audit log searches, the Purview scopes don’t work for searches performed using the Search-UnifiedAuditLog cmdlet.

This is an odd situation. Despite Microsoft’s sometimes unexplained messing with the Search-UnifiedAuditLog cmdlet, it remains a very significant and popular way to run audit log searches. However, the Search-UnifiedAuditLog cmdlet is part of the Exchange Online Management PowerShell module. The Exchange Online cmdlets use Exchange Role Based Access Control (RBAC) to limit their functionality and apply scoping and non-administrator accounts must be enabled to use the Exchange Online Management PowerShell module.

The requirements to use the Search-UnifiedAuditLog cmdlet are obviously very different to those needed to run Purview audit log searches. The mechanisms used also differ. Search-UnifiedAuditLog are synchronous, and the results are usually available much quicker than Purview searches (unless you use the high completeness option). Both Purview searches and those run using the Graph AuditLog Query API submit background jobs to find audit records. Depending on the number of records found by a search, audit results aren’t usually available for at least 10 minutes and can take far longer.

It’s odd that Microsoft allows a situation to persist where the scoping mechanisms used by Exchange Online and Purview are unsynchronized. The likely explanation is that two different engineering teams are involved who haven’t yet figured out how to implement common scoping behavior. It seems like this is a problem that should be well within the capability of the world’s largest software company, but logic doesn’t always hold true when different teams have different priorities in large organizations.

The net outcome is that inconsistent scoping for audit log searches creates the potential for inadvertent PII disclosure in customer tenants. It also means that managing scoped access to data is more difficult than it should be. Both are unacceptable when it comes to access to audit data. Let’s hope that Microsoft fixes this issue soon.

Keep up to date with developments like those affecting scoped audit log searches by subscribing to the Office 365 for IT Pros eBook. Our monthly updates make sure that our subscribers understand the most important changes happening across Office 365.

Change in Audit Records for Teams Meeting Recordings Since 2021

Three years ago, I wrote about how to use audit records to track the creation of Teams meeting recordings. The idea was to find the audit records created when a Teams meeting recording was uploaded to OneDrive for Business or SharePoint Online.

Time marches on and old blogs rot, as do old PowerShell scripts. Three years ago, Microsoft hadn’t completed the transition from Stream classic to Stream on SharePoint. The migration finished recently and Microsoft has moved to standardize how Teams meeting recordings and transcripts are stored in OneDrive for Business. Of course, OneDrive only holds recordings for personal meetings. Recordings for channel meetings, including Meet Now in the channel, end up in the SharePoint Online site belonging to the host team.

Closing a Compliance Gap

While some might think that I spend endless hours examining audit records, this is a fallacy. I check on an as required basis, which means that I didn’t notice that my script wasn’t working quite so well because the format of the audit records changed. One important change is that the user noted in all the audit records is app@sharepoint, the ubiquitous SharePoint utility account. No trace exists in the audit records about the user who recorded the meeting, as had happened before.

From a compliance perspective, this is a big deal. Audit records exist to track the actions taken by individuals and system processes, and in this case, it seems important to know who initiated a recording.

Unfortunately, there’s nothing in the audit record to indicate who initiated the recording of a channel message, so we’re left with the SharePoint app. Recordings for personal meetings used to end up in the OneDrive account of the user who started the recording (the organizer or a presenter). Some time ago, Microsoft changed this to a more logical arrangement where recordings always go into the meeting organizer’s OneDrive account. The URL of a OneDrive account contains the site URL, like:

https://office365itpros-my.sharepoint.com/personal/jane_ryan_office365itpros_com

Figuring Out the OneDrive Site Owner

It’s easy for a human to read the URL and know that the OneDrive account belongs to Jane.Ryan@office365itpros.com. With time, I could parse the URL to extract the email address, but I went for a simpler (faster) approach. I used the Get-SPOSite cmdlet from the SharePoint Online PowerShell module to fetch the set of OneDrive accounts in the tenant and created a hash table from the site URL and site owner. It’s fast to check the hash table with the site URL taken from an audit record to return the user principal name of the site owner:

$User = $OneDriveHashTable[$AuditData.SiteURL]
If ($null -eq $User) {
   $User = "SharePoint app"
}

Changes in Search-UnifiedAuditLog Too!

Another influence on the output was the change made by Microsoft in summer 2023 to how the Search-UnifiedAuditLog cmdlet works. Microsoft have denied to me that they did anything, but the evidence shows that:

• The SessionCommand parameter must now be set to ReturnLargeSet to force the cmdlet to return more than 120 records.
• Many more duplicate records are returned than before. This necessitates sorting by the unique audit event identifier to remove the duplicates.
• Search-UnifiedAuditLog returns unsorted data. If a sorted set is important to you, make sure that you sort the audit records by creation date.

$Records = $Records | Sort-Object Identity -Unique | Sort-Object {$_.CreationDate -as [datetime]} -Descending

Of course, you can try to run high completeness searches with Search-UnifiedAuditLog, but I have not had good luck with this preview feature.

Figure 1 shows the output from the updated script, which is available from GitHub. Normal service is resumed.

A Reminder to Check Audit Log Analysis Scripts

It would be nice if a script lasted a little longer, but the ongoing change within Microsoft 365 means that PowerShell developers need to keep a wary eye on updates that might affect production scripts. In this instance, the confluence of the Stream migration and the change to the Search-UnifiedAuditLog cmdlet made a mess of a perfectly good script. I guess life is like that sometimes. Maybe now is a good time to check your scripts that use the Search-UnifiedAuditLog cmdlet.

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365.

High Completeness Audit Log Searches Improves Search-UnifiedAuditLog Results

Message Center notification MC736435 (published 13 March 2024, Microsoft 365 roadmap item 383741) describes the new HighCompleteness switch for the Search-UnifiedAuditLog cmdlet. The preview for the new switch is rolling out with a goal to making it generally available in mid-April 2024.

Despite an unwillingness to confirm that they had made changes to how the Search-UnifiedAuditLog cmdlet works, there’s no doubt that Microsoft has been active in this space. I suspect that the increasing number of Microsoft 365 workloads that generate audit events made the unified audit log infrastructure creak a little. Forcing administrators to include the SessionCommand ReturnLargeSet parameter in search commands might have been an attempt to ease pressure by outputting unsorted search results.

Making Sure Audit Log Searches are Complete

The announcement for the new high completeness feature contains the interesting statement that “Very large queries aimed at retrieving a large number of audit records are susceptible to timeouts and may miss some results.”

To overcome the problem, the HighCompleteness parameter instructs audit log searches to prioritize completeness over speed by performing a more exhaustive and comprehensive search of the audit log. Because the search is more exact, the performance of high completeness searches is slower than “normal” searches. However, given the focus on “very large queries,” the difference between normal and high completeness searches is acceptable if you’re sure that all matching audit records are found.

High Completeness Audit Log Searches Can Retrieve Lots of Data

Until now, the Search-UnifiedAuditLog cmdlet has been limited to returning a maximum of 50,000 records. This sounds a lot, but it’s not for large tenants where tens of thousands of users take actions that result in audit records. To fetch 50,000 records, the cmdlet must include SessionCommand ReturnLargeSet in its parameters.

To test what a high completeness audit log search can do, I ran this command:

[array]$Data = Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-100) -EndDate (Get-Date).AddDays(1) -HighCompleteness -formatted -verbose

You don’t need to pass the SessionCommand parameter for high completeness audit log searches. The ResultSize parameter is supported to limit the number of audit records returned by a search. Figure 1 shows that the search returned 119,507 records in just under 17 minutes.

The results are unsorted, so to sort the records into date order, I ran:

$Data = $Data | Sort {$_.CreationDate -as [datetime]}

It seems like Microsoft limits the number of high completeness searches that an administrator can run. After running five over 20 minutes or so, my next attempt resulted in:

WARNING: Failed to process request via HighCompleteness flag, returning HttpRequestException. Exception: TooManyRequests , Reason: Too many requests. Please try after some time..

Waiting ten minutes to resubmit the search resolved the issue. During my tests, I also experienced a few 500 ‘internal server errors’ when running high completeness searches. Occasionally, a search failed with an error like:

WARNING: Failed to process request via HighCompleteness flag, returning HttpRequestException. Exception: Status: OK , Reason: The search request did not finish in time via HighCompleteness flag, returning. Execution time(seconds) :782.

These are examples of errors that are expected during previews of new functionality and I’m sure that Microsoft will resolve the underlying problems (and make the error messages more meaningful) before general availability.

Comparing Normal and High Completeness Audit Log Searches

To compare the time required to run normal and high completeness searches, I ran a test to retrieve all audit records for a user. The normal search took 11.5 seconds:

[array]$Data3 = Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-180) -EndDate (Get-Date).AddDays(1)  -formatted -UserIds Sean.Landy@office365itpros.com -ResultSize 5000 -SessionCommand ReturnLargeSet

The high completeness search took 4 minutes 22 seconds:

[array]$Data2 = Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-180) -EndDate (Get-Date).AddDays(1) -HighCompleteness -formatted -UserIds Sean.Landy@office365itpros.com

The normal search returned 950 records; the high completeness search returned 704. After sorting both sets by the creation date, the two sets had the same first and last record in the set. It seems that the difference is accounted for by duplicate records included in the “normal” set.

For instance, a MailItemsAccessed event appeared three times in the “normal” set. To check the theory, I created an array and used a ForEach-Object loop to populate properties in the array from the audit records, including the Id property in the AuditData multi-value property. I then sorted the array to find unique values of Id and ended up with 704 records, the same as returned by the high completeness search. Here’s the code I used:

$OutputReport = [System.Collections.Generic.List[Object]]::new()
ForEach ($Rec in $Data3) {
    $AuditData = $Rec.AuditData | ConvertFrom-JSON
    $ReportLine = [PSCustomObject][Ordered]@{
         UserPrincipalName   = $Rec.UserIds
         Timestamp           = $Rec.CreationDate
         Operation           = $Rec.Operations
         Id                  = $AuditData.Id
     } 
     $OutputReport.Add($ReportLine)      
}
$OutputReport.count
950
$O = $OutputReport | Sort-Object Id -Unique
$O.count
704

A New Way to Run Large Audit Log Searches

Administrators run audit log searches to extract information about many different types of activity. When they do, administrators expect Purview to respond with accurate and complete results. It seems that this hasn’t been the case in the past and that the likelihood of missing records grows as the number of audit records found by a query increases. That’s not good and I was surprised to find so many duplicates.

Based on what I see so far, high completeness searches do a good job of finding large quantities of audit records reasonably quickly. Being sure that 120,000 records are accurate and represent the total available set is a different matter. Checking the data fetched by more precise queries indicate that high completeness searches generate accurate results. This preview feature is worth investigating.

Stay updated with developments across the Microsoft 365 ecosystem by subscribing to the Office 365 for IT Pros eBook. We do the research to make sure that our readers understand the technology.

Future Focused on Unified Search Log

A January 26 post in the Microsoft Technical Community announced that Microsoft intends to retire the old cmdlets that report Exchange mailbox and administrative audit events on April 30, 2024. The cmdlets involved are Search-AdminAuditLog, Search-MailboxAuditLog, New-AdminAuditLogSearch, and New-MailboxAuditLogSearch. Microsoft says that the replacement is the Search-UnifiedAuditLog cmdlet.

Microsoft’s assertion is correct. Unlike their plan to retire the Search-Mailbox cmdlet at the end of March 2024, I think it is a good idea to deprecate the four search cmdlets because they only confuse the Microsoft 365 audit search landscape. The cmdlets appeared in Exchange 2010 as part of the introduction of audit functionality for Exchange Server. Today, the audit events gathered by Exchange Online flow into the unified audit log and there’s no need to interrogate the copies of the audit events retained in user mailboxes. The unified audit log is what is searched using the Audit Log feature in the Purview compliance portal (Figure 1).

It might be the case that some old scripts exist that depend on finding mailbox or admin audit events in Exchange, but it’s relatively easy to convert those scripts to use Search-UnifiedAuditLog.

Until the Search-UnifiedAuditLog Cmdlet Changes Without Warning

At least, it would be if Microsoft didn’t change how the Search-UnifiedAuditLog cmdlet works without warning, which is what they did in late summer 2023. Unannounced and unexplained change allied to slow delivery of commitments to make some important audit events available to Office 365 E3 tenants have shaken my confidence in Search-UnifiedAuditLog recently,

Anything to do with auditing needs to be consistent and precise. As seen with unannounced change, consistency is not something that I associate with the Search-UnifiedAuditLog cmdlet. Precision is often poor too. The group that manages the flow of audit events into the unified audit log insists on consistency for the base properties, such as the timestamp, name of the operation, the user responsible for an action, and so on. Things become far murkier when it comes to the AuditData property, which holds information deemed necessary by a workload to communicate details of an action.

The Mysteries of AuditData

AuditData is a JSON-formatted structure. There’s nothing wrong with that. My objections focus on the arbitrary inclusion of information in the structure. As an example, reporting details of license assignments to Entra ID user accounts is challenging. Entra ID generates audit events, but the content of AuditData is often obscure and defies interpretation. With over 1,600 different audit events flowing into the unified audit log, insisting on coherence and clarity in all events must be like cleaning the mythical Augean stables. But without full and precise information in audit events, the unified audit log loses credibility and becomes less valuable than it could be.

I should say that I regard the unified audit log as an extraordinarily valuable source of information about what actually happens within a Microsoft 365 tenant. All tenant administrators should know how to interrogate the audit log and understand (at least roughly) what the audit events returned by a search mean. Skilled tenant administrators go deeper and use the audit log as a source of understanding for how Microsoft 365 workloads work. Not everyone has the time to master the audit log at this depth, but it’s certainly a good goal to work toward.

Remove Decrepit Cmdlets But Fix Search-UnifiedAuditLog

I have zero problem with Microsoft removing old and decrepit cmdlets from the Exchange Online management module. It’s the right thing to do. I just wish that Microsoft would fix the problems in the Search-UnifiedAuditLog cmdlet before they did anything else. Everyone who works with Microsoft 365 audit data would benefit and it would establish a solid foundation for the future. Which would be nice.

Support the work of the Office 365 for IT Pros team by subscribing to the Office 365 for IT Pros eBook. Your support pays for the time we need to track, analyze, and document the changing world of Microsoft 365 and Office 365.

Entra ID Administrative Units and Compliance Roles Limit User Access to Microsoft 365 Audit Log Data

Entra ID administrative units are a premium feature that allows organizations to create partitions within the directory (analogous to organizational units in Active Directory). Microsoft Purview compliance solutions have recently added support for administrative units to restrict access of administrators to compliance activities for certain accounts based on membership of selected administrative units.

Although administrative units can include user accounts, groups, and devices, from a compliance perspective, Exchange Online is only interested in user accounts with mailboxes. Many compliance activities such as data lifecycle management (retention processing) target mailboxes, so it makes sense that Exchange Online should respect the boundaries imposed by sets of accounts listed in administrative units when it’s asked to provide information.

To retrieve details of the user and group objects that are members of administrative units, run the script described in this article.

Exchange Online Cmdlet to Work with Administrative Units

The Exchange Online management module contains the Get-AdministrativeUnit cmdlet. The cmdlets retrieves information about Entra ID administrative units stored in the Exchange Online directory. Exchange Online doesn’t synchronize updates about administrative units and membership immediately and it can take up to ten minutes for synchronization (new administrative units, changes to membership, etc.) to happen. After synchronization, run Get-AdministrativeUnit to view the details of administrative units as known to Exchange Online:

Get-AdministrativeUnit

Name                                   DisplayName
----                                   -----------
0555a6cd-f1eb-4843-ba62-a362db719704   Project Management (Sales) dynamic administrative unit
0ee53a45-bbee-4571-a407-56acc0b944a1   Ireland
112f5e71-b430-4c83-945b-8b665c14ff25   Global HQ dynamic administrative unit
150dccad-f8b8-4e54-9246-89834b8b5a25   Group HQ Users

This output is similar to that of the Get-MgDirectoryAdministrativeUnit from the Microsoft Graph PowerShell SDK:

Get-MgDirectoryAdministrativeUnit | Format-Table Id, DisplayName

There’s no equivalent of the Get-MgBetaAdministrativeUnitMember cmdlet to return a set of members for an administrative unit, but this can be done by running the Get-Recipient cmdlet to resolve a recipient filter based on the distinguished name of the administrative unit object (the copy in the Exchange Online directory rather than Entra ID). For example:

$AUName = (Get-AdministrativeUnit -Identity 'Information Technology dynamic administrative unit').distinguishedName
[array]$AUMembers = Get-Recipient -RecipientPreviewFilter "AdministrativeUnits -eq '$AUName'"
$AUMembers | Format-Table DisplayName, PrimarySmtpAddress, ExternalDirectoryObjectId

DisplayName                      PrimarySmtpAddress                   ExternalDirectoryObjectId
-----------                      ------------------                   -------------------------
Ben Owens (DCPG)               Ben.Owens@office365itpros.com        a3eeaea5-409f-4b89-b039-1bb68276e97d
Andy Ruth (Project Director)   Andy.Ruth@office365itpros.com        fdc6b121-44b8-4262-9ca7-3603a16caa3e

Using a recipient filter might seem convoluted, but it’s no more complicated than the steps required to retrieve membership information for an administrative unit with the Graph SDK:

$AUMembers = Get-MgBetaAdministrativeUnitMember -AdministrativeUnitId (Get-MgDirectoryAdministrativeUnit -Filter "displayName eq 'Information Technology dynamic administrative unit'" ).Id -All
[array]$MemberUsers = $AuMembers.additionalProperties | Where-Object {$_.'@odata.type' -eq "#microsoft.graph.user"}
$Memberusers | ForEach-Object { Write-Host $_.displayName “ “ $_.mail}

Some of this is due to the way that Graph requests return information, and part is due to some SDK foibles. It’s especially annoying in this case that the property names are case sensitive. “displayName” works but “DisplayName” does not.

Connecting Exchange Online and Purview Audit

The Audit Log search feature in the Microsoft Purview compliance portal supports administrative units. In other words, you can assign the Audit Reader compliance role to users to allow them to manage one or more administrative units. Purview Audit searches will limit the records retrieved from the Microsoft 365 audit log to those generated by members of the selected administrative units.

Figure 1 shows the parameters for a new search, which is limited to the United States administrative unit. Administrators can only choose administrative units that they have access to or search for audit events across the entire organization (the previous default).

When the search completes, the audit events are for actions performed by members of the selected administrative unit. The search works by looking at the AssociatedAdminUnits property in audit events. The property stores the identifier of the administrative units a user account is a member of (accounts can belong to multiple administrative units) and is updated for events when the Microsoft 365 audit log ingests data from workloads. You can see the administrative unit information in the details of audit events found by Audit search (Figure 2).

Very importantly, changes made to administrative unit membership do not replicate to previous audit events. For instance, if a user joins the United States administrative unit, none of the audit events captured for previous actions are available to administrators limited to searching for audit events associated with the United States administrative unit.

Limiting Access for the Search-UnifiedAuditLog Cmdlet to the Microsoft 365 Audit Log

The Search-UnifiedAuditLog cmdlet is part of the Exchange Online management PowerShell module. Its purpose is to run audit log searches, but currently the cmdlet does not respect administrative unit restrictions imposed by compliance center roles. This might change in the future.

The workaround is to use Role-Based Access Control (RBAC) to limit the data the cmdlet can process, specifically by creating management role assignments. For instance, to assign the Audit Logs management role to a user, run the New-ManagementRoleAssignment cmdlet and specify the user (alias, display name, external directory object id, or primary SMTP address) and the identifier for the target administrative unit.

New-ManagementRoleAssignment -User Ken.Bowers@office365itpros.com -Role "Audit Logs" -RecipientAdministrativeUnitScope "4d3ae8ee-212b-4be4-965c-8b5111d4488e"

Searches run with the Search-UnifiedAuditLog cmdlet by the user holding the role will now respect the administrative unit limit based on the AssociatedAdminUnits property in audit events. For instance, here’s an extract of an audit record generated for an account that belongs to three administrative units.

],
                 "AssociatedAdminUnits": [
                   "8a703400-7086-4e13-943a-7ed8df9ecd41",
                   "4d3ae8ee-212b-4be4-965c-8b5111d4488e",
                   "150dccad-f8b8-4e54-9246-89834b8b5a25"
                 ]

To resolve the identifiers and check that the correct administrative units are used, extract the data to an array and run the Get-AdministrativeUnit cmdlet against each identifier:

$AdminUnits = ($Records[0].AuditData | Convertfrom-Json).AssociatedAdminUnits
ForEach ($AU in $AdminUnits) { (Get-AdministrativeUnit -Identity $AU).DisplayName }

Group HQ dynamic administrative unit
United States
Group HQ Users

Summarizing Support for Administrative Units in Purview Audit

To summarize, organizations can use administrative units to limit user access to Microsoft 365 audit log data. The limitation depends on the compliance role assigned to the account and the data stamped into audit log records. An Exchange management role assignment is required to apply the same limitation to searches run using the Search-UnifiedAuditLog cmdlet.

FileRecycled Audit Event Replaces FileDeleted

In December 2021, I wrote about using events captured in the unified audit log to analyze file deletion activity in SharePoint Online and OneDrive for Business. Recently, some readers complained that the script (available from GitHub) wasn’t finding events for file deletions. A major advantage of PowerShell is that you see all the code and can modify the code to meet your needs. This also means that you can debug the code. My usual response to people who report problems with scripts is to prompt them to do some basic debugging by running the code line-by-line until the problem becomes apparent. Apart from learning how the script works, debugging is a great way to improve PowerShell skills.

In any case, a quick check revealed the problem. Microsoft changed the name of the operation captured in file deletion audit events from FileDeleted to FileRecycled. The change seems to have come into force in March 2023. At least, that’s the date of the first FileRecycled audit event generated by SharePoint Online I can find in my tenant. Microsoft didn’t say anything about the change. It just happened without warning.

File Deletion or Recycling

A case can be argued that FileRecycled is a more accurate description of the action than FileDeleted is (see this documentation update request from August 2022). SharePoint Online doesn’t actually delete an item until it goes through the two-stage recycle bin and exceeds the 93-day retention period that items remain in the recycle bin. The initial action is to move an item from a document library to the site recycle bin, hence the justification to use the FileRecycled name in audit records.

I wouldn’t have a problem if Microsoft told people about the change. Not everyone scans the documentation to detect name changes for audit log activities. Unless you checked the data returned by the Search-UnifiedAuditLog cmdlet or noticed the details for file deletions (or rather “recycled file”) operations returned by the audit log search in the Purview compliance portal (Figure 1), the change would probably have escaped undetected.

Microsoft also changed the UI of the audit search solution so that if you select “Deleted file” from the list of activities to search for, you’ll find events logged when SharePoint Online removes files from the recycle bin.

The Impact of Unannounced Changes

The problem here is that when Microsoft makes unannounced changes to audit data, it potentially affects scripts written by organizations to move data from the audit log to an external repository like Splunk. Among the reasons why organizations populate external repositories with audit data are:

• Long-term retention of audit data. Until recently, Microsoft only kept audit data for 90 days. On July 19, 2023, Microsoft announced a doubling of the audit data retention period to 180 days for Audit standard (Office 365 E3) customers. Audit premium customers have a 365-day audit data retention period with an optional add-on license available to increase the period to 10 years.
• Better search and investigation facilities. Although organizations have built tools to interrogate the unified audit log, the fact remains that the contents of audit log entries often need processing to extract useful information (like this example of extracting information about changes made to Entra ID account properties).

Obviously, if a new name is introduced for a common auditable activity like file deletion, it’s likely that processes to export audit data will ignore these events. I haven’t found any other activity renames but suspect that some might be lurking in the audit log.

Updates without Warning Reduce Confidence

The bottom line is that reliable audit data is an important part of a compliance ecosystem. If audit data is missing or becomes difficult to interrogate, those who work with audit data lose a little faith because it isn’t as comprehensive and accurate as they expect. And that’s a great pity.

Use PowerShell to Monitor Group Membership Changes

A reader asks how to monitor specific Azure AD groups so that they are notified if anyone updates the membership of these groups. Because of the pervasive use of Teams in Microsoft 365 tenants and the range of resources available to team members, it’s quite common to find that organizations want to keep an eye on some sensitive groups, like those used by senior management or for confidential purposes, like merger and acquisition activities. Various commercial products include this functionality but it’s always fun to see if you can create a solution from the out-of-the-box components available to tenants.

I’ve been down a similar road in the past, such as reporting the deletions of Microsoft 365 groups, but this request is a little different because it involves marking groups to be monitored. My initial thought was if this could be a scenario to use Azure AD custom security attributes? Administrators can define attribute sets and assign attributes to Azure AD objects. Unfortunately, Azure AD currently only supports custom security attributes for user objects, managed identities, and service principals (for apps). Azure AD custom security attributes are still in preview and the range of supported object types is likely to change before general availability but doesn’t help in this situation.

Exchange Online Custom Attributes for Groups

Exchange Online mail-enabled objects, like groups, have fifteen custom attributes capable of storing single values and five multi-value custom attributes. It should be easy to assign a custom attribute to mark groups for monitoring. In this example, I’m checking for membership changes to marked Microsoft 365 groups.

The first thing is to update the targeted Microsoft 365 groups with the flag. This is easily done with the Set-UnifiedGroup cmdlet (neither the Exchange admin center nor the Microsoft 365 admin center support access to the custom attributes):

Set-UnifiedGroup -Identity "Contract Workers" -CustomAttribute15 "Monitor"

After marking the groups, it’s possible to find the groups with a server-side filter. This is important because we don’t want to have to retrieve every group and then check its properties:

[array]$Groups = Get-UnifiedGroup -Filter {CustomAttribute15 -eq "Monitor"}

Writing the Code

The code to report membership changes to monitored groups has the following steps:

• Find the set of monitored Microsoft 365 Groups.
• Build a hash table of the group object identifiers and display names. For maximum performance, the script uses the hash table to check if an audit record relates to a monitored group.
• Define a variable holding the set of audit events to look for.
• Define the start and end date for the search. In this example, we look back 30 days.
• Run the Search-UnifiedAuditLog cmdlet to perform the search.
• Examine each event returned by the search to check if it’s related to a monitored group. If it is, capture information about the event.

Here’s the code to monitor group membership changes:

Connect-ExchangeOnline
Write-Host "Finding groups to monitor..."
[array]$Groups = Get-UnifiedGroup -Filter {CustomAttribute15 -eq "Monitor"}
If (!($Groups)) {Write-Host "No groups found to monitor - exiting" ; break }

$GroupIds = @{}
ForEach ($G in $Groups) {
   $GroupIds.Add($G.ExternalDirectoryObjectId,$G.DisplayName) }

$Operations = 'Add member to group', "Remove member from group"
$StartDate = (Get-Date).AddDays(-30); $EndDate = (Get-Date) 

Write-Host "Finding audit records for group member adds and deletes..."
[array]$Records = (Search-UnifiedAuditLog -Operations $Operations -StartDate $StartDate -EndDate $EndDate -ResultSize 1000 -Formatted)
If (!($Records)) { Write-Host "No audit records found for add or delete members from monitored groups - exiting" ; break }

$Report = [System.Collections.Generic.List[Object]]::new() # Create output file 

ForEach ($Rec in $Records) {
   $AuditData = ConvertFrom-Json $Rec.Auditdata
   Switch ($AuditData.Operation) {
    "Remove member from group." {
       $GroupId = $AuditData.ModifiedProperties | Where-Object {$_.Name -eq 'Group.ObjectId'} | Select-Object -ExpandProperty OldValue }
    "Add member to group." {
       $GroupId = $AuditData.ModifiedProperties | Where-Object {$_.Name -eq 'Group.ObjectId'} | Select-Object -ExpandProperty NewValue }
   }
   $GroupName = $GroupIds[$GroupId]
   If ($GroupName -ne $Null) { # Update is for one of the monitored groups
      
      $ReportLine = [PSCustomObject] @{
        TimeStamp = Get-Date($AuditData.CreationTime) -format g
        User      = $AuditData.UserId
        Group     = $GroupName 
        Member    = $AuditData.ObjectId
        Action    = $AuditData.Operation }        
      $Report.Add($ReportLine) 
  } #End if  

} # End ForEach audit record

$Report | Out-GridView

Figure 1 shows the result of the search and analysis of the audit records to find events relating to membership changes in the monitored groups.

Monitor Group Membership Changes Automatically

This is an excellent example of the kind of periodic check that’s suitable for execution by an Azure Automation runbook with the results delivered by email or posted to a Teams channel for action by whoever’s responsible for monitoring the membership of the groups.

The point is that the audit log holds a lot of useful information that can answer questions about Microsoft 365 operations. All you need to do is take advantage of the available data.

Learn how to exploit the data available to Microsoft 365 tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work, including how to monitor group membership changes without paying for another product.

Microsoft DART Likes the Office 365 Audit Log (Unified Audit Log)

On September 26, Microsoft’s Detection and Response Team (DART, aka the cybersecurity team you never want to meet) published an article on the Security, Compliance, and Identity blog called Forensic artifacts in Office 365 and where to find them. It’s a nice article that captures the standard and optional (requiring extra configuration or licenses) flows of data captured for Azure AD sign-in events, Azure AD admin events, and Office 365 activities (Figure 1). This data is of great help to forensic investigators as they attempt to understand what happened in cybersecurity incidents.

Regular readers of my written work or those who’ve heard me speak at conferences are well aware of my high regard for the Office 365 audit log. It’s a fantastic source of information about what happens inside a tenant and I believe that every administrator should be familiar with how to retrieve information from the audit log. I keep on coming back to the audit log to see what data shows up there for new features like reactions to Teams messages. It’s also my go-to place to find in-depth information about actions that might affect the tenant, like potential illicit consents.

Moving Office 365 Data to a SIEM

The downside of the Office 365 audit log is that data doesn’t stay there for very long. Even tenants with the Office 365 E5 plan have access to only 365 days of information. For Office 365 E3, the audit log only stores 90 days of information. In addition, Office 365 E3 tenants must enable auditing for every mailbox to make sure that Exchange Online sends mailbox events to the audit log. That’s a gap which I hope Microsoft closes soon.

A SIEM is a good place to hold audit data for longer periods. As Microsoft notes, it’s possible to ingest Office 365 audit data into Microsoft Sentinel. Not all data flows through as the connector used to transfer data to Sentinel is particular about the events it chooses, so that’s something to watch. Nevertheless, Sentinel is a good place to keep audit data if you use Azure.

As to other SIEM platforms, many PowerShell examples exist demonstrating how to use the Search-UnifiedAuditLog cmdlet to fetch audit events  (here’s an example). Once you have the data, it’s not hard to send them to a repository. Some SEIMs have more sophisticated ingestion mechanisms. For example, Splunk uses a registered Azure AD app to fetch audit data and bring it to its repository.

Poor Search Performance

I use PowerShell whenever I need to search the audit log. I have never liked the GUI Microsoft built for audit log search. It has always been unwieldly and inflexible, not to mention slow. Its sole benefit is that the GUI exposes some of the important information stuffed in the AuditData payload in audit records.

I was therefore taken aback by the enthusiasm shown about the new Audit search GUI (preview). According to Microsoft, the new GUI offers the following improvements:

• Search jobs initiated via the compliance portal UI no longer require the web browser window to remain open in order to complete. These jobs will continue to run even after the browser window is closed.
• Completed search jobs are now stored, giving customers the ability to reference historical audit searches. These search jobs are presented in the UI, listing the search name, search job status, progress %, Number of results, Creation Time, and Searched by.
• Each admin Audit account user can have a maximum of 10 search jobs in progress at a time.

I tried the new GUI with a very simple search looking for events for a single operation (someone changes a sensitivity label for a document) for the last 27 days. This search took less than three seconds by running the Search-UnifiedAuditLog cmdlet in PowerShell, but required 9 minutes and 24 seconds through the new search (Figure 2). This kind of performance is not usual in my experience.

Here’s the equivalent search in PowerShell:

Measure-Command { [array]$Records = Search-UnifiedAuditlog -Operations ComplianceSettingChanged -StartDate 1-Sep-2022 -EndDate 27-sep-2022 -Formatted -ResultSize 5000 }


Days              : 0
Hours             : 0
Minutes           : 0
Seconds           : 2
Milliseconds      : 470
Ticks             : 24701589
TotalDays         : 2.85898020833333E-05
TotalHours        : 0.00068615525
TotalMinutes      : 0.041169315
TotalSeconds      : 2.4701589
TotalMilliseconds : 2470.1589

Some overhead is expected to create and process search jobs in the background but this degradation in performance is extraordinary when you consider that both methods interrogate the same data source. You have to do more work with PowerShell when an audit search returns events, but anyone who’s worked with the event log will have that process well defined and understood, so the advantage of being able to view event details through the GUI (Figure 3) is probably only appreciated by those unfamiliar with the audit log.

Slow Interfaces are Unusable

The new audit search GUI reminds me about redesign of the content search interface, which also disappointed with its slowness and buggy nature. Microsoft introduced that redesign in May 2021 and it’s still slow. I hope that they manage to do a better job as they bring the new audit search from preview to general availability. If not, I doubt I would ever use the new audit search interface.

Microsoft DART considers that the new interface makes “large-scale data collection much simpler and more reliable.” However, that statement is softened considerably by saying that their “go-to approach here is to use PowerShell to extract the data we need.” I couldn’t agree more.

Learn more about how Microsoft 365 really works on an ongoing basis by subscribing to the Office 365 for IT Pros eBook. Our monthly updates keep subscribers informed about what’s important across the Office 365 ecosystem.

If You Have Office 365 E3 Licenses, Check the Flow of Mailbox Audit Events

A reader contacted me to report some problems that a compliance exercise had thrown up when it was discovered that the Office 365 audit log did not contain mailbox audit records for some Exchange Online mailboxes. As it turned out, all the affected mailboxes belonged to Azure AD accounts with Office 365 E3 licenses. Not having some expected mailbox audit events available in the audit log is not a great situation for any compliance officer or tenant administrator to find themselves in.

When the customer organization contacted Microsoft support, they were told that a bug existed in Exchange Online that stops the AuditEnabled setting being accurately reported for mailboxes with Office 365 E3 licenses. When administrators examined the setting with the Get-Mailbox or Get-ExoMailbox cmdlets, Exchange Online reported $True, meaning that auditing is enabled for the mailbox:

Get-ExoMailbox -Identity Brian.Weakliam -Properties AuditEnabled | Format-Table DisplayName, AuditEnabled

DisplayName                 AuditEnabled
-----------                 ------------
Brian Weakliam (Operations)         True

However, the mailbox audit events remain in Exchange Online and never make it into the Office 365 audit log. Everything works perfectly for mailboxes assigned Office 365 E5 licenses.

What Microsoft Says

Microsoft’s documentation says:

Although mailbox audit logging on by default is enabled for all organizations, only users with E5 licenses will return mailbox audit log events in audit log searches in the Microsoft Purview compliance portal (Figure 1) or via the Office 365 Management Activity API by default.

The page also says:

Even when mailbox auditing on by default is turned on for your organization, you might notice that mailbox audit events for some users aren’t found in audit log searches by using the compliance center, the Search-UnifiedAuditLog cmdlet, or the Office 365 Management Activity API. The reason for this is that mailbox audit events will be returned only for users with E5 licenses when you [use] one of the previous methods to search the unified audit log.

Apparently, Microsoft doesn’t consider it a bug when audit data from mailboxes with Office 365 E3 licenses doesn’t reach the Office 365 audit log. The justification is that:

• The audit events are available in Exchange Online (in the Audits folder in the Recoverable Items part of each mailbox) and can be found there.
• If the organization wishes to ingest the audit events into the Office 365 audit log, all they need to do is run the Set-Mailbox cmdlet to update the AuditEnabled setting to $True. In other words, the $True value reported by Exchange Online is accurate because it reports that mailbox events are available. Running Set-Mailbox to update the setting to $True (again) doesn’t change anything (the setting is still $True) but Exchange Online takes the hint and will ingest mailbox events into the Office 365 audit log thereafter.

Clearly, this is a non-optimal situation. I wrote about the same issue in March 2020 when Microsoft rolled out mailbox auditing by default across Exchange Online. It seems like the same documentation is online and people are still running into problems. And that this Exchange Online functionality hasn’t progressed much since.

Audit Events Appear to Flow for New Mailboxes

Or has it? I created a couple of new Azure AD accounts and assigned them Office 365 E3 licenses and then signed into each mailbox to perform several actions that I knew should generate audit records. After waiting 30 minutes to allow ingestion to occur, I ran the Search-UnifiedAuditLog cmdlet to see if any events existed in the Office 365 audit log. They were!

[array]$records = Search-UnifiedAuditLog -StartDate 18-Aug-2022 -EndDate 19-Aug-2022 -Formatted -userids michael.king@office365itpros.com
$records | ft creationdate, operations
 
CreationDate        Operations
------------        ----------
18/08/2022 12:54:31 SoftDelete
18/08/2022 12:54:23 MoveToDeletedItems
18/08/2022 12:54:19 SoftDelete
18/08/2022 12:53:19 SoftDelete
18/08/2022 12:53:14 MoveToDeletedItems

This result is exactly what you’d hope to see. No administrator intervention was necessary to have events flow from Exchange Online to the Office 365 audit log. I obtained the same results with mailboxes in another tenant. I asked Microsoft about the issue and learned that mailbox auditing was enabled by default because of the configuration of the Exchange Online forest that hosts the mailboxes. For performance reasons (to reduce the number of audit events processed by Office 365), this is not the situation for every Exchange Online forest. This is why Microsoft’s documentation recommends that you should re-enable auditing for the E3 mailboxes to force ingestion of audit events for these mailboxes into the Office 365 audit log.

The conclusion is there might be many mailboxes with Office 365 E3 licenses running in tenants around the world that don’t feed data into the Office 365 audit log. The only solution is to run Set-Mailbox. And you might as well run the cmdlet for all mailboxes with Office 365 E3 licenses just to be sure.

The script included in my previous post will do the job of finding mailboxes with Office 365 E3 licenses and updating their audit settings. The script uses the Get-AzureADUser cmdlet to find the relevant accounts. If you’ve switched to the Microsoft Graph PowerShell SDK, the updated code is:

[array]$Mbx = Get-MgUser -filter "assignedLicenses/any(s:s/skuId eq $Office365E3)" -All

Good for the Future?

If you use Office 365 E3 licenses, be sure to check that mailbox auditing works as you expect. It’s odd that Microsoft forces customers to go through the additional step to enable auditing for mailboxes that appear to be already enabled for auditing, but that’s the way the system works.

Make sure that you’re not surprised about changes that appear inside Office 365 applications by subscribing to the Office 365 for IT Pros eBook. Our monthly updates make sure that our subscribers stay informed.

Let Administrators Know When Team Archive and Restore Events Happen

Earlier this week, I discussed the topic of archiving teams at the end of their lifecycle and the challenges which result if the archived teams include shared or private channels. The inevitable question which arises is how administrators can know when team owners (or administrators) archive teams. It’s a reasonable question because if administrators know what’s happening within a tenant, they can take proactive steps to head off potential issues when necessary.

Look Into the Audit Log

My normal go-to place for information about Microsoft 365 workloads is to look in the “unified” audit log to see if Teams captures audit records when someone archives or restores a team.

Teams captures audit records for archive and restore events, but it doesn’t create dedicated events for these operations. Instead, Teams captures events using the generic TeamSettingChanged operation, which is used for other actions such as updating the name of a team. Given the fundamental nature of archiving a team, it’s surprising that Teams doesn’t consider it important enough to create audit events using specific events for the two operations, like TeamArchive or TeamRestore.

Searching the audit log for TeamSettingChanged operations is simple. After finding the audit records, the next step is to extract the JSON payload from the AuditData property and then process the records for archive operations. To differentiate between team archive and restore actions, the audit events store values in the NewValue property. If it’s True, the audit record is for an archive action. If it’s False, the action restores a team. The code handles these conditions.

[array]$Records = (Search-UnifiedAuditLog -Operations TeamSettingChanged -StartDate $StartDate -EndDate $EndDate -ResultSize 5000)
ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      If ($AuditData.Name -eq "Team is archived") {  # It's an archival team setting record
        Switch ($AuditData.NewValue) {
         "False"  { $Action = "Restored team" }
         "True" { $Action = "Archived team" }
        } #end switch

Retrieve Channel Information

As described in the article, it’s important to know if an archived team includes private or shared channels, so we need some code to retrieve this information. The display name of the archived or restored team is in the audit record, so the Get-Team cmdlet can retrieve the team’s group identifier and Get-TeamChannel can then retrieve the channel data. For the purpose of the script, I am only interested in the count of the different types of channel, but it would be easy to output the channel names if required.

Write-Host "Checking channels for" $AuditData.TeamName
         $TeamId = (Get-Team -DisplayName $AuditData.TeamName).GroupId
         If ($TeamId) {
            [array]$TeamChannels = Get-TeamChannel -GroupId $TeamId
            [array]$StandardChannels = $TeamChannels | ? {$_.Membershiptype -eq "Standard"}
            [array]$SharedChannels = $TeamChannels | ? {$_.Membershiptype -eq "Shared"}
            [array]$PrivateChannels = $TeamChannels | ? {$_.Membershiptype -eq "Private"}
            $ChannelStatus = "OK" }
         Else {
            $ChannelStatus = "Team might be deleted" }

Output the Audit Data

Afterwards, it’s a matter of reporting the results of scanning the audit records and outputting the data. The script creates a CSV file and pipes the data to the Out-GridView cmdlet (Figure 1) to make it easy to browse the audit data.

The example code for the script I used to create the report is available from GitHub.

If you want to distribute the report in other ways, you could:

• Format the content in HTML and send it via email (see this article for details).
• Create the report in a SharePoint document library (the basics of how to do this is explained here; the scenario is a script running in a Azure Automation runbook but the technique of using PnP cmdlets is the same in “regular” PowerShell).
• Post the report to a Teams channel or post a link to it in a message card created in a Teams channel using the inbound webhook connector. See this article for more information.

Possible Improvements

It would be easier if Teams generated specific audit records whenever a team archive or restore action occurs, but at least the data is available when you go looking for it. Another improvement Microsoft could make is to include the group identifier in the audit record as this is needed to retrieve information about the team using cmdlets from the Teams or Microsoft Graph PowerShell SDK modules, or indeed with a Graph query. I’m not holding my breath that any changes will happen though!

Learn more about how the Office 365 applications really work on an ongoing basis by subscribing to the Office 365 for IT Pros eBook. Our monthly updates keep subscribers informed about what’s important across the Office 365 ecosystem.

Who Deleted that Document?

Many searches of the Microsoft 365 audit log are attempts to answer questions. A good example is to search the log to discover email deletion audit events to answer a question like “who deleted a specific message in a shared mailbox?”

The equivalent question for SharePoint Online and OneDrive for Business is “who deleted that document?” You might not care too much about deletions in OneDrive for Business accounts because these are personal storage under the control of individual users; the same might not be true for SharePoint Online deletions, especially in sites used by Microsoft Teams and Outlook Groups where the simplicity of the Microsoft 365 Groups membership model mandates that all members have equal access to group data. In a nutshell, any team or group member can delete any file in the site.

SharePoint’s Deletion Process

SharePoint routes deleted items through a two-stage recycle bin process. All group members can recover items from the first stage recycle bin. Items remain in the first stage recycle bin for 30 days and then move to the second stage. Only group administrators (owners) can recover items after they reach the second stage recycle bin, where they remain for another 63 days. Ninety-three days after their original deletion, SharePoint Online automatically removes the items permanently and they become irretrievable. That is, unless SharePoint Online is forced to retain files because they have a retention label or come within the scope of a retention policy. In that case, SharePoint Online keeps a copy of the file in the site preservation hold library until the retention period lapses (users being able to delete files with retention labels is a recent change).

The audit records captured for item deletions in the Microsoft 365 audit log are often the result of user activity. In other words, someone selects a document in a folder and deletes it. SharePoint Online (including OneDrive for Business) captures a FileDeleted event when this happens. However, other processes can remove items, including:

• A retention policy applying to the site removes items after a set period.
• An administrator deletes a user account, and the SharePoint system account removes items from the user’s OneDrive for Business account.

Users can access the site recycle bin and remove items from it. Often this is an innocent activity, but it can also be evidence that someone wants to remove an item that they don’t want to be found. When someone removes an item from the first stage recycle bin, SharePoint Online captures a FileDeletedFirstStageRecycleBin audit event. Retention policies can also remove items form the recycle bin. Only site administrators can access items in the second stage recycle bin (Figure 1). If they remove items from the second stage recycle bin, SharePoint Online captures a FileDeletedSecondStageRecycleBin audit event.

In summary, SharePoint Online generates audit events as items move through site recycle bins to permanent deletion. Deletions are often user-initiated but can be the result of system processes. The Microsoft 365 audit log ingests the audit records approximately 15 minutes of them happening, and once the records are in the log, we can search for and report the events using the Search-UnifiedAuditLog cmdlet.

Of course, you can also look for these events using the audit log search feature in the Microsoft 365 compliance center but given the volume of audit events to deal with and the need to analyze information to make sense of what happened, it’s usually better to use PowerShell.

Searching for SharePoint Online Deletion Events

To illustrate the process, I created a script that you can download from GitHub. The script is very simple:

• Set up search parameters (I used a 90-day search period, you can adjust as necessary).
• Search for the three deletion events.
• Examine the AuditData payload in each record and extract relevant information.
• Sort the results by operation and date and export to a CSV file. I also output the results using the Out-GridView cmdlet (Figure 2) to make it convenient to see what’s found.

As the code is PowerShell, you can change it to meet your needs.

If you want to distribute the report in other ways, you could:

• Format the content in HTML and send it via email (see this article for details).
• Create the report in a SharePoint document library (the basics of how to do this is explained here; the scenario is a script running in a Azure Automation runbook but the technique of using PnP cmdlets is the same in “regular” PowerShell).
• Post the report to a Teams channel or post a link to it in a message card created in a Teams channel using the inbound webhook connector. See this article for more information.

Watch the Volume of Audit Events

One thing to pay attention to is the volume of deletion events in large tenants. The Search-UnifiedAuditLog cmdlet can retrieve up to 5,000 audit records without doing anything special. To fetch more, you must either:

• Break up the search to stay within the 5,000-record limit by running multiple limited searches (perhaps a daily search).
• Set the SessionCommand parameter for Search-UnifiedAuditLog to ReturnLargeSet. This allows the search to return up to 50.000 records. You need to sort the data.

It might be advantageous to export the search results to an external repository. Many organizations use Splunk for this purpose because they want to keep Microsoft 365 audit data for longer than Microsoft does (90 days for Office 365 E3, 365 days for E5) and to use the search and analysis capabilities often found in dedicated log aggregator products. If you don’t have a copy of the Office 365 for IT Pros eBook (reporting and auditing chapter), you can read this discussion in the Microsoft Technical Community to understand the process.

Knowing When Sharing Happens

A natural question flowing from the discussion about implementing the SharePoint Online expiring access policy for external users is how administrators know if people use the feature. Equally naturally, the first place to look is the Office 365 or “unified” audit log to see if SharePoint Online generates any helpful events when users extend sharing links.

Unhappily, although SharePoint Online captures a UserExpirationChanged audit event when someone extends a sharing link close to its expiration, the information stored in the event is not enough to easily identify the content the sharing link grants access to. If you look at the sample audit event shown below, the SiteUrl property tells us that this event relates to sharing some OneDrive for Business content. Apart from that, we can see:

• The user principal name of the user who extends the validity of the sharing link (Jane.Sixsmith@office365itpros.com).
• The user principal name of the target user being granted access (Jsmith_yandex.com#ext#@office365itpros.onmicrosoft.com). The form tells us that this is a guest account (JSmith@yandex.com).

It would be nice if the name of the actual folder or document being shared was captured, but that’s not the case.

RecordType   : SharePointSharingOperation
CreationDate : 15/11/2021 13:17:04
UserIds      : Jane.Sixsmith@office365itpros.com
Operations   : UserExpirationChanged
AuditData    : {
                 "AppAccessContext": {
                   "AADSessionId": "bfe559aa-a811-488b-828d-a1fa90062133",
                   "CorrelationId": "b45e03a0-50df-3000-73a8-a6b7cbd31cc0"},
                 "CreationTime": "2021-11-15T13:17:04",
                 "Id": "5ee7b4d0-97ca-476d-c7ef-08d9a83a37aa",
                 "Operation": "UserExpirationChanged",
                 "OrganizationId": "a562313f-14fc-43a2-9a7a-d2e27f4f3478",
                 "RecordType": "SharePointSharingOperation",
                 "UserKey": "i:0h.f|membership|1003bffd805c87b0@live.com",
                 "UserType": "Regular",
                 "Version": 1,
                 "Workload": "OneDrive",
                 "ClientIP": "51.171.212.129",
                 "ObjectId": "https://office365itpros-my.sharepoint.com/personal/jane_sixsmith_office365itpros_com",
                 "UserId": "jane.sixsmith@office365itpros.com",
                 "CorrelationId": "b45e03a0-50df-3000-73a8-a6b7cbd31cc0",
                 "EventSource": "SharePoint",
                 "ItemType": "Web",
                 "Site": "cc191cff-670a-4740-8458-e6067537c747",
                 "UserAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/95.0.4638.69 Safari/537.36 Edg/95.0.1020.44",
"WebId": "551065f1-04a6-4979-8b19-2c8a0c16319f",
                 "TargetUserOrGroupType": "Guest",
                 "SiteUrl": "https://office365itpros-my.sharepoint.com/personal/jane_sixsmith_office365itpros_com",
                 "TargetUserOrGroupName": Jsmith_yandex.com#ext#@office365itpros.onmicrosoft.com

Investigating SharePoint Sharing Events

To see if it was possible to find some other information that would allow me to link the UserExpirationChanged events back to other sharing events, I wrote a script to extract the events from the audit log and parse their content. The results are not what I hoped. You can track the progress of sharing an item through:

• SharingSet: A user shares an item.
• SecureLinkCreated: A sharing link is created for the item. This is what is sent to the recipient.
• UserExpirationChanged: The expiration date for the sharing link is adjusted in line with policy.
• SecureLinkUsed: The recipient uses the sharing link to access the shared content.

The audit records for the first three events often have the same date and time because they occur close together (within milliseconds). For this reason, they can appear in a different order when viewing the report (Figure 1).

In due course, if the sharing link validity is extended further, SharePoint logs another UserExpirationChanged event. The cycle continues until the sharing link expires.

Download the Script

The script isn’t all that interesting. It finds the relevant audit events, extracts information, and reports its findings (you can download the script from GitHub). Unless you focus on UserExpirationChanged events which happen outside the initial creation of sharing links, I don’t think it helps much in terms of understanding the extent of sharing link extensions. However, someone who is smarter than I might be able to tweak the script to derive better results.

Learn more about how Office 365 really works on an ongoing basis by subscribing to the Office 365 for IT Pros eBook. Our monthly updates keep subscribers informed about what’s important across the Office 365 ecosystem.

7 June 2024: See this article for updated coverage of this topic.

How Administrators Can Find Out When Recordings Occur

Recently, I published two posts explaining how auto-label retention policies for Teams meeting recordings work and how to validate that an auto-label policy works. All is well, and the auto-label policy apply the right retention labels to the right files in the right locations, which is what you want.

The inevitable question then arose: how can administrators know when someone records a Teams meeting? The book answer is that neither Teams nor Office 365 offer an obvious way to know when a new recording happens. Recording is, after all, a personal action decided by the meeting organizer or a presenter (or invoked automatically by a meeting setting). Once the meeting policy assigned to the organizer or presenter allows them to use “cloud recording,” Teams records the meeting and captures the MP4 file in OneDrive for Business (personal meetings) or SharePoint Online (channel meetings).

Recording a meeting is not an auditable activity so it’s not captured in the Office 365 audit log. However, the creation of a new file in OneDrive for Business or SharePoint Online is auditable, so we can examine those events to see if we can track new recordings.

Audit Events for a Teams Meeting Recording

First, let’s consider what happens on the creation of a new Teams meeting recording. Three events appear in the audit log:

• Creation of a temporary MP4 file in a FileUploaded event.
• Modification of the temporary file in a FileModified event. This is likely when Teams updates the properties of the file to add the programmatic identifiers Media and Meeting which mark the file as a Teams meeting recording.
• Renaming the file name of the temporary file to its final form. This is also in a FileModified event. The temporary file has a prefix of “~tmp.” Teams removes the prefix to create the final file name in the form meeting name, date, and “meeting recording.” For example, the file called H2 FY2021 Review 20210615_140058-Meeting Recording.mp4 is for a Teams meeting on June 15, 2021, starting at 14:00:58 (local).

Equipped with this knowledge, we can search the audit log with the Search-UnifiedAuditLog cmdlet and parse the audit events to extract data for analysis.

PowerShell Script to Find Creation of Teams Meeting Recordings

The PowerShell code is straightforward:

• Use the Search-UnifiedAuditLog cmdlet to retrieve the set of audit events for FileModified and FileUploaded actions over a set period (I used 14 days).
• Parse the AuditData content in each audit event to figure out if it’s a Teams recording. The basic test used is that the file is an MP4 stored in the /Recordings folder.
• Extract information about the recording and write to a PowerShell list. For instance, it’s good to know the title of the meeting.
• Finally, extract the records for the temporary file to create a set for the final creation of Teams meeting recordings. Output this data to the screen with Out-GridView and to a CSV file.

Here’s the main processing loop:

$StartDate = (Get-Date).AddDays(-15)
$EndDate = Get-Date
[array]$Records = Search-UnifiedAuditLog -Operations FileUploaded, FileModified -StartDate $StartDate -EndDate $EndDate -Formatted -ResultSize 5000 -SessionCommand ReturnLargeSet

If (!($Records)) {Write-Host "No audit records found - exiting!"; break}

$Records = $Records | Sort-Object Identity -Unique

$TaggedRecordings = [System.Collections.Generic.List[Object]]::new() 	
ForEach ($Rec in $Records) {
   $AuditData = $Rec.AuditData | ConvertFrom-Json
   If (($AuditData.SourceFileExtension -eq "mp4") -and ($AuditData.SourceRelativeUrl -like "*/Recordings")) { 
      $A = $AuditData
      $RecordingFileName = $AuditData.SourceFileName
      $DateLoc = $RecordingFileName.IndexOf("-202")
      If ($DateLoc -eq -1) {$Topic = $RecordingFileName} Else {$Topic = $RecordingFileName.SubString(0,$DateLoc)}
      $DataLine = [PSCustomObject] @{
         Workload            = $AuditData.Workload
         Date                = $Rec.CreationDate
         User                = $Rec.UserIds
         Recording           = $RecordingFileName
         "Meeting title"     = $Topic
         Site                = $AuditData.SiteURL
         FullURL             = $AuditData.ObjectId
         Folder              = $AuditData.SourceRelativeURL
         Operation           = $Rec.Operations }
    $TaggedRecordings.Add($DataLine) 

   } #End If
} #End For

Figure 1 shows the output as viewed through Out-GridView.

You can download the complete script from GitHub.

Search-UnifiedAuditLog can return up to 50,000 audit events from the log. In large tenants where many document events accrue daily, you might exceed this number of audit events in a couple of days or even sooner. In this situation, you’ll need to fetch audit events and store them in another repository and analyze them from there.

Exploit the Audit Log

People sometimes complain when they can’t find a simple off-the-shelf option in a Microsoft 365 administration GUI to do something. The Office 365 audit log contains lots of interesting information which answers many questions, like who’s creating Teams meeting recordings. You only have to look. Or rather, know where to look.

Learn how to exploit the Office 365 data available to tenant administrators through the Office 365 for IT Pros eBook. We love figuring out how things work.

Opening the Black Box

My article about how to create an auto-label policy to apply retention labels to Teams meeting recordings resulted in several questions. As I noted in the article, tracking the progress of auto-labeling can be challenging due to the black-box nature of the background processes which search for recording files to label. One suggestion was to use the technique explained in this blog post to use the SharePoint Online PnP PowerShell module to connect to sites and retrieve information about retention job activity. For example:

$SiteURL = "https://office365itpros.sharepoint.com/sites/Office365Adoption/"
Connect-PnPOnline -Url $SiteURL -Interactive
get-pnppropertybag -key "dlc_policyupdatelastrun"
get-pnppropertybag -key "dlc_expirationlastrunv2"
2/23/2021 11:18:42 PM
2/2/2021 8:02:41 PM

The first value (dlc_PolicyUpdateLastRun) is the date when the background job to evaluate retention dates for items last ran. The second (dlc_ExpirationLastRunv2) tells you the last time the background job ran to execute the retention action defined in labels when retention periods expire.

The background jobs which evaluate retention dates and execute actions are not directly connected to auto-label processing, but they give an insight into how SharePoint Online processes sites. In a nutshell, if a site is active, the background jobs process its content. If not, the site is ignored. This makes a lot of sense because it avoids SharePoint doing a bunch of work to check items in sites where no work is necessary. I don’t know if another value stores a date when action must be taken to process expired items, but it would make sense if it did.

These values date back to legacy management processing in SharePoint on-premises and while they still work, Microsoft introduced a new retention processing engine last year which might eventually nullify their use.

Off to the Audit Log

Interesting as these values are, they don’t tell us anything about the application of labels. In the last article, I mentioned that the Office 365 audit log captures the TagApplied event when a person or policy applies a retention label to an item. The audit events are available roughly 15 minutes after they occur, so this source seemed like a good place to investigate.

I ended up writing a script to do the following:

• Find audit records for the TagApplied event in the last 14 days.
• Filter the records to find those which apply the retention label used by the auto-label policy. The same filter makes sure to only select records for policy rather than user application.
• Find the date of the recording from the file name generated by Teams. For instance, a recording named Call with James Ryan-20210217_141123-Meeting Recording.mp4 started at 14:11 on 17 February 2021.
• Calculate how long it took to auto-label the recording file (the difference between the date the call started and the audit record).
• Write the details to a SharePoint list to make the data available for additional analysis.

Here’s the main loop of the code to process the audit records. You can download the complete script from the Office 365 for IT Pros GitHub repository.

[array]$Records = (Search-UnifiedAuditLog -Operations TagApplied -StartDate $StartDate -EndDate $EndDate -Formatted -ResultSize 2000)
$TaggedRecordings = [System.Collections.Generic.List[Object]]::new() 
ForEach ($Rec in $Records) {
   $AuditData = $Rec.AuditData | ConvertFrom-Json
   If (($AuditData.DestinationLabel -eq $RetentionLabel) -and ($AuditData.UserType -eq "CustomPolicy")) { 
      $RecordingFileName = $AuditData.DestinationFileName
      $DateLoc = ($RecordingFileName.IndexOf("-202")+1)
      $RDate = $RecordingFileName.SubString($DateLoc,8)
      $TimeLoc = $DateLoc + 9
      $RTime = $RecordingFileName.SubString($TimeLoc,4)
      $RecordingDateTime = $RDate + $RTime
      [datetime]$RecordingDate = [datetime]::ParseExact($RecordingDateTime,"yyyyMMddHHmm",$null)
      [datetime]$TaggingDate = Get-Date($AuditData.CreationTime)
      $TimeToTag = ($TaggingDate - $RecordingDate)
      $TotalSeconds = $TotalSeconds + $TimeToTag.TotalSeconds
      $TimeToTagFormatted = "{0:dd}d:{0:hh}h:{0:mm}m" -f $TimeToTag
# Add the data about our record          
      $DataLine = [PSCustomObject] @{
         Workload            = $AuditData.Workload
         Recording           = $AuditData.DestinationFileName
         "Retention Label"   = $AuditData.DestinationLabel
         "Tagging Date"      = Get-Date($AuditData.CreationTime) -format g
         "Recording date"    = Get-Date($RecordingDate) -format g
         "Days to label"     = $TimeToTagFormatted
         Site                = $AuditData.SiteURL
         FullURL             = $AuditData.ObjectId }
    $TaggedRecordings.Add($DataLine) 
   } # End if
} # End ForEach

The Final Answer

After processing all the audit records, I know what Teams meeting recordings the auto-label policy has labelled and how long it took on average for an item to receive a label.

25 audit records found for auto-applying the Teams recordings retention label between 09/06/2021 19:36:43 and 23/06/2021 19:36:43
Average elapsed time to auto-label recordings: 02d:13h:28m
The report file is available in C:\temp\TaggedTeamsRecordings.csv.

The average time between creation and labeling depends on the gap between the meeting and when the labeling job runs. This seems to be on a weekly workcycle and usually runs over the weekend, so labeling a recording can take anything up to a week. An average of between two and four days is normal given that Teams captures new meeting recordings over the work week.

The same technique can be applied to track the progress of any auto-label policy.

Keep up with the changing world of the Microsoft 365 ecosystem by subscribing to the Office 365 for IT Pros eBook. Monthly updates mean that our subscribers learn about new development as they happen.

Microsoft 365 Audit Log is a Rich Source of Information About Workloads

The Microsoft 365 audit log (aka the unified audit log) is a rich source of information about what happens inside a tenant. Audit events generated by workloads go through an ingestion process to be added to the log to ensure that every event has a common set of fields like the date when the event occurred, the account responsible for the event, and the name of the event. In addition, a workload-specific payload of audit data is inserted into the AuditData property of events. This data varies from workload to workload. Interpreting the workload data is one of the challenges of dealing with the audit log that quickly becomes second nature (when you’ve done it often enough).

You can search the Microsoft 365 audit log using the Audit facility in the Microsoft Purview Compliance portal (Figure 1). This is acceptable when you’re looking for a specific event, but if you need to cast a wider net to look for events that might lead you to an answer, it’s easier and faster to do the job with PowerShell.

Who Did What or What Happened?

Most auditing queries are run to answer “who did what” questions. In other words, you want to know who performed a specific action. For instance, who deleted a document, created a group, recorded a Teams meeting, or sent a message from a shared mailbox. Chapter 21 of the Office 365 for IT Pros eBook contains many practical examples of parsing audit data from multiple workloads to answer who did what questions.

Sometimes you need to know what happened to a particular object, like a document or a user. Finding audit events for one or more documents is easy – all you need to do is pass the document names in the ObjectIds parameter. In this example, we create an array of document names to search for and then pass the array as the ObjectIds parameter for the call to Search-UnifiedAuditLog:

[array]$docs = "New Signature API for Email Signatures.docx", "Controlling default creation of online meetings with OWA.docx", "Anticipating Microsoft Ignite 2020.docx"
$Records = Search-UnifiedAuditLog -ObjectIds $docs -StartDate 1-Sep-2020 -EndDate 1-Oct-2020 -ResultSize 500

The events found are for all actions performed against the documents, such as being modified or downloaded. The same technique works for users:

[array]$Users = "Oisin.Johnston@office365itpros.com", "Kim.Akers@office365itpros.com"
$Records = Search-UnifiedAuditLog -ObjectIds $Users -StartDate 1-Sep-2020 -EndDate 1-Oct-2020

This search returns events for actions performed for these users (like being added to a group membership) rather than events performed by the users.

Actions Performed Against a Microsoft 365 Group

Microsoft 365 Groups are not users, so if we want to find the actions performed against a group, we must use the FreeText parameter to search audit records for instances of unique values that identify the group we’re interested in. Fortunately, the object identifier for a group is a good search term. In this example, we extract the object identifier for a Microsoft 365 group and use it to search for audit events. We then group the audit events to get an overview of the kind of activity performed against our target:

$ObjectId = Get-UnifiedGroup -Identity "Office 365 for IT Pros" | Select -ExpandProperty ExternalDirectoryObjectId
[array]$Records = Search-UnifiedAuditLog -FreeText $ObjectId -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date).AddDays(+1) -ResultSize 1000 -SessionCommand ReturnLargeSet

$Records | Group Operations | Sort Count -Descending | Format-Table Name, Count

Name                      Count
----                      -----
RecipientChange              17
TabUpdated                   10
TabAdded                      4
Remove member from group.     3
MemberRemoved                 3
Add member to group.          3
Update group.                 2
MemberAdded                   2
TabRemoved                    1
Set-UnifiedGroup              1
PutPermissions                1
Assign label to group.        1
StreamInvokeVideoSetLink      1

The technique also works for finding audit records for security groups (but not for distribution lists). It also works for Azure AD accounts, including guest users, but it’s much slower than using the ObjectIds parameter. As the name implies, FreeText means that a free text search is used to find matching audit events. In a large tenant, a free text search across potentially millions of records won’t be fast.

Remember that a single action can result in multiple events. For instance, if you add someone to a group, the MemberAdded and Add member to group events are captured by different workloads and ingested into the audit log. The duplication is easily detected by comparing the creation date for the events.

Mine the Audit Log

Every Office 365 administrator should know how to mine the Microsoft 365 audit log to answer questions about their tenant. It’s not hard and you’ll understand a lot more about how Office 365 works once you spend time deep in audit data. That doesn’t sound fun, but it’s better than it seems.

Know When New Guest Accounts Are Added to Your Tenant

Updated 14-Aug-2023

A reader question asks if it’s possible to monitor the add member to Teams action, specifically the addition of new guest accounts. The easy answer is “of course” because you can create an activity alert to monitor the audit records generated in the Office 365 audit log by the addition of new members. The problem is that Teams doesn’t distinguish between the addition of tenant accounts or guest accounts when they are added to a team. Still, an activity alert is enough to check additions.

Process Audit Log Data with PowerShell

But given that audit records are generated (if you have Office 365 E3 or later), we can do a better job with some relatively simple PowerShell to extract and process the audit log data. The steps we need to perform are:

• Find audit records generated when members are added to a team and extract those relating to guest users.
• Figure out if the guest account is newly added or already exists (because they’re a member in another group or team or someone has shared a document or folder with them).
• Decide what to do next. For instance, email the person who added the guest user to ask them if the addition is warranted for business purposes.

These steps might sound complicated, but they are straightforward. An example script can be downloaded from GitHub.

Building the Script to Report the Add Member to Teams Action

The first part of the script finds audit records for additions to team membership – this example looks for any addition in the last week.

[array]$Records = Search-UnifiedAuditLog -StartDate ((Get-Date).AddDays(-7)) -EndDate ((Get-Date).AddDays(1)) -ResultSize 5000 -Operations MemberAdded -RecordType MicrosoftTeams

Next, we loop through the records returned by the search to find out if the user recorded as a new member is a guest and if so if it is a new guest account. Again, the check is for guest accounts added in the last seven days. Note that Teams records MemberAdded audit events for both users being added to a team and a group chat. This is why we need to check the CommunicationType property in AuditData.

If ($Records) {
   $Report = [System.Collections.Generic.List[Object]]::new() # Create output file for report
   Write-Host "Processing" $Records.Count "audit records for addition of users to Microsoft Teams"
   ForEach ($Rec in $Records) {
     $AuditData = Convertfrom-Json $Rec.AuditData # Get payload
     ForEach ($M in $AuditData.Members) { # Examine users added to see if any are guests
      If (($M -Like "*#EXT#@*") -and ($AuditData.CommunicationType -eq "Team")) { # We have a guest user who's been added to a team rather than a group chat
         $GuestUser = Get-MgUser -UserId $M.UPN -Property Id, DisplayName, Mail, CreatedDateTime
         $AccountAge = ($GuestUser.CreatedDateTime | New-TimeSpan).Days
         If ($AccountAge -le 7) { # Guest created within last 7 days so write out details
            $ReportLine = [PSCustomObject]@{ 
               Guest            = $GuestUser.Mail   
               Name             = $GuestUser.DisplayName
               Created          = $CreationDate 
               AgeInDays        = $AccountAge
               DateAddedTeams   = Get-Date($AuditData.CreationTime) -format g
               TeamName         = $AuditData.TeamName
               AddedBy          = $AuditData.UserId
               GruupId          = $AuditData.AADGroupId} 
            $Report.Add($ReportLine) 
         } # End if (AccountAge)   
     } # End if (Guest user check)
   } # End Foreach (Members)
 } # End ForEach (Records)
} #End if (Records)

Finally, we email the person who added the member to the team to ask them to provide a justification (Figure 1).

$htmlHeaderUser = "<h2>A new guest user has been created in our tenant</h2>"; $htmlbody = $htmlheaderUser + $BodyText + "<p>"
   $HtmlMsg = "" + $HtmlHead + $HtmlBody
  # Construct the message parameters and send it off...
    $MsgParam = @{
      To = $R.AddedBy
       From = $MsgFrom
       Subject = "New Guest User Added"
       Body = $HtmlMsg
       SmtpServer = $SmtpServer
       Port = $SmtpPort
       Credential = $O365Cred }
     Send-MailMessage @msgParam -UseSSL -BodyAsHTML

Script Will Need to be Updated

Send-MailMessage uses the SMTP AUTH protocol to connect and send the message. Microsoft has not yet said when they will deprecate SMTP AUTH as part of their ongoing effort to remove basic authentication. If they do, the script will need to be updated to use whatever method is provided to allow PowerShell scripts to send email using modern authentication.

In summary, this is yet another example of where the unified audit log holds valuable information to help tenant administrators understand what’s happening inside their organization. All it takes is a little PowerShell and some trial and error.

The Office 365 for IT Pros eBook features many practical examples of using Office 365 audit log data to solve problems. You never know when you might need our experience…

Sway Hasn’t Progressed as Hoped Since 2015

Sway, or as it was in those days “Office Sway,” made its debut at the Microsoft Ignite 2015 conference with the tagline “reimagine how your ideas come to life.” Generally available since August 2015, it’s clear that Sway has not achieved the kind of breakthrough that Microsoft hoped at the time.

Perhaps it’s because the Office community is too embedded in PowerPoint to want to change to a new tool. PowerPoint has received its own batch of improvements since 2015, including design ideas, and shows no sign of losing popularity. Sway certainly hasn’t dented the PowerPoint juggernaut. Due to pressure of space and the need to make room for more popular topics, we moved coverage of Sway to the companion volume of the Office 365 for IT Pros eBook in 2018.

No More Sway Audit Events

Microsoft posted Office 365 notification MC220283 on August 12 saying: “Effective immediately, we have retired support for organizations to audit Sway activities via the Microsoft 365 admin center.” In other words, the Sway application no longer generates audit events for ingestion into the Office 365 audit log.

Because the audit events aren’t in the log, administrators can’t search the log using the Audit log search option in the Compliance Center or with the Search-UnifiedAuditLog cmdlet for events like SwayCreate, SwayDelete, SwayEdit, or SwayShare. I tested if the change was effective as stated by working with several Sways in my tenant and sure enough, nothing appeared in the audit log.

Who Uses Sway?

On the surface, this isn’t a huge deal. Anecdotal evidence is that Sway isn’t used much in Office 365 tenants, especially those with the necessary E3 and E5 licenses needed for auditing. I hear that Sway gets more use in tenants with academic licenses, but if users don’t have the required licenses, there’s no point in Sway generating audit events.

Only Microsoft knows why they have broken the link between Sway and the audit log. Generating audit events is hardly a massive overhead for Sway and the ingestion of those events into the audit log shouldn’t pose any strain on the service. After all, the number of potential Sway events is a minor fraction of the billions created for SharePoint Online, which is the most verbose of all workloads in terms of audit events.

It’s possible that Microsoft’s much-loved (by Microsoft) telemetry indicates that tenants seldom search for Sway audit events and that this factor might have driven the decision. No one except Microsoft really knows why as the notification delivers a remarkable lack of explanation.

The Real Problem

Not many will miss the loss of Sway audit events. What’s more perturbing is Microsoft’s announcement of the deprecation without warning. In MC220283, Microsoft “apologize for the delayed notice and are working to ensure we provide timely guidance in the future.” The notice wasn’t delayed. It was given after Sway stopping generating events and that’s not good enough when it comes to compliance features which tenants might rely upon.

In this case, the damage is minimal and the worse thing is probably the need to update documentation. The question is whether Microsoft might withdraw a more popular feature with the same lack of up-front communication and detailed explanation in the future. Let’s hope that they don’t.

Many Ways to Get a Video Into Stream

Equipped with a suitable license, Office 365 users can upload content (in supported formats) to Stream in various ways:

• Uploading a video file created elsewhere to the Stream portal.
• With the Stream mobile app.
• Making a screen capture video with Stream.
• Recording a Teams meeting.
• Running a Live Event with Teams or Yammer.

Apart from people with frontline licenses, licensed users can upload a video to Stream. To accommodate uploads, Office 365 enterprise tenants are assigned 500 GB of video storage plus 0.5 GB of extra storage for every licensed user (except frontline users). You can discover how much Stream storage your tenant has consumed through the Stream admin settings (Figure 1).

All uploaded videos count against the overall quota for the tenant. The exact size of a video depends on its format, quality, and length. As a guide, expect to use approximately 7.5 MB per minute of 1080p MP4 video with smaller amounts consumed for lower-quality video. Stream counts the original file size of the uploaded video against the quota and doesn’t take other factors such as the size of transcoded videos and caption files into account.

Demand for Teams Recordings

When Microsoft originally created Stream, they probably anticipated that most videos uploaded would be high-quality corporate videos produced using professional equipment. Today, the reality is different and many organizations find that the demand for Stream recordings comes from Teams meetings.

Although it’s easy to discover how much of the assigned storage has been consumed, it’s harder to get other information out of Stream, like the size of an individual video. Without that information, you can’t discover who is uploading the big videos which consume all the storage without downloading the videos to note their size on disk.

Use the Audit Log

What you can do is use the Office 365 audit log to track who’s uploading videos to Stream. This example shows how to run the Search-UnifiedAuditLog cmdlet to find video upload events. The returned set are then analyzed to extract information about the video.

$StartDate = (Get-Date).AddDays(-90); $EndDate = (Get-Date) #Maximum search range for audit log for E3 users
$Records = (Search-UnifiedAuditLog -Operations StreamInvokeVideoUpload -StartDate $StartDate -EndDate $EndDate -ResultSize 2000)
If ($Records.Count -eq 0) {
    Write-Host "No audit records for Stream video uploads found." }
Else {
    Write-Host "Processing" $Records.Count "audit records..."
    $Report = [System.Collections.Generic.List[Object]]::new() # Create output file for report
    # Scan each audit record to extract information
    ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
        $ReportLine = [PSCustomObject] @{
           TimeStamp = Get-Date($AuditData.CreationTime) -format g
           User      = $AuditData.UserId
           Action    = $AuditData.Operation
           VideoURL  = $AuditData.ResourceURL
           VideoName = $AuditData.ResourceTitle }
      $Report.Add($ReportLine) } }

Remember that Office 365 only stores 90 days of audit data for E3 accounts, so if you want to go back further, you’ll need to either extract and store information on an ongoing basis or use a third-party reporting app. For output, you could pipe the data to the Out-GridView cmdlet or create a CSV file:

$Report | Sort {$_.TimeStamp -as [DateTime]} -Unique -Descending | Out-GridView

Report | Sort {$_.TimeStamp -as [DateTime]} -Unique -Descending | Export-CSV -NoTypeInformation c:\temp\ExportStreamVideos.csv

Using a figure for video storage of 400 MB/hour, you could even calculate how much of the tenant’s Stream quota is being consumed by each user.

The report data also allows us to do some basic analysis, such as finding out who uploads most videos:

$Report | Group User | Sort Count -Descending | Format-Table Name, Count

Name                               Count
----                               -----
Jane.Nix@office365itpros.com          74
James.Ryan@office365itpros.com        22
John.Hubbard@office365itpros.com      17
James.Joyce@office365itpros.com       15
Ben.Owens@office365itpros.com          9

Reporting Video Views

If you replace StreamInvokeVideoUpload (upload a video event) with StreamInvokeVideoUpView (view a video event) in the Search-UnifiedAuditLog command, you’ll create a report of Stream view events to know who’s looking at videos and what the most popular videos are. To see a summary, you change the Group command slightly to:

$Report | Group-Object Property VideoName | Sort Count -Descending |Format-Table Name, Count
Name                                     Count
----                                     -----
Microsoft 365 Groups and Teams Activity     19
Call with Kim Akers                          7
Successfully Manage Microsoft Teams          7
Troubleshooting PowerShell                   4
Have you seen my Exchange server             3

The Office 365 for IT Pros eBook is full of bright ideas like this. Subscribe now and make sure you’re not left behind by the rapid pace of change in Office 365.

What Did Users Do with SharePoint Documents?

A question popped up in an online group: How can I create a report for each user detailing the interaction with documents stored in SharePoint Online libraries? The answer seems straightforward: search the Office 365 audit log for SharePoint document operations and create a report from the events found, outputting it in CSV or HTML format. Chapter 21 of the Office 365 for IT Pros eBook includes many examples of how to extract information from the audit log that could be used as the basis for a solution. The post covering how to answer the question of who updated a document is also helpful.

Often the reports generated from the audit log cover actions taken by multiple users. In this case, the request is to generate a report on a per-user basis. Possibly the desire is to email the report to the user, or maybe the feeling is that it is easier to review access to sites and documents on a personal level.

Generating a List of Users

The first thing to resolve is what’s intended by “user”? We need to know this to generate the reports. A user could mean:

• Someone with an account in a tenant.
• Both tenant and guest users.
• Just guest users.

From a PowerShell perspective, you can generate a list of mailbox owners with Get-ExoMailbox (people with mailboxes are likely to have SharePoint Online licenses).

$Users = Get-ExoMailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited | Select UserPrincipalName,DisplayName

Alternatively, if you want to include guest accounts, you can create a list with Get-AzureADUser and include accounts of type Member (tenant account) and Guest.

$Users = Get-AzureADUser -All $True -Filter ("UserType eq 'Guest' or UserType eq 'Member'") | Select UserPrincipalName, DisplayName

You could filter the list further by removing tenant accounts who aren’t licensed for SharePoint Online. This is easy to do, but it’s probably not necessary because the report is generated from audit events that won’t exist unless an account is licensed.

Searching the Office 365 Audit Log

We’re going to search the Office 365 audit log for events generated by all users. The other search parameters needed are:

The events to look for: Depending on the applications used in a tenant, the audit log could include up to 1,500 different events. In this case, we want to know about events which manipulate documents stored in SharePoint or OneDrive for Business. Five events should suffice:

• FileAccessed. A user opens a file but does not modify the content.
• FileDownloaded. A user downloads a file to their workstation.
• FileModified. A user updates the content of a file.
• FileDeleted. A user deletes a file.
• FileUploaded. A user uploads (creates) a new file.

Although you can input the events directly into the search command, it’s easier to declare the set of events in an array:

$Operations = @('FileAccessed', 'FileDownloaded', 'FileModified', 'FileDeleted', 'FileUploaded')

The start and end date for the search. SharePoint Online is a verbose application when it comes to the generation of audit log records. To make processing easier, restrict the date range as much as possible. You can go back 90 days for Office 365 E3 accounts and 365 days for Office 365 E5 accounts.

Handling Large Quantities of Audit Records

In large tenants, consider splitting the processing up over several batches as otherwise the script will likely take a long time to complete. The easiest way to do this is to amend the script to create a filtered set of users and use the filtered list as input to the audit log search. This example uses the Get-ExoMailbox cmdlet with a filter applied to the CustomAttribute1 property to find a set of users:

$Users = Get-ExoMailbox -Filter {CustomAttribute1 -eq "Sales"} | Select -ExpandProperty UserPrincipalName 
Search-UnifiedAuditLog -Operations $Operations -UserIds $Users -StartDate $StartDate -EndDate $EndDate -ResultSize 5000

The Search-UnifiedAuditLog cmdlet is restricted to returning a maximum of 5,000 audit records at one time. More records might exist, and in this case, you must run the cmdlet until all available data is retrieved. Search-UnifiedAuditLog supports the retrieval of large amounts of data (up to 50,000 records) by allowing you to declare a session identifier (a value to link calls together) together with the ReturnLargeSet parameter. The data is unsorted when fetched, so it must be sorted for reporting purposes. If more than 50,000 audit records are available, you’ll have to divide processing up across multiple runs.

Processing Audit Data

It’s possible to take the raw data from audit records and output the records to a CSV file. However, I like to process Office 365 audit records to make more sense of what they contain. In this case, the script does the following:

• Format the timestamp so that it’s something like 4-May-2020 18:56.
• Drop a bunch of unneeded audit records generated by SharePoint Online for access to different graphic elements used by pages, records for background processing (app@sharepoint), and records with blank user agent information.
• Extract a human-friendly client identifier from the UserAgent property. For example, take a string like “Microsoft Office Word/16.0.12730.20144 (Windows/10.0; Desktop WOW64; en-IE; Desktop app; Microsoft Corporation/Surface Book 2)” and make it “Microsoft Word (desktop)” or “Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/84.0.4121.0 Safari/537.36 Edg/84.0.495.2” and make it “Microsoft Edge” (yes, there is a misspelling in the information written into the audit log. The version information is also extracted.

The processed audit records go into a PowerShell list object. This is much more efficient than adding records to an array. And we can do some rudimentary processing to generate some insight into what’s happening. For example, what kind of file operations are performed:

$Report | Group Operation | Format-Table Name, Count   

Name           Count
----           -----
FileAccessed    3594
FileUploaded     341
FileModified    3186
FileDownloaded    86
FileDeleted      327

Or the people who are creating documents:

$Report | Group UPN | Sort Count -Descending | Format-Table Name, Count                     

Name                                                                           Count
----                                                                           -----
tony.redmond@office365itpros.com                                                5669
michael.van.horenbeeck_thecollective.eu#ext#@office365itpros.onmicrosoft.com     690
jcgonzalez_itechcs.onmicrosoft.com#ext#@office365itpros.onmicrosoft.com          374

Generating the Per-User Reports

To create a report for each active user, we can loop through the set of users we created beforehand and extract the records for the selected user and write them out to a CSV file:

$UserRecords = $Report | ? {$_.UPN -eq $U.UserPrincipalName} 
    If ($UserRecords) {  
       $UserReports++  
       Write-Host "Writing out data for" $U.DisplayName
       $FileName = "c:\Temp\AuditHistory" + $U.UserPrincipalName + ".csv"
       $UserRecords | Export-CSV -NoTypeInformation $FileName }

Figure 1 shows what the contents of a CSV file looks like:

The per-user CSV files are created in the c:\temp\ directory (Figure 2), so it would be easy to find them and email them to the users… But that’s another day’s work.

In the meantime, the complete script containing everything described above is available for download from GitHub. Happy PowerShell!

Interrogating SharePoint and OneDrive Document Version History

A recent question asked how to use the SharePoint Online PnP PowerShell module to extract the version history of a document. The PnP (Patterns and Practices) module contains cmdlets to handle complex SharePoint provisioning and management scenarios. If you get to know PnP, you probably like it because it can handle actions from update a SharePoint document to create a new folder. However, the nature of PnP is that its interaction with objects is more complicated than other PowerShell modules.

The usual reason why people want to look at the version history for a document is to know who made a change to its content. Given how autosave captures document updates, the number of versions available for a document stored in SharePoint Online or OneDrive for Business can be large (Figure 1).

Office 365 Audit Log is an Alternative

If you’re not used to PnP, you might find it easier to extract information about events to update a SharePoint document from the Office 365 audit log. Every time a document is uploaded or updated in a SharePoint Online or OneDrive for Business document library, SharePoint creates an audit event that is later ingested into the Office 365 audit log (the event should be available about 15 minutes after the update). If we know the name of a document, it’s easy to search the audit log with the Search-UnifiedAuditLog cmdlet and find its audit records.

Searching for Document Change Audit Events

The PowerShell script below uses the $FileName variable to hold the name of the document to search for. If events occurred for this document over the last 90 days, the search should find events to record the initial upload of the document to the library (FileUploaded) and subsequent updates (FileModified) and views (FileAccessed). If the AutoSave feature is enabled for the document, multiple update records can accumulate over a short period. As is normal with audit records, a lot of interesting information is found in the AuditData property.

$FileName = (Read-Host "Enter file name to search")
$Records = (Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date).AddDays(+1)  -Operations FileModified, FileAccessed, FileUploaded -ObjectIds $FileName -ResultSize 1000)
If ($Records.Count -eq 0) {
   Write-Host "No audit records found for file names beginning with" $FileName }
 Else {
   Write-Host "Processing" $Records.Count "audit records..."
   $Report = [System.Collections.Generic.List[Object]]::new()
   ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      $ReportLine = [PSCustomObject]@{
           TimeStamp   = $Rec.CreationDate
           User        = $AuditData.UserId
           Action      = $AuditData.Operation
           SiteUrl     = $AuditData.SiteUrl
           Site        = $AuditData.SourceRelativeUrl
           File        = $AuditData.SourceFileName
           IpAddress   = $AuditData.ClientIP
           App         = $AuditData.UserAgent  }
      $Report.Add($ReportLine) }}

Listing the Results

After analyzing the audit records, we can list the set of actions found for the document:

$Report | Select Timestamp, User, Action

TimeStamp            User                               Action
---------            ----                               ------
22 Apr 2020 14:40:41 Jane.Maloney@office365itpros.com   FileModified
21 Apr 2020 15:19:03 Jane.Maloney@office365itpros.com   FileModified
21 Apr 2020 15:02:34 Kim.Akers@office365itpros.com      FileModified
21 Apr 2020 15:01:39 Jane.Maloney@office365itpros.com   FileUploaded

To distribute the report, you could simply print it or create a CSV file. Other distribution methods include:

• Format the content in HTML and send it via email (see this article for details).
• Create the report in a SharePoint document library (the basics of how to do this is explained here; the scenario is a script running in a Azure Automation runbook but the technique of using PnP cmdlets is the same in “regular” PowerShell).
• Post the report to a Teams channel or post a link to it in a message card created in a Teams channel using the inbound webhook connector. See this article for more information.

Is Ninety Days Enough?

If your accounts have Office 365 E5 or Microsoft 365 E5 compliance licenses, audit records are available for 365 days. However, 90 days is usually enough to find out who made a change to an important document. Unless the change was overlooked and has only just been noticed!

Practical information about using PowerShell to solve common Office 365 administrative problems is a hallmark of the Office 365 for IT Pros eBook. Subscribe today and learn from our experience!

It’s Just Different in the Cloud

One of the interesting things about Office 365 is the way that the integrated nature of the suite forces people to rethink how they previously approached tasks. Take for instance the question that Exchange administrators have been asked to answer for years: “who sent that email”? A question that takes us to Exchange Send As audit records.

The Development of Exchange Mailbox Auditing

When Exchange 2010 SP1 first introduced mailbox auditing, Microsoft gave administrators the tools to answer the question. The audit reports were clunky (horrible), but you could get the job done in PowerShell by running the Search-MailboxAuditLog cmdlet to search the audit records gathered for delegate activity to find the SendAs events generated when someone used that permission to send a message for another mailbox. That is, if you had enabled auditing for the mailboxes.

It took Microsoft far too long to enable auditing for all mailboxes by default. This was announced for Exchange Online in mid-2018 and eventually enabled in early 2019. However, as explained in this post, if you have Office 365 E3 licenses, you still need to manually enable mailbox auditing for mailboxes assigned those licenses.

Searching for Exchange Send As Audit Records

To return to the famous question, tenants can use the Search-UnifiedAuditLog cmdlet to search the audit log for SendAs events and produce an answer. And that’s exactly what you see in many of the scripts offered as the basis for answering our question, including an earlier post of mine.

Adjustments Needed for Office 365

However neat such an answer is, time has moved on and the answers given previously are now wrong. Unlike Exchange on-premises, where all SendAs events are generated by delegates sending messages for another mailbox, Exchange Online processes messages sent by other workloads that can show up in audit searches and skew results unless adjustments are applied. Here are some things to consider:

• Audit records with S-1-5-18 captured in the UserId property record the generation of a welcome message for a new team.
• Audit records are generated when Teams sends a welcome message.
• Audit records are generated for the group mailbox when a member posts a message to a conversation in an Outlook group using OWA. Records are not generated when messages are posted with other clients or arrive from guest members.
• Audit records are generated for the group mailbox when someone updates a task in Planner.

A Script to Find SendAs Audit Records

With these caveats in mind, here’s a script to search for SendAs records and process SendAs audit records and identify those belonging to user/shared mailboxes and those belonging to group mailboxes. The former category is normally what people are concerned with because they’re looking for instances where someone sent a message from a mailbox rather than posting to an Outlook group or adding a comment with Planner. To help identify the two categories, we create a hash table of primary email addresses for mailboxes and groups and look that table up for each audit event.

# First populate the Recipients Hash Table with user mailboxes, group mailboxes, and shared mailboxes
CLS
Write-Host "Populating Recipients Table..."
$RecipientsTable = @{}
Try {
    $Recipients = Get-Mailbox -ResultSize Unlimited -RecipientTypeDetails UserMailbox, SharedMailbox}
Catch {
    Write-Host "Can't find recipients" ; break}
# Now Populate hash table with label data  
$Recipients.ForEach( {
       $RecipientsTable.Add([String]$_.PrimarySmtpAddress, $_.RecipientTypeDetails) } )
# And include group mailboxes
[array]$GroupMailboxes = Get-Mailbox -ResultSize Unlimited -GroupMailbox
$GroupMailboxes.ForEach( {
       $RecipientsTable.Add([String]$_.PrimarySmtpAddress, $_.RecipientTypeDetails) } )
Write-Host "Finding audit records for Send As operations..."
$Records = (Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-90) -EndDate (Get-Date).AddDays(+1) -Operations "SendAs" -ResultSize 1000)
If ($Records.Count -eq 0) {
    Write-Host "No audit records for Send As found." }
Else {
    Write-Host "Processing" $Records.Count "Send As audit records..."
    $Report = [System.Collections.Generic.List[Object]]::new() # Create output file 
    # Scan each audit record to extract information
    ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      $MailboxType = $RecipientsTable.Item($AuditData.MailboxOwnerUPN) # Look up hash table
      If ($MailboxType -eq "GroupMailbox") {$Reason = "Group Mailbox Send"} Else {$Reason = "Delegate Send As"}
      If ($AuditData.UserId -eq "S-1-5-18") {$UserId = "Service Account"} Else {$UserId = $AuditData.UserId}
      $ReportLine = [PSCustomObject] @{
           TimeStamp   = Get-Date($AuditData.CreationTime) -format g
           SentBy      = $AuditData.MailboxOwnerUPN
           SentAs      = $AuditData.SendAsUserSmtp
           Subject     = $AuditData.Item.Subject
           User        = $AuditData.UserId
           Action      = $AuditData.Operation
           Reason      = $Reason
           UserType    = $AuditData.UserType
           LogonType   = $AuditData.LogonType
           ClientIP    = $AuditData.ClientIP
           MailboxType = $MailboxType
           ClientInfo  = $AuditData.ClientInfoString
           Status      = $AuditData.ResultStatus }        
      $Report.Add($ReportLine) }
}
$Report | ? {$_.MailboxType -eq "UserMailbox"} | Out-GridView
$Report |Export-Csv -NoTypeInformation -Path c:\temp\SendASAuditRecords.csv
Write-Host "Report File saved in" c:\temp\SendASAuditRecords.csv

Figure 1 shows an example of some audit records found for user and shared mailboxes as generated by the script. The complete set of processed records is written out to a CSV file where the data can be parsed and analyzed to your heart’s content.

The script is available for download in GitHub.

The Influence of the Substrate

The problem with taking on-premises solutions like scripts to the cloud is that the solutions often don’t accommodate the way applications work and integrate. The influence of the Office 365 substrate is felt across the entire suite and is growing, which is why it’s wise to keep an eye on how scripts work as Microsoft introduces new functionality.

Chapter 21 of the Office 365 for IT Pros eBook includes many examples of how to interrogate the audit log to retrieve useful information. It’s surprising just how much you can discover if you go looking.

Capturing Crucial Office 365 Audit Data Requires E5 Licenses

In January 2019, Microsoft announced that they were adding an event called MailItemsAccessed to the set of audited operations captured in the Office 365 audit log. Microsoft claimed that the new event would “capture details of when a message in a mailbox is opened by the mailbox owner, delegate (someone with read access to the mailbox) or using administrative access” leading to audit information delivering “comprehensive forensic coverage of mailbox accesses.”

Time moved on and in March 2019, Microsoft said that they had halted the deployment of MailItemsAccessed to Office 365 tenants. Software has a habit of hitting delays and it was speculated that the overhead involved in gathering a massive number of message access events would place a strain on Exchange Online.

All went quiet for a while, which prompted me to ask Microsoft in June what was happening. They provided an odd statement that faintly indicated that the MailItemsAccessed event might appear in Q3 (July to September).

Crucial Security or Compliance Audit Events

Q3 came and went without a trace of any message access being captured in the Office 365 audit log. But last month Microsoft released documentation for Advanced Audit in Microsoft 365 (now Purview Audit Premium) which makes it clear that MailItemsAccessed is now regarded as the first example of a “crucial” security or compliance-related audit event included in their advanced audit offering. Previously, Microsoft called these events “high-value.” In either case, Microsoft defines the event as “one that can help you investigate possible breaches or other forensic-related investigations.”

Update October 19: Microsoft has released three additional crucial events to handle email sends and searches of mailboxes and sites.

In a nutshell, if you want to see information about who accessed an item in a mailbox, you need to buy some Office 365 E5, Microsoft 365 E5 or Microsoft 365 E3 with Compliance licenses.

Some MailItemsAccessed records can be found in the Office 365 audit log for my tenant audit and viewed using the Search-UnifiedAuditLog cmdlet or the Audit log search (Figure 1). But all the records that have turned up so far (in about a month) are for “sync” activities for various folders like the Inbox. Sync records aren’t very exciting because all they record is the synchronization of a complete folder using a client like Outlook desktop. The really interesting data lie in bind records, which record access to individual messages.

It’s also interesting to learn that Exchange Online applies throttling for MailItemsAccessed events. If a mailbox generates more than 1,000 bind events in a 24-hour period, Exchange Online stops recording MailItemsAccessed events for bind operations for another 24 hours before resuming capture of these events. Microsoft says that less than 1% of mailboxes are subject to throttling.

You can download an example of how to extract and report MailItemsAccessed audit events from GitHub.

Audit Log Retention Policies

Apart from capturing crucial audit events, the advanced audit feature also allows tenants to configure audit log retention policies. These policies work much like mailbox retention policies. You define a retention policy for selected audit events with a set retention period and Office 365 removes those items after that period. A tenant supports up to 50 audit log retention policies.

This example runs the New-UnifiedAuditLogRetentionPolicy cmdlet to create an audit retention policy to remove any SearchQueryPerformed event executed by the background app@sharepoint process after three months instead of the twelve-month retention of audit events if the tenant has E5 licenses.

New-UnifiedAuditLogRetentionPolicy -Name "90-day Retention SearchQueryPerformed by app@sharepoint" -Description "Remove SearchQueryPerformed events from the app@sharepoint process after 90 days" -RecordTypes SharePoint -Operations SearchQueryPerformed -UserIds "app@sharepoint" -RetentionDuration ThreeMonths -Priority 8

You can only manage audit log retention policies with PowerShell using cmdlets accessible by connecting to the Compliance Center endpoint.

Purging the Office 365 Audit Log

You can choose to apply retention for any of the events captured in the Office 365 audit log and keep them for three, six, nine, or twelve months. That is, you can keep audit events for longer than 90 days for accounts with E5 licenses. Office 365 restricts E3 accounts to a 90-day retention period, which is also the period for which you can search audit events in the Compliance Center. Searches earlier than this point must be done with the Search-UnifiedAuditLog PowerShell cmdlet.

It’s a good idea for tenants who either want precise control over how long audit data is retained or want to clean up events that don’t add much value in terms of investigations. SharePoint is a notoriously “chatty” application when it comes to the capture of audit events, so I can see why tenants  might decide to keep important events like FileUploaded or FileAccessed for as long as possible while removing some of the chatter after 90 days.

Communication Woes

I don’t have any issue with Microsoft classifying the MailItemsAccessed event as crucial and demanding a premium for its capture into the audit log. Only some tenants will be interested in these events and they might well have E5 licenses already. I can also see the sense of not imposing a huge overhead on Office 365 to capture these events for E3 tenants. It’s just a pity that the communication around the introduction of MailItemsAccessed and its evolution to become a crucial audit event has been so fractured and incoherent. Microsoft can do better.

We track developments in Office 365 auditing, including the kind of events you can extract from the audit log, in a chapter in the Office 365 for IT Pros eBook. Knowing what goes on in a tenant is important and the audit log holds the answers to many mysteries.

Connectors Link Teams to Email

The Email addresses for Teams channels are interesting objects. Channels start off without email addresses and only get one when a team member retrieves an address for the first time (Figure 1). That email address stays in place until it is removed. Once the Teams channel email address exists, it can be used like any other email address, including being used for an Exchange Online mail contact object that appears in the GAL, or as a member of a distribution list.

Rather oddly in the opinion of some, any team member (from the tenant – not guests) can remove the email address from a channel. It seems like it would be better if this was something that only a team owner could do.

Phantom Mailboxes and Connectors

Behind the scenes, when an email address is assigned to a channel, Office 365 creates a “phantom” Exchange Online mailbox in a special tenant to receive email for the channel. The mailbox is a phantom because it can’t be seen by any public interface. When new messages arrive in the mailbox, Teams uses a connector to fetch and post the messages as new topics in the channel.

If the message is small enough (under 2,000 characters as a rough estimate), Teams can display the full text in the channel. Otherwise the user needs to access the copy of the message (plus any attachments) which Teams captures in an “email messages” folder (named for the month) in the Channel folder of the SharePoint document library belonging to the team.

Replies posted to email messages in channels remain in Teams and are not copied back to the original message recipients.

Finding Audit Records When Teams Channels are Email-Enabled

All of which brings us to how to know when a Teams channel is mail-enabled. One way is to use the PowerShell script described in this article to report teams channels that are mail-enabled (the script uses a mixture of PowerShell and Graph calls because the email address for channels is not revealed by the Get-TeamChannel cmdlet). Another method is to look for the events in the Office 365 audit log that Teams creates when a connector is added to enable email access to a channel or removed from a channel. Here’s a quick and dirty script to find the audit records and extract the necessary data from the audit payloads:

# Find Office 365 audit records when an Email Connector is added or removed
# from a Teams channel
CLS; Write-Host "Searching Office 365 Audit Records to find Email Connectors added to Teams channels"
$StartDate = (Get-Date).AddDays(-90); $EndDate = (Get-Date) #Maximum search range for audit log for E3 users
[array]$Records = (Search-UnifiedAuditLog -Operations ConnectorAdded, ConnectorRemoved -StartDate $StartDate -EndDate $EndDate -ResultSize 1000)
If ($Records.Count -eq 0) {
    Write-Host "No audit records for connectors found." }
Else {
    Write-Host "Processing" $Records.Count "audit records..."
    $Report = [System.Collections.Generic.List[Object]]::new() # Create output file for report
    # Scan each audit record to extract information
    ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      If ($AuditData.AddOnName -eq "Email Connector") {
          $ReportLine = [PSCustomObject]@{
           TimeStamp = Get-Date($AuditData.CreationTime) -format g
           User      = $AuditData.UserId
           Action    = $AuditData.Operation
           Team      = $AuditData.TeamName
           Connector = $AuditData.AddOnName
           Channel   = $AuditData.ChannelName }
      $Report.Add($ReportLine) }
}}

$Report | Format-Table TimeStamp, Action, Team, Channel, User -AutoSize

Figure 2 shows what you might see as a result of running the script. You can export the information to a CSV file if you need to preserve it for further examination.

Finding Email-Enabled Channels

Audit records can only tell us when Teams channel email addresses exist over a limited period (90 days for E3 tenants, 365 days for E5 tenants). A mixture of tracking audit changes and reporting on channels periodically should be enough to keep an eye on what’s happening for this aspect of a tenant.

The Office 365 for IT Pros eBook includes hundreds of PowerShell examples to help you manage different aspects of your tenant. Shouldn’t you be a subscriber and receive monthly updates?

Soft and Hard Deletions for Microsoft 365 Groups

Yesterday’s post addressed the topic of how to report the removals of Teams from an Office 365 tenant. This led to the logical question of how to know when Microsoft 365 Groups (and Teams) are removed because they expire due to the settings in the tenant group expiration policy.

The simple answer is that the Office 365 audit log captures details for all group deletions, including when the group expiration policy determines that a group is expired and puts the group into a soft-deleted state. Thirty days later, a background process permanently removes the soft-deleted group unless an administrator restores it beforehand. As is often the case with Office 365 technology, the simple answer hides some complexity, which we’ll dive into now.

Understanding Group Deletion Records

When you examine the records for group deletions stored in the Office 365 audit log, you find three conditions to handle:

1. A user deletes a Microsoft 365 group, team, or team-enabled site.
2. Microsoft background processes examine groups that come within the scope of the expiry policy and remove expired groups where no activity has occurred to force automatic renewal. These events are logged with a user identifier like ServicePrincipal_1342cefb-7a89-4ee2-af90-c8443053e1e8.
3. Both 1 and 2 put groups into a soft-deleted state. After 30 days, another background process called the Microsoft Online services Garbage Collector permanently removes these groups and all attached resources.

With these conditions in mind, we can create a PowerShell script to extract records from the Office 365 audit log and parse the records to extract some useful information. Here’s the script I created. It looks similar to the one discussed yesterday with some extra processing to handle the three conditions.

CLS; Write-Host "Searching Office 365 Audit Records to find auto-expired group deletions"
$StartDate = (Get-Date).AddDays(-90); $EndDate = (Get-Date) 
$PolicySoftDeletes = 0; $HardDeletes = 0; $UserSoftDeletes = 0
$Records = (Search-UnifiedAuditLog -Operations "Delete Group" -StartDate $StartDate -EndDate $EndDate -ResultSize 1000)
If ($Records.Count -eq 0) {
    Write-Host "No audit records for group deletions found." }
Else {
    Write-Host "Processing" $Records.Count "team deletion audit records..."
    $Report = [System.Collections.Generic.List[Object]]::new() # Create output file 
    # Scan each audit record to extract information
    ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      $User = $AuditData.UserId.Split("_")[0]    
      $GroupName = $Auditdata.Target | ? {$_.Type -eq 1} | Select -ExpandProperty Id
          Switch ($User)
          {
            "Certificate"  { # Hard delete of a group 
                 $HardDeletes++ 
                 $Reason = "Group permanently removed" 
                 $User = $User + " (System Process)" }
            "ServicePrincipal" { #Soft delete - expiration policy 
                 $PolicySoftDeletes++
                 $Reason = "Group removed by expiration policy"
                 $User = $User + " (System Process)" }
            default { #Regular delete by a user 
                 $UserSoftDeletes++ 
                 $Reason = "User deleted group" }
          }       
          $ReportLine = [PSCustomObject] @{
           TimeStamp = Get-Date($AuditData.CreationTime) -format g
           User      = $User
           Group     = $GroupName 
           Reason    = $Reason
           Action    = $AuditData.Operation
           Status    = $AuditData.ResultStatus }        
      $Report.Add($ReportLine) }
}
Cls
Write-Host "All done - Group deletion records for the last 90 days"
Write-Host "User deletions:"     $UserSoftDeletes
Write-Host "Policy deletions:"   $PolicySoftDeletes
Write-Host "Group hard deletes:" $HardDeletes
Write-Host "----------------------"
$Report | Sort Group, Reason -Unique | Select-Object Timestamp, Group, Reason, User | Out-GridView

If you examine the results as piped through the Out-GridView cmdlet (Figure 1), you’ll see examples where a record captures a soft-delete by user or policy followed 30 days later by a permanent removal.

Once you’re happy with the data generated, it’s easy to include an extra line of code to output a CSV file using the Export-CSVFile cmdlet or use the ImportExcel module to export the data to an Excel worksheet. Once again, access to this kind of information proves that PowerShell and the audit log are a very flexible tool to understand what happens behind the scenes in Microsoft 365.

Need more insight into mining the valuable information stored the Office 365 audit log? We’ve got a complete chapter covering auditing and reporting in the Office 365 for IT Pros eBook.

Office 365 Audit Records Useful Source of Information

Recently another MVP pointed out that Office 365 Activity Alerts don’t seem to work so well. At least, he tried to set one up to alert him when someone created a new team and no alert was ever sounded.

Activity alerts depend on events logged in the Office 365 audit log. I tried to create an activity alert for new team creations and Office 365 remained mute for several days. In fact, I haven’t seen an activity alert for team creation yet. Something odd is happening on the back end because the events are in the audit log.

Script for DIY Emailed Report

In any case, I decided to roll my own activity alert by running the Search-UnifiedAuditLog cmdlet to find team creation events, parsing the AuditData content, and emailing the resulting information. Because I don’t like recreating the wheel, I combined code from Chapter 21 of the Office 365 for IT Pros eBook to parse the results returned from Search-UnifiedAuditLog and some code from one of my Petri.com articles to format and send the message. The mailbox used must be enabled to use SMTP AUTH.

The original script appeared on 30 July 2019. This is version 2 of the code and it adds some information to the emailed report such as the privacy setting for the team, its classification, the number of group members, and the number of guests. You’ll also note that I sort the audit records by team name to get one record for each team. Sometimes Office 365 creates multiple audit records when a new team is created. Remember to update the $EmailRecipient variable with a valid email address before you run the script.

# TeamsCreationReportByEmail.PS1
# A script to locate Office 365 audit records for the creation of new Teams and report the fact via email.
# V2.0 22 Oct 2019
# Uses the Exchange Online PowerShell module...
$StartDate = (Get-Date).AddDays(-90); $EndDate = (Get-Date).AddDays(1)
#HTML header with styles
$htmlhead="
     <style>
      BODY{font-family: Arial; font-size: 10pt;}
	H1{font-size: 22px;}
	H2{font-size: 18px; padding-top: 10px;}
	H3{font-size: 16px; padding-top: 8px;}
    </style>"

#Header for the message
$HtmlBody = "
     <h1>Teams Creation Report for teams created between $(Get-Date($StartDate) -format g) and $(Get-Date($EndDate) -format g)</h1>
     <p><strong>Generated:</strong> $(Get-Date -Format g)</p>  
     <h2><u>Details of Teams Created</u></h2>"
#Person to get the email
$EmailRecipient = "SomeoneinYourTenant@Tenant.com" # &lt;- Update this with the real address

If (-not $O365Cred) { #Make sure we have credentials
    $O365Cred = (Get-Credential)}
$MsgFrom = $O365Cred.UserName ; $SmtpServer = "smtp.office365.com" ; $SmtpPort = '587'

# Find records for team creation in the Office 365 audit log
Write-Host "Looking for Team Creation Audit Records..."
$Records = (Search-UnifiedAuditLog -StartDate $StartDate -EndDate $EndDate -Operations "TeamCreated" -ResultSize 1000)
If ($Records.Count -eq 0) {
    Write-Host "No Team Creation records found." }
Else {
    Write-Host "Processing" $Records.Count "audit records..."
    $Report = [System.Collections.Generic.List[Object]]::new()
    ForEach ($Rec in $Records) {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      $O365Group = (Get-UnifiedGroup -Identity $AuditData.TeamName) # Need some Office 365 Group properties
      $ReportLine = [PSCustomObject]@{
        TimeStamp      = Get-Date($AuditData.CreationTime) -format g
        User           = $AuditData.UserId
        Action         = $AuditData.Operation
        TeamName       = $AuditData.TeamName
        Privacy        = $O365Group.AccessType
        Classification = $O365Group.Classification
        MemberCount    = $O365Group.GroupMemberCount 
        GuestCount     = $O365Group.GroupExternalMemberCount
        ManagedBy      = $O365Group.ManagedBy}
     $Report.Add($ReportLine) }
}
# Add details of each team
$Report | Sort TeamName -Unique | ForEach {
    $htmlHeaderTeam = "<h2>" + $_.TeamName + "</h2>"
    $htmlline1 = "<p>Created on <b>" + $_.TimeStamp + "</b> by: " + $_.User + "</p>"
    $htmlline2 = "<p>Privacy: <b>" + $_.Privacy + "</b> Classification: <b>" + $_.Classification + "</b></p>"
    $htmlline3 = "<p>Member count: <b>" + $_.MemberCount + "</b> Guest members: <b>" + $_.GuestCount + "</b></p>"
    $htmlbody = $htmlbody + $htmlheaderTeam + $htmlline1 + $htmlline2 + $htmlline3 + "<p>"
}
# Finish up the HTML message body    
$HtmlMsg = "" + $HtmlHead + $HtmlBody
# Construct the message parameters and send it off...
 $MsgParam = @{
     To = $EmailRecipient
     From = $MsgFrom
     Subject = "Teams Creation Report"
     Body = $HtmlMsg
     SmtpServer = $SmtpServer
     Port = $SmtpPort
     Credential = $O365Cred}
Send-MailMessage @msgParam -UseSSL -BodyAsHTML ; Write-Host "Teams Creation Report sent by email to" $EmailRecipient 

Figure 1 shows what the resulting email looks like:

You can download a copy of the script from GitHub. Feel free to amend the script to meet your own requirements. Don’t forget to tell us about all the great improvements you make by posting comments here.

Discovering Who Deleted What Message and When

Last year, I wrote about how to use events recorded in the Office 365 audit log to find out who deleted a message from an Exchange Online mailbox. Time marches on and we can make some improvements to the script.

This version also uses the techniques explained in Chapter 21 of the Office 365 for IT Pros eBook to fetch audit records with PowerShell and unpack the JSON-format information included in the records to retrieve information of interest. The major changes are:

• Include processing for records deleted by users (the normal case) and administrative tasks. These include messages deleted by compliance search actions and the Search-Mailbox cmdlet.
• Because admin deletions generate a different format of audit record, we need some conditional processing to extract the desired information. Although Office 365 audit records are normalized in terms of the basic fields they contain, dealing with differences in the AuditData content is one of the frustrating parts of dealing with Office 365 audit records.
• One of the steps taken for admin deletions is to retrieve the User Principal Name for the mailbox where a message is deleted. The audit record stores a GUID to point to the mailbox, but that’s not very human-friendly.
• Include the internet message identifier in the output.
• Pipe the report to the Out-GridView cmdlet to view the data after processing the records (Figure 1). You could also pipe the data to the Export-CSV cmdlet to create a CSV file.

# Look for Hard delete and soft delete records
$Records = (Search-UnifiedAuditLog -StartDate (Get-Date).AddDays(-30) -EndDate (Get-Date).AddDays(1) -Operations "HardDelete", "SoftDelete" -ResultSize 5000)
If ($Records.Count -eq 0) {
    Write-Host "No message delete records found." }
Else {
    Write-Host "Processing" $Records.Count "audit records..."
$Report = @()
ForEach ($Rec in $Records) {
  $AuditData = ConvertFrom-Json $Rec.Auditdata
  If ($AuditData.Folder.Path -ne $Null) { $Folder = $AuditData.Folder.Path.Split("\")[1]} Else {$Folder = "Unknown"}
  If ($AuditData.LogonType -eq 1) { # Admin deleted the message
     $Mbx = Get-Mailbox -Identity $AuditData.MailboxGuid -ErrorAction SilentlyContinue
     $Msg = "No message identifier"
     $Mailbox = $Mbx.UserPrincipalName }
     Else { # User deleted the message
      $Msg =  $AuditData.AffectedItems.InternetMessageId
      $Mailbox = $AuditData.MailboxOwnerUPN } 
  $ReportLine = [PSCustomObject]@{
    TimeStamp = Get-Date($AuditData.CreationTime) -format g
    User      = $AuditData.UserId
    Action    = $AuditData.Operation
    Status    = $AuditData.ResultStatus
    Mailbox   = $Mailbox
    Items     = $AuditData.AffectedItems.Subject
    Folder    = $Folder
    MsgId     = $Msg }
  $Report += $ReportLine
}}
$Report | Out-GridView

Remember that control over the capture of audit records for message deletions depends on the audit configuration applied to Exchange Online mailboxes. If the configuration doesn’t include hard and soft deletions, you won’t see events turn up in the Office 365 audit log. In most cases, the audit configuration only captures message deletions by delegates who access shared mailboxes.

Finding a Copy of a Deleted Message

The reason to include the internet message identifier in the set of properties returned for deleted messages is that you might have a situation where multiple messages have the same subject and you can’t identify who deleted what copy of the message. The problem can be solved if the mailbox where the messages were stored is on hold. Exchange will keep a copy of the deleted message in the Recoverable Items folder. That copy is discoverable, so we can run a content search to find the message and then download it to check its properties, including the internet message identifier. It would be easier if Microsoft included details of the original message sender in the properties captured in the audit record, but that’s unlikely in the near future.

Office 365 Developers Take 237 Days to Fix Compliance Issue

In September 2018, I reported the failure of a PowerShell demo at the UK Evolve conference. The failure was not my fault (my code was, of course, immaculate). It was caused by a truncation of data in Office 365 audit records generated for group creation (the add group operation).

Bafflingly, the same code had worked perfectly during a similar demo at the European Collaboration Summit in late May. Something had changed inside Office 365 to cause the truncation. Checking the audit records in my tenant revealed that the change to the audit record structure happened between July 5 and July 11. And as it turned out, the truncation affected other group actions too, like adding a member to a group or deleting a group.

A Chat at Ignite

I reported the problem to Microsoft on September 12. Two weeks later, I met with some Exchange engineers at the Ignite conference in Orlando to review the problem. I’m not sure that Microsoft understood that a failure in audit records was a serious issue in terms of compliance, but I did my best to emphasize that it was unacceptable for audit records to be compromised. I went away from the meeting happy that the problem was understood and would be corrected.

Alas, the problem wasn’t quite as easily fixed as I anticipated. In fact, the fix only became available on May 7, some 237 days since the original problem report and 306 days since a code update introduced the issue to the Office 365 audit log. The fix also applies to the other group-associated events that were truncated before.

Holes Remain in the Office 365 Audit Log

Truncated records remain in the Office 365 audit log and will not be backfilled. This means that any group creation or update event stored in the audit log since early July 2018 is truncated. Office 365 E5 tenants have access to audit record for 365 days while the records for other tenants are cleared after 90 days. Untruncated events are available in Office 365 Cloud App Security or third-party products like Quadrotech’s Radar for Security and Audit that ingest audit data from Office 365 without going through the audit log.

Office 365 tenant administrators might not have been aware of the problem because Microsoft filtered the truncated events out from the set shown in Security and Compliance Center audit log searches. The only way to find the problematic events was with the Search-UnifiedAuditLog cmdlet. Hopefully, the fixed events will now reappear in the audit log search.

So So Slow

Since last September, I have been in contact with multiple people inside the Office 365 development group to try and advance the fix. It’s been a source of wonderment and frustration to me that Microsoft could leave an obvious gaping hole in an audit/compliance function for so long. It has not been their proudest hour.

In any case, the fix is in and truncation has stopped (at least for these records). We should be thankful for small mercies.

For more information about the Office 365 audit log and many practical examples of how to interrogate its contents, read Chapter 21 of the Office 365 for IT Pros eBook. We’ve been complaining about the truncation problem since last September because it affected one of the examples used in Chapter 21. All fixed in the next update.

Rename SharePoint Site Address Answers a Long-Overdue Customer Request

Refreshed on 14 November to reflect new UI in SharePoint Admin Center.

Site owners have been able to change many properties of their sites (like logos, display names, and so on), but they haven’t been able to change site URLs. But now, the modern SharePoint Admin Center includes the ability to update the address (URL) and display name for a site. You still can’t change the tenant’s domain name (the tenant.sharepoint.com part of the URL); only the site name part can be renamed.

SharePoint administrators can rename on-premises sites with PowerShell (here’s one example). One workaround used is to create a new site and copy everything from the old to the new. This works, but it isn’t a recommended approach when sites belong to Office 365 Groups (including Teams) because the properties of the group objects include pointers to the SharePoint sites. For example:

Get-UnifiedGroup -Identity "Marketing Gurus" | Format-List SharePoint*

SharePointSiteUrl      : https://tenant.sharepoint.com/sites/marketinggurus
SharePointDocumentsUrl : https://tenant.sharepoint.com/sites/marketinggurus/Shared Documents
SharePointNotebookUrl 

The SharePointNotebookURL is blank if the shared OneNote notebook has never been used by the group.

Renaming a site is Office 365 roadmap item 56205. It first appeared as a preview feature in May 2019. Office 365 notification MC193275 on 16 October revealed that the feature rolled out to customers in mid-October 2019.

Different Nature of SharePoint Online

All of this proves that SharePoint Online is a more complex environment than SharePoint on-premises. Apart from working inside the multi-tenant Office 365 ecosystem, SharePoint Online is a provider of document management services to other apps while on-premises SharePoint Server is the center of its own ecosystem.

Office 365 tenants have asked Microsoft to allow the rename of sites for many years. When an Office 365 group or team is created, the SharePoint site is named after the group or team. You can rename an Office 365 group or team later to reflect changing circumstances (for example, a project used to be called “Alpha Contoso” and now is “Better Products”), but you couldn’t rename the site.

Rename SharePoint Site Address – SharePoint Admin Center

To rename a site, log on as a tenant global administrator, launch the SharePoint Admin Center, go to Active Sites, and select the site you want to rename, and open the properties pane. If you see the banner in Figure 1, it means that the selected site comes within the scope of an Office 365 retention policy or eDiscovery hold. You can’t change the site URL if these conditions exist. If you decide that you really need to change the URL, you’ll have to remove the site from the policy or hold.

Click the Edit link under the URL to begin the rename process. Now overtype the current name of the site to enter a new name. SharePoint checks that the new name is available and if everything’s OK, click Save to rename the site. SharePoint also asks if you want to rename the site (to keep it aligned with the new site URLs). You don’t have to do this, but it is a good idea.

After saving the new site address, you’ll be asked if you want to update the display name for the site too. Although this isn’t mandatory, it’s wise to have the display name match the new site address.

Processing the request to update the site address takes a little time to complete and the site is locked during this period. Once done, SharePoint returns to the Active Sites list. To check that everything works as expected, you can select the site, open the properties pane, and click on the site URL. If the site is connected to an Office 365 group, you can also run the Get-UnifiedGroup cmdlet to check that the URLs are adjusted as expected.

It’s important to understand that renaming a group-connected site does not affect any of the other group properties such as its display name, alias, or email address. If you want to change these properties, do this by running the Set-UnifiedGroup cmdlet.

Sharing Links are Upgraded after Rename

Sharing links are sent by site members to share documents with other people. The sharing links contain a reference to the site. Testing reveals it takes SharePoint a couple of minutes to create a redirection site in its namespace (you see a Server 500 error during this time). Once the redirect is in place, old sharing links work and bring users to the newly renamed site. OneDrive synchronization also continues to work after site renames.

See this page to learn how to query the redirects known to SharePoint and remove them if necessary.

Rename SharePoint Site Address – PowerShell

The latest version of the PowerShell module for SharePoint Online includes the Start-SPOSiteRename cmdlet. Here’s an example of renaming a site with PowerShell:

# Rename a SharePoint Site
Start-SPOSiteRename -Identity https://tenant.sharepoint.com/sites/europeanoffice365engage -NewSiteUrl https://tenant.sharepoint.com/sites/euroOffice365Engage

Confirm
Are you sure you want to perform this action?
This operation will change the URL for site
https://tenant.sharepoint.com/sites/europeanoffice365engage to
https://tenant.sharepoint.com/sites/euroOffice365Engage. Do you want to continue? Y/N
[Y] Yes  [A] Yes to All  [N] No  [L] No to All  [S] Suspend  [?] Help (default is "Y"): y

CurrentSiteUrl : https://tenant.sharepoint.com/sites/europeanoffice365engage
NewSiteUrl     : https://tenant.sharepoint.com/sites/euroOffice365Engage
NewSiteTitle   :
RenameID       : bad6b1ae-8995-77ae-9d01-2cac19bfb7bd
State          : InProgress
TriggeredBy    : SPO-administrator@office365itpros.com

Audit Records Generated for Rename SharePoint Site Addresses

When you rename a site, SharePoint captures details of the action in a SiteRenameScheduled audit record. After a short period, the audit record is ingested into the Office 365 audit log and is available for review (Figure 3).

The Search-UnifiedAuditLog cmdlet can also be used to find these records:

# Look for SharePoint Site Rename Records
Search-UnifiedAuditLog -Operations SiteRenameScheduled -StartDate 1-May-2019 -EndDate 10-May-2019 -SessionControl ReturnLargeSet -ResultSize 5000 | Format-Table Creationdate, Operations, Userids

CreationDate        Operations          UserIds
 ------------        ----------          -------
 8 May 2019 18:08:42 SiteRenameScheduled SPO-Administrator@office365itpros.com
 3 May 2019 11:05:04 SiteRenameScheduled Jan.Smith@office365itpros.com
 2 May 2019 12:33:40 SiteRenameScheduled Alan.Smith@Office365itpros.com
 2 May 2019 12:24:58 SiteRenameScheduled Ian.Best@Office365itpros.com
 1 May 2019 13:53:57 SiteRenameScheduled Jan.Akers@office365itpros.com

The information about the site being renamed and its new name are found in the AuditData property of the audit records. This property is in JSON format and must be unpacked to extract the information. You can learn how in Chapter 21 of the Office 365 for IT Pros eBook.

Understand the Side Effects of Rename SharePoint Site Address

Before rushing to rename a site, make sure that you read and understand the side effects of the action documented by Microsoft. Some of these, like losing items in the site recycle bin, are quite destructive.

Update: January 2, 2020: Renaming a site used to have an effect on the connection between Teams (via the Files channel tab) and SharePoint. Microsoft has fixed the problem and you shouldn’t have any problems with Teams now.

One side effect that isn’t documented is that if you have explicitly included or excluded a site in an Office 365 retention policy or eDiscovery hold, you should remove the site reference from the policy or hold before you update the URL. The reason is that the URLs of included or excluded sites are stored in the properties of the policy or hold. If you rename the site, the properties of the policy or hold are not changed to reflect the new URL, which then causes errors when Office 365 tries to apply the policy or hold against the old URL.

We cover SharePoint Online in Chapter 8 of the Office 365 for IT Pros eBook. We also cover a lot of PowerShell for Office 365 Groups and Teams in Chapter 14!

Teams Writes Items into Mailboxes

As I am sure everyone is now well aware, Teams creates items in group and personal mailboxes to capture compliance records for channel and personal conversations. The mailbox items are not perfect copies of Teams messages (they don’t record likes, for instance), but they are indexed and discoverable by Office 365 content searches and therefore useful for compliance purposes.

Searching the Office 365 Audit Log

Poking around in the nether reaches of Office 365 is kind of normal for the Office 365 for IT Pros writing team, and this week we noticed that the Office 365 audit log includes records for the capture of Teams compliance records. However, only records for messages posted to channel conversations by tenant users are captured in the audit log. Audit records for messages posted to personal chats or those posted by guest users are not captured. Capturing of these records happened over 90 days in the past, which is all you can go back in the Office 365 audit log unless you have an E5 license and your tenant has been upgraded to 365-day retention.

If you want to check the audit records on your tenant, use the Audit log search in the Security and Compliance Center or run the PowerShell command (adjust the dates to stay within the 90-day range) shown below:

$Records = Search-UnifiedAuditLog  -StartDate 21-Nov-2018 -EndDate 20-Feb-2019 -operations "Create" -resultsize 5000 | Format-Table CreationDate, Operations, UserIds

Among the records, you’ll probably see some for the special user S-1-5-18. These records capture the introductory message posted in the General channel when a new team is created.

Interpreting Audit Records for Teams Messages

Taking a technique explored in Chapter 21 of the Office 365 for IT Pros eBook to expand the content of the AuditData property of audit records, we can interpret the records we find (here’s another example). In this case, we can generate a quick count of messages posted to the teams in the tenant.

If ($Records.Count -eq 0) {
   Write-Host "No audit records records found." }
 Else {
   Write-Host "Processing" $Records.Count "audit records..."
   $Report = @()
   ForEach ($Rec in $Records) {
      If ($Rec.Operations -eq "Create") {
      $AuditData = ConvertFrom-Json $Rec.Auditdata
      $ReportLine = [PSCustomObject]@{
           TimeStamp   = $Rec.CreationDate
           User        = $AuditData.UserId
           Action      = $AuditData.Operation
           Team        = $AuditData.MailboxOwnerUPN
           Subject     = $AuditData.Item.Subject
           MessageId   = $AuditData.Item.InternetMessageId}
      $Report += $ReportLine
  }}}
$GroupData = $Report | Group-Object -Property Team
$GroupData | Sort Count -Descending | Select Name, Count

Name                                               Count
----                                               -----
Office365ITPros@Office365ITPros.com                  192
ExchangeMVPs@office365itpros.com                     130
Audie-tronadmirers@office365itpros.com               128
JapanRugbyWorldCup2019@office365itpros.com           112
Volleyball@office365itpros.com                       110
...

Remember that this count is inaccurate. It doesn’t include any messages posted to Teams by guest users, nor does it capture anything for messages posted to Teams channels via connectors or bots. However, knowing about these records and how to interpret them might come in handy as a way of looking at Teams activity.

To look at the data a different way, if you wanted to find out who is the most prolific poster to Teams, change the Group-Object command to:

$GroupData = $Report | Group-Object -Property User
$GroupData | Sort Count -Descending | Select Name, Count

Name                                Count
----                                -----
Tony.Redmond@office365itpros.com      155
Jeff.Guillet@office365itpros.com      122
S-1-5-18                               11
Kim.Akers@office365itpros.com          10
...

The interesting thing is that we can conclude that 11 new teams were created in the period because that’s the count of messages created by S-1-5-18!

Interesting though this little discovery is to the true Office 365 nerd, it’s probably not going to feature in the Office 365 for IT Pros eBook. We do have some standards!

Cloud App Security Alerts Join the Office 365 Audit Data

Office 365 keeps on changing. The recent announcement that Microsoft is surfacing Office 365 Cloud App Security alerts through extra interfaces is an example of a change that might be considered small, unless you work in the area of security and compliance.

One thing that attracted my attention is the fact that Office 365 Cloud App Security (bundled with E5 licenses) now sends its alerts to the Office 365 audit log. This makes sense because Office 365 alerts and alert policies are powered by the events captured in the audit log.

Analyzing Cloud App Security Audit Records

In any case, events in the audit log show up with RecordType SecurityComplianceAlerts. Like all events in the audit log, some work is needed to unpack and interpret the information stored in the AuditData property. I used some code from Chapter 21 of the Office 365 for IT Pros eBook to examine what useful material is included by running the Search-UnifiedAuditLog cmdlet to retrieve the records.

Office 365 audit log records are normalized, but only to a point. Normalization means that a set of the same basic fields are included in all records, no matter what workload generates a record. The devil in the detail is that the contents of the AuditData property is open to interpretation and each workload can do its own thing in terms of what is output. And in the case of Cloud App Security, the contents of AuditData vary depending on an alert.

The upshot is that more work than should be necessary is needed to parse the data to make it useful for reporting and analysis. I only found two types of alerts generated by Cloud App Security, so that’s what the code below deals with. You might find others and need to update the code to handle whatever Microsoft decided to stuff into AuditData for the alert.

$Records = (Search-UnifiedAuditLog -RecordType
SecurityComplianceAlerts -StartDate 1-Jan-2019 -EndDate 30-Jan-2019 -Formatted
-ResultSize 3000)
If ($Records.Count -eq 0) {
   Write-Host "No alert audit records found." }
Else {
   Write-Host "Processing" $Records.Count "audit records..."
$Report = @()
ForEach ($Rec in $Records) {
   $AuditData = ConvertFrom-Json $Rec.Auditdata
   $Data = ConvertFrom-Json $Auditdata.data
   If ($Rec.Operations -eq "AlertTriggered") {
      $ReportLine = [PSCustomObject]@{
           TimeStamp; = $Rec.CreationDate
           User        = $Data.f3u
           Action      = $Data.an
           Status      = $AuditData.ResultStatus
           Severity    = $AuditData.Severity
           Workload    = $AuditData.Source
           Operation   = $Rec.Operations
           Category    = $AuditData.Category }
      $Report += $ReportLine}
    Else {
      $ReportLine = [PSCustomObject]@{
           TimeStamp   = $Rec.CreationDate
           User        = $Data.eid
           Action      = $Data.lon
           Status      = $AuditData.ResultStatus
           Severity    = $AuditData.Severity
           Workload    = $AuditData.Source
           Operation   = $Rec.Operations
           Category    = $AuditData.Category }
        $Report += $ReportLine}
  }} 

$Report | Select Timestamp, Operation, User, Action
Processing 42 audit records...

TimeStamp            Operation            User          Action
---------            ---------            ----          ------
21 Jan 2019 16:58:00 AlertEntityGenerated Tony.Redmond@ eDiscoverySearchStartedOrExported
21 Jan 2019 16:58:00 AlertTriggered       Tony.Redmond@ eDiscovery search started or exported
2 Jan 2019 19:54:00  AlertTriggered       Tony.Redmond@ eDiscovery search started or exported
…

It’s worth pointing out that some of the alerts that flow into the audit log duplicate events already logged by a workload, which is certainly the case for the eDiscovery searches featured above.

Always Tracking New Developments

We’ll continue to track what happens as Microsoft releases the other updates mentioned in their post and update whatever we need to in the Office 365 for IT Pros eBook. Keeping up-to-date with developments inside Office 365 is what we do!