Shell command w/sqlite3

Installation Home Assistant OS
1

I am trying to automate a sqlite3 query that I wrote that will return an HTML table with a list of any sensors not updated in the last 30 minutes. I have the query written and working and I’m able to run it from the terminal add on and produce the proper file. But any time I’ve tried to run it via an HA shell command in the config.yaml, I get an error 127. I’ve tried every suggestion I could find in any interwebs post and still no joy. It seems as though the shell command just simply cannot access sqlite3 and does not know what it is. What am I missing?

Here’s my current try, which like all others produces error 127

shell_command:
  export_badsensors: sqlite3 /config/home-assistant_v2.db < /config/sensorquery.sql > /config/www/badsensors.html

I’ve tried putting this command in a .sh file and running that

I’ve tried putting full paths and relative paths on each item

I’ve tried doing an ‘ls > temp.txt’ in the shell command to make sure my files show up in the directory listing (they do)

It seems the bottom line problem is that sqlite3 just cannot be accessed by the shell command.

I installed it like this (which I found on an HA community thread about fixing db’s)
apk add sqlite

TIA

Rob

2

Your command needs to run in the HA container.

3

Thank you, I’m trying to follow the guide, but when I go to ssh into HA (hassio) either in the terminal add on or through putty via the terminal add on, docker cannot be found. And I can’t find any other way to enable ssh’ing into hassio but through that add on. I feel like I’m missing something.

I assume where your guide talks about connecting from machine A to machine B, since I’m just trying to run a shell command on the same machine, I am really connecting from machine A to machine A but I can follow the guide as though it’s two different systems/IP’s?

4

You don’t need the whole guide, just my post about the community addon-ssh with disabled protection mode.
20220724_234108
20220725_000452

The docker command is only available if the protection mode is disabled.

After the docker exec -it homeassistant bash command you see a prompt like

bash-5.1#

Now you have a terminal in the HA container.
Test your command here.

5

THANK YOU THANK YOU THANK YOU I can’t thank you enough
Was able to get into native bash session and install sqlite there, works wonderfully now!

Just for those who might come across this in the future - there are TWO add ons, “terminal and ssh” and “ssh and web terminal” - the second one is the one you want and you will not be able to use it until you go into the configuration panel, go into the little text configuration box, and add a strong password between the quote marks and save (obviously do all this at your own risk).

I had the wrong add on and hence also did not have the “protection mode” switch and was doubly lost. Story of my life :slight_smile:

Thanks again

Rob

6

Personally depending on what you want to do with the output of your SQL select (“bad sensors”), I’d go with the SQL - Home Assistant integration. This way you

  • have the data directly in HA (not in a file)
  • don’t need to hassle with SSH and dirty command_line sensors :wink:
7

Remember, if you reboot the container or update , you need to install the APK package again, what I do if I need an additional package in the HA container , I run a .sh script with automation, on start of HA, where I do the APK add command

8

I agree with you, and that was my original plan was to have a SQL sensor that would return the entire HTML table as a return string then I could simply display it in an entity card and it would render the table. Unfortunately the limit on a SQL sensor value is 255 characters, which makes sense but is not enough to store what is needed. But I did use a SQL sensor with a variant of the same query to give a number of sensors not updating, which I can use to trigger an automation to warn me that something has tripped offline

9

Even if I installed it in the native hass.io bash shell? I ask not to doubt you but as someone with a lot of DOS and Windows but limited Linux experience. Seems inconvenient but I understand, hass.io is meant to be robust and self-healing.

10

Not every restart but every time the container is rebuilt from the image. You added the apks to the container after it has been built from the image. Any time the HA container is built from the image it will only contain the contents of the image and the persistent volumes (config, share, etc). Apks added to the container will not be kept.

Containers for addons are rebuilt from their image every restart so adding an APK there doesn’t do a whole lot. The container for HA is generally only rebuilt on upgrade. Although it can also be rebuilt if the container is deemed unhealthy due to an issue.

Yes exactly. The infrastructure is effectively stateless. It is trivially easy to rebuild the container from scratch at any time and get it running again. It’s the docker way, if something is wrong just shoot it and rebuild it. Get the service running again immediately and then review what went wrong after when nothing is down.

Adding apks to the container isn’t a supported use case though. Hence why you had to go find a particular addon, turn off protection mode and ignore warnings to do it. You can do that but you’ll have to build in your own redundancy and error handling for when your apks go missing.

EDIT: realized I kind of rained on your parade without actually presenting an alternative. The safer way to do what you’re trying to do is to follow my guide above to set up ssh from shell commands. Then in your shell command ssh into the ssh addon and run your command there. That way you always have the dependencies you need through rebuilds, upgrades, etc.

11

Thank you, I appreciate it and when I have a bit more time to dedicate I will definitely see if I can get it going the other way so that it’s closer to what would be a supported case. The great frustration with this stuff is always it’s fun and useful but it sometimes demands more time than I can give it! Still it’s been incredibly useful already to see which sensors are lagging.

If anyone out there has time to build an add on to run a sql command and display the results in a card that would be awesome. I love the SQL sensors.

12

If you move the command to an external script, you could have that script first install sqlite3 if it’s not present with something like this:

SQLITE3='/usr/bin/sqlite3'
EXIT_CODE=0
RESPONSE=$(stat $SQLITE3) || EXIT_CODE=$?
if [ $EXIT_CODE -ne 0 ]; then
    apk add sqlite
fi
13

Note you don’t need to install sqlite3 if you use ssh instead of docker exec,
For example:

ssh -o StrictHostKeychecking=no -i <path_to_your_id_rsa_file> "sqlite3 /homeassistant/home-assistant_v2.db \"SELECT * FROM states\""

Take note of the escape quoting.

You can place your id_rsa file within the /homeassistant tree.
Note make sure the id_rsa is password-less and make sure it is stored under authorized_keys: found under the “Advanced SSH & Terminal” configuration tab.