Deleted Team Owner Reminder

This scenario causes a message to be sent to each team owner when a team that has recently been deleted.

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetDeletedTeamsByOwner
IsSentOnce	False

SQL Query

CREATE PROCEDURE [automation].[SpGetDeletedTeamsByOwner]
AS
BEGIN

    DECLARE @UpdatedInLastNDays             INT = 30
    DECLARE @UsersAddedMoreThanNHoursAgo    INT = 1

    SELECT 
        t.[Id] As TeamId
        ,u.[Id] As UserID
        ,t.[DisplayName] As TeamDisplayName
        ,u.[Mail] As UserMailAddress
        ,CAST(0 AS BIT) As OwnerIsExternal
        ,t.[LastUpdatedDateTime] AS TeamLastUpdated
        ,tu.[LastUpdatedDateTime] AS MembershipLastUpdated
    FROM dbo.Teams t
        JOIN dbo.[TeamUsers] tu on tu.[TeamId] = t.[Id]
        JOIN dbo.Users u on tu.[UserId] = u.[Id]
    WHERE t.[Deleted] = 1
        AND tu.[UserType] = 'Owner'
        AND t.[LastUpdatedDateTime] IS NOT NULL
        AND tu.[LastUpdatedDateTime] IS NOT NULL
        AND t.[LastUpdatedDateTime] >= DATEADD(Day, -@UpdatedInLastNDays, GETDATE())
        AND ABS(DATEDIFF(second, tu.[LastUpdatedDateTime], t.[LastUpdatedDateTime])) <= (@UsersAddedMoreThanNHoursAgo * 3600)
    GROUP BY
        t.[Id]
        ,t.[DisplayName]
        ,u.[Id]
        ,u.[Mail]
        ,tu.[LastUpdatedDateTime]
        ,t.[LastUpdatedDateTime]

END
GO

Example Data Model

{
  "RowsWithAdditionalProperties": [
    {
      "Value": {
        "TeamId": "b4fa62d8-b387-47d0-8e7c-6186375da2c2",
        "TeamDisplayName": "Team Name 1",
        "OwnerIsExternal": false,
        "TeamLastUpdated": "2021-06-28T13:36:07Z",
        "MembershipLastUpdated": "2021-06-28T13:30:02Z"
      }
    },
    {
      "Value": {
        "TeamId": "ec1edfa9-cb50-4476-8bbe-9dc7b269d1ff",
        "TeamDisplayName": "Team Name 2",
        "OwnerIsExternal": false,
        "TeamLastUpdated": "2021-06-04T09:28:13Z",
        "MembershipLastUpdated": "2021-06-04T09:22:25Z"
      }
    }
  ]
}

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_people_team_delete_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": "DELETED",
                  "horizontalAlignment": "Right",
                  "spacing": "None",
                  "size": "Large",
                  "color": "Attention",
                  "wrap": true,
                  "weight": "Bolder"
                }
              ],
              "width": 70
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "Your team '${RowsWithAdditionalProperties[0].Value.TeamDisplayName}' has been deleted",
      "wrap": true,
      "size": "Large",
      "color": "Accent"
    },
    {
      "type": "TextBlock",
      "text": "This is a notification that you are the owner of the team '${RowsWithAdditionalProperties[0].Value.TeamDisplayName}', which was recently deleted in Microsoft Teams.",
      "wrap": true,
      "separator": true
    },
    {
      "type": "TextBlock",
      "text": "Thank you for your help.",
      "wrap": true
    }
  ]
}