Enzo Unified | Send Slack Alerts when SharePoint List Changes

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:

EXEC [localhost,9550].bsc.slack.help

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.

EXEC [localhost,9550].bsc.slack.listchannels
EXEC [localhost,9550].bsc.sharepoint.lists

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 the GetListChanges 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) 

WHILE(1=1)
BEGIN

	SET @tmpToken = (
		SELECT TOP 1 changeToken 
		FROM [localhost,9550].bsc.SharePoint.ListChanges 
		WHERE 
			name='Companies' -- the SP List to monitor 
			AND changeToken = @cghToken )

	if (LEN(@tmpToken) > 0)
	BEGIN
		EXEC [localhost,9550].bsc.Slack.PostMessage 
			'C011FGD81S4',	-- the channel id
			'Record changed in Companies list'
		SET @cghToken = @tmpToken
	END

	WAITFOR DELAY '00:00:15'	-- wait 15 seconds

END

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.

Send Slack Alerts when SharePoint List Changes

Connect to Enzo Server from SSMS

Test Connectivity to Slack and SharePoint

Write the SQL Monitoring Script

Test the Integration

Conclusion

PLATFORM

SOLUTIONS

RESOURCES

COMPANY

BLOGS AND VIDEOS

IN THE NEWS

OTHER

Send Slack Alerts when SharePoint List Changes

Connect to Enzo Server from SSMS

Test Connectivity to Slack and SharePoint

Write the SQL Monitoring Script

Test the Integration

Conclusion

PLATFORM

SOLUTIONS

RESOURCES

COMPANY