Retain SharePoint Online Logs

Retaining SharePoint Logs for over 20 Years

Recently, I worked with a client who required the retention of SharePoint Online audit logs for over 20 years. In this article, I documented a solution to this problem.

Why not use the out-of-the-box Unified Microsoft Logs?

Microsoft 365 Unified Log can retain the events for up to 10 years. While this is an impressive period, it does not meet our requirement of 20+ year retention.

TLDR Solution: Deploy Sentinel

Deploy Microsoft Sentinel and funnel the logs to Azure Blob Storage. Free Sentinel trial is available for 31 days. To calculate the price, use the Sentinel Price Calculator 📱

This is a high-level list of steps that are involved in the solution:

• Deploy Microsoft Sentinel and ensure that the Microsoft 365 Data connector is enabled in Sentinel. Use the Sentinel all-in-one deployment script
• Create a Summary rule to filter the needed SharePoint activities from the OfficeActivity table. Data will be pushed to a custom Log Analytics table.
• Configure an automated Data Export from the custom Log Analytics table or a custom table to Azure Storage Account.

Architecture

This is the high-level architecture that shows all important components:

Step 1: an Azure Storage Account

• Create a new Azure storage account. It will be used for storing exported logs.

Select the cheapest tier and the lowest redundancy option for cost savings.

Step 2: Deploy Microsoft Sentinel

Microsoft Sentinel is an Azure-based security information and event management (SIEM) service. Among other things, Microsoft Sentinel can funnel all events from Microsoft 365 and push them to Azure Monitor Log Analytics workspace. Microsoft Sentinel is billed for the volume of data stored in a Log Analytics workspace and analyzed in Microsoft Sentinel.

• Use the Sentinel all-in-one deployment script
• Navigate to Microsoft Sentinel > Data Connectors
• Confirm Microsoft 365 Data Connector is present in the list of connectors:

• Navigate to Log Analytics Workspace > Logs
• Run the following query:

OfficeActivity

• Confirm the query returns some data.

💡It might take 5-20 minutes for the OfficeActivities table to start populating. If you are working on a test tenant, you might want to click on some files in SharePoint to make sure some if your activity is captured.

Step 3: Create a Summary Rule

• Navigate to Microsoft Sentinel > Summary rules > Create

  

• Specify the rule name. For example, SharePointFileActivitiesPlusSharing
• Specify the new custom log table. For example, SharePointFileActivitiesPlusSharing

  

• Specify the rule query, for example:

// Files and Sharing, ALL IN ONE
OfficeActivity
| where OfficeWorkload in ("OneDrive", "SharePoint")
| where Operation in ("FileAccessed", "FileCopied", "FileDeleted", "FileDeletedFirstStageRecycleBin", 
                      "FileDeletedSecondStageRecycleBin", "FileDownloaded", "FileModified", "FileMoved", 
                      "FileVersionsAllMinorsRecycled", "FileVersionsAllRecycled", "FileVersionRecycled", 
                      "FileRenamed", "FileUploaded",
                      "SharingSet", "SecureLinkCreated", "SecureLinkUsed", "AddedToSecureLink", "CompanyLinkCreated", 
                      "AnonymousLinkCreated", "AnonymousLinkUpdated", "SharingInvitationCreated", "SharingInvitationUpdated", 
                      "CompanyLinkUsed" )
| where UserId != "app@sharepoint"
| where SourceFileName != "AllItems.aspx"
| project TimeGenerated, DestinationFileName, Operation, 
    UserId,    
    ClientIP, 
    Site_Url,
    SourceFileName,
    ItemType,
    TargetUserOrGroupName,
    TargetUserOrGroupType,
    OfficeObjectId,
    UserAgent

This query will filter out specific events from the OfficeActivity table into a dynamic table. Keep reading for more details about this particular query.

• Run Summary every 20-60 minutes.

Summary Rule wizard result:

• Navigate to Log Analytics workspace > Tables and look for the new table called SharePointFileActivitiesPlusSharing_CL. In your case, the name can be different.

Summary Rule KQL Queries

It will be expensive to retain all SharePoint activities. This is why I recommend only retaining the activities you absolutely need. Use the official list of Audit activities as a reference. For example, I recommend the following operations:

