Help, please. I am trying to create a file containing a sensor value every minute

stevemann · May 30, 2019, 3:00am

I am trying to create a file containing a sensor value every minute.
Here’s what I’ve done so far:

In my configuration.yaml file:

# Add the notify sensor. 
notify:
  - name: Test Data
    platform: file
    filename: Test_Data
    timestamp: true

In my sensors.yaml file:

  - platform: mqtt
    name: "Test_Temperature"
    state_topic: "ds18bq/temperature"
    force_update: true
    unit_of_measurement: "°F"

In my dev-state (<>)tool, (Commas added for clarity), I can see the data arriving from the sensor:

sensor.test_temperature 71.37, unit_of_measurement: °F, friendly_name: Test_Temperature

In my automations_old_yaml file:
(I’ve never gotten a UI-created automation to work)

# Automation that writes the Temperature data to a file every minute
- alias: Check Test Temperature
  initial_state: true
  trigger:
    platform: time
    minutes: '/1'
    seconds: 00
  action:
    service: notify.test_data
    data:
      message: {sensor.test_temperature}

Here is the log file showing the notify component, (and no errors that I can see)
2019-05-29 22:41:42 INFO (MainThread) [homeassistant.loader] Loaded notify from homeassistant.components.notify
2019-05-29 22:41:42 INFO (MainThread) [homeassistant.loader] Loaded notify.file from homeassistant.components.notify.file
2019-05-29 22:41:42 INFO (MainThread) [homeassistant.setup] Setting up notify

So, here’s my questions.
1) Am I doing this right?
2) Where is my data file?

lmamakos · May 30, 2019, 3:44am

The docs at File - Home Assistant says

filename

 (string)(Required)Name of the file to use. The file will be created if it 
 doesn’t exist and saved in your configuration folder.

You could also try to specify an absolute path for the filename to eliminate any ambiguity.

stevemann · May 30, 2019, 3:49am

Thanks. That answers question number 2. I think the problem for the first question is in my automation. If I change the message line to:

message: ", Hello World"

I do get the file with ‘timestamp, Hello World’ in my configuration folder.

What I would like to get is the sensor data. So, how should I format the message line to show the sensor data?

Tinkerer · May 30, 2019, 6:30am

You need to read the templating docs, starting here.

    data_template:
      message: "{{ states('sensor.test_temperature') }}"

nickrout · May 30, 2019, 7:21am

Why are you trying to replicate information that home assistant already records in its database?

stevemann · May 30, 2019, 2:49pm

Thanks for the input. Templates have always been my Achilles heel in understanding Home Assistant; I find them quite confusing. My problem with “read the docs” is I don’t know what to look for.

This works, thank you.

data_template:
  message: "{{ states('sensor.test_temperature') }}"

Can you explain, please, why this doesn’t? (I thought that whitespace was ignored in yaml).

data_template:
  message: "{{states('sensor.test_temperature')}}"

By the way- the info here in the docs did answer some of my questions about using data templates. Thanks for pointing mr there.

stevemann · May 30, 2019, 3:19pm

Do you mean: home-assistant_v2.db ?

Perhaps I could have been more descriptive of my goals. I am building a solar-powered gardening system and I want to monitor the battery voltage. I will be generating a chart of the voltage over time to determine if my solar cell is large enough. (I am using a temperature sensor for my tests since the final hardware for my project is not built yet).

The database is HUGE and finding the data from my sensor is a challenge. I don’t have a database program to extract the data and by the time I get up to speed on SQLite, my project will be over. (I.E. Winter is coming).

The file I am generating in the automation is something I can import into MS Excel as a CSV (after removing the header lines). A few clicks and I have my chart.

AhmadK · May 30, 2019, 9:40pm

stevemann:

This works, thank you.
data_template:
  message: "{{ states('sensor.test_temperature') }}"
Can you explain, please, why this doesn’t? (I thought that whitespace was ignored in yaml).
data_template:
  message: "{{states('sensor.test_temperature')}}"

do you have any proof?

lmamakos · May 31, 2019, 1:59am

The home assistant database is just sqlite3, and it’s pretty easy to use. There’s a command line tool that you can use to poke around at the database, or you could use a python library.

Using the command line tool, you can query the states table for the relevant entity. Here’s just a quick hack to grab the last few measurements from the table:

louie@hass[221] $ sqlite3 -csv /tmp/home-assistant_v2.db
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> select created,entity_id,state from states where entity_id='sensor.ow_outside_h_temp' order by created desc limit 20;
"2019-05-31 01:44:47.602685",sensor.ow_outside_h_temp,73.0
"2019-05-31 01:43:42.574080",sensor.ow_outside_h_temp,72.8
"2019-05-31 01:43:10.123315",sensor.ow_outside_h_temp,72.7
"2019-05-31 01:42:37.538063",sensor.ow_outside_h_temp,72.8
"2019-05-31 01:42:05.088129",sensor.ow_outside_h_temp,72.7
"2019-05-31 01:40:59.821806",sensor.ow_outside_h_temp,72.5
"2019-05-31 01:40:27.387789",sensor.ow_outside_h_temp,72.4
"2019-05-31 01:39:54.709631",sensor.ow_outside_h_temp,72.7
"2019-05-31 01:39:22.264257",sensor.ow_outside_h_temp,73.0
"2019-05-31 01:38:49.637563",sensor.ow_outside_h_temp,73.2
"2019-05-31 01:38:17.252672",sensor.ow_outside_h_temp,73.0
"2019-05-31 01:37:44.669761",sensor.ow_outside_h_temp,72.7
"2019-05-31 01:37:12.210474",sensor.ow_outside_h_temp,72.5
"2019-05-31 01:36:39.676839",sensor.ow_outside_h_temp,72.3
"2019-05-31 01:36:07.192790",sensor.ow_outside_h_temp,73.0
"2019-05-31 01:35:34.566064",sensor.ow_outside_h_temp,73.1
"2019-05-31 01:35:02.121316",sensor.ow_outside_h_temp,73.0
"2019-05-31 01:34:29.579887",sensor.ow_outside_h_temp,73.2
"2019-05-31 01:32:51.979445",sensor.ow_outside_h_temp,73.1
"2019-05-31 01:31:14.581173",sensor.ow_outside_h_temp,73.2
sqlite>

