MQTT messages - receiving and storing for further external analysys

[gjgj]

Hello,
I’ve successfully set up MQTT and Mosquito broker and I’m able to see received messages via MQTT Explorer, but searching google and yoube doesn’t return any help information on how to set up MQTT message recording or loging for further analysis. Could anyone, please, share any insight where to look at - for guides or tutorial how to set up MQTT message recording or logging for Home Assistant?

[Sir_Goodenough ((SG) WhatAreWeFixing.Today)]

Hello gjgj,

Troubleshooting MQTT? MQTT-Explorer can help you be successful!
If you have Add-ons available, try adding this Add-on repository to your Add-on list. GitHub - GollumDom/addon-repository.
With this you can see that the broker sees

[FloatingBoater (James Derrick)]

Hi,

There’s lots of MQTT examples in this forum, from client automatic discovery, to logging scripts.

Here’s a few examples (search is your friend)…

Saving mqtt sensor values as text file to server

I have a bash script which takes a MQTT payload and logs it to a text file from cron - similar actions, but not quite the same technology… #!/bin/bash # subscribe to a topic waiting for ONE value, print only the payload, use tr to strip " quotes: LOG_STRING=$(mosquitto_sub -C 1 -q -d -i "monitor" -h BROKER --username 'UNAME' --pw "SECRET" -v -t 'your/topic/here' -F %p | tr -d '"') /bin/echo "$LOG_STRING" >> log.txt This runs from cron on the server which has the logfile, and connects back to…

MQTT connecting to appdaemon

Hi, Here’s a generic guide to getting the MQTT broker add-on working The best test is to install a MQTT tool like https://mqtt-explorer.com/ and use it to connect to the broker. Try IPv4 addresses first, not hostnames, not IPv6. The command line tool mosquitto_sub is also useful to extract particular topics - I’ve used it in a simple Bash script before without need for Python (although the paho.mqtt libraries also work well): # RX: subsrcribe to the special wildcard topic mosquitto_sub -d -…

MQTT update implementation help

Hi, Sorry - I’m not going to read through an entire GitHub repository for reasons. Can you tell us what are you trying to do specifically please? I’ve implemented fully automated HASS integration via HASS MQTT Discovery in Python, although the dev cycle is quick as you can test instantly using mosquitto_pub & mosquitto_sub in Bash scripts to prototype MQTT auto-discovery: HASS MQTT setup help is here: Here’s a mosquitto_pub example to get you started hacking together a proof of concept: …

If this helps, this post!

[IOT7712]

HomeAssistant is set up to have the inbuilt MQTT broker write correctly detected and correctly configured entity data to the database.

Not clear if you want external access to the database, exporting it, or are you happy to access the stored data in your automations as part of normal operations?

Tell us what you REALLY want to do.

[gjgj]

Thank you for pointing to your topics! Unfortunately this doesn’t look helpful - I have Home Assistant green and no way to run shell scripts on separate server. Also although I have some minimal experience with scripting and perl - Home assistant for me seems whole different world - I simply do not understand how/what to search to get what I need to solve

[gjgj]

Ideally I need csv file of all messages of MQTT topics - each sensor ID and its measurement at particular time. It would also be ok some sort of text file.

[gjgj]

UPDATE:
Finally found one topic on data logging:

How do I create a CSV file and append values to it?

Hi there, as the title explains, I just need help with CSV files. I’m pretty sure there is some sort of CSV functionality in Home Assistant, but I can’t seem to figure it out. Most people either on forums or YouTube deal with Google Sheets but not a plain CSV file, so if anyone could help me out with this, then that’d be greatly appreciated

But still lot is unclear to me.
Here is the code from what I’m able to understand, but unfortunately this is just best guess based on some examples:
UPDATE2:
And yes - this is not working

in configuration.yaml file

sensor: !include mqtt_sensors.yaml
automation: !include mqtt_automations.yaml

notify:
  - name: write_to_csv
    platform: file
    filename: mqtt_BB_logs.csv

in mqtt_sensors.yaml file

#sensors:
- platform: mqtt
  name: "BBF4
  unique_id: "BBF4"
  state_topic: "BBF4/Meter_value_ElectrickWhConsumed"
  value_template: "{{ sensor.BBF4 }}" 
- platform: mqtt
  name: "BBF7"
  unique_id: "BBF7"
  state_topic: "BBF7/Multilevel_Sensor_Air_temperature"
  value_template: "{{ sensor.BBF7 }}" 

in mqtt_automations.yaml file for BBF7 sensor

alias: BBF7 data log
trigger:
  - platform: mqtt 
    topic: "BBF7/Multilevel_Sensor_Air_temperature"
action:
  - service: notify.send_message
    data: 
    message: "{{ now(),sensor.BBF7,states('sensor.BBF7') }}"
    target:
      entity_id: notify.write_to_csv

Can someone comment what is wrong with my guesses and how to correct
and is there some reference guide on all yaml instructions so
I can combine them appropriately to achieve what I need?
I can only find basic information and some usage
examples not suitable for my case
at Documentation - Home Assistant

[IOT7712]

Still I have lots of questions, some dumb, some to try to get you to look in places you may not have done yet. Not sure about your yaml yet - we might look at that later after some more input from you. You seem to be the type to try everything, hoping to stumble across something that works. How about we take a more structured approach and break things down into smaller, easy manageable steps you can master and build on, and offer us clues to go by? I have no idea what you are doing, so some of these questions may appear dumb to you, but might offer clues that we can seize to offer the breakthrough you are desperately seeking. Be patient with me, and everybody else will also jump in with ideas. Going off in tangents is distracting, but is hopefully broadening your knowledge.

Your devices:
You appear to be running HomeAssistant on a Green. Is it running well? Is it up to date? Network Ok? All the other sensors and controls working Ok? Any custom addons, even from HACS?

What devices are these BBF4 and BBF7? Who manufacturers them? What do they do? Do they have inputs, outputs, or both? Are they parts of a larger system, a car charger, a solar system, a weather station, a home heater, what? Do you have other devices from that manufacturer that you have already integrated, or intend to integrate? Are you going to be using the data collected to control other devices? What and how?

Connections:
How is the data from these devices arriving at your HomeAssistant Green? RS485/Modbus, WiFi, Ethernet, BlueTooth, ZigBee, Matter/Thread, what? Is the connection reliable and robust?

MQTT:
Have they been configured to speak to your MQTT broker correctly? How? Passwords, MQTT topics, etc?
Are you able to see them doing MQTT updates via the MQTT Explorer add-on?
What entities are being updated with MQTT sensor data? Are they happening as you expect?
Does your MQTT error log have any entries? Look in Settings/ System/ Logs and look at all of the ones from the drop down menu you see on the right.

Bigger picture questions:
Is MQTT the appropriate way to collect this data? Any alternatives? Is HomeAssistant the appropriate ecosystem to store it? Does the device vendor offer an alternative, even cloud based solutions everybody hates?

Databases:
Have you used SQLite Web add-on to browse the raw HomeAssistant data? Yes, it is a database, not a csv text file.

Your aims and goals:
What are you looking to do with this data you are collecting?

What is your end goal?

Special request: Please make liberal use of the forum quoting ability </> in posting logs, yaml code, etc, as often spacing is the problem, and the forum can hide that if you don’t quote it properly. Thanks.

[gjgj]

Thank you very much for the input!

Yaml
Yes, I’m absolutely new to yaml. I have some minor experience with Perl, php and Javascript and MySQL - but yaml documentation is quite different. I still have no idea how data bits need to be structured within config files - dependencies and I couldn’t find that in docs. Also there are many major changes in yaml over time do documentation is quite different from what I’m able to find in this community support forum. So due lack of detailed reference documentation and frequent yamal changes for me there is no other way than try - also time frame on which I’m allowed to play with yaml is limited to not more than one-two weeks among other duties.

1) Yes I have Home Assistant Green (up to date)
and three z-wave hubs on the same IP network. Particular Z-wave hubs have no Home Assistant integration so the only way to receive data is via MQTT. Sensors Aeotec Multisensor 6 and SmartPlugs are connected to hub and hub provides settings for MQTT. BBF4 and BBF7 are hub identifiers and ‘ms-THIU’ is multisensor identification and ‘sp’ is SmartPlug identification. MQTT messages from the hub with sensor readings are received and can be verified with MQTT explorer - so far it is fine.

---

The problem is I need to save sensor readings (for every MQTT topic) preferably to csv file to analyse these data in xls over longer period. No controls are required - just collect analyze sensor data over longer period.

---

There are some other hubs and sensors and EV chargers connected to Home Assistant via supported integrations and they are available directly within dashboard with all logs, which can be downloaded - no problem with those.

---

Relevant to MQTT installed are:
MQTT
MQTT DiscoveryStream Alt
---
Mosquitto broker
MQTT Explorer
SQLite Web
File editor

Not relevant to MQTT installed are:
Open Charge Point Protocol (OCPP)
SMLIGHT SLZB
SmartThings
Zigbe Home automation
Thread
Matter
---
Z-wave JS UI

2) Connections
Home Assistant and Z-wave hubs are on same LAN via Ethernet - no problem there

3) MQTT
MQTT is working fine - topics are received bay Mosquitto broker and can be examined by MQTT explorer - no problem there

---

System logs show following (these are errors caused by my incorrect yaml)

Automation with alias 'BBF7-msTHIU851-T MQTT data log' could not be validated and has been disabled: extra keys not allowed @ data['automation BBF7']. Got None

BBF7-msTHIU851-T MQTT data log: Error executing script. Service not found for call_service at pos 1: Action notify.write_to_csv not found

4) Bigger picture questions
unfortunately MQTT is the only alternative - this is the only communication supported by particular Z-wave hubs - no way tho change these as these are provided with in project.

5) Databases
Yes, SQLite Web is installed - I read that it also could be use to log MQTT data, but it seems extra step as I need csv/txt file of sensor data collected over longer period.

6) Aims and goals
are to collect z-wave sensor data from three z-wave hubs received by Mosquitto broker for all relevant topics over longer period to further analyze these data in xls. Ideally all sensor data in single file structured as follows:
Date-Time | HUB-Sensor-ID | MQTT-message

I’d very much appreciate any info how to improve yaml my configuration files to store MQTT messages received by Mosquitto Broker over longer period into csv/txt file for further analysis.
Thank you again!