Posted on

Setting up subscription service for Office 365 Audit log data using Office Management API

In the previous blogs here, we have seen how to pull audit log data using Office 365 Management Activity API and store it in Azure Table for further processing. In this blog, we will look at how to start the subscription service for Office 365 Management Activity API before the audit log data can be retrieved.

Prerequisties

We need to fulfil some of the prerequisties before starting the subscription service

  1. We will need a Tenant Admin account to create an Azure AD application and granting admin consent to API permissions
  2. An Internet browser to query and receive initial code for token generation
  3. An API query tool such as Postman to query POST requests and get the token

Steps

In order to start the subscription, we will need an Azure AD app which has been given Office 365 Management API permissions. We will then use the App to generate an access token to start the service. Below are the steps on how to do that.

  • The first step is to create an Azure AD app. Make sure to provide a valid redirect uri in Authentication section. The redirect uri will be required to gather the code when authenticating the app later.
    For detailed steps on how to create an Azure AD app, check here.
  • Below are the API permissions to be provided for the Azure AD app. The API permissions will need Tenant Admin consent for them to be activated
  • After the Azure AD app is ready, the Tenant admin needs to generate a consent code that could be use to retrieve the application token to start the subscription service. To get the cosent code, generate the url as below and then put it on a browser. Then login with the Tenant admin account to consent the access.

https://login.windows.net/common/oauth2/authorize?response_type=code&resource=https%3A%2F%2Fmanage.office.com&client_id={client_id}&redirect_uri={redirect_url}
where,
client_id = Azure AD App Client ID,
redirect_url = Redirect URI provided with the Azure AD app

After consenting the above URL, it will generate a consent code in the below format. Please copy that code and remember to ignore the session_state parameter following the code.

https://www.xyz.com?code=AQABAAIAAAApVMil8KPQ4…
  • After the above code is available, we will use Postman to do a POST query and fetch the access token. Below is the URL and Request body of the request (see screenshot for reference)

POST URL: https://login.windows.net/common/oauth2/token

Request Header: Content-type = application/x-www-form-urlencoded

Request body parameters of type x-www-form-urlencoded
resource: https://manage.office.com
client_id: <Azure AD App ID>
redirect_uri: <Redirect url from Azure AD App>
client_secret: <Azure AD App Client secret>
grant_type: authorization_code
code: <Code from above step>

As output of the above request, we will get a json output with the access token parameter to start the service

  • With the access token received above, we can start the service using the below URL with a POST request in Postman.

https://manage.office.com/api/v1.0/{tenantID}/activity/feed/subscriptions/start?contentType={Audit.SharePoint}
where,
tenantID = Tenant ID of the subscrption, can be found in Azure AD App Overview or Azure AD properties
contentType = Type of content to be subscribed. E.g. Audit.SharePoint, more information can be found here

The access token needs to put in the Authorization tab of the Request in Postman.

If the above process, worked fine, we would received a HTTP sucessful response (200 OK) with a Json reply stating the subscription is enabled.

Conclusion

In this blog, we looked at how to enable the subscription service for Office 365 management API.

Posted on

Office365-AzureHybrid: Building an automated solution to pull Office 365 Audit logs

Custom reporting with Office 365 Audit log data could be implemented using Audit Logs fetched from the Security and Compliance center. In the previous blogs here, we have seen how to use PowerShell and Office 365 Management API to fetch the data. In this blog, we will look at planning, prerequisites and rationale to help decide between the approaches.

The Office 365 Audit logs could be fetched from the Security and Compliance center once enabled. Currently logging is not enabled by default and needs to be enabled from the 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. Once enabled, the Audit information across all Office 365 services are tracked.

The Audit log search in Security and Compliance center allows to search the audit logs but is limited in what is provided. Also it takes a long time to obtain the results.

In order to provide efficiency and performance, the data could be pulled from Office 365 Audit logs but needs custom hosting.

Planning and prerequisites:

Few considerations for custom processes are as follows:

  1. Need additional compute to process the data – since the audit log data is huge and queries take a longer time, it is recommended to do a periodic job to fetch the data from the Office 365 audit log using a custom process. This could be done using a PowerShell job or Azure Function App as detailed below.
  2.  Need additional hosting for storing Office 365 Audit log data – The records could range from 5000 to 20000 per hour depending on the data sources and relevant data size. Hence to make it easier to retrieve the data later, it is advisable to store the data in a custom database. Since the data cost could be significant for this, it is recommended to use either dedicated hosting or NOSQL hosting such as Azure Tables/CosmosDB (Azure) or SimpleDB / DynamoDB (AWS)
  3. Might need additional Service Account or Azure AD App – Since the data will be retrieved using an elevated process, it is recommended to run it with an Azure AD app or service account to gather the data. For more information about this, please refer to this blog here.

