Posting to Slack from Qlik Sense load scripts, part 1

When thinking about it, it’s kind of strange that Slack has been so enormously successful.

Sure, it’s a great instant messaging tool, but it’s not a new concept – IM tools have been around for decades, and IRC, Google chat and others rose to be very dominant and present in everyone’s minds.

Still, Slack does bring something new with the ease of setup, the slick user interface, really good clients on both web, Windows, OSX, IOS and Android. And more than anything else, they provide a very extensive set of integrations (also mentioned in earlier post on great OSX tools), which makes it a breeze to have other systems send messages, status notifications etc to your Slack channel. Or vice versa – messages in Slack can be sent to those other systems. As of this writing the list of integrations is more than 85 entries long, including services like Dropbox, IFTTT, Twitter, various email integrations, Jira etc.

The options

While Qlik Sense is really great in most aspects, it is IMHO lacking in terms of debug output from the load script. Sure, you can write data to the regular log files, or even to text/log files that you crete yourself. For some time I’ve been toying with the idea of using Slack as a  notification hub for alerts and other messages sent by various systems – it would make sense (no pun intended..) as me and my co-workers usually have Slack open at all times, both on our laptops and on mobile. It’s also possible to configure Slack to send email notifications to you when messages adressed to you specifically appear in a Slack channel – thus possible to be notified also via email when something really serious happens.

If we can achieve this, a lot of interesting opportunities open up. We can then do things like

  • Have different reload tasks post to different Slack channels, depending on what the tasks do.
  • Post messages when a scheduled reload job starts and ends.
  • Post status messages for long running reload tasks. It is suddenly trivial to get an idea of how far along that 3 hour reload task has come.
  • Post information about failures during the reload. This is especially relevant if a reload task should continue even if some failure occurs – quite often you would still want to notify someone about the failure that did happen.
  • Notify end users and stakeholders that their Sense apps have been refreshed with new data.

As usually is the case, there is more than one solution to a problem. In this case we have options like

  1. Use Qlik Sense’s regular “Load… From” syntax to call the Slack API with the desired message. Should in theory work, but I couldn’t get this to work. Probably something about what character encodings are allowed in http calls in Sense load scripts – not sure. I gave up on this one.
  2. Call a Windows program, which in turns calls the Slack API to deliver the desired message. Works, but requires you to lower the security level in Sense, in order to allow calling of Windows programs.
  3. Create a small proxy running on the Sense server. The proxy’s only mission is to accept an incoming call from the Sense load script, and then forward this to the Slack API. The benefit of this approach is that Sense can be running with highest possible security settings.

This post will focus on option 2 above, option 3 will be described in a later post.

Posting Slack messages by calling a Windows executable

The Sense “execute” call allows you to run any executable on the Sense server from the load script. By default this feature is disabled, as allowing it introduces potential security risks. Still, if you are using Sense Desktop that risk is probably small and this options could be of interest. The help text for execute tells us the pre-requisites for Execute to work

  • You must run in legacy mode (applicable for Qlik Sense and Qlik Sense Desktop).
  • You need to set OverrideScriptSecurity to 1 in Settings.ini (applicable for Qlik Sense).
    Settings.ini is located in C:\ProgramData\Qlik\Sense\Engine\ and is generally an empty file.

With those in place, posting to Slack is simply done by placing this line in the reload script:

EXECUTE CMD.EXE /C c:\cygwin64\bin\curl -X POST --data-urlencode 'payload={"channel": "#general", "username": "webhookbot", "text": "This is posted to #general and comes from a bot named webhookbot.", "icon_emoji": ":ghost:"}' '<....enter_your_key_here....>';

Which gives us this in Slack (I ran the above command twice, thus the two entries below)

Slack entry created from Sense reload script

Some things to note:

  • You need to substitute your own Slack incoming webhook URL in the <….enter_your_key_here….> section of the command above.
  • You need to have some variant of curl installed on the Windows machine. Curl is a command found on Linux systems, it is used to emulate a web browser sending data to some remote host. Quite useful for scripting purposes. I use the excellent CygWin software to get access to curl and other useful Linux commands on my windows machine here, but there are also other curl implementations on Windows.

That’s it really. You can also of course add to the above and create a re-usable subroutine that takes a channel name, the sender’s name, a message and an emoji as parameters – could look something like this GitHub gist (it’s really basic, but gets the job done).

As for formatting of messages, there is a lot that can be done. Slack provides really good documentation on this, here are some starting points: