New Public Teams without Guests

This scenario causes a message to be sent to each team owner when a public team without guests is created (or a team becomes public). It is only sent once. It will be triggered both when a public team is created, and if a team is switched from public to private.

Required Settings

Setting	Value
StoredProcedureName	automation.SpBotNewPublicTeamsWithoutGuestsNotifications
IsSentOnce	True

SQL Query

CREATE PROCEDURE [automation].[SpBotNewPublicTeamsWithoutGuestsNotifications]
AS
BEGIN

SELECT
		t.[Id] As TeamId
		,u.[Id] As UserID
		,t.[DisplayName] As TeamDisplayName
		,u.[Mail] As UserMailAddress
		,t.[LastUpdatedDateTime] AS TeamLastUpdated
		,t.[CreatedDateTime] AS CreatedDateTime
		,c.Id as GeneralChannelId
		,GETUTCDATE() as ScenarioRunDateTime
	FROM dbo.Teams t
		JOIN automation.[TeamUsers] tu on tu.[TeamId] = t.[Id]
		JOIN automation.[TeamUsers] tg on tg.[TeamId] = t.[Id]
		JOIN dbo.Users u on tu.[UserId] = u.[Id]
		Join dbo.Channels c on c.TeamId = t.Id And c.DisplayName = 'General'
	WHERE t.[Visibility] = 'Public'
		AND tu.[UserType] = 'Owner'
		AND tg.[DataType] <> 'Guest'
		AND t.[CreatedDateTime] >= cast(GETUTCDATE() - 7 as date)
		AND t.Deleted = 0
		AND t.IsArchived = 0

	GROUP BY
		t.[Id]
		,t.[DisplayName]
		,u.[Id]
		,u.[Mail]
		,t.[LastUpdatedDateTime]
		,t.[CreatedDateTime]
		,c.Id

END
GO

Plain textCopy

Example Data Model

{
  "RowsWithAdditionalProperties": [
    {
      "Value": {
        "TeamId": "Team Id",
        "TeamDisplayName": "Team Name",
        "TeamLastUpdated": "2021-04-13T07:55:30Z",
        "CreatedDateTime": "2021-04-07T14:24:42Z",
        "GeneralChannelId": "Channel Id",
        "ScenarioRunDateTime": "2021-04-13T07:55:56Z"
      }
    }
  ]
}

Plain textCopy

Example Card Template

Basic format to be customised

{
    "type": "AdaptiveCard",
    "body": [
        {
            "type": "Container",
            "$data": "${RowsWithAdditionalProperties}",
            "items": [
                {
                    "type": "TextBlock",
                    "text": "TeamID: ${Value.TeamID}"
                },
                {
                    "type": "TextBlock",
                    "text": "TeamDisplayName: ${Value.TeamDisplayName}"
                },
                {
                    "type": "TextBlock",
                    "text": "TeamLastUpdated: ${Value.TeamLastUpdated}"
                },
                {
                    "type": "TextBlock",
                    "text": "CreatedDateTime: ${Value.CreatedDateTime}"
                },
                {
                    "type": "TextBlock",
                    "text": "GeneralChannelId: ${Value.GeneralChannelId}"
                },
                {
                    "type": "TextBlock",
                    "text": "ScenarioRunDateTime: ${Value.ScenarioRunDateTime}"
                }
            ]
        }
    ],
    "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
    "version": "1.2"
}