Cleanup sensor useless attributes filling database

Configuration
1

Hi,

I have a sensor collected via zigbee2mqtt - MQTT that is generating a large amount of useless data stored in attributes. I will ask for zigbee2mqtt correction.

Waiting for correction, is there a way to cleanup useless attributes to avoid exploding database ?
I only need to keep the attributes in bold !

Thanks for your help,
Regards,
Pierre

SELECT
*
FROM states
where
entity_id = “sensor.linky_easd02”;

state_id domain entity_id state attributes
35275309 sensor sensor.linky_easd02 15970.036 ** {“state_class”:“measurement”, “active_enerfy_out_d01”:7791.507,“active_enerfy_out_d02”:15970.036,“active_enerfy_out_d03”:null,“active_enerfy_out_d04”:null,“active_power”:2098,“active_power_max”:6663,“active_power_ph_b”:3250,“apparent_power”:6002,“available_power”:9,“average_rms_voltage_meas_period”:240,“current_date”:“H220306224851”,“current_index_tarif”:1,“current_price”:“HEURE CREUSE”,“current_summ_delivered”:23761.519,“current_tarif”:“H PLEINE/CREUSE”,“current_tier10_summ_delivered”:0,“current_tier1_summ_delivered”:7791.483,“current_tier2_summ_delivered”:15970.036,“current_tier3_summ_delivered”:0,“current_tier4_summ_delivered”:0,“current_tier5_summ_delivered”:0,“current_tier6_summ_delivered”:0,“current_tier7_summ_delivered”:0,“current_tier8_summ_delivered”:0,“current_tier9_summ_delivered”:0,“days_number_current_calendar”:null,“days_number_next_calendar”:null,“days_profile_current_calendar”:null,“days_profile_next_calendar”:null,“drawn_v_a_max_n1”:5563,“linkquality”:75,“message1”:“PAS DE ###MESSAGE”,“message2”:"",“meter_serial_number”:“06206118xxxx”,“power_threshold”:9,“relais”:1,“rms_current”:25,“rms_current_max”:65535,“rms_voltage”:240,“site_id”:“2145658453xxxx”,“software_revision”:2,“start_mobile_point1”:null,“start_mobile_point2”:null,“start_mobile_point3”:null,“status_register”:“003A0000”,“stop_mobile_point1”:null,“stop_mobile_point2”:null,“stop_mobile_point3”:null,“tomorrow_color”:"",“update”:{“state”:“idle”},“update_available”:false,“unit_of_measurement”:“kWh”,“device_class”:“energy”,“friendly_name”:“Linky EASD02”,“last_reset”:“1970-01-01T00:00:00+00:00”}
35275336 sensor sensor.linky_easd02 15970.036 {“state_class”:“measurement”,“active_enerfy_out_d01”:7791.507,“active_enerfy_out_d02”:15970.036,“active_enerfy_out_d03”:null,“active_enerfy_out_d04”:null,“active_power”:2098,“active_power_max”:6663,“active_power_ph_b”:3250,“apparent_power”:6002,“available_power”:9,“average_rms_voltage_meas_period”:240,“current_date”:“H220306224851”,“current_index_tarif”:1,“current_price”:“HEURE CREUSE”,“current_summ_delivered”:23761.519,“current_tarif”:“H PLEINE/CREUSE”,“current_tier10_summ_delivered”:0,“current_tier1_summ_delivered”:7791.483,“current_tier2_summ_delivered”:15970.036,“current_tier3_summ_delivered”:0,“current_tier4_summ_delivered”:0,“current_tier5_summ_delivered”:0,“current_tier6_summ_delivered”:0,“current_tier7_summ_delivered”:0,“current_tier8_summ_delivered”:0,“current_tier9_summ_delivered”:0,“days_number_current_calendar”:null,“days_number_next_calendar”:null,“days_profile_current_calendar”:null,“days_profile_next_calendar”:null,“drawn_v_a_max_n1”:5563,“linkquality”:78,“message1”:“PAS DE MESSAGE”,“message2”:"",“meter_serial_number”:“06206118xxxx”,“power_threshold”:9,“relais”:1,“rms_current”:25,“rms_current_max”:65535,“rms_voltage”:240,“site_id”:“2145658453xxxx”,“software_revision”:2,“start_mobile_point1”:null,“start_mobile_point2”:null,“start_mobile_point3”:null,“status_register”:“003A0000”,“stop_mobile_point1”:null,“stop_mobile_point2”:null,“stop_mobile_point3”:null,“tomorrow_color”:"",“update”:{“state”:“idle”},“update_available”:false,“unit_of_measurement”:“kWh”,“device_class”:“energy”,“friendly_name”:“Linky EASD02”,“last_reset”:“1970-01-01T00:00:00+00:00”}
2

Exclude your mqtt sensor from the recorder and create a template sensor that only has the attributes that you want.

2 Likes
3

You can’t remove those attributes from entities in your database. Those are required for long term statistics and they are not a bug. I can guarantee the Zigbee2MQTT will deny your request to have them removed.

Your only option is to exclude the entities themselves from recorder, or manually configure these entities in MQTT without those settings. Just keep in mind that if you use the energy dashboard, those entities will no longer work in them.

4

I don’t get it. I’m only willing to remove data that are not related to that specific sensor. I don’t think removing the blob of data linked to others sensors would hurt. To be clear, I removed most of the sensor excluded from recorder but implementation in zigbee2mqtt do add them anyway as attributes to each of the data. I.e. in day power consumption sensor I get in attributes night power consumption, instant power, serial number, subscription etc…

5

Home assistant REQUIRES those attributes for the Energy Panel. Zigbee2MQTT will not remove them in the default configuration because home assistant requires them.

Your only options are

  1. to do as @ondras12345 says, exlcude your zigbee2mqtt device from recorder and make a template sensor that stores in your database. However this will make it so the energy panel will not see your device.
  2. Manually configure the device via MQTT and exclude those attributes. However this will make it so the energy panel will not see your device.

Summary:

You either begrudgingly store all that information in history, or you exclude the entity entirely, or remove those attributes and store it in history but lose Energy and long term statistics.

6

I thought OP wanted to keep the attributes in bold and exclude everything else, not the other way around. If so, Energy dashboard should work.

7

well, it looks like I read that backwards

8

@ondras12345
indeed, that’s my need. I will go via new sensor, waiting for zigbee2mqtt to solve this (I guess they will).