Send Slack Alerts when SharePoint List Changes
May 25 2020
In this blog you will see how you can easily send a Slack message to an existing channel when a
change has been detected in a SharePoint list. This can be useful for approving changes, or simply monitoring lists
in SharePoint. Conceptually, a T-SQL script running in SQL Server monitors changes made to a SharePoint List, and
when a change is detected the SQL script sends a Slack message to a Channel.
To follow this blog you must first have downloaded and installed the latest version of Enzo Server, and ensure that you can
connect to Enzo using SQL Server Management Studio (SSMS). For further instructions or to download Enzo Server,
please visit please visit the download page.
This blog also assumes you have installed and configured the Slack and SharePoint adapters.
See the SharePoint
and Slack documentation for information.
Last but not least, we will be using Linked Server to communicate to both Slack and SharePoint from SSMS.
As a result, a Linked Server connection is also required to follow this blog.
See the Linked Server
documentation for more information.
Connect to Enzo Server from SSMS
Now that your Slack adapter has been configured, start SQL Server Management Studio (SSMS).
When the login screen appears, enter the information to connect to any SQL Server database.
In the example below, I am connecting to my localhost database on an instance called ENZO:
You should now be able to access Enzo through a Linked Server connection. Assuming your Linked Server is created
as [localhost,9550], the following SQL command should work:
A list of available commands that can be executed on the Slack adapter will be returned.
Test Connectivity to Slack and SharePoint
Let’s make sure you are able to connect to both Slack and SharePoint. Execute the following SQL
commands; you should receive a similar result. Also, note the name of the list you want
to monitor, and the ChannelId you will be using to send messages to; they will be used shortly.
Note: it is important to select the necessary permissions for Enzo Server to function with Slack, including Channels:History, Channels:Read, Channels:Write and Chat:Write at a minimum. Other permissions may be required.
Write the SQL Monitoring Script
At this point we can write a simple SQL script that will monitor changes to a SharePoint list, and when a
change is detected, send a message to a Slack Channel. Changes to SharePoint lists can be easily identified using
handler (ListChanges table) on the SharePoint adapter; this handler works by passing the name
of the list, and the last change token (if any). The script is designed to send a single message even if multiple
changes were made within a 15 second window, to avoid spamming the Slack Channel.
The following SQL command returns all changes detected in the Companies
list in SharePoint; we can see from the
output that three updates were made recently (the date of the change is shown, along with the ID of the item updated):
SELECT * FROM [localhost,9550].bsc.SharePoint.ListChanges WHERE name='Companies'
Now that we know how to obtain the list of changes made in a SharePoint list, we can write the following SQL script.
The call is wrapped in a continuous WHILE loop, to simulate a job; make sure to
remove the loop if you intend to create a SQL Job. Also, if placed in a SQL Job, you would need to
store the last ChangeToken
value in a SQL Server table and retrieve it before calling GetListChanges
DECLARE @cghToken nvarchar(255)
DECLARE @tmpToken nvarchar(255)
SET @tmpToken = (
SELECT TOP 1 changeToken
name='Companies' -- the SP List to monitor
AND changeToken = @cghToken )
if (LEN(@tmpToken) > 0)
'C011FGD81S4', -- the channel id
'Record changed in Companies list'
SET @cghToken = @tmpToken
WAITFOR DELAY '00:00:15' -- wait 15 seconds
Test the Integration
Now that we have a SQL script that monitors changes on a SharePoint list, let’s change a list item in the SharePoint List.
You can either logon to your SharePoint site and make a change manually, or use Enzo to make a change.
Here is an example on how to update a SharePoint list item in the Company list
(the SQL command below assumes you are connected directly to Enzo – not through Linked Server).
This command updates a field called Opportunities
; you can update any field, insert a new item in the list, or delete an item.
UPDATE Sharepoint.list@Companies SET Opportunities=3 WHERE ID=52
Within a few seconds a message will appear in Slack indicating the list was changed.
This blog post shows you how to quickly and easily integrate SharePoint lists and Slack, and monitor list changes
by sending messages in a Slack Channel using SQL commands.
Enzo allows you to react to changes in your environment and send alerts and messages accordingly.