Summary

The following are the customer concerns with the SharePoint sites on Microsoft 365 cloud regarding audit reports.

• SPO site collection admins do not receive the same GUI presentation for site audit reports that were available on the SharePoint on-premises.
• Currently, the reports are available only in the CSV files.
• Currently, O365Tenant admins must run the report to get the CSV files

As per the customer, in the SharePoint on-premises SCA could set the following audit options and pull it from the Site Settings. The same is not possible in SPO.

• Opening or downloading documents, viewing items in lists, viewing item properties
• Editing items, Checking out or checking in items, Moving or copying items to another location on the site, Deleting or restoring items
• Editing content types or columns, Searching site content, Editing users and permissions

Step by Step Solution

The SharePoint online workload in Microsoft 365 tracks audit logs data at the tenant level. The tenant administrator can get these data (in CSV format) from the compliance center portal, which is a manual process for the admin.

To automate the process Office 365 Management API can be used to get these audit data. There are two types of Office 365 Management API?

Office 365 Service Communications API, which can do the following:

• Get Services: Get the list of subscribed services.
• Get Current Status: Get a real-time view of current and ongoing service incidents.
• Get Historical Status: Get a historical view of service incidents.
• Get Messages: Find Incident and Message Center communications.

Office 365 Management Activity API, which can do the following.

• Use the Office 365 Management Activity API to retrieve information about the user, admin, system, and policy actions and events from Office 365 and Azure AD activity logs.
• Audit and activity logs to create solutions that provide monitoring, analysis, and data visualization.

The Office 365 Management Activity API allows for pulling of audit logs for the following workloads.

1. Audit.AzureActiveDirectory
2. Audit.Exchange
3. Audit.SharePoint
4. Audit.General (includes all other workloads not included in the previous content types)
5. DLP.All (DLP events only for all workloads)

Step # 1 In Compliance Center turn on auditing

• Go to https://compliance.microsoft.com and sign in.
• In the left navigation pane of the Microsoft 365 compliance center, click Audit.
• If auditing is not turned on for your organization, a banner is displayed prompting you to start recording user and admin activity.
• Click the Start recording user and admin activity banner.
• It may take up to 60 minutes for the change to take effect.

Step # 2 Register an Azure AD App

To register the Azure AD application you can follow this step.

Add and grant consent to the following Application permissions.
– Office 365 Management API
1. ActivityFeed.Read
2. ActivityFeed.ReadDlp
3. ServiceHealth.Read

Step # 3 Start a subscription for a workload(s)

Prior to making this call you will need to get the Access Token by using the Azure AD app. The following call will add Audit.SharePoint, you can add more workloads.

*** REQUEST TO BE MADE ***
POST {root}/subscriptions/start?contentType=Audit.SharePoint&PublisherIdentifier={TenantGUID}
Content-Type: application/json; utf-8
Authorization: Bearer eyJ0e...Qa6wg

RESPONSE
HTTP/1.1 200 OK
Content-Type: application/json; charset=utf-8
{
    "contentType": "Audit.SharePoint",
    "status": "enabled",
}

Step # 4 Activity API operations

After one time subscription, you can make a call to the feed API to get audit information. The subscription can be stopped as well.

*** MAKE a Call to get the list of Audit reports ***
*** StartTime and End Time must not exceed 24 hours ***
*** The times must be in the form of yyyy-MM-ddTHH:mm:ss.fffZ ***
*** PowerShell $startTimeDt.ToString("yyyy-MM-ddTHH:mm:ss.fffZ")" ***

REQUEST

GET https://manage.office.com/api/v1.0/{{TenantID}}/activity/feed/subscriptions/content?
startTime={{startTime}}&
endTime={{endTime}}&
contentType={{contentType}}&
PublisherIdentifier={{TenantID}}

RESPONSE 

