Configuring Automation Scenarios

Create or Edit a Scenario

Everything related to TWA Automation is located within the TWA database which will be located within the “automation” schema, for example, a table would be labelled as “automation.tablename”. Unless specifically instructed here do not change the database schema or data content of this database. Only the areas specified in these docs should be configured as documented.

Creating a new scenario involves:

1. Creating a new stored procedure to retrieve the list of users which match the scenario and any associated data
2. Adding the scenario to the list of scenarios in configuration
3. Defining a new template for the messages sent to users (via Microsoft Teams message)
4. Defining a schedule for the new scenario to operate on

Existing scenarios can be modified in any of the 4 ways described.

1. Create a Stored Procedure

1. Connect to the TWA Database

2. Create a stored procedure, ensure that it has a schema (prefix) of “automation”, for example, “automation.spNewScenario”

3. It is important to the whole process, that the stored procedure has to return the following columns

• UserId
• UserMailAddress
• These columns must return values and are case sensitive. If there are any rows that do not have a value for one of these properties, that row will be ignored and logged. Only valid rows will be sent to the cloud solution. Any additional columns that are returned will be processed.

4. Any additional columns which are returned will be supported, but are not a requirement in order to function

All additional columns should return values. If any of the additional columns do not have a value (null), the message for that row will still be sent, but the the value will display as (null).

1. Parameters are not supported, so do not add these e.g. automation.spNewScenario @Name nvarchar(30)

2. Run the SQL inside of the procedure and test that the data you expect is returned and that no errors occur

3. Use the code below (BotGuestNotifications Scenario) as a guide to writing your own custom scenario:

TextText

CREATE PROCEDURE [automation].[SpGetTeamsWithGuests]
AS
BEGIN
    SELECT
        teams.[TeamID]
        ,teams.[DisplayName] AS TeamDisplayName
        ,teams.[LastActivity]
        ,towners.[UserID] AS UserID
        ,towners.[UserName] AS OwnerDisplayName
        ,towners.[Mail] AS UserMailAddress
        ,towners.[External] AS OwnerIsExternal
        ,COUNT(tguests.[UserID]) AS GuestCount
    FROM automation.Teams teams
        JOIN automation.TeamUsers towners ON towners.[TeamID] = teams.[TeamID]
        JOIN automation.TeamUsers tguests ON tguests.[TeamID] = teams.[TeamID]
    WHERE
        teams.IsArchived=0
        AND towners.[External] = 0
        AND towners.[UserType] = 'Owner'
        AND tguests.[DataType] = 'Guest'
        AND towners.[Anonymised] = 0
    GROUP BY
        teams.[TeamID]
        ,teams.[DisplayName]
        ,teams.[LastActivity]
        ,towners.[UserID]
        ,towners.[UserName]
        ,towners.[Mail]
        ,towners.[External]
END
GO

2. Add the scenario to the list of scenarios in the configuration

Edit the table “automation.Scenarios”, adding a new row to define the new scenario.

This table contains the following columns:

• Name – This should match the parameter passed in by the notification service.
• StoredProcedureName – The name of the stored procedure which shall be searched for when the notification service runs.
• IsSentOnce
  • If set to 1 (true), a message (IM) with specific content will only be sent to a user once, if the scenario is run again the message will not be resent. If set to 0 (false), a message will be sent every time the notification service is run regardless of if it’s been sent before.
  • Additionally, when true, each message will contain data from a single row returned by the stored procedure, meaning a single user can receive many messages. Whereas when turned off, rows are grouped by the user they are being sent to, meaning a single user will only receive one message detailing all returned rows per scenario.

By default, the following values should be populated in the table:

3. Defining a new template for the messages sent to users (via Microsoft Teams message)

Template definition is defined in the Azure Table Storage store, in the “ScenarioTemplates” table. This table has the following columns:

• PartitionKey – represents the tenant id
• RowKey – must match the name of the scenario
• Template – the content which will display when the scenario sends a Teams IM (Configuring Adaptive Cards)

If this table does not exist or is completely empty the four default scenarios which mentioned above will still work using hardcoded values.

Templates for existing scenarios can be changed by editing this table, and will take effect the next time the scenario is scheduled.

Here is an example template, which is the default guest scenario.

4. Defining a schedule for the new scenario to operate on

Each scenario requires its own scheduled task on the machine where the bot notification service is located.

The bot notification service only processes 1 scenario, which is detected by a parameter passed into the service. This is why, once the ARM template has been deployed to a machine, there will be 4 scheduled tasks, all configured and set up to point to the scenarios configured in SQL. As seen below:

Create a new scheduled task, copying the executable path from the other scheduled tasks. The only difference is that you will need to enter in the scenario name value that you gave within the SQL DB. To do this, follow these steps:

1. Set up a new scheduled task/or re-use existing task by:
   1. Export existing task
   2. Import the exported task with the name of your new scenario
2. Open your new task
   1. Click “Actions”
   2. Highlight the “Start a program” action
   3. Click “Edit”
   4. Update the “Add Arguments (Optional)” parameter to be the same as the scenario name you entered in SQL.

Make sure the task has “run when there is no one logged in” set.

Ensure that the name matches exactly the same as the “name” column that you added into the Scenarios SQL table.

Pay close attention to the scheduled times which you set-up for your task, take into account what your custom stored procedure is doing (for example, is the data over a specified amount of time) and set a reasonable time to run, also consider the remaining tasks scheduled times.

Running Configured Scenario

Follow all the steps above should now mean that any user that meets the criteria for your scenario will receive a Teams IM when ran at the configured time.

If you wish to test your scenario immediately, simply connect to the machine where the scheduled tasks are configured and run the task. However, bear in mind that if all configured correctly – the users will receive a Teams IM.

There is a cap in the notification service that only returns a max of 35 teams, per user. This is due to restrictions of Bot Framework message sizes.

Adaptive Card Limits

Each notification can contain a maximum of 35 teams, if there are more than 35 teams, only the first 35 will be included in the notification. This is due to a restriction of bot framework message sizes.

A single adaptive card template is used to generate the message sent to Teams. This means they look very similar when viewed in both.