Monthly Archives: May 2017

MQTT and Kibana – Open source Graphs and Analysis for IoT

Following my previous article on how to interface MQTT with ElasticSearch, here, I am belatedly following up with an article on how you can use Kibana to graph the data.


You should have run through my tutorial on MQTT with Elastic Search, so that you actually have some data to look at.

Installing Kibana

To avoid compatibility issues, you should ensure that you are working with a version of Kibana compatible with your elastic search installation.  The easiest way to ensure this is by updating both.   I won’t repeat the instructions which are available on the Kibana web site.

Kibana and ElasticSearch are two parts of a single product offer, so there is very little difficulty in getting them to work together.

MQTT data format for Kibana

Kibana is ideal for working with time series data. The only tricky thing that I found using Kibana was to get it to interpret time data as a time, rather than string, or numeric.   For this you need to create a Mapping for your elastic search index, which in other words tells elasticsearch that the data you are sending is to be stored and interpreted as a time rather than string or integer.

mappingJson={"mappings": {
  "json": {
    "properties": {
      "timestamp": {
        "type": "date"

      "type": "float"


The above mapping will tell elasticsearch to expect data with three elements, a timestamp and a float value called dataFloat.  Most importantly this will cause elasticsearch to try to interpret the timestamp field as a time rather than storing it as a string.

Analysing MQTT Data with Kibana

Once you have got elasticsearch to interpret your data as a timestamp, you are able to take advantage of all of the functionality of Kibana that comes out of the box, this includes counts, averages values, derivatives and many others.


The set up we described is great for prototyping in a closed environment, but as we have been developing the project, we found ourselves hampered by the lack of security features on Kibana.  It is possible to provide basic login functionality using NGINX, but we could not find an easy way to provide restricted access to data according to account (this is a paid feature in elastic/Kibana).   For this reason we have started to use Grafana with InfluxDB as an alternative.

Zibawa Open Source Project

Zibawa is a project which brings together a number of open source tools to produce a secure IoT system , fully open source from device to dashboard.  The project includes device manager, device and user security management (LDAP), queue management and monitoring (RabbitMQ), Big data storage and api (InfluxDB) and Dashboards (Grafana).


More information

Zibawa Open IoT project source code








How to automate and track email campaigns on google analytics using thunderbird

In this article we will show you how to create a personalized email campaign and create a tracker on google analytics so that you can see who has clicked on the links in your campaign.  We will use opensource mail client Thunderbird, with a plugin called Mail Merge, and google analytics.


You need to have set up google analytics on your web site.

Install thunderbird

Install mail merge plug in for thunderbird

From thunderbird, go to   Tools>Addons>Extensions   then search for Mail merge plugin.

After installing restart thunderbird.

Create a csv file of your contact list

You can create a csv file using excel, or LibreOffice Calc, using save as csv option.

Each column will be some data you want to include in the marketing mail.

Each row will be transformed into a single email.

The CONTACT_ID should be a code number which we will use to identify the customer and will appear in google analytics.

Take care to remember exactly the syntax used for the first row, these will be our PLACEHOLDERS below.

Screenshot from 2017-05-24 12-09-16


Create a mail in thunderbird

Use the format {{<place holder>}}  where <place holder> is the Column Header of the data in the csv file you want to merge into your mails.    The text must match perfectly.

In my example I am using {{EMAIL}} for the users email.(see below)


Screenshot from 2017-05-24 10-37-14

We strongly recommend you include a text or link to enable people to UNSUBSCRIBE from your mailing list.

Add a custom link with google analytics tracking code to your email

Now edit the links to include the tracking code as follows:

The format is:{{CONTACT_ID}}

You need to replace the parts in red with your web landing page, campaign name and keyword.  If you have two or more links in your email, then by using two different keywords you can tell which link the user has pressed.

The CONTACT_ID placeholder will pull the CONTACT_ID column that you put in your csv file earlier just like we did for the email address, so do not change this.

Note! The contact_ID must NOT be anything you can directly identify a user with (such as an email or name) since that contravenes privacy laws in many countries.


Create the mails using mail merge

When you have finished editing, press


Screenshot from 2017-05-24 10-45-11

Source- csv

Deliver mode: send later

csv – select the csv file you created ealier

Character set, Field delimeter and Text Delimiter should be the same as you used when saving the csv file. (Recommended UTF-8, Tab, “)

Rest of values- leave blank

Click OK and one mail will be created for each line in your csv file in your OUTBOX.

Check the contents of your marketing campaign

Before sending, make sure that the mails have been created as you expected in your OUTBOX.  Check several mails, and pay attention to how the place holders and links have been built.

Check the links by clicking on them yourself.


Send your campaign mails

Outbox, right click , send unsent messages

Check the results in google analytics

You will probably need to wait at least 24 hours before any results show up.  You can see them in your google analytics account.  Analytics>Acquisition>Campaigns.

Screenshot from 2017-05-30 16-13-58mail_campaigns



Here you will see the Campaign properties we included in the link for every time a link is clicked.   If you want to know who the user is, you need to add a secondary dimension, which you will find under Advertising>Ad Content or Advertising>Keyword.

This way we know not only how many users clicked on our campaign mail, but also who they are, so we can follow up with those customers.