Automation Monitoring with Microsoft Teams integration

• Are you concerned about your Automations, and whether they are running as expected?

• Is your organisation using Microsoft Teams?

If you can reply yes to both of the points above, you have come to the right place.

The following solution will give you a tool to keep an eye on your automations, and trigger a notification in a selected Microsoft Teams channel, as illustrated above, so you can take appropriate actions, but also use the teams notification to initiate a dialogue with appropriate members who might not have access to Marketing Cloud.

The process is following:

1. Find automation in the Automation_Status data extension where MID matches the one of BU where the script is running.
2. Fetch information using WSProxy about that particular automation
3. Compare health parameters from data extension with what has been retrieved from SFMC
   1. Is status either Running or matching the ExpectedStatus in the data extension?
   2. Has last run occurred within the number of hours defined in alertHours in the data extension?
   3. Is latestStatus Complete?
4. If any of these three checks fail, check if an error notification was sent for this automation within last 6 hours (we don’t want to spam anyone here) – if no: send error notification
5. Update Automation_Status data extension appropriately

The backbone of the automation monitor is the Automation_Status data extension having following structure:

Name	Type	Not NULL	PK	Default Value
Automation_Name	Text(255)	false	false
Customer_Key	Text(255)	true	true
Status	Text(255)	false	false
ScheduledTime	Text(255)	false	false
ExpectedStatus	Text(255)	false	false
MID	Number	false	false
LatestCheck	Date	false	false
LastRun	Text(50)	false	false
alertHours	Number	false	false
LastResult	Text(20)	false	false
Monitor	Boolean	false	false	False
errorNotificationSent	Date	false	false

This should be populated with the automations you want to keep an eye on, like in the example below:

You should at least add a single record in this data extension, following below descriptions:

Name	Description	Updated by
Automation_Name	Name of the automation to monitor	User
Customer_Key	External key of the automation to monitor	User
Status	Latest status obtained during last monitoring execution	Monitor
ScheduledTime	Upcoming scheduled execution as obtained during last monitoring execution	Monitor
ExpectedStatus	The correct status of the automation, which is to be expected during monitoring execution	User
MID	The business unit where the automation is to be found	User
LatestCheck	Timestamp (UTC-6) of the last monitoring execution for this particular automation	Monitor
LastRun	Timestamp (UTC-6) of the last run of this particular automation	Monitor
alertHours	How many hours since last run before alert is to be triggered	User
LastResult	The last status of the automation run (Complete/Errored)	Monitor
Monitor	Boolean value, to determine whether this automation is to be monitored	User
errorNotificationSent	When (if ever) was the last error notification triggered by the monitor	Monitor

Fields where it says User, should be filled out by you when configuring the solution, while the Monitor fields are automatically updated by the SSJS which monitors the automations.

You can either use this solution in a single Business Unit, and also in a multi-BU environment. In the latter approach, you will need to create the data extension in a shared data extension folder, as well as prepend the reference to the data extension in the below SSJS code with “ENT.”

<script runat="server">
Platform.Load("Core", "1.1.1");
var api = new Script.Util.WSProxy();

function formatDate(date) {
    var d = new Date(date),
        month = '' + (d.getMonth() + 1),
        day = '' + d.getDate(),
        hour = '' + d.getHours(),
        minute = '' + d.getMinutes(),
        year = d.getFullYear();

    if (month.length < 2)
        month = '0' + month;
    if (day.length < 2)
        day = '0' + day;
    if (hour.length < 2)
        hour = '0' + hour;
    if (minute.length < 2)
        minute = '0' + minute;

    var formattedDate = year + '-' + month + '-' + day + " " + hour + ":" + minute;
    return formattedDate
}