You could push your data into influxdb and point grafana at influxdb to generate pretty graphs. Or even use the influx to run queries on the data and extract a series. I have a bunch of states that get pushed into influxdb, a really nice time series database. This example is also just temperature, but I also have AC line voltage, power measurements, DC battery level of the generator’s starter battery, etc. in there.

Just a quick dashboard that took 2 minutes to set up in my grafana/influx installation:

There’s a bunch of tools at your disposal that might be a more rapid path to a solution. In my circumstance, I have Home Assistant, influxdb and grafana running in different docker containers that we each fairly easy to get setup. If you’re using Hass.io, there are probably pre-built extensions that you can click to install pretty easily.

Regardless of what path you go down (even using the home assistant database), just try to be aware of the I/O load going to the database. The SD card on a Raspberry Pi is really going to struggle if you’re trying to push a bunch of data into it. I’m running Home Assistant on a NUC-type device with an SSD, and the I/O performance is just not any issue in that environment. To help manage this, if you’re using the influxdb component in Home Assistant, you might want to be selective in only having it store the sensors that you care about in there, and not ones that changes on a frequent basis that don’t have any interesting data .

chairstacker · May 31, 2019, 2:32am

I think you can remove the header line(s) in the text file once it’s been created because only entries will be added at the bottom.
To do this stop HA, deleite the first line(s) of your file in your preferred editor and start HA again - worked for me, but I only deleted the empty(?) with the first line but kept the headers for the graphs.

And here’s something else I’m doing as I recently discovered this great feature in the free MS Tool called Power BI:
You can link - i.e. not import - the csv-file as data source into Power BI, build your chart and then simply hit the ‘Refresh’ button once you overwrite the old csv-file with the new version downloaded. No copy/paste or re-creating graphs required:

stevemann · May 31, 2019, 2:43am

That’s a lot to swallow, but I will definitely put those tools on the list of things I really should know.

This is only a one-time data measurement over a few days, mostly cloudy, hopefully. After collecting the data I will remove the automation. (Hmm, I wonder if I could dynamically test for a trigger file to exit the automation if the trigger file doesn’t exist?)

I think a NUC would be the next iteration of my journey into Home Assistant. I wish there was a way to mount a USB drive in HassIO like I could do on Raspian. Then I wouldn’t worry so much about stressing my Micro SD.

stevemann · May 31, 2019, 3:09am

Wow, I had never heard of PowerBI before. But then, I have been out of the professional Office user corps for 20-years (I retired early). I am using MS Office 2007- because it does all I need. (Much to my wife’s frustration when I ask her for Excel help- she is using the latest version of everything Microsoft where she works).

I installed PowerBI Desktop and in less than a minute I was able to create the same graph I made in Excel. It looks like I can save it as a PDF with the free version. Nice tool. (I have another project not related to HA that may benefit from this tool- just what I need, more stuff to learn).

I just dragged (drug? What’s the passive of drag?) my CSV file right out of my config folder into BI and it ignored the header lines that HA added.

Thanks for the tip.

nickrout · May 31, 2019, 5:23am

I am not sure why you don’t just use the graphs that home assistant creates?

DavidFW1960 · May 31, 2019, 5:34am

Well you CAN if you do a generic linux install of Hass.io - I did that before I switched to the NUC… so it’s possible - just not with HassOS.

Tinkerer · May 31, 2019, 6:12am

That one should work fine, but it’s nothing like what you posted originally

The original post had two issues:

data:

and

message: {sensor.test_temperature}

Because you used data then even if that had been a valid template, which it wasn’t, then it’d have displayed as {sensor.test_temperature}.

That aside, it wasn’t a valid template. You only had one curly bracket, and the contents wouldn’t extract the state of the entity you listed.

stevemann · May 31, 2019, 2:56pm

I think that’s how I am running Home Assistant. I went to Installation - Home Assistant and downloaded he image for the Raspberry Pi 3, flashed it and set up ssh, then proceeded from there.

I can make the mount point in /mnt then insert a USB thumb drive, but the drive is not in the /dev directory, so I can’t mount it.

How did you do it?

stevemann · May 31, 2019, 3:13pm

Ummm, I didn’t think of that? But it would suit my purpose.
OTOH, Microsoft BI is a really neat tool to work with data. I would have never heard of it if not for Chairstacker’s post.

Alos, I plan to use the raw numbers for analysis.

Tinkerer · May 31, 2019, 3:34pm

That’s not a generic Linux install, that guide is further down that page. You installed HassOS.

stevemann · May 31, 2019, 3:55pm

There are so many ways to install Home Assistant that I am easily confused. But, a NUC is in my future as soon as I get a lot of whileYou’reAtIts from my wife finished. (The spousal job-jar).

DavidFW1960 · June 1, 2019, 12:11am

No it’s not. There is no image as such for the installation I describe. I installed Raspbian Lite, Docker and then ran the scripts for the generic linux install so totally different to you where you are running HassOS and Hass.io. I stull have hass.io but on Raspbian, not HassOS and can do a USB boot.