MQTT to Graphite to Grafana

[raymonvdm]

In the past i have used Cacti as a way to log my historical data from my domotica devices. There was a script which pulled data from the MySQL database of Domotiga and created the graphs in Cacti (eventually) but after a change from Domotiga -> HS2 -> OpenHAB -> HS2 -> HS3 -> OpenHAB -> HS3 (for now) the database graph creation was lost.

Until this week and i will use this topic to describe the steps i have taken to get things going.

At first i needed to get the data out of HS3 into something i could use on other platforms. Since many of the domotica software is now talking MQTT (in some sort of way) i first installed an MQTT Broker

Installing an MQTT broker on a Ubuntu 14.04 LTS server

Code: Select all

sudo add-apt-repository ppa:mosquitto-dev/mosquitto-ppa
apt-get install mosquitto mosquitto-clients python-mosquitto

And made some changes to /etc/mosquitto/conf.d/local.conf

Install the HS3 MQTT Plugin

Now to get the data onto the broker i installed the MQTT Plugin created by Geert-Jan and configured it to send some sensor data to the broker we just installed on the Ubuntu Server

Since there is data available on the broker we need to get it out to something usable. I planned to use cacti for this and i found a python script created by JPMens called MQTTwarn the export to cacti failed because my input file could only hold one value but MQTTwarn is updating the file with each value on a new line in the file so i have suspended the log to file project for the moment. But taking another look in the features of MQTTwarn i found out that it is also capable of sending "carbon" output and although i heard that name before it took me a day to realize that it was used by Graphite as an input source

Send the MQTT data to carbon

Code: Select all

[defaults]
hostname  = '192.168.1.200'
port      = 1883

; name the service providers you will be using.
launch   = file, log, carbon

[config:file]
append_newline = True
targets = {
    'f01'       : ['/tmp/f.01'],
    'douchet'    : ['/etc/snmp/douchet.txt'],
    'douchev'    : ['/etc/snmp/douchev.txt'],
    'kantoort'    : ['/etc/snmp/kantoort.txt'],
    'kantoorv'    : ['/etc/snmp/kantoorv.txt'],
    'slaapkamert'    : ['/etc/snmp/slaapkamert.txt'],
    'slaapkamerv'    : ['/etc/snmp/slaapkamerv.txt'],
    'woonkamert'    : ['/etc/snmp/woonkamert.txt'],
    'woonkamerv'    : ['/etc/snmp/woonkamerv.txt'],
    'buitent'    : ['/etc/snmp/buitent.txt'],
    'buitenv'    : ['/etc/snmp/buitenv.txt'],
    'mqttwarn'  : ['/tmp/mqttwarn.err'],
    }

[config:log]
targets = {
    'info'   : [ 'info' ]
  }

[config:carbon]
targets = {
        'c1' : [ '192.168.1.198',2003 ],
        }


[#]
topic = #
targets = file:f01

[/homeseer/398/value]
topic = /homeseer/398/value
targets = file:douchet,carbon:c1

[/homeseer/400/value]
topic = /homeseer/400/value
targets = file:douchev,carbon:c1

[/homeseer/402/value]
topic = /homeseer/402/value
targets = file:kantoort,carbon:c1

[/homeseer/403/value]
topic = /homeseer/403/value
targets = file:kantoorv,carbon:c1

[/homeseer/405/value]
topic = /homeseer/405/value
targets = file:slaapkamert,carbon:c1

[/homeseer/409/value]
topic = /homeseer/409/value
targets = file:slaapkamerv,carbon:c1

[/homeseer/413/value]
topic = /homeseer/413/value
targets = file:woonkamert,carbon:c1

[/homeseer/415/value]
topic = /homeseer/415/value
targets = file:woonkamerv,carbon:c1

[/homeseer/398/value]
topic = /homeseer/398/value
targets = file:buitent,carbon:c1

[/homeseer/397/value]
topic = /homeseer/397/value
targets = file:buitenv,carbon:c1

[/homeseer/#]
topic = /homeseer/#
targets = carbon:c1

After we start python mqttwarn.py the carbon messages are sent upon reception of an MQTTmessage

The Grafite / Grafana Part

This is a little teaser to show the potential of Grafana


And here is a little how to install Graphite / Grafana

Note: I will update this topic as soon as i have some historical data and a second install run

[Bwired]

nice thanks sofar!

[raymonvdm]

I also received an update of JPmens

Code: Select all

have implemented code for this with the overwrite parameter:

[config:file]
append_newline = True
overwrite = True
targets = {
   'mylog'     : ['/tmp/mqtt.log'],
  }

[raymonvdm]

Is there someone who can help me out to understand the data which is send to MQTT / CARBON. For example when i look in the file that is logged from temperature the temperature is noted as 19,50 but i think both Grafana and Cacti need 19.50

This value is used by RFXcom Temp sensors but i`m also using a Fibare 1-Wire sensor. Is this the result of a dutch language setting in Windows 7 or is it HS3 related (and changeable?)



I just changed this setting on the Windows 7 machine running HS3

Start -> Control Panel -> Region and Language -> Formats -> Additional settings -> Decimal Symbol changed from , to .

[raymonvdm]

For Windows 10 you need to change this using

Code: Select all

Start -> Control Panel -> Time and Language -> Formats -> Additional date, time & regional settings -> Region -> Change data, time, or number formats -> Additional settings -> Numbers -> Decimal Symbol changed from , to .

Note: In mij case this seem to have caused issues with the JowiHue plugin which cannot see the lights anymore, so maybe a different aprouch is needed

So i`m trying to translate 16,3 to 16.3 using MQTTwarn

I have modified mqttwarn.ini

Code: Select all

[defaults]

; name the service providers you will be using.
launch   = file, log, carbon


hostname  = 'mqtt.test.nl'
port      = 1883
username  = mqttwarn
password  = Sadsc
clientid     = 'mqttwarn'
functions = functions.py

[/homeseer/#]
topic = /homeseer/#
format = replace_comma()
targets = carbon:c1

and created a functions.py

Code: Select all

def replace_comma(data):
    if type(data) == dict and ',' in data:
            return "."
    return None

It is not working yet, but this seems to have something to do with the dict