[
    {
        "contentUri": "https://manage.office.com/api/v1.0/b07282ed-2513-42ff-8322-de55ebce98f1/activity/feed/audit/20220404172343761148151$20220405052516832093374$audit_sharepoint$Audit_SharePoint$na0034",
        "contentId": "20220404172343761148151$20220405052516832093374$audit_sharepoint$Audit_SharePoint$na0034",
        "contentType": "Audit.SharePoint",
        "contentCreated": "2022-04-05T05:25:16.832Z",
        "contentExpiration": "2022-04-11T17:23:43.761Z"
    },
    {
.... removed for brevity
]

Step # 5 Using the above response for each “contentUri” make another get call to get the JSON response

As you can see from the response there are multiple data in the JSON response. These data can be different based on what content type is subscribed. Here is the schema for all response values.

 {
        "AppAccessContext": {
            "AADSessionId": "5e5d69ef-a701-4ff7-9068-adc9eaa444ba",
            "CorrelationId": "ff8230a0-20f5-c000-c88a-0b7be49d5f5b",
            "UniqueTokenId": "bflaGhwgWUuOpcy6h_cdAA"
        },
        "CreationTime": "2022-04-04T19:17:56",
        "Id": "aa088334-d15b-413b-3ed1-08da166fd2f6",
        "Operation": "FileAccessed",
        "OrganizationId": "b07282ed-2513-42ff-8322-de55ebce98f1",
        "RecordType": 6,
        "UserKey": "i:0h.f|membership|sdasdasd@live.com",
        "UserType": 0,
        "Version": 1,
        "Workload": "SharePoint",
        "ClientIP": "255.255.23.12",
        "ObjectId": "https://somesite.sharepoint.com/sites/appcatalog/clientsideassets/c40b89d1-d05f-4623-b02e-b78276a050d2/navigation-panel-application-customizer_039e677240705a0c1bbc4023a93bf51e.js",
        "UserId": "bobk@somewhere.com",
        "CorrelationId": "ff8230a0-20f5-c000-c88a-0b7be49d5f5b",
        "CustomUniqueId": false,
        "EventSource": "SharePoint",
        "ItemType": "File",
        "ListId": "300cfa22-0c1e-411b-a10e-7d6b81978e76",
        "ListItemUniqueId": "a25248f3-f04d-4442-b184-accadd27335c",
        "Site": "7b8c0cf1-acef-4e86-b3dc-009f68708b39",
        "UserAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.74 Safari/537.36 Edg/99.0.1150.46",
        "WebId": "aaae54a6-b040-43b4-965f-55aaa97b95df",
        "SourceFileExtension": "js",
        "SiteUrl": "https://somesite.sharepoint.com/sites/AppCatalog/",
        "SourceFileName": "navigation-panel-application-customizer_039e677240705a0c1bbc4023a93bf51e.js",
        "SourceRelativeUrl": "ClientSideAssets/c40b89d1-d05f-4623-b02e-b78276a050d2"
    }

Automating the above steps

After the above steps you should be able to see the audit data. But these are all manual steps to get the content. To automate these you will need to use the Azure resources such as Azure Storage, Azure Functions, and Azure Cosmos DB. The following is the logical architecture that depicts those Azure resources.

Service Type	Description
Azure Cosmos DB	To store the Audit Data from Store Events Azure Function
Azure Service Bus	To queue messages from the Queue Events Azure Function
Azure Storage	Optionally you could use Azure Queue instead of Azure Service Bus.
Azure Function	The Azure Function compute resources are where the Queue Event and Store Events will run
Azure Application Insights	This is part of the Azure Monitor required for the Azure Function monitoring.
Power BI Premium	The Power BI Premium license is required for the dashboard.

To create the above resources in your Azure demo tenant you can use this ARM template.

The Queue Events Azure Function Code is located here.

The Store Events Azure Function Code is located here.

Once the data is gathered in the Azure Cosmos DB, the Power BI can generate a report similar to the following. The report shown is a basic example but once the data is collected to the Azure Cosmos DB you can create more such reports to meet your audit need.

The report can answer the following questions:

Who did an operation to the File, List, or Library?

When the operation was performed?

What operation was performed?

What sites are used?

The Power BI PBX file is located here.

Conclusion

The above solution is a proof of concept (POC) for SharePoint or OneDrive data. There are other workloads audit data that can collect such as Exchange, DLP, Azure AD, etc.

Reference:

Using the Office 365 Management Activity API and Power BI for security analysis (Part 1)