Teams Activity Reminder

This scenario causes a message to be sent to each team owner for a team which has been inactive for a configured amount of days (default is set to 30).

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetTeamActivityByOwner
IsSentOnce	False

SQL Query

CREATE PROCEDURE [automation].[SpGetTeamActivityByOwner]
AS
BEGIN

    DECLARE @InactivityGreaterThanNDays INT = 30

    SELECT 
        teams.[TeamID]
        ,towners.[UserID] AS UserID
        ,teams.[DisplayName] AS TeamDisplayName
        ,teams.[LastActivity]
        ,towners.[UserName] AS OwnerDisplayName
        ,towners.[Mail] AS UserMailAddress
        ,towners.[External] as OwnerIsExternal
    FROM automation.Teams teams
        JOIN automation.TeamUsers towners ON towners.[TeamID] = teams.[TeamID]
    WHERE 
        teams.[IsArchived] = 0 
        AND teams.[LastActivity] IS NOT NULL 
        AND teams.[LastActivity] <=  DATEADD(Day, -@InactivityGreaterThanNDays, GETDATE())
        AND towners.[UserType] = 'Owner'
    GROUP BY
        teams.[TeamID]
        ,teams.[DisplayName]
        ,teams.[LastActivity]
        ,towners.[UserID]
        ,towners.[UserName]
        ,towners.[Mail]
        ,towners.[External]

END
GO

Example Data Model

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

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_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": "INACTIVE",
                  "horizontalAlignment": "Right",
                  "spacing": "None",
                  "size": "Large",
                  "color": "Attention",
                  "wrap": true,
                  "weight": "Bolder"
                }
              ],
              "width": 70
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "${RowsWithAdditionalProperties[0].Value.OwnerDisplayName}, you are the owner of inactive teams",
      "wrap": true,
      "size": "Large",
      "color": "Accent"
    },
    {
      "type": "TextBlock",
      "text": "This is a notification that you are the owner of 1 or more inactive teams in Microsoft Teams.",
      "wrap": true,
      "separator": true
    },
    {
      "type": "FactSet",
      "facts": [
        {
          "title": "Team",
          "value": "${Value.TeamDisplayName}"
        },
        {
          "title": "Last Activity",
          "value": "{{DATE(${Value.LastActivity}, SHORT)}} {{TIME(${Value.LastActivity})}}"
        }
      ],
      "$data": "${RowsWithAdditionalProperties}",
      "separator": true,
      "spacing": "default"
    },
    {
      "type": "TextBlock",
      "text": "If this team is no longer needed, please delete or archive it. Thank you for your help.",
      "wrap": true,
      "separator": true
    }
  ]
}