Sample KQL query that retrieves file-related events only:

OfficeActivity
| where OfficeWorkload in ("OneDrive", "SharePoint") and Operation in (
    "FileAccessed", "FileCopied", "FileDeleted", "FileDeletedFirstStageRecycleBin", "FileDeletedSecondStageRecycleBin", "FileDownloaded", "FileModified", "FileMoved","FileVersionsAllMinorsRecycled", "FileVersionsAllRecycled", "FileVersionRecycled", "FileRenamed", "FileUploaded" )

Sharing and access request activities

The following table describes the user sharing and access request activities in SharePoint Online and OneDrive for Business. For more information, see Use sharing auditing in the Office 365 audit log.

💡Users can be either members or guests based on the UserType property of the user object. A member is usually an employee, and a guest is usually a collaborator outside of your organization. When a user accepts a sharing invitation (and isn’t already part of your organization), a guest account is created for them in your organization’s directory. Once the guest user has an account in your directory, resources may be shared directly with them (without requiring an invitation).

SharePoint List Activities

The following table describes how users interact with list items in SharePoint Online.

Columns to Retain

I recommend retaining only the most important columns to save on the data transfer and storage costs. For example:

• TimeGenerated
• DestinationFileName
• Operation
• UserId
• ClientIP
• Site_Url
• SourceFileName
• ItemType
• TargetUserOrGroupName
• TargetUserOrGroupType
• OfficeObjectId
• UserAgent

⭐ Recommended KQL for the Summary Rules

Considering all of the above, I came up with this KQL query. It filters out all important file-related and sharing-related events:

// Files and Sharing, ALL IN ONE
OfficeActivity
| where OfficeWorkload in ("OneDrive", "SharePoint")
| where Operation in ("FileAccessed", "FileCopied", "FileDeleted", "FileDeletedFirstStageRecycleBin", 
                      "FileDeletedSecondStageRecycleBin", "FileDownloaded", "FileModified", "FileMoved", 
                      "FileVersionsAllMinorsRecycled", "FileVersionsAllRecycled", "FileVersionRecycled", 
                      "FileRenamed", "FileUploaded",
                      "SharingSet", "SecureLinkCreated", "SecureLinkUsed", "AddedToSecureLink", "CompanyLinkCreated", 
                      "AnonymousLinkCreated", "AnonymousLinkUpdated", "SharingInvitationCreated", "SharingInvitationUpdated", 
                      "CompanyLinkUsed" )
| where UserId != "app@sharepoint"
| where SourceFileName != "AllItems.aspx"
| project TimeGenerated, DestinationFileName, Operation, 
    UserId,    
    ClientIP, 
    Site_Url,
    SourceFileName,
    ItemType,
    TargetUserOrGroupName,
    TargetUserOrGroupType,
    OfficeObjectId,
    UserAgent

💡 Audit records for some SharePoint activities indicate the app@sharepoint account. I prefer filtering these out.

Step 5: Create Data Export

Finally, we want to export the logs from the custom SharePointFileActivitiesPlusSharing_CL table and push them to the Azure Blob Storage.

• Navigate to Log Analytics Workspace > Data export > New export rule

  

• Give the export rule a name. For example, SharePointFileActivitiesPlusSharing

• Pick the source table. In our case it’s SharePointFileActivitiesPlusSharing_CL

• Select the destination subscription and the storage account

Viewing the logs

The exported logs are stored in the am-sharepointfileactivitiesplussharing-cl container. In your case, the container name will be different.

The logs are stored in the .JSON files organized in the deep folder structure. For example:

Tam-sharepointfileactivitiesplussharing-cl / WorkspaceResourceId= / subscriptions / 591f62bc-9c19-472e-9039-60a78f976abb / resourcegroups / sentinel-all-in-one / providers / microsoft.operationalinsights / workspaces / sentinel-all-in-one-workspace / y=2025 / m=01 / d=20

Sample JSON log file:

