Tag Archives: Taskcentre

Automating E-Mail Support Requests using FOAAS and Taskcentre

In this day and age, everyone is looking for prompt responses to e-mails and support requests. In this blog post I’m going to cover integrating FOAAS into e-mail using Taskcentre in order to provide prompt responses to support requests.

By responding quickly to emails, it can greatly improve satisfaction levels, plus also using FOAAS’s technology can also reduce the number of support requests*.

* by increasing the likelyhood of a P45.

In order to integrate the solution, we need to break it down into 3 major steps:

  • Intercept Email
  • Call Web Service
  • Send Response

Intercept E-Mail
FOAAS - Intercept E-MailIn order to intercept the email, we use a standard “SMTP” input trigger. This trigger stores the following into variables:

  • From E-Mail
  • E-Mail Subject
  • E-Mail Body

Additionally, we also need to use the “Text Parser” tool in order to extract the following from the “From E-Mail”

  • Sender Name
  • Sender E-Mail

Call Webservice
FOAAS - Web Service CallFOAAS is a REST based webservice. So I’ve built up a web service call, passing through the following parameters:

  • Sender Name
  • From Name

This web service returns two values:

  • message
  • subtitle

Additionally, the web service requires custom headers which have been amended using the tool

Send Response
FOAAS - Send ResponseA response is then sent to the inbound e-mail automatically, using the following steps:

  1. XML to Recordset (HTML Tool only recognises recordsets)
  2. HTML (To build up an email)
  3. Outbound SMTP

Example
Below you can see an inbound email from a used. As you can see, the email contains both a subject and a request for the support helpdesk. In my VM, the “From” name is “James – Demonstration”, as I use the VM for testing and demonstration purposes.
FOAAS - Inbound E-Mail

Here is the response to the email, including the automated response to the web service:
FOAAS - Outbound E-Mail

Source Task and Installation
Below is the download link for the example, plus also the web service connector. In order to install the task, the install needs to be done in the following order:

  1. Import Web Services Connector
  2. Import Task

FOAAS Web Service Connector
FOAAS Task

This task has been provided for humour/educational use only.

Stripping all Non Alphanumeric Characters From String in SQL

With one of the projects I’m currently working on using Taskcentre, I required all non alphanumeric characters stripping from a string in a SQL query (in order to perform an inner join with another table).

With a bit of research I found a brilliant solution here, by Even Mein (Why re-invent the wheel!)

This solution involves creating a function as follows:

