July 23, 2017

Creating an Azure Function to Audit SharePoint Sites

In this post I will cover the process of auditing all of your SharePoint Online sites with an Azure Function. SharePoint standards are important so that you can set a consistent use of SharePoint within your organization. This ultimately makes your SharePoint environment easier to manage and reduces support and project costs. In a previous post I went into some detail on the subject and presented one example using PowerShell to audit for SharePoint site standards.

I will tackle the same problem by taking an enterprise developer approach, constructing an audit process using an Azure Function. I will test for the standard that all SharePoint security groups should be set so that membership is editable by the group owner and not by the group members. (this might be useful in a couple situations such as SharePoint community sites, but in general we want this set to ‘Group Owner’.)

Azure Functions are actually a very good fit for this solution. Azure Functions are often discussed in the context of web services or microservices, but Azure Functions have the ability to be triggered on a scheduled basis. So Azure Functions can actually be used in situations where you may have used an Azure Webjob. For an audit process like the one we’re creating here, a daily report would be ideal, so our Azure Function will be configured to kick off on a daily basis at 9pm.

Creating, Deploying, and Configuring an Azure Function – In my post SharePoint Online Site Creation using Microsoft Flow and Azure Functions, I covered the process of setting up Visual Studio for development of Azure Functions as well as deploying and configuring the Azure Function.

I followed this process to create a new Visual Studio 2017 project and add a function, SPSiteAudit as a timer triggered function. The syntax for the timer schedule is that used with cron, plus an extra parameter for seconds. (see the references section for an article that covers this in more detail). So for example, daily at 9pm – “schedule”: “0 0 21 * * *”

In Visual Studio I added the NuGet package for SharePointPnpCoreOnline. My solution utilizes this package for the authentication manager feature as well as the ability to send an Email using an Office 365 account. Adding this package also brings in a reference to the SharePoint client side object model (CSOM), which I will also be using.

After publishing the Azure Function to my Azure account, I will open up the application settings and add name / value pairs for the user name, password, the SharePoint admin URL, and the email address to send the report to.

Keep in mind that even though our Azure Function is timer based, you can kick it off at any time by selecting the function app and then clicking the ‘run’ button.

And here’s a look at the email that comes from the audit function.

Code Walkthrough
The complete source code is available as a GitHub repo at the following link : https://github.com/FrankStadler/AzureFunction_SPSiteAudit

At a high level the function loops through each site collection in the SharePoint Online tenant and checks each site collection’s SharePoint groups. If it finds an offending group with the edit membership set incorrectly, it adds the details to a string builder object. At the end of the function we email the audit report to an email address configured in the app settings.

On lines 28-36 I am setting up the process to iterate through each site collection within the SharePoint Online tenant.

On line 39 I am creating a context object for that particular site collection. Our base context at the tenant level does not allow us to ‘drill down’ into the details of the site collection, so we need to create a new context for each site collection we inspect.

On line 43 I am creating a lambda statement that selects just the SharePoint group properties I’m interested in. Taking this approach is an excellent way to improve performance of your CSOM code. For each group only the title and the AllowMembersEditMembership properties are brought back, not the complete object. The request completes quickly against the SharePoint tenant and only the two properties are brought back over the web service call (in the json response) to our Azure Function.

On line 56 I am generating the response as an email to the address configured in the app settings. The call uses the PnP utility class. (why write your own email function?)

using System;
using System.Text;
using Microsoft.SharePoint.Client;
using PnPAuthenticationManager = OfficeDevPnP.Core.AuthenticationManager;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Online.SharePoint.TenantAdministration;

namespace AzureFunctionSPSiteAudit
{
    public static class SPSiteAudit
    {
        //Timer Trigger runs at 9pm every day
        [FunctionName("SPSiteAudit")]
        public static void Run([TimerTrigger("0 0 23 * * *")]TimerInfo myTimer, TraceWriter log)
        {
            string userName = System.Environment.GetEnvironmentVariable("SPUser", EnvironmentVariableTarget.Process);
            string password = System.Environment.GetEnvironmentVariable("SPPwd", EnvironmentVariableTarget.Process);
            string SPRootURL = System.Environment.GetEnvironmentVariable("SPRootURL", EnvironmentVariableTarget.Process);
            string sendReportTo = System.Environment.GetEnvironmentVariable("SendReportTo", EnvironmentVariableTarget.Process);
            var authenticationManager = new PnPAuthenticationManager();
            var clientContext = authenticationManager.GetSharePointOnlineAuthenticatedContextTenant(SPRootURL, userName, password);
            try
            {
                log.Info($"SPAudit Timer trigger function executed at: {DateTime.Now}");


                var tenant = new Tenant(clientContext);
                var siteProperties = tenant.GetSiteProperties(0, true);
                clientContext.Load(siteProperties);
                clientContext.ExecuteQuery();

                var sbOutput = new StringBuilder();
                sbOutput.AppendLine("Audit Report Results<br/>");
                sbOutput.AppendLine("---------------------<br/>");
                foreach (SiteProperties sp in siteProperties)
                {
                    //since we're iterating through the site collections we need a new context object for each site collection
                    var siteClientContext = authenticationManager.GetSharePointOnlineAuthenticatedContextTenant(sp.Url, userName, password);

                    Web web = siteClientContext.Web;
                    GroupCollection groupCollection = web.SiteGroups;
                    siteClientContext.Load(groupCollection, gc => gc.Include(group => group.AllowMembersEditMembership, Group => Group.Title));
                    siteClientContext.ExecuteQuery();

                    foreach (Group group in groupCollection)
                    {
                        if (group.AllowMembersEditMembership)
                        {
                            sbOutput.AppendLine($"Site Collection = {sp.Url} . Group with Edit Membership found = {group.Title}<br/>");
                        }
                    }
                }
                sbOutput.AppendLine("---------------------<br/>");
                sbOutput.AppendLine($"Audit Report Complete at {DateTime.Now}");
                OfficeDevPnP.Core.Utilities.MailUtility.SendEmail(clientContext, new string[] { sendReportTo }, null, "SharePoint Audit Report Results", sbOutput.ToString());
                log.Info($"SPAudit completed normally at: {DateTime.Now}");
            }
            catch (Exception ex)
            {

                log.Info($"SPAudit Timer Exception at: {DateTime.Now}");
                log.Info($"Message: {ex.Message}");
                log.Info($"Stack Trace: {ex.StackTrace}");
                OfficeDevPnP.Core.Utilities.MailUtility.SendEmail(clientContext, new string[] { sendReportTo }, null, "SharePoint Audit Report Has Errored", ex.Message + ex.StackTrace);
            }
        }
    }
}

