Teams Guest Reminder

This scenario causes a message to be sent to each team owner which has guests in.

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetTeamsWithGuests
IsSentOnce	False

SQL Query

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.[UserType] = 'Owner' 
        AND tguests.[DataType] = 'Guest'
    GROUP BY
        teams.[TeamID]
        ,teams.[DisplayName]
        ,teams.[LastActivity]
        ,towners.[UserID]
        ,towners.[UserName]
        ,towners.[Mail]
        ,towners.[External]

END
GO

Example Data Model

{
  "RowsWithAdditionalProperties": [
   {
      "Value": {
        "TeamID": "231ec081-aba3-4643-b603-470de6209081",
        "TeamDisplayName": "Team Name 1",
        "LastActivity": "2020-11-17T12:11:58Z",
        "OwnerDisplayName": "joe.bloggs@emaildomain.com",
        "OwnerIsExternal": false,
        "GuestCount": 2
      }
    },
    {
      "Value": {
        "TeamID": "f14591a2-7606-4eb3-9029-e5a08b38165d",
        "TeamDisplayName": "Team Name 2",
        "LastActivity": "2020-11-17T10:10:58Z",
        "OwnerDisplayName": "temp.temp@emaildomain.com",
        "OwnerIsExternal": false,
        "GuestCount": 2
      }
    }
  ]
}

Example Card Template

Basic format to be customised

{
  "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
  "version": "1.2",
  "type": "AdaptiveCard",
  "originator": "OriginatorIdPlaceholder",
  "body": [
    {
      "type": "Container",
      "backgroundImage": {
        "url": "https://cdn.modalitysystems.com/TeamworkAnalytics/Automation/Backgrounds/header_lightblue.png"
      },
      "items": [
        {
          "type": "ColumnSet",
          "columns": [
            {
              "type": "Column",
              "items": [
                {
                  "type": "Image",
                  "url": "https://cdn.modalitysystems.com/Icons/ic_fluent_guest_filled.png",
                  "size": "Small"
                }
              ],
              "width": 30
            },
            {
              "type": "Column",
              "items": [
                {
                  "type": "TextBlock",
                  "text": "Teams Status",
                  "horizontalAlignment": "Right",
                  "wrap": true,
                  "color": "Dark",
                  "size": "Small"
                },
                {
                  "type": "TextBlock",
                  "text": "TEAMS WITH GUESTS",
                  "horizontalAlignment": "Right",
                  "size": "Large",
                  "color": "Attention",
                  "wrap": true,
                  "weight": "Bolder",
                  "spacing": "None"
                }
              ],
              "width": 70,
              "horizontalAlignment": "Right"
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "${RowsWithAdditionalProperties[0].Value.OwnerDisplayName}, you are an owner of teams with guests",
      "wrap": true,
      "size": "Large",
      "color": "Accent"
    },
    {
      "type": "TextBlock",
      "text": "This is a notification that you are the owner of 1 or more teams in Microsoft Teams that have external users (guests). As an owner, please ensure that access is appropriate for the content being shared in that team.",
      "wrap": true
    },
    {
      "type": "FactSet",
      "facts": [
        {
          "title": "Team",
          "value": "${Value.TeamDisplayName}"
        },
        {
          "title": "Guests",
          "value": "${Value.GuestCount}"
        },
        {
          "title": "Last Activity",
          "value": "{{DATE(${Value.LastActivity}, SHORT)}} {{TIME(${Value.LastActivity})}}"
        }
      ],
      "$data": "${RowsWithAdditionalProperties}",
      "separator": true,
      "spacing": "default"
    },
    {
      "type": "TextBlock",
      "text": "Thank you for your help.",
      "wrap": true,
      "separator": true
    }
  ]
}