Timer Trigger Function Apps in Azure

Introduction

In the not too distant past, if you wanted to run code at regular intervals you had a few go-to options to choose from.  If you wanted a “down and dirty” solution, a quick PowerShell script could be scheduled using the Task Scheduler.  If you needed anything more advanced or formal, you could write a Windows Service to house both the logic and the scheduling for unattended execution.  In the age of cloud computing you may think of firing up an Azure Virtual Machine to accomplish the same tasks, but without needing an on-premise server always running.  But why maintain an entire machine for code that needs to run once a day, or even once an hour?  Fortunately, Microsoft has provided a flexible, easy to use solution for this type of task in the form of Timer Trigger functions within Azure Function Apps.

Azure Functions let you write the code to perform your task, without worrying about the infrastructure that runs it.  Depending on the trigger type, there are different languages available to code your logic, including C#, PowerShell, TypeScript, and others.  Regardless of which you choose, you get a powerful browser-based user interface with which to write, configure, and monitor your code.

In my case I was looking to create an automated daily check to see who didn’t complete their timesheets for the prior business day, sending an email with the list of offenders should any exist.  We use Project Online to track daily hours, so I wanted to directly pull from that data using the OData reporting interface to make the determination.  Before running through these steps in your own environment, be sure you understand Azure pricing models.  The solution described here costs pennies per month, but that could change based on total usage, subscription plans, or future changes to the pricing models.

Getting started

To get started, navigate to portal.azure.com.  In the portal, click on the “All resource” navigation link where you will see everything associated with your instance.  To create a new Function App, click on the Add button in the ribbon.  This will bring up a list of everything available in the Azure Marketplace.  In the search box, search for and select “Function App”, which should bring up the description, publisher, pricing, and documentation panel for the app.

 

 

 

 

 

 

Press the Create button to get started.  You will first be presented with a list of general options for your function app.

 

 

 

 

 

 

 

 

 

 

 

 

Notes:

• The app name must be globally unique, so you may want to preface it with your company or product name
• Be sure to read up and understand Resource Groups, Storage accounts, Locations, and the difference between Consumption Plan and App Service plan, as they can have a drastic impact on the charges incurred

Once you have setup the basics of the Function App, it is time to add an actual function.  As of this writing, there are 33 different triggers to choose from!  For our case we will use the Timer Trigger.

 

 

 

 

 

 

Add the Timer Trigger by finding the Timer trigger card and clicking on a language choice, such as C#.  You will then be prompted for a name and a Timer trigger schedule.  Don’t worry if you don’t understand cron expressions; there are plenty of examples and documentation within the designer.  For our daily job, we use the expression “0 30 14 * * 1-5”, to specify Monday through Friday at 2:30 PM UTC (9:30 AM Eastern).

Setting up

You should now be in the designer, with the file “run.csx” open.  This will be the main entry point for your function.  The default template will provice a Run method that passes in the time and a TraceWriter for logging purposes.  Before we get too far along here, we need to think about what other libraries we want to use in order to gain access to SharePoint Online and the Project OData endpoints.  To do this, expand the “View Files” pane on the right and select the “project.json” file.  This is the “package reference” used to tell NuGet which packages to retrieve for your project.  A reference can be found at https://docs.microsoft.com/en-us/nuget/archive/project-json.  For this project, we are using the SharePoint Online CSOM library and the Newtonsoft Json library.  Our library file will look like this:

 

 

 

 

 

This will implicitly create a project.lock.json file with all of the details regarding the individual assemblies brought into our application.

We’ll also want to add a few application settings for our mail configuration, rather than having them directly within the code.  Application Settings can be configured by going to the top-level node of your function in the tree view, and selecting the “Application settings” tab.  You can add, edit, or delete your settings in the Application settings section of that page, to be referenced within your code as needed.

On to the code!

Our code will start with our using statements, as well as some configuration data we’ll pull from application settings and/or hard code as desired.

using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Net;
using System.Net.Mail;
using Microsoft.SharePoint.Client;
using Newtonsoft.Json;

// Mail server configuration
private static string mailHost = ConfigurationManager.AppSettings[“MailHost”];
private static int mailPort = Convert.ToInt32(ConfigurationManager.AppSettings[“MailPort”]);
private static string username = ConfigurationManager.AppSettings[“EmailUsername”];
private static string password = ConfigurationManager.AppSettings[“EmailPassword”];
private static string toAddress = ConfigurationManager.AppSettings[“ToAddress”];

// Mail message constants
private const string mailSubjectTemplate = @”Missing Hours for {0}”; // {0} = Date
private const string mailBodyTemplate = @”<h3>The following people were missing hours yesterday…</h3>{0}”; // {0} = List of users

// API constants
private const string pwaPath = @”https://timlin.sharepoint.com/teams/projects/PWA/”;
private const string pwaApiPath = @”_api/ProjectData/”;

Next up, we construct a Url to use to retrieve the prior day’s time.  This call will use the TimesheetLineActualDataSet data, selecting just the columns we need and filtering to a specific day.  The date value will be a string template that we format in at runtime.  I would recommend working the exact syntax out in your own environment via a browser to make sure you have it right.

// Templates for REST OData calls

