Team Guest Reminder

# Teams Guests Reminder

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

Required Settings

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
    }
  ]
}