Introduction

Microsoft Teams has become ubiquitous in the business world. Being able to integrate Teams with other systems can produce some exciting benefits. What if you could message your ERP System to request information or inquire about the latest commodity prices from a Microsoft Teams chat window?

Microsoft allows you to create fully-fledged bots with Teams using its bot framework. Building bots for Teams is a pretty involved process, creating a Microsoft Teams Outbound Webhook is much easier to implement and is an excellent introduction to the world of bots.

This post will show you how to configure a Microsoft Teams Outbound Webhook to call a secured Oracle REST Data Services (ORDS) REST service to get the weather forecast for a given city and state.

The end-to-end process will work as follows:

The user creates a new Conversation in Microsoft Teams
The user @ mentions the Outbound Webhook and enters a city and state
MS Teams builds a JSON document and calls a REST Webhook developed using ORDS and PL/SQL. The ORDS Rest service will do the following:
- Verify that the ORDS service is being called by the specific Outbound Webhook and return an unauthorized response if not
- Call the Oracle eLocation API to get the longitude and latitude of the city
- Call a weather.gov API to get the coordinates of the local weather station using the longitude and latitude of the city
- Call a second weather.gov API to get the weather forecast
- Build a JSON document including the weather forecast data, representing an Adaptive Card and return it to MS Teams
MS Teams receives the JSON response from the ORDS REST service and displays the card to the end-user in the chat

The video below demonstrates the finished state. Example showing Teams getting weather with ORDS

Create the REST Webhook with ORDS

We first need to create an ORDS REST service to receive the message from the Teams Outbound Webhook.

Code for Demo

You can get the code to create the ORDS Handler and the PL/SQL code referenced in this post from my Github Repository. I encourage you to download the code now, as reviewing this code will help when reading the remainder of this blog.

Prerequisites

ORDS is up and running and accessible over an HTTPS end-point
You have a schema that has been ORDS Enabled
You have granted your database user access to execute the dbms_crypto package e.g., grant execute on sys.dbms_crypto to demo;.

ORDS Module, Template, and Handler

The below code creates an ORDS module, template, and POST handler. The handler will be referenced when we get to the Teams configuration. The remainder of the post assumes the end-point for the ORDS handler is: http://www.example.com/ords/demo/teams/weather_webhook

BEGIN
  ORDS.DEFINE_MODULE(
      p_module_name    => 'teams',
      p_base_path      => '/teams/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'Module used to host REST Services for Teams Integrations.');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'teams',
      p_pattern        => 'weather_webhook',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'Used to Process Requests from the Teams Outbound WebHook');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'teams',
      p_pattern        => 'weather_webhook',
      p_method         => 'POST',
      p_source_type    => 'plsql/block',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'DECLARE
  l_payload_clob    CLOB;
BEGIN
  l_payload_clob := :BODY_TEXT;
  cndemo_teams_bot_pk.teams_weather
   (p_request       => l_payload_clob,
    p_provided_hmac => :provided_hmac_value,
    x_status        => :status);
END;');

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'teams',
      p_pattern            => 'weather_webhook',
      p_method             => 'POST',
      p_name               => 'X-ORDS-STATUS-CODE',
      p_bind_variable_name => 'status',
      p_source_type        => 'HEADER',
      p_param_type         => 'INT',
      p_access_method      => 'OUT',
      p_comments           => NULL);

  ORDS.DEFINE_PARAMETER(
      p_module_name        => 'teams',
      p_pattern            => 'weather_webhook',
      p_method             => 'POST',
      p_name               => 'Authorization',
      p_bind_variable_name => 'provided_hmac_value',
      p_source_type        => 'HEADER',
      p_param_type         => 'STRING',
      p_access_method      => 'IN',
      p_comments           => NULL);
COMMIT;
END;

PL/SQL Code to Process the Request

The procedure cndemo_teams_bot_pk.teams_weather (called in the ORDS Handler above) orchestrates all of the steps required to handle security, parse the JSON payload, fetch the weather forecast and generate a JSON response for Teams. I won't go into the logic in this blog; instead, I encourage you to review the code yourself.

The Sky is the Limit. The code called by the ORDS Handler can be anything you want as long as you return an HTTP 201 Status and a JSON document that conforms to one of the standard JSON responses. See this link for details of the supported Teams card responses.

Securing the ORDS Service

Microsoft sends an HTTP Header called 'Authorization' along with an HMAC value, e.g., HMAC NLvfAHhW9MqjHAZyNz/mGi1wRzZweo7FHHO0OsGSGh0=. On our side, we need to generate our own HMAC value using the payload and the 'Security Token' generated when configuring the Outbound Webhook in Teams (see below for how to get the Security Key). If these two HMAC values match, we can be sure the specific Teams Outbound Webhook called our ORDS Service.

❗Without this check, anyone could call your ORDS Service.

The function below performs the security check. Microsoft has some sample JavaScript code which I used to figure out the appropriate PL/SQL code. Microsoft also has a descriptive list of steps to calculate and compare the HMAC values here.

FUNCTION verify_authorization
  (p_payload       IN VARCHAR2,
   p_provided_hmac IN VARCHAR2) RETURN BOOLEAN IS

  l_raw_mac          RAW (2000);
  l_calculated_hmac  VARCHAR2(32000);
  l_provided_hmac    VARCHAR2(250);