private const string getHoursPwaRequestPathTemplate = @”TimesheetLineActualDataSet?$filter=TimeByDay%20eq%20datetime%27{0}%27&$select=ResourceName,ActualWorkBillable&$format=json”;

Next we create and initialize a dictionary to store the list of users we want to track.

// Dictionary of users to report on
private static Dictionary<string, double> userHours = new Dictionary<string, double>() {
{ “John Doe”, 0.0 },
{ “Jane Smith”, 0.0 },
{ “Hapie Goluky”, 0.0 },
{ “Joe Piccirilli”, 0.0 }
};

We also have a few “helper” functions to keep our main code clean.

// Get the prior business date
private static DateTime GetYesterday() {
var date = DateTime.Today;
switch (date.DayOfWeek) {
case DayOfWeek.Sunday:
date = date.AddDays(-2);
break;
case DayOfWeek.Monday:
date = date.AddDays(-3);
break;
default:
date = date.AddDays(-1);
break;
}
return date;
}

private static dynamic DeserializeJsonObject(string content) {
return JsonConvert.DeserializeObject<dynamic>(content);
}

private static void SendMessage(string subject, string body) {
var smtpClient = new SmtpClient();
smtpClient.UseDefaultCredentials = false;
smtpClient.Credentials = new System.Net.NetworkCredential(username, password);
smtpClient.Port = mailPort;
smtpClient.Host = mailHost;
smtpClient.DeliveryMethod = SmtpDeliveryMethod.Network;
smtpClient.EnableSsl = true;
var mailMessage = new MailMessage();
mailMessage.From = new MailAddress(username);
mailMessage.To.Add(new MailAddress(toAddress));
mailMessage.Subject = subject;
mailMessage.Body = body;
mailMessage.IsBodyHtml = true;
smtpClient.Send(mailMessage);
}

Reading the Project Online data uses .Net’s HttpWebRequest object, with SharePointOnlineCredentials providing the authentication mechanism.  We encapsulate the credentials and the web GET calls with other helper properties and functions.

private static SharePointOnlineCredentials _creds;
private static SharePointOnlineCredentials Credentials {
get {
if (_creds == null) {
var securePassword = new SecureString();
foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
_creds = new SharePointOnlineCredentials(username, securePassword);
}
return _creds;
}
}

private static string WebGet(string requestUrl) {
var req = (HttpWebRequest)WebRequest.Create(requestUrl);
req.Credentials = Credentials;
req.Headers[“X-FORMS_BASED_AUTH_ACCEPTED”] = “f”;

var resp = (HttpWebResponse)req.GetResponse();
var receiveStream = resp.GetResponseStream();

var readStream = new StreamReader(receiveStream, Encoding.UTF8);

return readStream.ReadToEnd();
}

One final helper method constructs the call to WebGet.

// Get hours from PWA OData service for the prior business day
private static string GetHoursPwa(string date) {
return WebGet(pwaPath + pwaApiPath + string.Format(getHoursPwaRequestPathTemplate, date));
}

Within our main Run method, we orchestrate the overall logic.  First, we get the date and log a message to know what date we ran for.  These messages are visible in the Logs panel below the code window, and in the Monitor page accessible from the tree view navigation for all execution.

public static void Run(TimerInfo myTimer, TraceWriter log)
{
_log = log;

// Get yesterday’s date
var date = GetYesterday().ToString(“yyyy-MM-dd”);
_log.Info($”Running for {date}”);

Next we get and deserialize the data into a “dynamic” object.

// Get the PWA OData
var data = GetHoursPwa(date);

// Deserialize to a dynamic object
var dynamicObject = DeserializeJsonObject(data);

We’ll then iterate over the data in the dynamic object and aggregate the hours for each resource’s time entries into our dictionary.

// Populate our userHours dictionary based on hours in timesheet
foreach (var user in dynamicObject.value) {
if (userHours.ContainsKey(user.ResourceName.ToString())) {
userHours[user.ResourceName.ToString()] += Double.Parse(user.ActualWorkBillable.ToString());
}
}

We only need to deal with users with no hours, so we’ll use a quick Linq statement to extract them.

// Extract the names of users with 0 hours

var usersWithNoHours = userHours.Where(x => x.Value == 0.0).Select(x => x.Key);

Finally, we’ll send an email message out to our distribution list if there are any offenders or log the fact that all is clear if not.

  // Send the message, if there are any users without hours
if (usersWithNoHours.Any()) {
var subject = string.Format(mailSubjectTemplate, date);
var body = string.Format(mailBodyTemplate, string.Join(“<br />”, usersWithNoHours));
_log.Info(body);
SendMessage(subject, body);
}
else
{
_log.Info(“No offenders found!”);
}

Wrapping up

Once the code is in place, the App is running, and the timer function is enabled, the code will wake up every day, run through the logic, and go back to sleep until needed again.  As we have this configured, using a Consumption pricing tier, this once-daily execution costs less than $0.10 per month beyond any standard subscription costs.  As stated before, your mileage may vary based on the specifics of your plan, number of calls, disk / data usage, etc., so be sure to research these items first and monitor your application within Azure to ensure your costs are in line with expectations.