using System;

using System.Text;

using Microsoft.SharePoint.Client;

using PnPAuthenticationManager = OfficeDevPnP.Core.AuthenticationManager;

using Microsoft.Azure.WebJobs;

using Microsoft.Azure.WebJobs.Host;

using Microsoft.Online.SharePoint.TenantAdministration;

namespace AzureFunctionSPSiteAudit

{

public static class SPSiteAudit

{

//Timer Trigger runs at 9pm every day

[FunctionName("SPSiteAudit")]

public static void Run([TimerTrigger("0 0 23 * * *")]TimerInfo myTimer, TraceWriter log)

{

string userName = System.Environment.GetEnvironmentVariable("SPUser", EnvironmentVariableTarget.Process);

string password = System.Environment.GetEnvironmentVariable("SPPwd", EnvironmentVariableTarget.Process);

string SPRootURL = System.Environment.GetEnvironmentVariable("SPRootURL", EnvironmentVariableTarget.Process);

string sendReportTo = System.Environment.GetEnvironmentVariable("SendReportTo", EnvironmentVariableTarget.Process);

var authenticationManager = new PnPAuthenticationManager();

var clientContext = authenticationManager.GetSharePointOnlineAuthenticatedContextTenant(SPRootURL, userName, password);

try

{

log.Info($"SPAudit Timer trigger function executed at: {DateTime.Now}");

var tenant = new Tenant(clientContext);

var siteProperties = tenant.GetSiteProperties(0, true);

clientContext.Load(siteProperties);

clientContext.ExecuteQuery();

var sbOutput = new StringBuilder();

sbOutput.AppendLine("Audit Report Results<br/>");

sbOutput.AppendLine("---------------------<br/>");

foreach (SiteProperties sp in siteProperties)

{

//since we're iterating through the site collections we need a new context object for each site collection

var siteClientContext = authenticationManager.GetSharePointOnlineAuthenticatedContextTenant(sp.Url, userName, password);

Web web = siteClientContext.Web;

GroupCollection groupCollection = web.SiteGroups;

siteClientContext.Load(groupCollection, gc => gc.Include(group => group.AllowMembersEditMembership, Group => Group.Title));

siteClientContext.ExecuteQuery();

foreach (Group group in groupCollection)

{

if (group.AllowMembersEditMembership)

{

sbOutput.AppendLine($"Site Collection = {sp.Url} . Group with Edit Membership found = {group.Title}<br/>");

}

sbOutput.AppendLine("---------------------<br/>");

sbOutput.AppendLine($"Audit Report Complete at {DateTime.Now}");

OfficeDevPnP.Core.Utilities.MailUtility.SendEmail(clientContext, new string[] { sendReportTo }, null, "SharePoint Audit Report Results", sbOutput.ToString());

log.Info($"SPAudit completed normally at: {DateTime.Now}");

}

catch (Exception ex)

{

log.Info($"SPAudit Timer Exception at: {DateTime.Now}");

log.Info($"Message: {ex.Message}");

log.Info($"Stack Trace: {ex.StackTrace}");

OfficeDevPnP.Core.Utilities.MailUtility.SendEmail(clientContext, new string[] { sendReportTo }, null, "SharePoint Audit Report Has Errored", ex.Message + ex.StackTrace);

}

Conclusion. Overall, I’m impressed with the capabilities and ease of use of Azure Functions. The integration with Visual Studio 2017 makes it very easy to develop and publish projects. Functions are easy to configure, test and run within the Azure portal. And the SharePoint Patterns and Practices library and the SharePoint CSOM library make it easy to work with SharePoint.

The only challenges I encountered with my project were in researching features there were not well known such as iterating through all SharePoint site collections using the CSOM library.

It wouldn’t be difficult to take the process I’ve developed here and add additional audit tests for other SharePoint standards.

References:

Azure Functions Timer Trigger