Scenarios:

Some of the scenarios when the Office 365 Audit log data could be useful.

  1. Creating custom reports for user activities and actions
  2. Storing audit log data for greater than 90 days
  3. Custom data reporting and alerts not supported in Security and Compliance center

Approaches:

Below are few approaches to pull the data from the Office 365 Audit Logs. Also have listed the benefits and limitations of the approaches in order to help decide on implementation.

Using PowerShell

Search-UnifiedAuditLog of Exchange Online PowerShell could be used to retrieve data from Office 365 Audit log. More implementation details could be found at the blog here.

Benefits:

  1. No additional compute hosting required. The PowerShell could be run on a local system with a service account or on a server.
  2. One off data-pull could be done and retrieved when needed
  3. Data more than 90 days could be retrieved from Office 365 Audit log
  4. No session time out constraints as long the PowerShell console can stay active
  5. Local date formats could be used to fetch data

Limitations:

  1. Need Tenant Admin rights when connecting to Exchange PowerShell so the cmdlets could be downloaded
  2. Needs connection to Exchange online PowerShell every time the data needs to be retrieved
  3. Couldn’t be run on cloud using Azure or AWS as connection with Exchange Online PowerShell cmdlet is not possible in serverless environment
  4. Could run for longer period that could range to hours of continuous run

Using Office 365 Management API :

The Office Management API provides another way to retrieve data from Azure Logs using a subscription service and Azure AD App. For more detailed information, please check the blog here.

Benefits:

  1. Support of any language such as C#, Javascript, Python etc.
  2. Parallel processing allows greater speed and flexibility of data management
  3. Controlled data pull depending on data size to increase efficiency and performance

Limitations:

  1. Additional compute hosting required such as serverless workloads or web jobs to process the data
  2. Need an Azure AD app or OAuth layer to connect to the subscription service
  3. Need additional Time zone processing since all dates are in GMT for retrieving data
  4. Session timeout might occur in data pull involving large datasets. So advisable to use smaller time slot windows for data pull
  5. Multilevel data pull required to fetch the audit log. Please check the blog here to get more information

Final Thoughts

Both PowerShell and Office 365 Management Activity APIs are a great way to fetch Office 365 Audit log data in order to create custom reports. The above points could be used to decide on an approach to fetch the data efficiently and process it. For more details on the steps of the process, please check the blog here (PowerShell) and here (Office 365 Management API).

Posted on

Retrieve Office 365 audit logs using Office Management API and Azure Functions

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 Code – Github 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;
view raw Connect to Azure AD in Azure Function hosted with ❤ by GitHub

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;
}
}
view raw Get data from Office 365 Azure Log hosted with ❤ by GitHub

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.

Posted on

Retrieve Office 365 Audit logs using PowerShell and store in Azure table for quick retrieval

To create custom reports for Office 365 events, we could use the Audit logs from Security and Compliance center. The process is quite simple and could be implemented easily using PowerShell. In this blog, we will look at the steps for the same.

Later we will also see how we could store this data in a Azure Storage Table, so it is easy to fetch the data available.

Steps to fetch data from Office 365 Audit log using Exchange Online PowerShell

  1. The first step in the process is to import the commands from Exchange online PowerShell.


    $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $Credential -Authentication Basic -AllowRedirection
    Import-PSSession $Session -AllowClobber

    view raw

    Import Exchange Online PowerShell

    hosted with ❤ by GitHub

    In the above script, we are initializing the PowerShell session for Exchange Online PowerShell

  2. After the commands are imported, then we could search the audit log using the Search-UnifiedAuditLog command. Below is the cmdlet and some helpful information about the parameters.


    $data = Search-UnifiedAuditLog
    -StartDate $startInterval
    -EndDate $endInterval
    -Operations FileAccessed, FilePreviewed, PageViewed, PageViewedExtended, SearchViewed, CompanyLinkUsed, SecureLinkUsed, FileDownloaded, FileModified, FileUploaded, FileDeleted, FolderModified, CompanyLinkCreated, SharingInheritanceBroken, ListUpdated, FileSyncDownloadedFull, FileSyncUploadedFull
    -SessionId $sessionId
    -SessionCommand ReturnLargeSet
    -ResultSize 5000
    ## Start Date – Date and Time in local Computer Date Time format
    ## End Date – Date and Time in local Computer Date Time format
    ## Operations – For more information see here – https://docs.microsoft.com/en-gb/office365/securitycompliance/search-the-audit-log-in-security-and-compliance#sharing-and-access-request-activities
    ## Session Id – Essential for paging and handling when data goes more than threshold limit
    ## Session Command – Determines the size of the data returned
    ## ResultSize – 5000 seems to be the upper limit for a successfull data pull

    view raw

    Search Unified Audit Log

    hosted with ❤ by GitHub

    To get more information about more parameters here – https://docs.microsoft.com/en-us/powershell/module/exchange/policy-and-compliance-audit/search-unifiedauditlog?view=exchange-ps

  3. After the audit log data is pulled, the data could be formatted and updated to provide more relevant information about the audited information.
    For eg. RecordType and UserType information could be updated provide more information than just numbers.

