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
  To send Microsoft Teams notifications from Autonomous Database, configure a bot in your Microsoft Teams App. Next, create a credential and use DBMS_CLOUD_NOTIFICATION procedures to send Microsoft Teams notifications.
• Send Messages to a Microsoft Teams Channel
  You can use the DBMS_CLOUD_NOTIFICATION to send messages to a Microsoft Teams channel.
• Send Query Results to a Microsoft Teams Channel
  You can use the DBMS_CLOUD_NOTIFICATION to send the results of a query to a Microsoft Teams channel.

Prepare to Send Microsoft Teams Notifications from Autonomous Database

To send Microsoft Teams notifications from Autonomous Database, configure a bot in your Microsoft Teams App. Next, create a credential and use DBMS_CLOUD_NOTIFICATION procedures to send Microsoft Teams notifications.

To send Microsoft Teams notifications:

1. Create your Microsoft Teams app and add a bot in it. The bot should have a secret key and the permission scope for 'Team', along with permissions to send notifications.

   See Developer Portal for Teams for information on setting up an app.

2. Publish the app to the org to obtain after obtaining approval from the admin, and install the app.
3. Request access for Files.ReadWrite.All and ChannelSettings.Read.All permissions for Graph API from the Azure Portal. These permissions will need to be approved by an ADMIN. To locate the permissions in Azure Portal, use the left panel to navigate to Azure Active Directory and select the Application option. On the Application page the owned application and bot will be displayed. Select the bot within the owned application to view the overview and copy the tenant id. Use the left panel to navigate to API permissions and select 'Add permission' and select Microsoft Graph and then Application Permission. Search for the Files.ReadWrite.All and ChannelSettings.Read.All permissions and add them. The ADMIN will then need to approve the permissions by navigating to the Applications page and selecting 'All Application' to find the applications and bot. From there they can view the API permissions and grant ADMIN consent for both permissions.
4. After the app is approved by an admin, the bot ID and secret key are used to create the credential object and generate a bot token.
5. If you are sending a query result to a Microsoft Teams channel, obtain the team id and the tenant id. 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

6. Obtain the channelID. It 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

7. On the Autonomous Database instance, the ADMIN creates a credential object to access the Microsoft Teams app. The username is the bot_id and the password is the bot_secret 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.

8. The ADMIN user provides access to the Microsoft Teams credential to users that want to send messages and queries to Microsoft Teams.

Send Messages to a Microsoft Teams Channel

You can use the DBMS_CLOUD_NOTIFICATION to send messages to a Microsoft Teams channel.

1. Obtain access to the credential object to send notifications to a Microsoft Teams channel. The ADMIN user creates the credential object you use to send Microsoft Teams notifications. See Prepare to Send Microsoft Teams Notifications from Autonomous Database for more information.
2. Use DBMS_CLOUD_NOTIFICATION.SEND_MESSAGE to send a message to a Microsoft Teams channel.

   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 from Step 6 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

You can use the DBMS_CLOUD_NOTIFICATION to send the results of a query to a Microsoft Teams channel.

1. Obtain access to the credential object to send notifications to a Microsoft Teams channel. The ADMIN user creates the credential object you use to send Microsoft Teams notifications. See Prepare to Send Microsoft Teams Notifications from Autonomous Database for more information.
2. Use DBMS_CLOUD_NOTIFICATION.SEND_DATA to send the output of a query to a Microsoft Teams channel.

   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 from Step 5 in Prepare to Send Microsoft Teams Notifications from Autonomous Database

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

   • channel: specifies the channel ID from Step 6 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.