BEGIN

  -- The Header Value is like this: HMAC NLvfAHhW9MqjHAZyNz/mGi1wRzZweo7FHHO0OsGSGh0=
  -- We need to strip the 'HMAC ' from this to get the HMAC value only.
  l_provided_hmac := REPLACE(p_provided_hmac, 'HMAC ', NULL);

  -- Generate a HMAC256 encoded String using the JSON Payload from Teams (p_payload)
  --   and the 'Security Key' generated when we created the Outbound Webhook in Teams (GC_TEAM_SECURITY_TOKEN)
  l_raw_mac := dbms_crypto.mac
    (src => UTL_I18N.STRING_TO_RAW (p_payload, 'UTF8'),
     typ => DBMS_CRYPTO.HMAC_SH256, 
     key => UTL_ENCODE.base64_decode(UTL_I18N.STRING_TO_RAW(GC_TEAM_SECURITY_TOKEN, 'UTF8')));
  l_calculated_hmac := UTL_I18N.RAW_TO_CHAR(UTL_ENCODE.base64_encode(l_raw_mac), 'UTF8');

  -- Compare the HMAC generated above to the HMAC provided in the Header.
  IF l_provided_hmac = l_calculated_hmac THEN
    RETURN TRUE;
  ELSE
    RETURN FALSE;
  END IF;

END verify_authorization;

Testing the ORDS Service

I recommend testing the ORDS handler using Postman (or something similar) before you start testing from MS Teams. Using Postman lets you iterate and ensure you get the results you want faster than troubleshooting through the Teams UI.

❗You will need to temporarily comment out the call to the function verify_authorization when testing from Postman.

Postman Testing ORDS Webhook

You can use the following JSON for testing. Change the value of the text field to test different cities.

{
    "type": "message",
    "id": "1657323465598",
    "timestamp": "2022-07-08T23:37:45.6214878Z",
    "localTimestamp": "2022-07-08T16:37:45.6214878-07:00",
    "serviceUrl": "https://smba.trafficmanager.net/amer/",
    "channelId": "msteams",
    "from": {
        "id": "DEMO",
        "name": "Jonathan Dixon",
        "aadObjectId": "DEMO"
    },
    "conversation": {
        "isGroup": true,
        "id": "DEMO",
        "name": null,
        "conversationType": "channel",
        "tenantId": "DEMO"
    },
    "recipient": null,
    "textFormat": "plain",
    "attachmentLayout": null,
    "membersAdded": [],
    "membersRemoved": [],
    "reactionsAdded": null,
    "reactionsRemoved": null,
    "topicName": null,
    "historyDisclosed": null,
    "locale": "en-US",
    "text": "<at>Weather</at>San Diego, CA",
    "speak": null,
    "inputHint": null,
    "summary": null,
    "suggestedActions": null,
    "attachments": [{
        "contentType": "text/html",
        "contentUrl": null,
        "content": "<div><div><span itemscope=\"\" itemtype=\"http://schema.skype.com/Mention\" itemid=\"0\">Time In</span> London</div>\n</div>",
        "name": null,
        "thumbnailUrl": null
    }],
    "entities": [{
        "type": "clientInfo",
        "locale": "en-US",
        "country": "US",
        "platform": "Web",
        "timezone": "America/Los_Angeles"
    }],
    "channelData": {
        "teamsChannelId": "DEMO",
        "teamsTeamId": "DEMO",
        "channel": {
            "id": "DEMO"
        },
        "team": {
            "id": "DEMO"
        },
        "tenant": {
            "id": "DEMO"
        }
    },
    "action": null,
    "replyToId": null,
    "value": null,
    "name": null,
    "relatesTo": null,
    "code": null,
    "localTimezone": "America/Los_Angeles"
}

Here is an example response generated by my code. This JSON will produce a simple Adaptive card in Teams.

{
    "type": "message",
    "attachments": [
        {
            "contentType": "application/vnd.microsoft.card.adaptive",
            "content": {
                "type": "AdaptiveCard",
                "version": "1.4",
                "body": [
                    {
                        "type": "TextBlock",
                        "text": "Tonight-Mostly cloudy, with a low around 64. Northwest wind around 5 mph.",
                        "wrap": true
                    },
                    {
                        "type": "Image",
                        "url": "https://api.weather.gov/icons/land/night/bkn?size=medium"
                    }
                ]
            }
        }
    ]
}

The above JSON produces the below response in MS Teams: Screenshot of Adaptive Card Response

Configuring the Teams Outbound Webhook

Now it is time to set up the Outbound Webhook in Teams. I assume you (or an administrator) has already created a team and that you are the team owner. In my case, I have a team called 'ORDS Integration'. You can complete the steps below in the desktop or browser version of MS Teams.

Click on the ellipses to the right of the team name and click 'Manage team'
Click on the link 'Create an outgoing webhook'
Enter a name and description, and select a profile picture for your Webhook. Then enter the URL for your ORDS end-point (created above) in the 'Callback URL' field
Click the 'Create' button.
Teams will then show you a security token. ✏️ Take note of this value; the verify_authorization function uses it in the sample PL/SQL code. Click 'Close'
You should then see the Webhook in the list of Apps available for the Team

❗The name you enter above will be used to reference the App when you '@' mention it in a Teams chat. The description also shows up when you '@' mention the App, so make it short and meaningful.

That concludes the MS Teams setup.

Conclusion

Hopefully, I have provided everything you need to build your own Outbound Webhooks with Teams and Oracle REST Data Services. This technology combination allows your users to engage with hundreds of other services from the comfort of their Teams chat window.

Use MS Teams to Chat with the World via Oracle REST Data Services

Table of contents