function dateDiff(startDate, subtractDate, interval) {

    Array.includes = function(val, arr) {
        for (i = 0; i < arr.length; i++) {
            if (!r || r == false) {
                r = val == arr[i] ? true : false;
            }
        }
        return r;
    }

    if (!(typeof startDate.getMonth === 'function')) {
        return 'Not a valid Start Date';
    }

    if (!(typeof subtractDate.getMonth === 'function')) {
        return 'Not a valid Subtraction Date';
    }

    var arr = ["y", "q", "m", "w", "d", "h", "mi", "s", "year", "quarter", "month", "week", "day", "hour", "minute", "second"]
    if (!(Array.includes(interval.toLowerCase(), arr))) {
        return 'Incorrect interval passed';
    }

    var startDateMS = startDate.getTime();
    var subtractDateMS = subtractDate.getTime();

    var diffMS = startDateMS - subtractDateMS;

    switch (String(interval).toLowerCase()) {
        case 'y':
            diff = (((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth()) / 12;
            break;
        case 'year':
            diff = (((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth()) / 12;
            break;
        case 'q':
            diff = (((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth()) / 3;
            break;
        case 'quarter':
            diff = (((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth()) / 3;
            break;
        case 'm':
            diff = ((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth();
            break;
        case 'month':
            diff = ((subtractDate.getFullYear() - startDate.getFullYear()) * 12) - subtractDate.getMonth() + startDate.getMonth();
            break;
        case 'w':
            diff = diffMS / (1000 * 60 * 60 * 24 * 7);
            break;
        case 'week':
            diff = diffMS / (1000 * 60 * 60 * 24 * 7);
            break;
        case 'd':
            diff = diffMS / (1000 * 60 * 60 * 24);
            break;
        case 'day':
            diff = diffMS / (1000 * 60 * 60 * 24);
            break;
        case 'h':
            diff = diffMS / (1000 * 60 * 60);
            break;
        case 'hour':
            diff = diffMS / (1000 * 60 * 60);
            break;
        case 'mi':
            diff = diffMS / (1000 * 60);
            break;
        case 'minute':
            diff = diffMS / (1000 * 60);
            break;
        case 's':
            diff = diffMS / (1000);
            break;
        case 'second':
            diff = diffMS / (1000);
            break;
    }

    var arr2 = ["y", "q", "m", "year", "quarter", "month"]
    if ((Array.includes(interval.toLowerCase(), arr2)) && diff < 0) {
        diff = Math.ceil(diff)
    } else {
        diff = Math.floor(diff)
    }

    return diff;
}

function retrieveAutomationHistory(key, limit) {

    var limit = limit || null;

    var cols = [
        "Status",
        "Name",
        "CustomerKey",
        "CompletedTime",
        "StartTime"
    ];

    var filter = {
        Property: "CustomerKey",
        SimpleOperator: "equals",
        Value: key
    };

    var records = [],
        moreData = true,
        reqID = data = null;

    while (moreData) {

        moreData = false;

        if (reqID == null) {
            data = api.retrieve("AutomationInstance", cols, filter);
        } else {
            data = api.getNextBatch("AutomationInstance", reqID);
        }

        if (data != null) {

            moreData = data.HasMoreRows;
            reqID = data.RequestID;

            for (var k in data.Results) {

                var item = data.Results[k];

                var o = {
                    Status: item.Status,
                    StatusMessage: item.StatusMessage,
                    StartTime: item.StartTime,
                    CompletedTime: item.CompletedTime
                }
                records.push(o);
            }

        }
    }

    records.sort(function(a, b) {
        return (new Date(a.CompletedTime) < new Date(b.CompletedTime)) ? 1 : -1
    });

    var num = (limit != null && limit <= records.length) ? limit : records.length;

    return records.slice(0, num);

}
var output = "";
var today = new Date();
var au;
var mid = Platform.Recipient.GetAttributeValue('memberid');
var dataRows = Platform.Function.LookupRows('Automation_Status', ['Monitor', 'MID'], ['1', mid]);
if (dataRows && dataRows.length > 0) {
    for (au = 0; au < dataRows.length; au++) {

        var errorNotificationSent = dataRows[au]["errorNotificationSent"];
        var automationCustomerKey = dataRows[au]["Customer_Key"];
        var expectedStatus = dataRows[au]["ExpectedStatus"];
        var alertHours = dataRows[au]["alertHours"];

        var rr = Platform.Function.CreateObject("RetrieveRequest");
        Platform.Function.SetObjectProperty(rr, "ObjectType", "Automation");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "ProgramID");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "CustomerKey");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "Name");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "Status");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "ScheduledTime");
        Platform.Function.AddObjectArrayItem(rr, "Properties", "CompletedTime");


        var sfp = Platform.Function.CreateObject("SimpleFilterPart");
        Platform.Function.SetObjectProperty(sfp, "Property", "CustomerKey");
        Platform.Function.SetObjectProperty(sfp, "SimpleOperator", "equals");
        Platform.Function.AddObjectArrayItem(sfp, "Value", automationCustomerKey);

        Platform.Function.SetObjectProperty(rr, "Filter", sfp);

        var retrieveStatus = [0, 0, 0];
        var automationResultSet = Platform.Function.InvokeRetrieve(rr, retrieveStatus);
        var Name = automationResultSet[0]["Name"];
        var Status = automationResultSet[0]["Status"];
        var ScheduledTime = automationResultSet[0]["ScheduledTime"];

        if (ObjectId != "null")

        {
            if (Status == "-1")
                var State = 'Error';

            else if (Status == "0")
                var State = 'BuildingError';
            else if (Status == "1")
                var State = 'Building';
            else if (Status == "2")
                var State = 'Ready';
            else if (Status == "3")
                var State = 'Running';
            else if (Status == "4")
                var State = 'Paused';
            else if (Status == "5")
                var State = 'Stopped';
            else if (Status == "6")
                var State = 'Scheduled';
            else if (Status == "7")
                var State = 'Awaiting Trigger';
            else
                var State = 'InactiveTrigger';

            var history = retrieveAutomationHistory(automationCustomerKey, 1);

            var latestStatus = history[0].StatusMessage;
            var latestRun = history[0].CompletedTime;

        } else {

        }

        var datediffs = dateDiff(today, latestRun, "h");
        var errorNotificationSent = new Date(errorNotificationSent);

        if (errorNotificationSent != null) {
            var hoursSinceLastAlert = dateDiff(today, errorNotificationSent, "h");
        }
        var rootcause = null;

        if ((State != expectedStatus) && (State != "Running")) {
            var rootcause = 'wrongState';
        }
        if (datediffs > alertHours) {
            var rootcause = 'delayedRun';
        }
        if (latestStatus != 'Complete') {
            var rootcause = 'errored';
        }

        if (rootcause !== null) {
            var url = 'https://url-to-your-microsoft-teams-webhook';
            var contentType = 'application/json';

            switch (rootcause) {
                case 'wrongState':
                    var notification = {
                        "@context": "https://schema.org/extensions",
                        "@type": "MessageCard",
                        "potentialAction": [{
                            "@type": "OpenUri",
                            "name": "Go to Automation Studio",
                            "targets": [{
                                "os": "default",
                                "uri": "https://mc.s50.exacttarget.com/cloud/#app/Automation%20Studio/AutomationStudioFuel3/"
                            }]
                        }],
                        "sections": [{
                            "facts": [{
                                "name": "Automation Name:",
                                "value": Name
                            }, {
                                "name": "Last run:",
                                "value": latestRun
                            }, {
                                "name": "Status:",
                                "value": State
                            }, {
                                "name": "Expected status:",
                                "value": expectedStatus
                            }],
                            "text": "An automation has a different status than expected:"
                        }],
                        "summary": "Automation error",
                        "themeColor": "0072C6",
                        "title": "Automation error"
                    };
                    break;
                case 'delayedRun':
                    var notification = {
                        "@context": "https://schema.org/extensions",
                        "@type": "MessageCard",
                        "potentialAction": [{
                            "@type": "OpenUri",
                            "name": "Go to Automation Studio",
                            "targets": [{
                                "os": "default",
                                "uri": "https://mc.s50.exacttarget.com/cloud/#app/Automation%20Studio/AutomationStudioFuel3/"
                            }]
                        }],
                        "sections": [{
                            "facts": [{
                                "name": "Automation Name:",
                                "value": Name
                            }, {
                                "name": "Last run:",
                                "value": latestRun
                            }, {
                                "name": "Status:",
                                "value": State
                            }],
                            "text": "An automation has not run for more than " + alertHours + " hours"
                        }],
                        "summary": "Automation error",
                        "themeColor": "0072C6",
                        "title": "Automation error"
                    };
                    break;
                case 'errored':
                    var notification = {
                        "@context": "https://schema.org/extensions",
                        "@type": "MessageCard",
                        "potentialAction": [{
                            "@type": "OpenUri",
                            "name": "Go to Automation Studio",
                            "targets": [{
                                "os": "default",
                                "uri": "https://mc.s50.exacttarget.com/cloud/#app/Automation%20Studio/AutomationStudioFuel3/"
                            }]
                        }],
                        "sections": [{
                            "facts": [{
                                "name": "Automation Name:",
                                "value": Name
                            }, {
                                "name": "Last run:",
                                "value": latestRun
                            }, {
                                "name": "Status:",
                                "value": latestStatus
                            }],
                            "text": "An automation has errored on its last execution"
                        }],
                        "summary": "Automation error",
                        "themeColor": "0072C6",
                        "title": "Automation error"
                    };
            }
            if (hoursSinceLastAlert > 6 || hoursSinceLastAlert !== null) {
                var authRequest = HTTP.Post(url, contentType, Stringify(notification));
            }
            var errorNotificationSent = today;
            var updateResults = Platform.Function.UpsertData("Automation_Status", ["Customer_Key"], [automationCustomerKey], ["Automation_Name", "Status", "ScheduledTime", "LatestCheck", "LastRun", "LastResult", "errorNotificationSent"], [Name, State, ScheduledTime, today, latestRun, latestStatus, today]);
        } else {
            var updateResults = Platform.Function.UpsertData("Automation_Status", ["Customer_Key"], [automationCustomerKey], ["Automation_Name", "Status", "ScheduledTime", "LatestCheck", "LastRun", "LastResult"], [Name, State, ScheduledTime, today, latestRun, latestStatus]);
        }
    }
}
</script>

The above SSJS should be pasted into a Script Activity in Automation Studio, which should be added to an automation as the only required activity. How often you choose your automation to run, is up to you and on how critical the monitored automations are.

Now you still need to configure your Microsoft Teams channel, by adding a webhook. You do this by clicking on the three dots in the upper right corner, and then select Connectors

You should then add a new incoming webhook:

Clicking Configure takes you to this page, where you should provide a name for your webhook and (optionally) a logo image:

Once you click Create, you are being provided with a.o. a URL:

This URL needs to be placed in your SSJS, where it will replace the placeholder value: https://url-to-your-microsoft-teams-webhook

Once all this is done, you are good to go!
Should you have any questions or suggestions for improving this solution? Please put it in a comment below.