{ "_BinSize": 20, "_BinStartTime": "2025-01-20T15:40:00.0000000Z", "_RuleLastModifiedTime": "2025-01-20T04:49:50.3950041Z", "_RuleName": "SharePointFileActivitiesPlusSharing", "_OriginalTimeGenerated": "2025-01-20T15:45:46.0000000Z", "Operation": "FileModified", "UserId": "denis@contoso.com", "ClientIP": "2607:fea8:879f:9a10:3184:a800:6b7f:35b1", "Site_Url": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/", "SourceFileName": "Howard the Cat.one", "ItemType": "File", "OfficeObjectId": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/Documents/Personal/8_Pets, Nature and - Science/Howard the Cat.one", "UserAgent": "Microsoft Office OneNote/16.0.18324.20092 (Windows/10.0; Desktop x64; en-US; Desktop app; ASUS/System Product Name)", "TimeGenerated": "2025-01-20T16:03:47.9818100Z", "TenantId": "aa230883-692c-4119-b714-29e622f39b1f", "_ItemId": "21d1504c-d748-11ef-b8e1-000d3a841abf", "_Internal_WorkspaceResourceId": "/subscriptions/591f62bc-9c19-472e-9039-60a78f976abb/resourcegroups/sentinel-all-in-one/providers/microsoft.operationalinsights/workspaces/sentinel-all-in-one-workspace", "Type": "SharePointFileActivitiesPlusSharing_CL"}
{ "_BinSize": 20, "_BinStartTime": "2025-01-20T15:40:00.0000000Z", "_RuleLastModifiedTime": "2025-01-20T04:49:50.3950041Z", "_RuleName": "SharePointFileActivitiesPlusSharing", "_OriginalTimeGenerated": "2025-01-20T15:49:46.0000000Z", "Operation": "FileAccessed", "UserId": "denis@contoso.com", "ClientIP": "2607:fea8:879f:9a10:3184:a800:6b7f:35b1", "Site_Url": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/", "SourceFileName": "Howard the Cat.one", "ItemType": "File", "OfficeObjectId": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/Documents/Personal/8_Pets, Nature and - Science/Howard the Cat.one", "UserAgent": "Microsoft Office OneNote/16.0.18324.20092 (Windows/10.0; Desktop x64; en-US; Desktop app; ASUS/System Product Name)", "TimeGenerated": "2025-01-20T16:03:47.9818100Z", "TenantId": "aa230883-692c-4119-b714-29e622f39b1f", "_ItemId": "21d15050-d748-11ef-b8e1-000d3a841abf", "_Internal_WorkspaceResourceId": "/subscriptions/591f62bc-9c19-472e-9039-60a78f976abb/resourcegroups/sentinel-all-in-one/providers/microsoft.operationalinsights/workspaces/sentinel-all-in-one-workspace", "Type": "SharePointFileActivitiesPlusSharing_CL"}
{ "_BinSize": 20, "_BinStartTime": "2025-01-20T15:40:00.0000000Z", "_RuleLastModifiedTime": "2025-01-20T04:49:50.3950041Z", "_RuleName": "SharePointFileActivitiesPlusSharing", "_OriginalTimeGenerated": "2025-01-20T15:52:20.0000000Z", "Operation": "FileModified", "UserId": "denis@contoso.com", "ClientIP": "2607:fea8:879f:9a10:3184:a800:6b7f:35b1", "Site_Url": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/", "SourceFileName": "Howard the Cat.one", "ItemType": "File", "OfficeObjectId": "https://contoso-my.sharepoint.com/personal/denis_contoso_com/Documents/Personal/8_Pets, Nature and - Science/Howard the Cat.one", "UserAgent": "Microsoft Office OneNote/16.0.18324.20092 (Windows/10.0; Desktop x64; en-US; Desktop app; ASUS/System Product Name)", "TimeGenerated": "2025-01-20T16:03:47.9818100Z", "TenantId": "aa230883-692c-4119-b714-29e622f39b1f", "_ItemId": "21d15051-d748-11ef-b8e1-000d3a841abf", "_Internal_WorkspaceResourceId": "/subscriptions/591f62bc-9c19-472e-9039-60a78f976abb/resourcegroups/sentinel-all-in-one/providers/microsoft.operationalinsights/workspaces/sentinel-all-in-one-workspace", "Type": "SharePointFileActivitiesPlusSharing_CL"}

Conclusion

If you are still reading this, congratulations. You now know how to retain SharePoint Online Logs for an extended period.