To fetch the activity logs of a particular SharePoint Online site such as how many users viewed or downloaded a file in the document libraries or visited a page and visualise the data in one place, we can use the Office 365 Management Activity API in Microsoft Power Automate to retrieve the activity logs. We can then use the actions and events from the Office 365 activity logs to create solutions that provide monitoring, analysis, and data visualization.
The Office 365 Management Activity API is a REST web service that relies on Azure AD and the OAuth2 protocol for authentication and authorization. To access the API, we will need to first register an application in Azure AD and configure it with appropriate permissions. This will enable us to request the OAuth2 access tokens it needs to call the API.
The diagram below shows the end to end solution.
Prerequisite
Register an Azure AD app with 'ActivityFeed.Read' permission and obtain the app credentials
Workflow
- Using the obtained application credentials (Client ID and Client Secret), we will use Power Automate to prove its identity when requesting for an access token
- After receiving the access token, we will use it to make the API call to fetch only the SharePoint audit logs from the previous day
- Filter out only the required SharePoint site logs from the full SharePoint workload
- Filter out only the events you want to visualise
- Store the information into a SharePoint Online list
- Finally, visualise the data in Power BI from the SharePoint Online list
Comments