Updating data into an Azure Table using Azure Storage PowerShell

After the data is processed and ready to be used from the above steps, we can either export this data to a CSV or store it in a Azure Table. For this blog, we will export this into CSV and then import it into an Azure Table. The benefits of Azure Table are as follows:

  1. Low cost storage
  2. Easy connection and data retrival
  3. NO SQL format allows storing information in multiple schema formats easily
  4. Data Types can be easily set and managed

Below is the script for the same.


[Reflection.Assembly]::LoadWithPartialName("System.Web.Script.Serialization")
$resourceName = "<Resource Group Name>"
$azureStorage = "<Storage Container Name>"
$azureTable = "<Azure Storage Table>"
$Credential = Get-Credential
function UploadDataToAzureTableStorage {
param (
[Parameter(Mandatory = $true, HelpMessage = 'Please provide the FileName')][ValidateNotNullOrEmpty()][String]$fileName
)
Write-Output "Connecting to Azure Subscription"
Connect-AzureRmAccount -Credential $Credential
Write-Output "Connect to Storage Container"
$storageCont = Get-AzureRmStorageAccount -ResourceGroupName $resourceName -Name $azureStorage
$ctx = $storageCont.Context
Write-Output "Get Office 365 Audit Log Data Table"
$table = Get-AzureStorageTable -Name azureTable -Context $ctx
$partitionKey = "<PartitionKey>"
$date = Get-Date -Format s
$rowKeyPrefix = "Data_" + $date.Replace(":", "")
$JSSerializer = [System.Web.Script.Serialization.JavaScriptSerializer]::new()
$dataJson = (Get-Content -Path $fileName) | Out-String | ConvertFrom-Json;
for ($i = 0; $i -lt $dataJson.Count ; $i++) {
Write-Output $("Adding Row " + $($i + 1))
$hashtable = $JSSerializer.Deserialize(($dataJson[$i] |ConvertTo-Json), 'Hashtable')
$rowKey = $rowKeyPrefix + $i
$null = Add-StorageTableRow -table $table -partitionKey $partitionKey -rowKey ($rowKey) -property $hashtable
}
Disconnect-AzureRmAccount
}

view raw

Azure Table Scripts reading from CSV

hosted with ❤ by GitHub

Conclusion

In this blog, we will see how we could search Office 365 Audit, retrieve the data and then store it in a Azure Table for later use.

Posted on

PowerShell scripts for SharePoint On Prem Analysis

— Updated 14 Jun 2017 – Added more scripts below (blue color) —

Recently working on a SharePoint 2010 to Online migration project, many PowerShell scripts were helpful for analyzing the SharePoint environment. Below are a list of those. I will be adding more when I built them, so that it is easy to find and run them. This is also helpful for analyzing your on-prem environment.

The Scripts can be found here in the github folder for reference – https://github.com/AsishP/SharePointOnPrem/tree/master/PowerShell

  1. Find Large Lists
  2. Export Site Collection Features
  3. Exports Web Features
  4. Check Windows vs Claims Authentication
  5. Check Language Packs
  6. Get the count of webs in a Site Collection
  7. Get list of all Web parts on sites and web
  8. Get web sites with last modified date information
  9. Get details of External Lists and BDC connections
  10. Get Empty SharePoint groups
  11. Get Broken Inheritance report
  12. Get Documents Greater than 50 MB
  13. Check Orphaned SharePoint Users (users who don’t exist in AD)
  14. Delete Orphaned SharePoint Users 
  15. Get Entries from SharePoint Audit Log
  16. Get last accessed documents from Document libraries