Sending SMS messages with the EXECUTE command

 2013-06-01

Sending SMS messages with the EXECUTE command

Version 10 of ACL Analytics (formerly ACL Desktop) introduced us to the EXECUTE command which allows us to run applications and processes external to ACL. This could change the way we perform data analysis in ACL. From opening applications to running external programs, the possibilities are endless. For example, I would like to convert transactions with amounts from different currencies into one common currency. Prior to Analytics 10, you had to go to foreign exchange rate website, find the relevant currencies and manually import this into ACL and then convert everything into one currency. Now with the EXECUTE command, we can point ACL to the direction of the online exchange rates, import into a table and convert the transaction amounts – all with a click of a button.

One of the first practical examples of the EXECUTE command (by ACL’s Dan Zitting) made phone calls to test the responsiveness of on-call staff. The ACL script would import a list of on-call staff, run an external program to make phone calls and then finally import the results back into ACL to be reviewed. The ACL script we will create in this walkthrough will send out SMS notifications once the script has completed.

Prerequisites

Here are prerequisites you need before we can get up and running:

1. A working copy of ACL Analytics 10.
2. A Twilio account. This is the service that will allow us to programmatically make phone calls and send text messages. (You can trial it by signing up for a demo account.)
3. A programming language of your preference, we will be using Ruby for this example – don’t worry, no programming knowledge is required. You can find the useful how-to’s and sample code in the Twilio Docs as well as the steps required to configure your environment.

Walkthrough

Our process will work as follows:

1. Complete your ACL scripts as you would do normally.
2. Export the contacts to text file to be read by our Ruby program.
3. In the Ruby program, send a custom message each contact in the text file.

The ACL Script

Once we have completed our normal testing, we will export the contact numbers to a text file and the run Ruby program:

COM ******************************************************
COM ** Perform your analysis first
COM ******************************************************

COM ** Create a windows folder where we will store our contacts
EXECUTE ‘cmd /c MD “C:ACL_Download”‘

COM ** Export the phone numbers to a text file, ready for Twilio
OPEN Contacts
EXPORT FIELDS INCLUDE(Phone_Number, “+0123456789”) TO “C:ACL_DownloadContacts”

COM ** Now run the Ruby program that is stored in “C:ACL_AppsRuby_Programs”
EXECUTE “ruby C:ACL_AppsRuby_Programssend-sms.rb”

The Ruby program – send-sms.rb

I have amended the sample code that appears on the Twilio website so that it reads the text file we exported from ACL. The Ruby program will read the numbers and will send a text message to each one:

require ‘rubygems’
require ‘twilio-ruby’

# set up a client to talk to the Twilio REST API
# please enter your Twilio account here
account_sid = “YOUR-TWILIO-ACCOUNT-ID”
auth_token = “YOUR-TWILIO-AUTH-TOKEN”
from = “YOUR-TWILIO-NUMBER”
client = Twilio::REST::Client.new account_sid, auth_token

# Read the phone numbers from the text file that we just exported from ACL
@numbers = []
File.read(“C:ACL_DownloadContacts.txt”).each_line do |line|
@numbers from,
:to => number,
:body => “Hey! Your ACL script has completed. Check your ACL GRC P2P project to review the results!”
)
end
end

If you would like to give this a go, then here is the ACL script and the Ruby program used in this example.