Schedule SharePoint Site audit report using Microsoft Power Automate + o365 Management activity API

In my previous blog saw how to generate audit report for SharePoint online site using O365 unified audit logs. But sometimes due to governance restriction it’s not possible to provide access to site collection administrators in o365 security & compliance center portal for them to generate this audit report.

Most of our business stakeholders like to have an automatic audit report of their site to be sent to their email with and attachment in monthly or weekly fashion for their audit purpose.

What do we need?

  • Office 365 Management Activity API
  • Microsoft Power Automate

Step 1: Enable Audit log

Hope you we have enabled audit log search in our o365 security & compliance center portal. Follow our previous blog.

Step 2: Create a Power Automate Flow

Honestly its very easy as I reused this available OOTB flow template. Go and search “audit logs” in the power automate you will see as below, start it and we will customize as per our requirement then

You will be redirected to this page to show the flow connections where Microsoft will by default authenticate with the available account if you don’t need to you can choose your preferred login permissions.

Once permissions as validated click “continue” it will take us to the real flow page where it shows all the individual steps in it as below. These are the default steps available the OOTB flow template.

Step 3: Alter the “Iterate through list” step

Expand the “Iterate through list” step. This default step is like a for loop, it iterates through the SharePoint list items which we don’t need for our requirement now. So literally drag each of the steps out of this step “Iterate through list” step. Just mouse over on the border of each box you will see drag symbol.

When try dragging it will pop you with a warning like this, it’s because “Title” used inside the “Compose URL” step lets go and delete it. And then move all steps outside.

After moving all steps outside then delete the “Iterate through list” step and the “Get items” step. Then the flow steps will look like this.

Note: While moving the steps out please keep the same sequence, don’t alter.

Step 3: Create a CSV/Excel

Just before “Send an email” step add a new action/step. In choose action search for “Create CSV” it will populate the related available templates. Select “Create CSV Table” as below

Add the required header and values you want in your CSV report file

Step 4: Add CSV to send mail attachment

Go to the “Send an email” step. Add name of your report file as “SharePointSiteAuditLogReport.csv” and in attachments content field add “Ouput” (Output of Create CSV file)

Step 5: Update the URL of Site

We are in the final step now, we need to update the o365 Management Activity API but before that lets initiate the site url by creating this “Initialize Site variable” step just above the compose URL step. In the value field add your SharePoint Online site for which you need the report.

In Compose URL update the “Inputs” field as below

In http action update with your username and password in it which has the required permissions to o365 management log API.

Step 6: Schedule & Test

We are ready with our custom flow to send automatic audit report to my email id.

So, cool I received a mail with my SharePoint site audit report attached in it.

Thanks for reading my blog. #sharingiscaring.