For creating custom reports on Office 365 content, the best approach is to fetch the Audit data from Office 365 Management Audit log, store it in a custom database and then create reports through it. In an earlier blog here, we looked at steps to retrieve Office 365 Audit log data using PowerShell. In this blog, we look at a similar process to gather audit data by using Office 365 Management API in Azure Functions.

Source CodeGithub Repo

Update 17 Aug 2019 – The code shared below gives a snippet of capturing Audit log data and is not a complete solution. For a complete solution, please check this github repo here.

Some of the key features of solution are as follows:
1. Azure Function 1.x – The solution repo uses Azure Function 1.x but could be upgraded to Azure Function 2.x. In case of using SharePoint Online CSOM, the solution might need Azure Function 1.x.
2. Microsoft.Azure.Storage.Common and WindowsAzure.Storage for using Azure Table operations
3. Newtonsoft.Json > 10.0.0.0 and SharePointOnlinePnPCore for using SharePoint Online CSOM

PS: There is another blog upcoming with more details about the set up required for starting to capture Audit log.

Steps:

To start with, we will create an Azure AD app to connect to the Office 365 Audit log data store. Even though it might sound difficult, creating the Azure AD app is quite easy and simple. It is as simple as going to the Azure AD. Here is a quick blog with steps for the same.

After the Azure AD app is created, we will create an Azure Function (with Function code Authentication) to pull the data from Office 365 Azure Content blob, for doing that we will need to subscribe to the service first.

There are few prerequisites for setting up the Azure content blob service which are as follows:

  1. Enable the Audit log service in Security and Compliance center. This could be turned on (if not done already) via the Start recording user and admin activity on the Audit log search page in the Security & Compliance Center. This is going to be automatically On by Microsoft in future.
  2. Turn on the subscription service from the Office 365 Management Api. For this hit the below URL to start the subscription service on your tenancy. Replace the tenant Id with the tenant Id from Azure Active Directory
    https://manage.office.com/api/v1.0/{tenant_id}/activity/feed/subscriptions/start?contentType=Audit.SharePoint

Next, back to the Azure Function, we will connect to the Azure subscription service using Azure AD app Id and secret using the below code. The below process is back and forth data pull from the Azure Content blob so read through the steps and code carefully as it might be a little confusing otherwise.

using System;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using System.Collections.Generic;
using Newtonsoft.Json;
using Microsoft.Azure.WebJobs.Extensions.Http;
using System.Threading.Tasks;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using Microsoft.Extensions.Logging;
string TenantID = <TenantID>;
string authString = "https://login.windows.net/&quot; + TenantID;
string SPServiceUrl = "https://manage.office.com/api/v1.0/&quot; + TenantID + "/activity/feed/subscriptions/content";
string resourceId = "https://manage.office.com&quot;;
string clientId = <Client App Id>;
string clientSecret = <Client App secret>;
var authenticationContext = new AuthenticationContext(authString, false);
ClientCredential clientCred = new ClientCredential(clientId, clientSecret);
AuthenticationResult authenticationResult = null;
Task runTask = Task.Run(async () => authenticationResult = await authenticationContext.AcquireTokenAsync(resourceId, clientCred));
runTask.Wait();
string token = authenticationResult.AccessToken;

After connecting to the Azure subscription, we could request for content logs for a SharePoint events using a timeline window. Note that the date time are to be in UTC formats.

The detailed audit logs data are not provided in the initial data pull. The initial data pull from Office 365 Management Api returns the content URI to the detail audit log data. This content URI then provides the detailed audit log information hence the next step is a two-step process. The first step is to get the content blog URI details during the first call which then has the detailed log information URI to get the detail data entry from the Azure Subscription service.

Since the audit log data returned from the Office Management subscription service is paged, it is needed to loop through the NextPageURI to get the next URI for the next data pull.

The below code has the break up of data calls and looping for the next page URI. Brief overview of the code is as follows:

  1. Use the Do-While loop to call the initial data URI
  2. Call the initial data URI and get the response data
  3. Process the initial log data and convert to JSON data objects
  4. Get the ContentURI property and then call the data
  5. Next call the content URI to get the detailed audit log data
  6. After the data is fetched, convert to JSON data objects
  7. Add to the final data objects
