Send Microsoft Teams Notifications from Autonomous Database

Describes how to configure Microsoft Teams so that you can send messages, alerts, or output of a query from Autonomous Database to a Microsoft Teams Channel. Also describes the procedures you use to send Microsoft Teams notifications.

• Prepare to Send Microsoft Teams Notifications from Autonomous Database
  Get started by configuring a bot in your Microsoft Teams app. Next, create a credential to use with the DBMS_CLOUD_NOTIFICATION procedures to send Microsoft Teams notifications from Autonomous Database.
• Send Messages to a Microsoft Teams Channel
  
• Send Query Results to a Microsoft Teams Channel
  

Prepare to Send Microsoft Teams Notifications from Autonomous Database

Get started by configuring a bot in your Microsoft Teams app. Next, create a credential to use with the DBMS_CLOUD_NOTIFICATION procedures to send Microsoft Teams notifications from Autonomous Database.

To configure Microsoft Teams notifications:

1. Create your Microsoft Teams app and add a bot in it. See Developer Portal for Teams for information on setting up an app.
2. From the Bot Management section, ensure that the bot has a secret key, scope set to Team and permission to send notifications.
3. Publish the app to your org to make it available to the people in your org.
4. After your IT admin approves the app from the admin center, install the app from the Apps section in Teams.
5. Request the Files.ReadWrite.All and ChannelSettings.Read.All permissions to the app for Graph API from the Azure Portal using the following instructions:
   1. Log into your Azure Portal, navigate to Azure Active Directory using the left panel, and select the Application option.
   2. The Application page displays the apps that you own along with the bots added to those apps. Click the bot to view its details.
   3. Copy the directory/tenant id from the bot overview page for later use.
   4. Then, go to API permissions in the left panel. Under API permissions, click Add permission, select Microsoft graph and then Application permission.
   5. Search for Files.ReadWrite.All and ChannelSettings.Read.All permissions and add them.
6. Have your IT admin approve the above-requested permissions from the Azure portal by following the steps below:
   1. Log into your Azure Portal, navigate to Azure Active Directory using the left panel, and select the Application option.
   2. Select All applications from the Application page.
   3. Search the application/bot by its name, go to API permissions, and grant the ADMIN consent for the requested permissions: Files.ReadWrite.All and ChannelSettings.Read.All.

   Tip:

   After your app is approved by the IT Admin, you can provide the bot id and secret key to other users to install the app within Teams in the org.
7. After the app is approved by the IT admin and the above requested permissions are granted, you can use the app's bot ID and secret key are used to create the credential object and generate a bot token.
8. To send a query result to a Microsoft Teams channel, obtain the team id and the tenant id.

   Tip:

   The team id is located in the team link between /team/ and /conversations. The tenant id is located after "tenantId=" at the end of the team link. This link is found by clicking the three dots next to the team name and selecting Get link to team.

   For example:

   https://teams.microsoft.com/l/team/teamID/conversations?groupId=groupid%tenantId=tenantid

9. Obtain the channelID.

   Tip:

   channelID is located in the channel link between /team/ and the channel name. This link is found by clicking the three dots next to the channel name and selecting Get link to channel.

   For example:

   https://teams.microsoft.com/l/channel/channelID/channel_name?groupId=groupid&tenantId=tenantid

10. Create a credential object to access the Microsoft Teams app from Autonomous Database.

    Tip:

    If you can not use the CREATE_CREDENTIAL procedure successfully, consult with the ADMIN user to grant execute access on DBMS_CLOUD packages.

    The credential's username is the bot_id and the password is the bot key.

    For example:

    
    BEGIN     
        DBMS_CLOUD.CREATE_CREDENTIAL(credential_name => 'TEAMS_CRED',       
            username        => 'bot_id', 
            password        => 'bot_secret');
    END;
    /
     

    See CREATE_CREDENTIAL Procedure for more information.

Send Messages to a Microsoft Teams Channel

After creating the Microsoft Teams credential object as described in Prepare to Send Microsoft Teams Notifications from Autonomous Database, you can use the DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE procedure to send a message to a Microsoft Teams channel.

Example:

BEGIN
     DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE(
        provider        => 'msteams',
        credential_name => 'TEAMS_CRED',
        message         => 'text from new teams api',
        params          => json_object('channel' value 'channelID'));
END;
/
 

Use the params parameter to specify the channel.

• channel: specifies the Channel ID obtained from Step 10 in Prepare to Send Microsoft Teams Notifications from Autonomous Database.

See SEND_MESSAGE Procedure for more information.

Send Query Results to a Microsoft Teams Channel

After creating the Microsoft Teams credential object as described in Prepare to Send Microsoft Teams Notifications from Autonomous Database, you can use the DBMS_CLOUD_NOTIFICATION.SEND_DATA procedure to send the output of a query to a Microsoft Teams channel.

Example:

BEGIN
     DBMS_CLOUD_NOTIFICATION.SEND_DATA(provider => 'msteams',
        credential_name => 'TEAMS_CRED',
        query           => 'SELECT tablespace_name FROM dba_tablespaces',
        params          => json_object('tenant'value '5b743bc******c0286',
                                       'team'value '0ae401*********5d2bd',
                                       'channel'value '19%3a94be023*****%40thread.tacv2',
                                       'title'value 'today',
                                       'type'value 'csv'));
END;
/
 

Use the params parameter to specify the tenant, team, channel, title, and data type in string values.

• tenant: specifies the tenant ID obtained from Step 8 in Prepare to Send Microsoft Teams Notifications from Autonomous Database.

• team: specifies the team ID obtained from Step 8 in Prepare to Send Microsoft Teams Notifications from Autonomous Database.

• channel: specifies the channel ID obtained from Step 9 in Prepare to Send Microsoft Teams Notifications from Autonomous Database.

• title: specifies the title of the file. The title can only contain alphabets, digits, underscores, and hyphens. The file name that appears in Microsoft Teams will be a concatenation of the title parameter and the timestamp to ensure uniqueness. The maximum title size is 50 characters.

  For example: 'title'_'timestamp'.'format'

• type: This specifies the output format. Valid values are CSV or JSON.

Note:

The maximum file size supported when using DBMS_CLOUD_NOTIFICATION.SEND_DATA for Microsoft Teams is 4MB.

See SEND_DATA Procedure for more information.