CREATE FUNCTION [dbo].[fn_StripCharacters]
(
    @String NVARCHAR(MAX), 
    @MatchExpression VARCHAR(255)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    SET @MatchExpression =  '%['[email protected]+']%'

    WHILE PatIndex(@MatchExpression, @String) > 0
        SET @String = Stuff(@String, PatIndex(@MatchExpression, @String), 1, '')

    RETURN @String

END

The function can be called as part of the SQL query as follows:

Alphabetic only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z')

Numeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^0-9')

Alphanumeric only:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', '^a-z0-9')

Non-alphanumeric:

SELECT dbo.fn_StripCharacters('[email protected]#f4$', 'a-z0-9')

Plotting Customers on Map from Microsoft Dynamics Nav

In this blog post, I am going to cover plotting UK customers in a map from Microsoft Dynamics Nav using Taskcentre. In this post I’m using Nav 2013R2, with the Extended Demo Database. (This has a lot more customers and data compared to the standard demo database).

In order to plot the locations on the map, firstly we need to translate the postcodes into Latitude and Longitude. On this Website, there’s a download link to a SQL script to create and populate a table with postcodes within the UK plus the appropriate latitudes and longitudes.

SSIS Import PostcodesDue to the size of the file, I had problems running the SQL query on my Virtual Machine, so instead used an SSIS job (via the Import/Export data tool on SQL Server). Depending on the amount of RAM available on the SQL server, it should be possible to run the script directly.

SQL Query Customer Postcode Longitude LatitudeOnce the file has been imported into the Nav database, I write a SQL query to extract the customer information. Note that in the Postcode table there’s no space between the first half and the second half of the postcode. Therefore using SQL we need to remove the spaces from the postcode table as part of the join.

The join on the two tables is:

REPLACE("CRONUS EXT UK Ltd_$Customer"."Post Code",' ','') = postcodelatlng.postcode

This produces a data set as follows:
Customer Postcode Data

VBScript - Customer Location StringUsing this data, we can then create a HTML page in order to visualise the data. Firstly we need to use a VBScript step which cycles through each record from the SQL query, storing the data into a variable. This variable will later be used within Taskcentre when building up the Javascript.

VBScript - Customer Location Build HTMLAfter creating this string, this is then combined with two snippets of HTML (stored within variables for ease of editing) to produce a final, complete HTML document. This HTML document is then saved to disk and can be opened in a browser. Using the FTP tools, it could be uploaded to a website if required. The document consists of some JavaScript which uses the Google Maps Javascript API. The principle could be used for any API, such as the Bing Maps API, however personally I do prefer the Google API over Microsoft’s.

The Javascript also uses the “click” event listener. This then shows a dialog box with the customer details when the icon is clicked. This contains just the customer number and name in this example, but could contain information such as full address, contact information, sales order information etc.
Customer Map

Here’s the complete task within Taskcentre:
Customer Map - Task

Google Custom MarkersThis task is only a very basic task, but with some imagination and skill, it could be used as a basis for pretty much any use. Especially when you extend the task adding the ability to have custom markers.

Some examples could include:

  • Trend analysis
  • Deliveries for a courier
  • Customers/areas with bad debt/chargebacks

eBook Price Tracking

Having recently purchased my first eReader, the Kobo Aura, I’ve really started to enjoy reading books more. Instead of having to carry large books about, the small device can sit in my pocket and read books on the go. With the backlight I can even read in bed without the bedside table lamp on.

The only downside is the price of eBooks. Compared to paperbacks at places such as The Works (especially when you take a photo of your receipt and send it to TopCashBack – saving £5 when you spend £20), eBooks can be quite pricey…

There are services out there which will crawl and monitor stores to tell you the prices as these change, such as Luzme, but where’s the fun in that..?

In this post, I’m going to explore crawling the Kobo Store using TaskCentre. Once crawled, any price changes will be updated back to a database and an email to myself sent about the changes.

If a XML Web Service was available, this would make life easy as this could be called directly, but in this case there is not one available, so we need to be creative and download the HTML, extracting the required text.

Database Structure
Kobo Database StructureFirstly we need a database in able to store the data, plus a few books which we wish to keep an eye on.

Here’s the SQL Query to make the table:

CREATE TABLE [dbo].[KoboBooks](
	[UniqueID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](max) NULL,
	[URL] [nvarchar](max) NULL,
	[CurrentPrice] [decimal](18, 2) NULL,
	[PriceLastUpdated] [datetime] NULL,
	[PreviousPrice] [decimal](18, 2) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Additionally I’ve added a couple of books to the database which I’m interested in monitoring.

Downloading HTML and Processing
Kobo VBScript Download HTMLUsing a standard OLEDB connection to extract the information from the SQL database, this is passed onto a VBScript step within Taskcentre. This VBScript step uses the WinHttp.WinHttpRequest.5.1 COM Object in order to download the HTML document. Afterwards standard VBScript String Functions are used to extract the required text and stored in a variable.

Additionally, a variable is set to highlight if the price has changed. This is to ensure that only the updated prices are updated back to the database.

Updating Database
Kobo VBScript MemoryIn order to update the database, I could write a stored procedure to perform the logic required, but instead I’ve used a lesser used feature in Taskcentre – the memory within the VB Script tool. The memory can be used to call SQL queries and update the data in the database, without writing complex stored procedures. In the screenshot to the left, you can see the mapping, plus also I’ve used the “StoreData” variable to decide whether or not to run the SQL queries to store the data within the database. (See the VB Script in the previous screenshot to see the variable being set.)

Alerting on Price Changes
Kobo Database UpdateAfter storing the data back into the SQL database, this can be extracted via a simple SQL query, dropped into email and sent to myself. To the right is a copy of the data currently stored within the database.

The SQL query to return the data required is:

SELECT  
	KoboBooks.UniqueID AS UniqueID,
	KoboBooks.Title AS Title,
	KoboBooks.URL AS URL,
	KoboBooks.CurrentPrice AS CurrentPrice,
	KoboBooks.PriceLastUpdated AS PriceLastUpdated,
	KoboBooks.PreviousPrice AS PreviousPrice
FROM
	KoboBooks
WHERE
	KoboBooks.PriceLastUpdated >= GetDate() - 1

Putting it all together, here’s a screenshot of the full task:
Kobo Full Task

And a copy of the alert triggered:

Kobo Email Alert

Possible Improvements
As with everything, there’s always possible improvements. These could be:

  • GUI for managing list of books
  • Multiple Stores (WHSmith etc)
  • Error handling (If website changes slightly, it will throw error messages)
  • Using XPath to extract prices instead of simple string functions.
  • Use SQL triggers to send details of price changes, possibly with a copy of the web page embedded within the email

Using Taskcentre to Post HTML Tables to HipChat

HTML Email ExampleI use Taskcentre from Orbis Software quite a lot to send HTML emails to recipients. These usually are bog standard HTML tables with content. For example, this is a sample report based on Microsoft Dynamics Nav database. This is not the standard out of the box Cronus database, but the extended version. The standard database has very little data (about 600k rows of data), whereas the extended has closer to 2.5 million!

Email has it’s uses, however does have it’s disadvantages, such as:

  • E-Mail Ping Pong – Emails going backwards and forwards, but not getting anywhere
  • Users not replying to latest email – 20 minutes into an email conversation, someone else joins in using an earlier email
  • Too detailed – Sometimes people email a 20 line email, where a single sentence will do

More recently I’ve been trialling HipChat as an alternative to E-Mail. In a previous blog post, I covered posting error messages from Taskcentre and posting in Hipchat – Taskcentre Alert Notifications To HipChat.

Extracting Data From Nav
In order to extract information from Nav, first I need to use either ODBC or OLEDB to return the data. I prefer OLEDB as I’ve seen performance benefits over ODBC, but either is suffice.

Here’s the SQL query as generated by Taskcentre:

SELECT
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name AS Name,
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" AS "Salesperson Code",
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)") AS Sales,
	SUM(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Amount (LCY)"-(dbo."CRONUS EXT UK Ltd_$Sales Line"."Outstanding Quantity"*dbo."CRONUS EXT UK Ltd_$Sales Line"."Unit Cost (LCY)")) AS Profit
FROM
	dbo."CRONUS EXT UK Ltd_$Sales Line",
	dbo."CRONUS EXT UK Ltd_$Sales Header",
	dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser"
WHERE
	(dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = 1) AND (dbo."CRONUS EXT UK Ltd_$Sales Header".No_ = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document No_" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Document Type" = dbo."CRONUS EXT UK Ltd_$Sales Line"."Document Type" AND dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code" = dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Code)
GROUP BY
	dbo."CRONUS EXT UK Ltd_$Sales Header"."Salesperson Code",dbo."CRONUS EXT UK Ltd_$Salesperson_Purchaser".Name

Building HTML for HipChat
VBScript Creating Table RowsNext we need to build the HTML for HipChat. Unfortunately the “Format as Text” tool will not work in this situation (it does not allow outputting of the created text), so we need to use VBScipt instead.

Firstly I’ve created a variable within the task called “TempHTML”. This variable will be used to store the created HTML before posting to HipChat. Then the VBScript step is created which is set to repeat for every row in the returned record set. This is done by checking the box accordingly as per the screenshot.

For clarity, here’s the code used:

' ProcessData Function
' 
Function Create_Table_Rows_Function()
Variables("TempHTML") = Variables("TempHTML") + "<TR>"
Variables("TempHTML") = Variables("TempHTML") + "<TD><B>" + ThisStep.RecordSource("Name") + "</B></TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Sales"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "<TD>£" + CStr(Round(ThisStep.RecordSource("Profit"))) + "</TD>"
Variables("TempHTML") = Variables("TempHTML") + "</TR>"
End Function

For those who have HTML skills, you will notice that this does not include any <TABLE> tags, nor column headers. In order to create these, a second VBScript step is created which finishes these off:

' ProcessData Function
' 
Function Create_Headers_Function()
Variables("TempHTML") = "<B>Sales Statistics:</B><BR/><TABLE BORDER=1><TR><TD><B>Salesperson</B></TD><TD><B>Sales</B></TD><TD><B>Profit</B></TD></TR>" + Variables("TempHTML")
Variables("TempHTML") = Variables("TempHTML") + "</TABLE>"
End Function

Posting to HipChat
Posting this to HipChat is very simple, using the HipChat Send Room Notification web service. I’m not going to cover it in this post as it was covered previously, simply use the variable inside the XML Web Service Tool.

Complete Task
Here’s the complete task (I’ve still left in the HTML email example, which is outside of the scope of this blog post):
Sales Order Totals With HipChat

When the task runs, here’s the posting to HipChat:
HipChat HTML Message - Table

Borders
I’ve tried using CSS Table Formatting and even the HTML Table Border Attribute, however neither of these work, so I assume HipChat does not support borders (it does state that only basic HTML is supported and anything unsupported is stripped out). Possibly in future HipChat may support these.

Taskcentre Triggers – Helper Tasks

This post is in response to a discussion on the Taskcentre LinkedIn Group. In the discussion, I’ve suggested using a “Helper” task in order to limit the number of triggers on a database, improving performance on the SQL Database.

SQL Trigger
First to define what a SQL Trigger is. There’s a lot of information on MSDN regarding SQL Triggers. To shorten this down, basically it’s a Stored Procedure which runs every time an event happens on the table (such as INSERT/UPDATE/DELETE).

Performance Issues
Single triggers work well, but as you create more and more triggers, this can impact performance. Lets take for example, the Sales Header table on Microsoft Dynamics NAV. If you are unsure of the data structure, this table is used to store the following unposted sales documents:

  • Quote
  • Order
  • Invoice
  • Credit Memo
  • Blanket Order
  • Return Order

Imagine you had some logic in a taskcentre task which ran for each document type. This could mean that you have 6 triggers which run for every update!

Improving Performance
Helper Task - TriggerIn order to improve performance, I make a “Helper” task which runs once. Then taskcentre uses the logic to decide which tasks to run. This moves the overhead from SQL (which you want to be nice and fast) and taskcentre can chug away doing all the complex work.

As you can see from the SQL Trigger, this trigger will run the Taskcentre Task when the status changes from “Open” to “Released”. I’ve also filtered the document type to Quote/Order/Return Order too in order to prevent the taskcentre task running too often. In theory you don’t need these filters and you could handle these in taskcentre, but it depends on the business logic which you are trying to implement.

It’s worth noting that the trigger will still run for every single update. It just wont start the taskcentre task unless it matches the criteria.

Decisions in Taskcentre
Helper Task - DecisionAs you could see from the trigger, the variable “DocumentType” is set to match the Document Type in the updated record. Using the decision step as shown, the next series of tasks to run can be selected. In the example to the left, I’ve chosen the Document Type of 0 which is “Quote” (Dynamics Nav stores all “Option” data types as integers).

Below shows the completed task. Each step calls a VB Script task which in turn calls another task. (I’ll write about that at some time in the future)
Helper Task - Complete Task

Monitoring Website Availability

Nagios Service DetailThere are various tools out there which can be used to monitor website availability. My favourite being the defacto standard – Nagios. Not only is it open source, it is very flexible and powerful. The only downside with Nagios is the config and Linux skills (I’m not scared of Linux at all, but some people are). But when you’ve got it set up, it’s pretty rock solid and will just keep running away monitoring all of your services.

Installing Nagios
Nagios is very simple to install on most Linux systems. On the Nagios Quickstart website, there is detailed instructions on installing Nagios on Fedora/openSUSE/Ubuntu. However with Ubuntu, the installation can be simpler using apt-get to install:
sudo apt-get install -y nagios3
(Although it’s worth noting that aptitude usually has an older version. A trade-off for ease of installation!)

Configuring Nagios
Once installed, Nagios can be configured. I’m not going to cover it in this blog post here, as there are that many guides out there which already cover this. However a good starting point is this article which covers setting up monitoring for public services.

Twitter Integration
I’ve been looking at doing something nice with Nagios to allow it to tweet on Twitter when a website is down – “I’m sorry we’re down, we’ll be back up later”. However it seems a bit of a pain to install and setup Twurl. Therefore I thought it could be interesting to explore this using Taskcentre

Using Taskcentre as Alternative to Nagios
Looking at Twurl as it looks very painful to integrate into Nagios. So looked at it from the other angle. “What integrations have I done with Twitter which I can get to monitor a website”. I’ve previously created a task in Taskcentre which tweets, so with any task such as this, I broke it down into small manageable steps and tackled each one, one at a time:

  • Check site to see if up or down
  • Check to see if status has changed
  • Send notification

Check Site Availability
VBScript Site StatusA very basic check would be to “PING” the website to see if the server is available. This is a very basic check and I would never recommend this as a way of checking to see if your website is available. Some servers block pings, others will respond with a ping, but still not be delivering pages. The best way is to actually request the page and await the return of the HTTP Status Code. Generally any response other than one in the 200-299 range means that the website is not available. In this example, I’m focusing on the primary response “200” which means “OK”.

Using VBScript, this small script which will request a page from my website. If it returns a 200, it then sets the “CurrentStatus” variable to “True”. In every other case, it sets it to “False”.

Check to see if the status has changed
Firstly we need to know what the previous status was in order to compare this to the current status. Creating either a global variable, or task level variable to store the previous value, then comparing the current value to the previous value.

This is pretty easily done using the following decision step:
Variables("CurrentStatus") <> Variables("PreviousStatus")

Afterwards, a small bit of code is required to store the value. This is to ensure that the next time the task runs, it will highlight the change in status. (Rather than highlighting each time).
Variables("PreviousStatus") = Variables("CurrentStatus")

Send Notification
Twitter PostingFinally you need to decide what to do with your alert. In this case, I’ve tweeted on Twitter. (a good way to keep in touch with customers). Using a bit of logic, I’ve changed the text so that the message is different depending on the status change. A positive change says “We’re back now”. A negative change says “Sorry we’re down, but we’re looking into it”.

Obviously this task could send an email, send message via HipChat (as done in my previous post – here), or even text message instead of tweeting.

Here’s the completed task. Obviously needs a “schedule” task in there so that it runs every 5 minutes or so. (In my VM which I use for testing and prototyping, I tend not to schedule anything automatically to run).
Website Availability Task

Further Improvements
As with everything you do, there’s always ways to improve this further. My first thoughts are the following, but I bet there’s lots more..!

  • Check the content of the site, looking for specific text
  • Check a list of sites
  • Flap Detection
  • Different levels of alerts, such as:
    • First Alert – Email Staff
    • Second Alert – Update Twitter
    • Third Alert – Text Message Staff

Taskcentre Alert Notifications

Taskcentre is a software product created by Orbis Software. Taskcentre is “middleware” software which integrates between various software products, ranging from high end ERP solutions such as Microsoft Dynamics Nav, to other pieces of software such as Magento. This is not really a marketing blog post, so for further information, have a look at their website or just ask 🙂

If there is a problem with a task, Taskcentre automatically sends an email to system administrators. Sometimes email is not a reliable messaging system (for example if the email server is down), so in this blog post, I’m going to show how to integrate Taskcentre into itself and post to the free private chat system for business HipChat. Although it is possible to do pretty much anything with the error (you could even print it out on a printer or tweet it if required..!)

To integrate Taskcentre into HipChat for alerting, there are 3 steps which are required:

  1. Integrate Taskcentre into Taskcentre
  2. Integrate Taskcentre into HipChat
  3. Join it all together!

Integrate Taskcentre Into Taskcentre
Firstly we can only do this if Taskcentre is set up to use the SQL database option. If your still using the integrated database, I’d recommend changing to the SQL option. Not only does this improve performance, it also allows further analysis of logs.

Taskcentre Database Structure
If we look at the database structure, there are 2 tables which we require for this project.

  • EventLog – This contains all event log entries
  • Tasks – This contains all the tasks within the Taskcentre installation

We need to first create a SQL trigger on the EventLog table. This is to look for errors related to tasks which have failed. This is done by filtering the table where EventType = 2 AND TaskID >0 (EventType 2 is Error, Ensuring the TaskId is greater than 0 ensures that it only triggers for task errors.)

Taskcentre SQL Trigger

When the trigger fires, this passes the “EventID” to a variable called “EventID”. (We use this when the task triggers so that we know which row caused the task to run.)

Integrating Taskcentre into HipChat
Integration into HipChat is done with the XML Webservice Tool in Taskcentre. The tool allows Taskcentre to call XML Web Services. In this case, we wish to call the Send Room Notification web service.

This web service requires the following:
HipChat Room Notification Webservice

  • id_or_name The name of the room to send the message
  • color (or Colour for the non american) – The colour of the message
  • message The message to post
  • notify Should it trigger a user notification
  • message_format Is the message HTML or plain text
  • auth_token The authentication Token

HipChat Room Notification Web Service TokenIn order to post, we need to create an authentication token via the HipChat website. The best option is to create a room token with an associated “Label”. This is available HipChat Rooms This is then stored in Taskcentre, allowing it to post onto HipChat under the associated “Label”.

Alternatively, a personal authentication token could be used. The only downside with this is that it will post under your name instead of the pre-defined name for taskcentre. A personal token can be created here: Create Personal Token

Join it all together!
So now we can trigger from Taskcentre for every error, and also post to a HipChat room. All we need to do is join it together.

In order to ensure that the message is clear in HipChat (since the EventID is a bit useless on it’s own), We add a OLEDB connection to the taskcentre database and execute the following script. Note that I’ve excluded the current task from this query (I could do it within the Trigger, however we’d have to look up the TaskID and filter that one out – quite messy). This is to ensure that Taskcentre doesn’t get into a “while true” loop if there is an issue with this task.


SELECT
Tasks.TaskName AS TaskName,
EventLog.EventDesc AS EventDesc,
EventLog.EventTime AS EventTime
FROM
Tasks INNER JOIN EventLog ON Tasks.TaskID = EventLog.TaskID
WHERE
Tasks.TaskName <> 'Hipchat Error Posting'
AND EventLog.EventID = {=Variables("EventID")}

Putting it all together, we get the following task:
HipChat Error Posting - Complete Task

When an error is thrown in Taskcentre, the following is shown in HipChat:
HipChat Screenshot

Note: Although this is working well, for some reason the “notify” parameter is not working correctly. Regardless of what I pass to it (“false”,”true”,”0″,”1″,”yes”,”no”, Cbool(True) etc) it throws the following error. I’m wondering if it’s an issue with the webservice itself. Will continue testing to try and find a solution for this.

Value u'false' for field 'notify' is not of type 'boolean'