using System;
using System.Collections.Generic;
using System.Net.Http.Headers;
using System.Web;
using Newtonsoft.Json.Linq;
using System.Net.Http;
using Newtonsoft.Json;
using System.IO;
using Microsoft.Extensions.Logging;
using System.Linq;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;
using Microsoft.WindowsAzure.Storage.File;
using System.Text;
using CsvHelper;
using System.Threading.Tasks;
using System.Globalization;
using Microsoft.Online.SharePoint.TenantAdministration;
using System.Security;
using Microsoft.SharePoint.Client;
using System.Net;
//JSON object for Initial Call
public class AuditInitialReport
{
public string ContentUri { get; set; }
public string ContentId { get; set; }
public string ContentType { get; set; }
public string ContentCreated { get; set; }
public string ContentExpiration { get; set; }
}
public class AuditDetailedReport
{
public DateTime CreationTime { get; set; }
public string Id { get; set; }
public string Operation { get; set; }
public string Workload { get; set; }
public string ObjectId { get; set; }
public string UserType { get; set; }
public string UserTypeName { get; set; }
public string RecordType { get; set; }
public string RecordTypeName { get; set; }
public string UserId { get; set; }
public string EventSource { get; set; }
public string SiteUrl { get; set; }
public string Site { get; set; }
public string WebId { get; set; }
public string WebSiteName { get; set; }
public string ListId { get; set; }
public string ListName { get; set; }
public string ListItemUniqueId { get; set; }
public string ItemName { get; set; }
public string ItemType { get; set; }
public string SourceFileExtension { get; set; }
public string SourceFileName { get; set; }
public string SourceRelativeUrl { get; set; }
public string UserAgent { get; set; }
public string EventData { get; set; }
public string TargetUserOrGroupType { get; set; }
public string TargetUserOrGroupName { get; set; }
public string TargetExtUserName { get; set; }
public string UniqueSharingId { get; set; }
public string OrganizationId { get; set; }
public string UserKey { get; set; }
public string ClientIP { get; set; }
public string CorrelationId { get; set; }
}
public class AuditLogDataPull
{
string TenantID = <TenantID>;
string authString = "https://login.windows.net/&quot; + TenantID;
string urlParameters = $"?contentType=Audit.SharePoint&startTime={startDateString}&endTime={endDateString}";
// Loop through the Office 365 Management API call till the NextPageURI is null i.e. there are no pages left
do
{
// Get teh initial data entry for the data pull
auditInitialDataObject = getAuditInitalData(SPServiceUrl, urlParameters);
// Get the next page URI to form the next parameter call
if (auditInitialDataObject.AuditNextPageUri != "")
urlParameters = "?" + auditInitialDataObject.AuditNextPageUri.Split('?')[1];
//List of JSON objects from the initial data call
List<AuditInitialReport> auditInitialReports = auditInitialDataObject.AuditInitialDataObj;
// To increase performance call multiple endpoints at a time using Parallel loops
int maxCalls = 200;
int count = 0;
Parallel.ForEach(auditInitialReports, new ParallelOptions { MaxDegreeOfParallelism = maxCalls }, (auditInitialReport) =>
{
int loopCount = count++;
log.LogInformation("Looking at request " + loopCount);
// For brevity, have omitted the definition of AuditDetailedReport object. Please create this class and add variables to map
List<AuditDetailedReport> auditDetailReports = getAuditDetailData(auditInitialReport.ContentUri);
log.LogInformation("Got Audit Detail Reports of " + auditDetailReports.Count + " for loop number " + loopCount);
foreach (AuditDetailedReport auditDetailReport in auditDetailReports)
{
auditDetailReportsFinal.Add(auditDetailReport);
}
});
} while (auditInitialDataObject.AuditNextPageUri != "");
// Method to get the data for initial data pull
public AuditInitialDataObject getAuditInitalData(string SPServiceUrl, string urlParameters)
{
AuditInitialDataObject auditInitialDataObj = new AuditInitialDataObject();
try
{
List<AuditInitialReport> auditInitialReports = new List<AuditInitialReport>();
// **** Call the Http Client Service ****
HttpClient client = new HttpClient();
client.BaseAddress = new Uri(SPServiceUrl);
// Add an Accept header for JSON format.
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken.ToString());
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
// List data response.
HttpResponseMessage response = client.GetAsync(urlParameters, HttpCompletionOption.ResponseContentRead).Result; // Blocking call!
if (response.IsSuccessStatusCode)
{
// Parse the response body. Blocking!
Stream dataObjects = response.Content.ReadAsStreamAsync().Result;
StreamReader reader = new StreamReader(dataObjects);
string responseObj = reader.ReadToEnd();
auditInitialReports = JsonConvert.DeserializeObject<List<AuditInitialReport>>(responseObj);
IEnumerable<string> values;
if (response.Headers.TryGetValues("NextPageUri", out values))
{
auditInitialDataObj.AuditNextPageUri = values.First();
auditInitialDataObj.AuditInitialDataObj = auditInitialReports;
}
else
{
auditInitialDataObj.AuditNextPageUri = "";
auditInitialDataObj.AuditInitialDataObj = auditInitialReports;
}
}
else
{
log.LogError($"{(int)response.StatusCode} ({response.ReasonPhrase})");
}
}
catch(Exception ex)
{
log.LogError($"Error while fetching initial Audit Data. Error message – {ex.Message}");
}
return auditInitialDataObj;
}
// Method to get the Audit Log data
// Note: The definition for Audit Detailed report class is neglected in this example here
public List<AuditDetailedReport> getAuditDetailData(string SPServiceUrl)
{
List<AuditDetailedReport> auditDetailData = new List<AuditDetailedReport>();
try
{
int retries = 0;
bool success = false;
// **** Call the Http Client Service ****
HttpClient client = new HttpClient();
string urlParameters = "";
client.BaseAddress = new Uri(SPServiceUrl);
// Add an Accept header for JSON format.
client.DefaultRequestHeaders.Add("Authorization", "Bearer " + accessToken.ToString());
client.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
// List data response.
HttpResponseMessage response = client.GetAsync(urlParameters, HttpCompletionOption.ResponseContentRead).Result; // Blocking call!
if (response.IsSuccessStatusCode)
{
success = true;
// Parse the response body. Blocking!
Stream dataObjects = response.Content.ReadAsStreamAsync().Result;
StreamReader reader = new StreamReader(dataObjects);
string responseObj = reader.ReadToEnd();
auditDetailData = JsonConvert.DeserializeObject<List<AuditDetailedReport>>(responseObj);
}
}
catch(Exception ex)
{
log.LogError($"Error while getting Detailed Audit Data. Error message – {ex.Message}");
}
return auditDetailData;
}
}

After the data is retrieval is complete, the final could be stored in an Azure Table for further processing.

Final Thoughts

The above custom process using Azure Function and Office 365 Management API allows us to connect to the Audit log data through a custom job hosted in Office 365. After getting the data we could create reports or filter the data.

11 Comments

  1. There are some gaps in the logic which don’t allow me to follow. What type of Azure function are you creating? Did you have to install any special libraries to get this to work?

    Like

  2. Hi,
    Could you give us more details on how to create the AuditDetailedReport class ? I did not understand what were its fields.
    Thanks

    Like

  3. Couldn’t get the code to work at all. I am trying to create an Azure Function using Visual Studio 2019. None of the code is laid out for C# and, for example, the Do/While clause is inside of a class? If you do have this working, is it possible to post the src files on GitHub or provide a download to a ZIP file? I cannot figure out how this code flows sans namespaces and structure.

    Like

  4. Hi All, Based on the feedback above, I am working on two items to address the issues above.
    1. Share a Git hub repo of the code to provide a starting point. The code compiles fine on my end. Will be updating the post and sharing soon
    2. Also adding a pre-requisties blog with additional steps that are missing in this blog. Will be posting soon.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s