Owner Policy Reminder

This scenario causes a message to be sent to each team owner which has a team with less than or equal to a configured amount of owners (default is set to 1).

Required Settings

Setting	Value
StoredProcedureName	automation.SpGetTeamOwnerCounts
IsSentOnce	False

SQL Query

CREATE PROCEDURE [automation].[SpGetTeamOwnerCounts]
AS
BEGIN

    DECLARE @MaximumNumberOfOwners INT = 1

    SELECT * FROM (
        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
            ,COUNT(others.[UserID]) AS TotalOwnerCount 
        FROM automation.Teams teams
            JOIN automation.TeamUsers towners ON towners.[TeamID] = teams.[TeamID]
            JOIN automation.TeamUsers others ON others.[TeamID] = teams.[TeamID]
        WHERE 
            teams.[IsArchived]=0
            AND towners.[UserType] = 'Owner' 
            AND others.[UserType] = 'Owner'
        GROUP BY 
            teams.[TeamID]
            ,teams.[DisplayName]
            ,teams.[LastActivity]
            ,towners.[UserID]
            ,towners.[UserName]
            ,towners.[Mail]
            ,towners.[External]
    ) AS teamOwners
    WHERE teamOwners.[TotalOwnerCount] <= @MaximumNumberOfOwners

END
GO

Example Data Model

{
  "RowsWithAdditionalProperties": [
    {
       "Value": {
        "TeamID": "7bf4f130-51f2-4251-a4b3-9818a21b2f51",
        "TeamDisplayName": "Team Name 1",
        "LastActivity": "2020-11-17T12:11:58Z",
        "OwnerDisplayName": "joe.bloggs@emaildomain.com",
        "OwnerIsExternal": false,
        "TotalOwnerCount": 1
      }
    },
    {
      "Value": {
        "TeamID": "7f684987-b089-4abc-8a1f-e33c756665a0",
        "TeamDisplayName": "Team Name 2",
        "LastActivity": "2020-11-17T12:10:58Z",
        "OwnerDisplayName": "temp.temp@emaildomain.com",
        "OwnerIsExternal": false,
        "TotalOwnerCount": 1
      }
    }
  ]
}

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_error_filled.png",
                  "altText": "brand icon",
                  "size": "Small"
                }
              ],
              "width": 30
            },
            {
              "type": "Column",
              "items": [
                {
                  "type": "TextBlock",
                  "text": "Teams Status",
                  "horizontalAlignment": "Right",
                  "wrap": true,
                  "color": "Dark",
                  "size": "Small"
                },
                {
                  "type": "TextBlock",
                  "text": "TOO FEW OWNERS",
                  "horizontalAlignment": "Right",
                  "spacing": "None",
                  "size": "Large",
                  "color": "Attention",
                  "wrap": true,
                  "weight": "Bolder"
                }
              ],
              "width": 70
            }
          ]
        }
      ]
    },
    {
      "type": "TextBlock",
      "text": "${RowsWithAdditionalProperties[0].Value.OwnerDisplayName}, you are the owners of teams with less than 2 owners",
      "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 which do not have the required number of Owners. It is company policy in Modality to have at least 2 owners per team.",
      "wrap": true,
      "separator": true
    },
    {
      "type": "FactSet",
      "facts": [
        {
          "title": "Team",
          "value": "${Value.TeamDisplayName}"
        },
        {
          "title": "Owners",
          "value": "${Value.TotalOwnerCount}"
        },
        {
          "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
    }
  ]
}