MSQL – Send Slack Notifications via CLR UDF

Hi,

In this post i would like to showcase a feature in which i was able to send out on-demand SQL notification to slack channel via web hooks.

This methodology is useful in the areas where users can skip instead of email notification, they can opt to subscribe the Slack channel to keep updated of the server events.

This is achieved by creating a function in SQL via CLR:

 [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
        public static SqlChars SendMessageToSlackChannelByUri(SqlChars channelUri, SqlChars fallbackText ,SqlChars titleText, SqlChars themeCodeText, SqlChars serverNameText, SqlChars priorityText, SqlChars messageText)
        {
            try
            {
                var uri = channelUri.ToSqlString().ToString();
                var httpWebRequest = (HttpWebRequest)WebRequest.Create(uri);
                httpWebRequest.ContentType = "application/json";
                httpWebRequest.Method = "POST";

                using (var streamWriter = new StreamWriter(httpWebRequest.GetRequestStream()))
                {
                    string json = "{\"mrkdwn\": true,\"fallback\": \"" + fallbackText.ToSqlString().ToString() + 
                                    "\",\"pretext\":\"*" + titleText.ToSqlString().ToString() + "*\",\"color\": \"" + themeCodeText.ToSqlString().ToString() + 
                                    "\", \"fields\": [{\"title\": \"Server: " + serverNameText.ToSqlString().ToString() + " -- Priority: " + priorityText.ToSqlString().ToString() + 
                                    "\",\"value\": \"" + messageText.ToSqlString().ToString() + "\"}]}";
                     
                    streamWriter.Write(json);
                    streamWriter.Flush();
                    streamWriter.Close();
                }

                var httpResponse = (HttpWebResponse)httpWebRequest.GetResponse();
                using (var streamReader = new StreamReader(httpResponse.GetResponseStream()))
                {
                    var result = streamReader.ReadToEnd();
                }

                return new SqlChars(httpResponse.StatusDescription);
            }
            catch (Exception ex)
            {
                return new SqlChars(ex.Message);
            }
        }

This project can be created with the “SQL Database Project” template in VS2017.

And the necessary reference for the same is as below:

CLR User-Defined Functions (MSDN)

CLR UDF Example

And the necessary information for the slack incoming web hook API can be found at the following location:

Incoming Webhooks – Slack

This similar methodology can also be employed to “Microsoft Teams” chat application as well.

And the resultant of the above can be achieved as follows:

Hope it helps.