Introduction

In my post Use MS Teams to Chat with the World via Oracle REST Data Services, I discussed posting messages from MS Teams to an ORDS Web Service and returning a response to the MS Teams chat (essentially a lightweight bot). This post will focus on sending actionable cards from the Oracle Database to MS Teams using a Teams Incoming Webhook. User input to these cards can then be posted back to an ORDS REST service in real-time.

Example use cases include:

• Notifying users of events that occur in your ERP system (and responding to them):
  • Sending a pending inventory outage to buyers and receiving the go-ahead from a buyer (via Teams) to automatically create a Purchase Order
  • Sending customer credit check failures to the credit team
  • Notify IT security of employee terminations in HR so that accounts can be end dated
• Sending system alert notifications and capturing an acknowledgment
• Sending out brief surveys, receiving and processing responses
• Sending messages from an APEX application to Teams Users and receiving responses

The demo code for this post is available in this GitHub Repository.

Example Use Case

This post will follow the example of an Alert Notification to let APEX Developers know about slow-running APEX pages. The process works like this:

1. An APEX Automation calls a PL/SQL package to check for slow-running pages using the apex_workspace_activity_log table
2. If slow-running pages are found, the PL/SQL package creates a JSON document and POSTs it to a URL generated when we create the Teams Incoming Webhook
3. The MS Teams user receives the Message Card, enters a comment, and clicks the Submit button on the Message Card
4. The Submit button on the MS Teams card initiates a POST request to an ORDS REST service which captures the users' comment
5. The ORDS REST service accepts the payload with the comment from Teams, verifies a token, and processes the comment

Here is a screenshot of an example card sent via the above process. The Teams user receives the card, enters their comment, and clicks the 'Submit' button. The user's comments are then posted to an ORDS REST service, where they can be processed.

Configure the Teams Incoming Webhook

Let us start by creating the MS Teams Incoming Webhook. Creating the Incoming Webhook will give us the URL we need to POST messages to a specific Teams Channel. This post assumes you have already created a Team and are the owner.

1. Click on the ellipses to the right of the Team and Click 'Manage Team'
2. Click on the 'Apps' tab
3. Search for and click on 'Incoming Webhook'
4. Click 'Add to team'. This enables Incoming Webhooks for the Team, but we still need to configure a specific Incoming Webhook for the Teams Channel
5. Select your Channel and then click 'Set up a connector'
6. Click on 'Configure'
7. Enter a name, upload an image and click 'Create'
8. Teams will then generate a unique URL. ✏️ Take a note of the URL

Testing the Incoming Webhook

Before getting into the code, I recommend testing the webhook using Postman (or a similar tool). This testing will allow you to iterate quickly through different card formats and test responses sent to the ORDS Web Service. Setup Postman as follows, the URL is the Incoming Webhook URL generated above. You should expect a 200 OK response with an empty response body. You can use the sample JSON in the 'Generating the JSON' section below to test from Postman.

Designing the Teams Card

This is also a good time to design and test the card format you want to send to the Teams Channel. I am creating a card of the type 'MessageCard', also known as an 'Office 365 Connector card' in the demo code. Although this is a 'Legacy' card type, it is currently the only card type that supports posting back to a custom URL (e.g., ORDS REST Endpoint) using the HttpPOST method. You can use this web site to help design 'MessageCards'.

If you do not need to post a reply to an ORDS web service, then I suggest you use the Adaptive Card type. Adaptive Cards seem to be the future direction for Microsoft.

See this link for a description of the different card types supported by MS Teams.

Sending Cards to the Teams Channel

Now that we have our Inbound Webhook created and tested, we need to build code to send the card to the Teams Channel. In this section, I will highlight some critical aspects of the demo code. See the Github Repository for the complete demo code for this post.

Initiating the Message

In my example, I have created an APEX Automation that periodically calls a procedure to check the apex_workspace_activity_log table for any pages that have taken two or more seconds to render. Here are two screenshots that show the Automation: Please read my post for more on APEX Automations.

The Automation calls a procedure automation_apex_alert, passing in the date and time the Automation last ran. This procedure first checks if there were any slow-running page views since the Automation last ran. If there are, it proceeds to create a JSON payload and send it to the Teams Channel.

Generating the JSON

If there are slow-running pages, then the procedure automation_apex_alert builds a JSON document conforming to the 'MessageCard' or 'Office 365 Connector card' format. I won't go through all of the logic here. I will focus on the code that generates the JSON to render the Comments field on the Teams Card, and POSTs a response to our ORDS Web Service when the user clicks the Submit button on the Teams Card.

    apex_json.open_array('potentialAction');
    apex_json.open_object; -- {
    apex_json.write('@type', 'ActionCard');
    apex_json.write('name', 'Add Comments');
      apex_json.open_array('inputs');
      apex_json.open_object; -- {
      apex_json.write('@type', 'TextInput');
      apex_json.write('id', 'comment');
      apex_json.write('title', 'Comment');
      apex_json.write('isMultiline', TRUE);
      apex_json.close_object; -- }
      apex_json.close_array; -- } inputs
      apex_json.open_array('actions');
      apex_json.open_object; -- {
      apex_json.write('@type', 'HttpPOST');
      apex_json.write('name', 'Submit');
      -- Send a response URL on the card. This ORDS endpoint will be called when the user clicks the Acknowledge button on the card.
      apex_json.write('target', 'https://www.example.com/ords/demo/teams/incoming_webhook_response?response_token='||GC_CARD_RESPONSE_TOKEN);
      -- You can put anything you want in the body; this will be passed back as a JSON Payload when the above URL is called.
      apex_json.write ('body', '{"comment":"{{comment.value}}"}');
      apex_json.close_object; -- }
      apex_json.close_array; -- ] actions
    apex_json.close_object; -- }
    apex_json.close_array; -- ] potentialAction

