Read Retained SharePoint Online Logs from Blob Storage

This is the second part of the ‘Retain SharePoint Online Logs’ article. I recommend reading first, before you dive into this article.

Problem

If you are using Sentinel to capture Microsoft 365 logs and Data Exports to export data the Blob storage, you will end up having thousands of PT5M.json files in various folders. For example:

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"}

As you can see, this is a multiline JSON format.

To query a single PT5M.json file I recommend using the “Query” feature available in your Azure Log Analytics:

externaldata (
    _BinSize:int,
    _BinStartTime:datetime ,
    _RuleLastModifiedTime:datetime,
    _RuleName:string,
    _OriginalTimeGenerated:datetime ,
    TimeGenerated:datetime, 
    DestinationRelativeUrl:string,
    DestinationFileName:string,
    Operation:string, 
    UserId:string,        
    Site_Url:string,
    SourceRelativeUrl:string,
    SourceFileName:string,
    _ItemId:string,
    _Internal_WorkspaceResourceId:string,
    Type:string
    )[
    @"https://hierarchical00storage.blob.core.windows.net/am-officeactivitiesfilesfolders-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=02/d=09/h=18/m=30/PT5M.json?sv=2022-11-02&ss=bfqt&srt=sco&sp=rwdlacupyx&se=2025-08-29T09:48:01Z&st=2025-02-10T02:48:01Z&spr=https&sig=<SAS_TOKEN_GOES_HERE>"
     ] 
    with (  format = "multijson"  );

Make sure to replace the path to the .json file with your file.

If you don’t yet have a SAS, token, create it:

At first, this approach might feel appealing, but in reality it won’t work if you need to query many PT5M.JSON files at once.

To query multiple PTM5M.JSON files, you will have to make sure you your Azure Storage account is using the hierarchical namespace:

Next, you’ll have to create the Azure Data Explorer resource.

Choose the workload. I recommend using Dev/Test for quick tests and cost savings:

Once the Azure Data Explorer is provisioned, create a new database:

Next, create an external table:

Select your blob storage as the source:

Grant the permission to query external tables:

Review the final query:

This is a sample query that created my external table. In your case, the query will be different:

.create external table ['February2025Events'] (['_BinSize']:int,['_BinStartTime']:datetime,['_RuleLastModifiedTime']:datetime,['_RuleName']:string,['_OriginalTimeGenerated']:datetime,['Operation']:string,['UserId']:string,['Site_Url']:string,['SourceRelativeUrl']:string,['SourceFileName']:string,['TimeGenerated']:datetime,['TenantId']:guid,['_ItemId']:guid,['_Internal_WorkspaceResourceId']:string,['Type']:string)
    kind = adl
    dataformat = multijson
    (
        h@'abfss://am-officeactivitiesfilesfolders-cl@hierarchical00storage.dfs.core.windows.net/WorkspaceResourceId=/subscriptions/591f62bc-9c19-472e-9039-60a78f976abb/resourcegroups/sentinel-all-in-one/providers/microsoft.operationalinsights/workspaces/sentinel-all-in-one-workspace/y=2025/m=02/d=10;impersonate'
    )
    with (FileExtension=json)

If you have too many .json files, I recommend limiting the folder path to a specific year, month and day:

Query the external table

To query your external tale use the following syntax:

external_table("<NAME_OF_THE_EXTERNAL_TABLE>")

Sample:

Cost of running Azure Data Explorer

The Azure Data Explorer might be expensive to host if you are only using it occasionally. Be mindful of the costs. Even the Dev Tier will cost you over $100 USD per month.

Conclusion

Using Azure Data Explorer is really the only way to query multiple JSON files from Blob Storage in a manageable way. While it might seem like overkill at first, it’s the tool that lets you effectively run queries across thousands of PT5M.json files in your storage account. The best part? You don’t have to keep it running all the time. Simply provision it when you need to read the logs, and delete it when you’re done to save on costs.

So, if you have a ton of SharePoint Online logs and need to analyze them efficiently, Azure Data Explorer is your go-to solution. Happy querying!