SQL Server and sending messages to Slack. ( or how I stopped worrying about emails) Part 1

Background

Several years ago I joined a company that used Slack. I could not fathom why. It was basically a messenger tool, but I had spent so many years being sent emails that I saw Slack as an extra thing to check it took me a long time to fully appreciate it. Eventually I came up with a list of pros and cons. The main pro was unlike email all of the conversation is in one place. you don’t end up entering half way through a dialog potentially missing parts of the conversation. Or finding out that a separate conversation had been carried out elsewhere as someone had used a different email list that you had not been added to.

The biggest issue still remained though, all my daily reports for the health of my servers continued to be transmitted by email. All the failure messages of things needing attention, sales figures or something not completing still came via emails. It was only when I changed to another company that had a paid Slack subscription did this issue get sorted. In the paid version of slack an integration for receiving emails could be installed on a specific channel in your Slack Workspace and you could see the emails in the channel feed. At this point I went from a DBA who had an email client open constantly through the day to checking it once or twice a day. My work tickets were arriving in Slack, the emails were arriving in Slack when needed, my team communicated via Slack. There was minimal need for my email client for hour to hour working.

The Email integration in Slack had solved a problem, but it is clunky. You could see the email subject and heading but to get to the contents of the email you had to expand it to see any more than a couple of lines, it was not pretty especially for something that could be summarised into a single message. You could see information but the format wasn’t great, but I was getting what I needed to keep the servers running and all from within Slack.

I’d noticed some people had built custom Slack Bot integrations, but I am not a Developer, My main hat is still a DBA one (it’s a green bowler hat, very dashing) . I can code rudimentary stuff, mainly in SQL, I can get by in python if not too complex. So I never really looked at creating bot integrations till SQL Server started allowing python code in its ML component, it opened my comfort zone a little and then I wondered if I could use SQL Server Python to send messages to Slack you can and it’s simple. The following 3 article show how I implemented a SQL Server based python script to send Messages to one or more slack channels.

Pre-requisites

To follow along with what I created you will need a SQL Server Instance, I used 2019, with The ML Component of Python installed. You will also need a Slack Workspace. I have a corporate account, but for testing I created a simple stand alone workspace for testing. I used Azure Data Studio for testing as I find this a useful method when switching between SQL and Python. SSMS can be used but the python scripts need a bit more packaging to run in the early testing.

Setting up the Bot

create a new Slack Bot go to api.slack.com and click on the top right corner option of your apps. you will need an account set up linked to your workspace.

click on the button Create New App a menu option comes up and select “create from scratch”.

Next option page gives the opportunity to choose the Name and which work space to install the App in. I would recommend developing this off your main production systems at first then once established moving the code to a more professional environment. In this case I’m going to call my Bot “Barbara Gordon” after the DC Comics heroine “Oracle” because it amuses me.

On the “Add Features and Functionality” Click the Bots Item Menu, then go to “Review Scopes to Add”

Under the item Scopes click Add an OAuth Scope 

In this article we will only be adding the chat:write Scope, the other options add a more features that you could use for example to look up your users in the workspace for example but this will not be covered in this series of articles.

Once the Scope has been selected click the button at the top of the page Install to WorkSpace at the top of the page. and then the Allow button, once you’ve reviewed you choices. If you are part of an organization there may be a Slack Administrator who will have to approve the adding of this Bot to your companies workspace. The chat:write option is going to allow anyone with the token that is created to spam your Slack system, however this is a required option to allow you to spam your workspace. if you add additional features (and they can be added at a later time) be aware you may have to justify why the additional scopes have been added in the same way a DBA really needs justification why the application really needs sysadmin.

Once the integration has been created for your workspace you will be presented with the Bot User OAuth Token. This allows access to your Bot. This is a very private string do not share, do not put into GitHub or any other insecure source control, I once Someone I know once accidentally put a token into github.com and 30 seconds later their integration was disabled by Slack Technologies and They (I) got an Email alerting them to the breech.

Congratulations you now Have a Slack Bot Integration app.

Now it just needs adding to a channel so you can send messages. to do this click on the channel name to get to properties of a Slack channel and click the integrations tab.

The Apps window allows you to Add an App.

from the list of apps select the one you’ve just made. You now have created an Slack integration that can receive messages from python. You can change the Icon if you want to something more fun, The Icon I eventually used for this integration was Dina Meyer who played Barbara Gordon in the Birds of Prey TV series.

In the next article I will cover how to the above token with Azure data Studio to send a “hello world” message to your slack work space.

This entry was posted in python, slack, SQL. Bookmark the permalink.

1 Response to SQL Server and sending messages to Slack. ( or how I stopped worrying about emails) Part 1

  1. Pingback: SQL Server and sending messages to Slack. ( or how I stopped worrying about emails) Part 3 | SQLPip

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s