Author Archives: JRevell

Securing Web Services

Being an IT professional, I’m aware of the need for security over web services.

Depending on the data, there are various levels of protection, such as passing a username and password of the user, through to more complex solutions using Oauth

However Moonpig have done a brilliant job with theirs, allowing all customer data to be available via web service call with absolutely no security at all.

From the looks of it, you can even place orders online and view limited credit card information.

Luckily they have closed the issue by disabling the web service, only took them since 2013…

Read full article

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.

Tesco Christmas Gifts

Following on from yesterday’s blog post which covered Party Food, in this post I’m going to cover Christmas Gifts.

As noted in my previous post, I’m a member of Tesco’s Orchard Program. This program rewards me with money off vouchers in order for me to sample goods and review them either online, via Facebook, Twitter etc.

Buying online was a breeze, shopping via Tesco Direct‘s website and getting delivered to my local store (less than 10 minutes away). No hanging about waiting at home to have a parcel delivered!

I ordered some presents for family for Christmas and these where available next day for collection. The range was quite large and was pretty comparable to the large competitors such as ARGOS etc. Although it’s worth noting that Tesco has gone down the Amazon route and let other sellers list on their site. Therefore you need to filter on “Sold By Tesco” if you want to have it delivered to store.

Through TopCashBack (see my blog post Get paid to shop), I also received a 0.5% cashback on top of my spend. (I know it’s not much, but as they say, every little helps!)

Tesco Finest Party Food

This blog post may sound like an advert for Tesco… In actual fact, it pretty much is.

I’m a member of Tesco’s Orchard Program. Through this program, I receive money off vouchers for products, and in return need to tweet on twitter, share on facebook, write blog posts etc. Also I receive club card points too which can be redeemed for cash.

For a company wishing to promote their brands further, I think it’s quite a good way to have lots of SEO and inbound links to your site.

I don’t really buy party food much, just once in a while for special occasions. So when the program opened up and I received the vouchers, I decided to splash out. (Why not.. It’s Christmas!) In the local Tesco, there was quite a large range of party food – all on a 3 for 2 offer. Price wise compared to the standard product, it’s a little bit more expensive, but nothing major (a couple of quid more). We purchased the following:

One thing I do like with Tesco’s products is the detailed allergy information. Some supermarkets are very vague with their information, but Tesco covers Peanut and Tree Nut Allergies very well.

All the products needed to be put in the oven. To our surprise, all products went in the oven at the same temperature (It is annoying when the temperatures need to be different for each product as some stuff doesn’t cook properly)

Taste wise, the products tasted good, a lot better than the standard product. The only downside was that the pies where a little soft. I’m not sure if this is due to design or the trays which they require baking in.

Would I buy them again? It entirely depends on the event. BBQ with friends round… No. Special celebration with family… Yes.

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

Spam Spam Spam

Spam is a wonderful thing, clogging up your inbox with viagra adverts, fake invoices (containing viruses, worms and other nasties). I love receiving spam, it makes me feel popular . I like many others get annoyed at the level of spam I receive no a day-to-day basis. Owning my own domain name is quite a good thing, as it allows you to see where the spam comes from and block at source.

Each company who I provide an email address to, gets a completely different email address. That way I can filter these out once I get annoyed at the level of spam.

For example: @JRevell.com

Quite recently, I’ve been receiving an insane number of spam emails from quite a few large companies, including:

  • ASDA
  • Doncaster Racecourse
  • Groupon (Although they may have shared my email with someone I bought from)
  • PayPal (Again may have shared my email with someone I bought from)
  • Monster
  • Abbey National

As an IT Professional, I’ve even tried to contacting one of the above to advise them that somewhere they have a virus infection which keeps hammering out emails to me (reverse lookup on the IP address from the headers clearly identified it as their head office). To which I was pushed off as some insane person who receives too much spam and wanted someone to blame…

That company then instantly got added to my blacklist I will not do my online shopping through them ever again.

Good thing is, if you don’t own your own domain, there’s services out there which will do this for you, such as Spam Gourmet.

In due course, I’ll be moving my email in order to give me more storage and better spam filtering (don’t get any at present). Have a few options but still weighing it up:

I’m leaning more towards GMail at the minute, as this way I don’t have to manage anything myself nor install updates or hotfixes etc. Plus it will integrate more with my Android mobile phone, but time will tell. I’m in no rush at the minute.

FreeNas 9.3 Released

FreeNAS is a free, open source NAS operating system which has many features (beyond being a Network Attached Storage Device).

Based on the FreeBSD operating system, it’s a product which has been out for many years (since 2005 ish) I started using it in about 2008.

iXSystems (the supporters of FreeNAS) have recently released an update which includes quite a few changes, including:

  • Improved user interface, hiding advanced options which could confuse new users
  • Server 2012 Clustering and Offloaded data transfers
  • ZFS Boot device
  • WebDAV file sharing
  • Many More…

There’s a video demonstrating a load of these improvements on their website here. Or alternatively jump right in and Download FreeNAS

Exchange 2010 SP3 Update Rollup 8

Earlier on this month Microsoft released Update Rollup 8 for Exchange 2010 SP3.

Unfortunately this update has caused issues with Outlook syncing with Exchange. (This does not affect Activesync, OWA, POP etc)

If you are receiving the “Cannot Open Item” error message and you have this update installed, uninstall it and roll it back to the to Update Rollup 7.

In due course, Microsoft will release Update Rollup 8 which will replace the dodgy update.

There’s more information on the Exchange Releases website.

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'