• In the actions array, the code that starts apex_json.write('@type', 'HttpPOST'); generates JSON which indicates to Teams that we want to render a button called 'Submit' that when clicked, should send a HttpPOST request to the URL `example.com/ords/demo/teams/incoming_webhoo..
• We are also passing a parameter with the URL ?response_token='||GC_CARD_RESPONSE_TOKEN. The constant GC_CARD_RESPONSE_TOKEN is a token used by the ORDS service during the response to Authenticate the request (see below for details)
• The line apex_json.write ('body', '{"comment":"{{comment.value}}"}'); generates JSON which tells Teams what should be sent in the body of the HttpPOST request. The value {{comment.value}} is replaced with the value entered by the Teams user in the Comment field of the card

Here is an example of a JSON payload sent to Teams by my sample code:

{
    "@type": "MessageCard",
    "@context": "https:\/\/schema.org\/extensions",
    "title": "APEX Slow Page Alert [PROD]",
    "summary": "APEX Slow Page Alert",
    "themeColor": "0078D7",
    "sections": [{
        "startGroup": true,
        "activityTitle": "Application ID: [4000]",
        "activitySubtitle": "Page ID: [7021]",
        "activityImage": "https:\/\/objectstorage.us-ashburn-1.oraclecloud.com\/n\/nueva\/b\/nueva-public\/o\/Blog%20Public%20Files%2FTeams%20Incoming%20Webhooks%2FAPEX%20Rounded%20Corners.png",
        "facts": [{
            "name": "Number of Ocurrences",
            "value": 3
        }, {
            "name": "Max. Duration (seconds)",
            "value": 2.49
        }]
    }, {
        "startGroup": true,
        "activityTitle": "Application ID: [4850]",
        "activitySubtitle": "Page ID: [100]",
        "activityImage": "https:\/\/objectstorage.us-ashburn-1.oraclecloud.com\/n\/nueva\/b\/nueva-public\/o\/Blog%20Public%20Files%2FTeams%20Incoming%20Webhooks%2FAPEX%20Rounded%20Corners.png",
        "facts": [{
            "name": "Number of Ocurrences",
            "value": 1
        }, {
            "name": "Max. Duration (seconds)",
            "value": 2.01
        }]
    }, {
        "startGroup": true,
        "activitySubtitle": "Issues detected between 13-JUL-2022 05:38:42 pm and 13-JUL-2022 08:44:43 pm UTC"
    }],
    "potentialAction": [{
        "@type": "ActionCard",
        "name": "Add Comments",
        "inputs": [{
            "@type": "TextInput",
            "id": "comment",
            "title": "Comment",
            "isMultiline": true
        }],
        "actions": [{
            "@type": "HttpPOST",
            "name": "Submit",
            "target": "https:\/\/www.example.com\/ords\/demo\/teams\/incoming_webhook_response?response_token=S3TwFW283839qQCRAwwt18=",
            "body": "{\"comment\":\"{{comment.value}}\"}"
        }]
    }]
}

Sending the JSON

Once the JSON has been built, it is just a matter of POSTing it to the URL Teams generated when we created the Incoming Webhook. This can be done with apex_web_service.make_rest_request:

      l_teams_response := apex_web_service.make_rest_request
       (p_url         => GC_TEAMS_CHANNEL_URL,
        p_http_method => 'POST',
        p_body        => l_teams_json);

The URL for the Incoming Webhook will look something like this https://cloudnueva.webhook.office.com/webhookb2/fe1f1b7e-123r-4d65-8c48-b3d5b732f987@990876cd-77d7-4fab-8cd2-57020376ed11/IncomingWebhook/34d33b0195204a0c9aba6ae8b240ef4f/111a2f8-d26c-4fc1-b1c0-ffbaf2af1337

If everything goes well, you should expect a 200 OK response from Teams.

Handling Responses from Teams

In the demo code, you will see an ORDS handler called incoming_webhook_response. This handler receives a request from Teams when a user enters a comment and clicks the Submit button on the card. The handler calls the procedure capture_card_response, which does the following:

1. Verifies the token passed in the response_token parameter against the token we initially passed to the Teams Card. If they do not match, then it responds with a 400 un-authorized response
2. Parses the JSON payload to get the comment entered by the user in Teams

I am not doing anything of note with the response payload in my demo code, but you could.

Improving Security

Instead of using a constant token value, I recommend generating a random string for each card generated, storing it in a table then checking for the existence of that string in your ORDS service when a response is received. Remember to invalidate or delete the token once it has been used. See the Microsoft documentation on Security Requirements Action Message Cards for more details.

Conclusion

Being able to send messages to MS Teams opens up a whole new way of communicating with your employees (and potentially your customers). Being able to take action on Teams user responses to these messages opens the door to frictionless automation.

But, as the saying goes...

With great power comes great responsibility. You should only use this approach for important and urgent communications and avoid spamming people in Teams with unimportant system-generated messages.

🔗 Read More

• 📖 Use MS Teams to Chat with the World via Oracle REST Data Services
• 🩳 APEX Shorts
• #️⃣ APEX Posts