Tag Archives: HipChat

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 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'