Hi! my city water utility provides a website (https://annami.aquahawk.us/) that shows water usage for our house. I would like to parse out the usage information into home assistant. They provide a really nice usage graph and leak detection.
When I try to add ‘a website’ through the gui, the website prompts me to login but then returns me to the home screen.
Could someone advise a better way to access this data through home assistant?
This will depend on how you authenticate into the website. At a cursory glance, it looks like it’s not an HTTP Basic Auth or Digest Auth, which means that a scraper won’t really be able to log into the website in order to get any information.
Someone went through a similar discovery back in this thread a couple of years ago.
My water company also uses Aqua Hawk. I would love to be able to pull my usage into ha. Anyone have any luck with this?
Hey @g.shepperd, good timing. I’ve been meaning to do this for a couple years and finally got around to it.
It’s a bit dirty but does the trick. Fortunately AquaHawk sends a json response, and there’s a lot of data in there to potentially use. Maybe someone can turn this all into an integration, but not me.
#!/bin/bash
# this script requires jq, unless you want a bunch of personal account info in the json sent to HA
username="[email protected]" # email address
password="thepassword"
districtName="thedistrict" # look at developer tools -> Network while logging into AquaHawk from chrome
cmd="curl -s -k -i 'https://www.aquahawk.us/login' -H 'content-type: application/x-www-form-urlencoded' --data-raw 'username=$username&password=$password'"
res=$(eval $cmd)
cookie=$(echo "$res" | grep cookie | sed -r 's|.*cookie: ([^;]*).*|\1|')
cmd2="curl -s 'https://www.aquahawk.us/accounts?districtName=$districtName' -b '$cookie' -H 'accept: application/json'"
res2=$(eval $cmd2 | jq -r '.accounts[0].metricAggregates')
echo "$res2"
- platform: command_line
name: AquaHawk Water
command: "/config/aqua.sh"
scan_interval: 14400 # probably don't ping too frequent to be kind to AquaHawk
unique_id: aquahawk_water_meter
json_attributes:
- waterUse
- billAmount
command_timeout: 30
value_template: '{{ value_json["waterUse"]["prior"]["1 day"]["gallons"] }}'
- name: Water Projected Bill
state: '{{ state_attr("sensor.aquahawk_water", "billAmount")["projected"]["billing period"]["total"] }}'
unit_of_measurement: "$"
- name: Water Prior Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Current Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Yesterday
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Today
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
Let me know if you have any questions.
It took me a while to get to this, but it seems like I need some help.
I added the following to my configuration.yaml
sensor: !include sensors.yaml
template: !include template.yaml
Then I created those files. They look like the following:
- sensor:
- name: Water Projected Bill
state: '{{ state_attr("sensor.aquahawk_water", "billAmount")["projected"]["billing period"]["total"] }}'
unit_of_measurement: "$"
- name: Water Prior Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Current Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Yesterday
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Today
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
- platform: command_line
name: AquaHawk Water
command: "/config/aqua.sh"
scan_interval: 30 # probably don't ping too frequent to be kind to AquaHawk
unique_id: aquahawk_water_meter
json_attributes:
- waterUse
- billAmount
command_timeout: 30
value_template: '{{ value_json["waterUse"]["prior"]["1 day"]["gallons"] }}'
I also created aqua.sh as instructed and changed my username/password/district.
I am getting the following errors in the logs:
render_info._result = self.async_render(variables, strict=strict, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 411, in async_render
raise TemplateError(err) from err
homeassistant.exceptions.TemplateError: UndefinedError: 'None' has no attribute 'prior'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.components.template.template_entity] TemplateError('UndefinedError: 'None' has no attribute 'prior'') while processing template 'Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["billing period"]["gallons"] }}")' for attribute '_attr_native_value' in entity 'sensor.water_prior_billing_period'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.helpers.event] Error while processing template: Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["billing period"]["gallons"] }}")
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 409, in async_render
render_result = _render_with_context(self.template, compiled, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1859, in _render_with_context
return template.render(**kwargs)
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 1291, in render
self.environment.handle_exception()
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 926, in handle_exception
raise rewrite_traceback_stack(source=source)
File "<template>", line 1, in top-level template code
File "/usr/local/lib/python3.9/site-packages/jinja2/sandbox.py", line 303, in getitem
return obj[argument]
jinja2.exceptions.UndefinedError: 'None' has no attribute 'current'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 525, in async_render_to_info
render_info._result = self.async_render(variables, strict=strict, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 411, in async_render
raise TemplateError(err) from err
homeassistant.exceptions.TemplateError: UndefinedError: 'None' has no attribute 'current'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.components.template.template_entity] TemplateError('UndefinedError: 'None' has no attribute 'current'') while processing template 'Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["billing period"]["gallons"] }}")' for attribute '_attr_native_value' in entity 'sensor.water_current_billing_period'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.helpers.event] Error while processing template: Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["1 day"]["gallons"] }}")
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 409, in async_render
render_result = _render_with_context(self.template, compiled, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1859, in _render_with_context
return template.render(**kwargs)
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 1291, in render
self.environment.handle_exception()
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 926, in handle_exception
raise rewrite_traceback_stack(source=source)
File "<template>", line 1, in top-level template code
File "/usr/local/lib/python3.9/site-packages/jinja2/sandbox.py", line 303, in getitem
return obj[argument]
jinja2.exceptions.UndefinedError: 'None' has no attribute 'prior'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 525, in async_render_to_info
render_info._result = self.async_render(variables, strict=strict, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 411, in async_render
raise TemplateError(err) from err
homeassistant.exceptions.TemplateError: UndefinedError: 'None' has no attribute 'prior'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.components.template.template_entity] TemplateError('UndefinedError: 'None' has no attribute 'prior'') while processing template 'Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["1 day"]["gallons"] }}")' for attribute '_attr_native_value' in entity 'sensor.water_yesterday'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.helpers.event] Error while processing template: Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}")
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 409, in async_render
render_result = _render_with_context(self.template, compiled, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1859, in _render_with_context
return template.render(**kwargs)
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 1291, in render
self.environment.handle_exception()
File "/usr/local/lib/python3.9/site-packages/jinja2/environment.py", line 926, in handle_exception
raise rewrite_traceback_stack(source=source)
File "<template>", line 1, in top-level template code
File "/usr/local/lib/python3.9/site-packages/jinja2/sandbox.py", line 303, in getitem
return obj[argument]
jinja2.exceptions.UndefinedError: 'None' has no attribute 'current'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 525, in async_render_to_info
render_info._result = self.async_render(variables, strict=strict, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 411, in async_render
raise TemplateError(err) from err
homeassistant.exceptions.TemplateError: UndefinedError: 'None' has no attribute 'current'
2022-09-13 12:28:43 ERROR (MainThread) [homeassistant.components.template.template_entity] TemplateError('UndefinedError: 'None' has no attribute 'current'') while processing template 'Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}")' for attribute '_attr_native_value' in entity 'sensor.water_today'
2022-09-13 12:28:44 ERROR (SyncWorker_0) [root] Uncaught exception
RuntimeError: module compiled against API version 0xf but this version of numpy is 0xe
2022-09-13 12:28:45 WARNING (MainThread) [custom_components.hacs] You have 'maykar/kiosk-mode' installed with HACS this repository has been removed from HACS, please consider removing it. Removal reason (Repository is archived)
2022-09-13 12:28:50 ERROR (MainThread) [homeassistant] Error doing job: Unclosed connection
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:29:07 ERROR (SyncWorker_1) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:29:07 WARNING (SyncWorker_1) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:29:37 ERROR (SyncWorker_3) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:29:37 WARNING (SyncWorker_3) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:30:07 ERROR (SyncWorker_0) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:30:07 WARNING (SyncWorker_0) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
2022-09-13 12:30:10 WARNING (Recorder) [homeassistant.components.sensor.recorder] sensor.energy_usage has unit W which is unsupported for device_class energy
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:30:37 ERROR (SyncWorker_5) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:30:37 WARNING (SyncWorker_5) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:31:07 ERROR (SyncWorker_1) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:31:07 WARNING (SyncWorker_1) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
/bin/sh: /config/aqua.sh: not found
2022-09-13 12:31:37 ERROR (SyncWorker_5) [homeassistant.components.command_line] Command failed: /config/aqua.sh
2022-09-13 12:31:37 WARNING (SyncWorker_5) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
Any help would be appreciated!
I also ran into the similar issues.
2022-09-13 14:59:28.484 ERROR (MainThread) [homeassistant.components.hassio.handler] /store return code 500
2022-09-13 14:59:28.501 ERROR (MainThread) [homeassistant.components.hassio] Failed to to call /store -
2022-09-13 14:59:31.811 ERROR (SyncWorker_0) [homeassistant.components.command_line] Command failed (with return code 126): /config/aqua.sh
2022-09-13 14:59:31.821 WARNING (SyncWorker_0) [homeassistant.components.command_line.sensor] Empty reply found when expecting JSON data
2022-09-13 14:59:44.481 ERROR (MainThread) [homeassistant.helpers.event] Error while processing template: Template("{{ state_attr("sensor.aquahawk_water", "billAmount")["projected"]["billing period"]["total"] }}")
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 419, in async_render
render_result = _render_with_context(self.template, compiled, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 1904, in _render_with_context
return template.render(**kwargs)
File "/usr/local/lib/python3.10/site-packages/jinja2/environment.py", line 1301, in render
self.environment.handle_exception()
File "/usr/local/lib/python3.10/site-packages/jinja2/environment.py", line 936, in handle_exception
raise rewrite_traceback_stack(source=source)
File "<template>", line 1, in top-level template code
File "/usr/local/lib/python3.10/site-packages/jinja2/sandbox.py", line 303, in getitem
return obj[argument]
jinja2.exceptions.UndefinedError: 'None' has no attribute 'projected'
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 535, in async_render_to_info
render_info._result = self.async_render(variables, strict=strict, **kwargs)
File "/usr/src/homeassistant/homeassistant/helpers/template.py", line 421, in async_render
raise TemplateError(err) from err
homeassistant.exceptions.TemplateError: UndefinedError: 'None' has no attribute 'projected'
2022-09-13 14:59:44.492 ERROR (MainThread) [homeassistant.helpers.template_entity] TemplateError('UndefinedError: 'None' has no attribute 'projected'') while processing template 'Template("{{ state_attr("sensor.aquahawk_water", "billAmount")["projected"]["billing period"]["total"] }}")' for attribute '_attr_native_value' in entity 'sensor.water_projected_bill'
2022-09-13 14:59:44.499 ERROR (MainThread) [homeassistant.helpers.event] Error while processing template: Template("{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["billing period"]["gallons"] }}")
Additions to config.yaml:
sensor: !include sensor.yaml
template: !include template.yaml
#!/bin/bash
# this script requires jq, unless you want a bunch of personal account info in the json sent to HA
username="USERNAME" # email address
password="PASSWORD"
districtName="LOCATION" # look at developer tools -> Network while logging into AquaHawk from chrome
cmd="curl -s -k -i 'https://www.aquahawk.us/login' -H 'content-type: application/x-www-form-urlencoded' --data-raw 'username=$username&password=$password'"
res=$(eval $cmd)
cookie=$(echo "$res" | grep cookie | sed -r 's|.*cookie: ([^;]*).*|\1|')
cmd2="curl -s 'https://www.aquahawk.us/accounts?districtName=$districtName' -b '$cookie' -H 'accept: application/json'"
res2=$(eval $cmd2 | jq -r '.accounts[0].metricAggregates')
echo "$res2"
Sensor.yaml:
- platform: command_line
name: AquaHawk Water
command: "/config/aqua.sh"
scan_interval: 60 # probably don't ping too frequent to be kind to AquaHawk
unique_id: aquahawk_water_meter
json_attributes:
- waterUse
- billAmount
command_timeout: 30
value_template: '{{ value_json["waterUse"]["prior"]["1 day"]["gallons"] }}'
template.yaml:
- sensor:
- name: Water Projected Bill
state: '{{ state_attr("sensor.aquahawk_water", "billAmount")["projected"]["billing period"]["total"] }}'
unit_of_measurement: "$"
- name: Water Prior Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Current Billing Period
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["billing period"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Yesterday
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["prior"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
- name: Water Today
state: '{{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}'
unit_of_measurement: "gal"
I thought it might be related to “# this script requires jq” as I have no idea what JQ is. Simple mechanical engineer here, so a bit out of my element.
I got the script to return data by logging in via ssh and running
tr -d '\r' < aqua.sh > aqua.bash
in the /config directory
Then I changed the line in sensor.yaml to
command: "bash /config/aqua.bash"
and it returned all the data. Everything seems to be working now!
So far, its updating by itself but only 1-2 times a day. Probably at the mercy of the water district
a more simple fix is to leave the original alone: (dont need the tr -d ‘\r’ < aqua.sh > aqua.bash)
instead… all you need to do is go to the config directory and execute the following command in ssh:
chmod 555 aqua.sh
aqua.sh did not have the proper rights to execute. the chmod command changes it to executable.
This is great! I modified the original program and added 2 bash programs called by the original bash script.
For the original program, I put dummy data in the database so that it records a timestamped data point each hour. This is required (I think) because the database does not update if the data is the same, and aquahawk is sporadic in importing the data from my city.
The first program called from the bash script (another bash script) pulls my hourly water usage from aquahawk and saves it as a csv file.
The 2nd program parses the csv file and modifies the home-assistant_v2.db database for the entries (each hour) I want using the values from the csv files that I download. This can now display a graph of my hourly and daily water usage (with a bit of overhead for the extra hourly data polls/ingest by home assistant database and with a delay due to my municipality’s frequency in exporting my data to aquahawk).
This doesn’t seem to work as of July 2023. I’m guessing Aqua Hawk just revamped their site as the dashboard they provide doesn’t seem to provide anything in the JSON data with the phrase “metricAggregates”. The error I keep getting with the above aqua.sh is parse error: Invalid numeric literal at line 1, column 10, which is basically jq complaining about the data formatting. I tried deleting the HTML header info from the data captured by curl, but the problem seems to be deeper than that. Basically anytime jq hits a space or a colon it says it’s invalid.
I’m also having an issue with this as of a few days ago. Seems to sporadically work and sometimes fail with
* TemplateError('UndefinedError: 'None' has no attribute 'current'') while processing template 'Template<template=({{ state_attr("sensor.aquahawk_water", "waterUse")["current"]["1 day"]["gallons"] }}) renders=26>' for attribute '_attr_native_value' in entity 'sensor.water_today'
Unfortunately, I don’t have the skills to figure out what’s wrong
I was able to modify this a bit for use in the Energy dashboard, thank you!!!
This does not grab estimated or projected bill, but an additional aqua.sh could be used for estimated/projected bill with a much higher scan interval.
#!/bin/bash
# this script requires jq, unless you want a bunch of personal account info in the json sent to HA
username="[email protected]" # email address
password="thepassword"
districtName="thedistrict" # look at developer tools -> Network while logging into AquaHawk from chrome
cmd="curl -s -k -i 'https://www.aquahawk.us/login' -H 'content-type: application/x-www-form-urlencoded' --data-raw 'username=$username&password=$password'"
res=$(eval $cmd)
cookie=$(echo "$res" | grep cookie | sed -r 's|.*cookie: ([^;]*).*|\1|')
cmd2="curl -s 'https://www.aquahawk.us/meters?districtName=$districtName' -b '$cookie' -H 'accept: application/json'"
res2=$(eval $cmd2 | jq -r '.meters[0].metricAggregates')
echo "$res2"
- platform: command_line
name: AquaHawk Water
command: "/config/aqua.sh"
scan_interval: 3600 # probably don't ping too frequent to be kind to AquaHawk
unique_id: aquahawk_water_meter
state_class: total
device_class: water
unit_of_measurement: 'gal'
json_attributes:
- waterUseReading
command_timeout: 30
value_template: '{{ value_json["waterUseReading"]["current"]["1 hour"]["reading"] }}'
Use the command line sensor directly in the water consumption of the Energy Dashboard.