Need some help on how to use command_line sensor and sqlite3

Configuration
,
1

Ok, since it is so troublesome to access long term statistics using the SQL integration due to the fact that it doesn’t support parameters or templates, I came up with a real “hack”. In theory it should work, but I am limited by my unix/HA skills. I am a front end developer by trade and have very little expertise using stuff like ssh or bash commands. I have never used Unix in my life (windows/mac person). This is what I har so far:

a command line sensor:

command_line:
  - sensor:
      name: simplesql
      command: >
sqlite3 /config/home-assistant_v2.db "SELECT count(*) from state_attributes"

And this just give me “unknown” and error code 127. From reading countless threads here I understand that has something to do with different docker containers or something…

Someone here suggested that I need to add some commands before my actual command to make this work:

ssh -i /root/.ssh/id_rsa -o StrictHostKeyChecking=no -q [email protected]

If I put that stuff before my sqlite3… it should work and the sensor should return 23211 or what ever number the count is. Note, I only plan to return scalar values, numbers and text, never lists.

So, my problem is that I have no clue what the above ssh line is or what I should do to make it work on my HA device. I have read the posts, but still clueless. I have a HA Green and a windows pc with windows 11. I have installed the add on “terminal & SSH”. Thats about it.

Thank you

2

You could just use the ad-hoc integration if you want to query the HA db:

3

Thank you, but as I mentioned in my post, the SQL integration has one BIG flaw, and it is that I cant use parameters or templates for the queries. A simple {{parameter }} would have solved my issue, but somehow the developers didn’t think that was needed.

I had working code and a cumbersum and “hacky” solution using helpers, automations, scripts and the REST-api, but for some equally strange reason they decided not to include LTS in their “statistics” API. Go figure… and it is not mentioned anywhere in the documentation either.

This is the only solution that I have tried that actually might work to dynamically query long term statistics. I can use parameters for my queries, I can use whatever SQL query I want and I can finally easily access LTS. All I need is to get that fricki´n command_line sensor to work.

It is stupidly complicated, I know, but after having spent several days with utility meters, REST-API etc etc etc, this is my final idea…

4

Ah sorry, read too quick.

5

I needed to query long term statistics and ended up using node red and the node-red-node-sqlite palette.
It worked well for a newbie like me.

6

Intriguing. I have never used node red, and was kind of hoping to avoid any rabbit holes like that (big tools that takes lots of time to learn).

I will have a look at this, any documents, tutorials or code examples that can help me out along the way? I have found this integration in HACS, is that the one I need? HACS – Home Assistant

7

install the node red addon.

Install HACS addon

Install HACS integration

Using HACS you install the Node-RED Companion Integration

In node red, go to the menu and click on manage pallete
Install node-red-node-sqlite

Then import the flow below:

[
    {
        "id": "258e651031754485",
        "type": "sqlite",
        "z": "7547a558cf945eb1",
        "mydb": "f235761944d077a5",
        "sqlquery": "msg.topic",
        "sql": "SELECT\n  state\nFROM\n  statistics\n  INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id\nWHERE\n  statistics_meta.statistic_id = 'sensor.consumo_total'\n  AND statistics.start_ts = strftime('%s', '2025-01-06 11:00:00')\nLIMIT\n  1;\n",
        "name": "",
        "x": 1680,
        "y": 700,
        "wires": [
            [
                "dfeeca3795cb819f",
                "986254e886b19148"
            ]
        ]
    },
    {
        "id": "92151399e29a39c6",
        "type": "inject",
        "z": "7547a558cf945eb1",
        "name": "",
        "props": [
            {
                "p": "sensor",
                "v": "sensor.consumo_total",
                "vt": "str"
            },
            {
                "p": "data_ultima_leitura",
                "v": "2025-01-09T11:00",
                "vt": "str"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "x": 1250,
        "y": 700,
        "wires": [
            [
                "8e60c843a93faa17"
            ]
        ]
    },
    {
        "id": "dfeeca3795cb819f",
        "type": "debug",
        "z": "7547a558cf945eb1",
        "name": "debug 22",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "true",
        "targetType": "full",
        "statusVal": "",
        "statusType": "auto",
        "x": 1960,
        "y": 600,
        "wires": []
    },
    {
        "id": "8e60c843a93faa17",
        "type": "function",
        "z": "7547a558cf945eb1",
        "name": "function 3",
        "func": "msg.topic = `\nSELECT\n  state\nFROM\n  statistics\n  INNER JOIN statistics_meta ON statistics.metadata_id = statistics_meta.id\nWHERE\n  statistics_meta.statistic_id = '${msg.sensor}'\n  AND statistics.start_ts = strftime('%s', '${msg.data_ultima_leitura}')\nLIMIT\n  1;\n`;\nreturn msg;\n",
        "outputs": 1,
        "timeout": 0,
        "noerr": 0,
        "initialize": "",
        "finalize": "",
        "libs": [],
        "x": 1420,
        "y": 700,
        "wires": [
            [
                "258e651031754485"
            ]
        ]
    },
    {
        "id": "986254e886b19148",
        "type": "ha-sensor",
        "z": "7547a558cf945eb1",
        "name": "simplesql",
        "entityConfig": "2bd5e7607412cee5",
        "version": 0,
        "state": "payload[0].state",
        "stateType": "msg",
        "attributes": [],
        "inputOverride": "allow",
        "outputProperties": [],
        "x": 1940,
        "y": 700,
        "wires": [
            []
        ]
    },
    {
        "id": "f235761944d077a5",
        "type": "sqlitedb",
        "db": "/share/ha_db/home-assistant_v2.db",
        "mode": "RO"
    },
    {
        "id": "2bd5e7607412cee5",
        "type": "ha-entity-config",
        "server": "33803899.487d48",
        "deviceConfig": "",
        "name": "simplesql",
        "version": 6,
        "entityType": "sensor",
        "haConfig": [
            {
                "property": "name",
                "value": "simplesql"
            },
            {
                "property": "icon",
                "value": ""
            },
            {
                "property": "entity_picture",
                "value": ""
            },
            {
                "property": "entity_category",
                "value": ""
            },
            {
                "property": "device_class",
                "value": ""
            },
            {
                "property": "unit_of_measurement",
                "value": ""
            },
            {
                "property": "state_class",
                "value": ""
            }
        ],
        "resend": false,
        "debugEnabled": false
    },
    {
        "id": "33803899.487d48",
        "type": "server",
        "name": "Home Assistant",
        "version": 5,
        "addon": true,
        "rejectUnauthorizedCerts": true,
        "ha_boolean": "y|yes|true|on|home|open",
        "connectionDelay": true,
        "cacheJson": true,
        "heartbeat": false,
        "heartbeatInterval": "30",
        "areaSelector": "friendlyName",
        "deviceSelector": "friendlyName",
        "entitySelector": "friendlyName",
        "statusSeparator": "at: ",
        "statusYear": "hidden",
        "statusMonth": "short",
        "statusDay": "numeric",
        "statusHourCycle": "h23",
        "statusTimeFormat": "h:m",
        "enableGlobalContextStore": true
    }
]
1 Like
8

THANK YOU!

I finally found a working solution with Node Red. It is stupidly complicated to access LTS this way, but the values are finally there. I have lots of work left to do before it is totally solid, but now I am happy to finally show the total on my energy graph page

image
image937×266 22.6 KB

image
image1204×537 30.4 KB

Very much WIP, but slowly getting there…

1 Like