Domoticz : How to remove fancy values ?



Background

Sometimes, when using a self managed counter, you are getting fancy values. Could be test, could be an error, could be wrong value read from a sensor .... Basically, it happens. This is a problem since it is faking your statistics. There is a way to remove those values but it does not always work. Let's find out how to solve this problem.

The problem

When looking at the history graph for the last 5 days, there is a spike here (in case of you wonder, this is a water meter graph) :


Domoticz offers a way to remove this value by "shift-clicking" it and then ask you confirmation to remove :


The thing is, it does not always works especially when the spike is composed of dozen of values, you can spent hours shift-clicking ... I have found a more radical way to sort this out !

The solution

Of course all those data are written in a database. The database is located on the domoticz folder in my case /home/pi/domoticz/domoticz.db

Take note of the sensor number, we will need it later :

Domoticz runs on a SQLite3 database and to perform any modifications, you need a client. Before, a little disclaimer : 

⚠️ EDITING THE DOMOTICZ DATABASE IS DANGEROUS ⚠️

First thing first, let's do a backup of the existing database, but before we have to shutdown domoticz to make sure everything is in clean state : 

$ sudo service domoticz.sh stop
$ cp domoticz.db domoticz.db.back

Now, we can play around with the DB !

Install sqlite3 client (if not installed already)

$ sudo apt-get install sqlite3
[...]
$ sqlite3 domoticz.db
SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite>

SQLite syntax can be confusing ... it is not following the same standard as Oracle or mariadb/mySQL. Let's dig into it.

First, let's list all tables and enable headers display :

sqlite> .tables
Applications          MobileDevices         SetpointTimers
BackupLog             MultiMeter            SharedDevices
Cameras               MultiMeter_Calendar   Temperature
CamerasActiveDevices  MySensors             Temperature_Calendar
CustomImages          MySensorsChilds       TimerPlans
DeviceStatus          MySensorsVars         Timers
DeviceToPlansMap      Notifications         ToonDevices
EnOceanNodes          Percentage            UV
EventMaster           Percentage_Calendar   UV_Calendar
EventRules            Plans                 UserSessions
Fan                   Preferences           UserVariables
Fan_Calendar          PushLink              Users
Floorplans            Rain                  WOLNodes
Hardware              Rain_Calendar         Wind
LightSubDevices       SceneDevices          Wind_Calendar
LightingLog           SceneLog              ZWaveNodes
Meter                 SceneTimers
Meter_Calendar        Scenes
sqlite> .headers on

We need to check for the content of the Meter table, this is where the utility sensors are located

sqlite> SELECT * FROM Meter;
DeviceRowID|Value|Usage|Date
5693|1788429|0|2023-06-14 22:20:00
6009|1631882|0|2023-06-14 22:20:00
5693|1788429|0|2023-06-14 22:25:00
6009|1631882|0|2023-06-14 22:25:00
5693|1788429|0|2023-06-14 22:30:00
6009|1631882|0|2023-06-14 22:30:00
5693|1788429|0|2023-06-14 22:35:00
6009|1631882|0|2023-06-14 22:35:00
5693|1788429|0|2023-06-14 22:40:00
6009|1631882|0|2023-06-14 22:40:00
5693|1788429|0|2023-06-14 22:45:01
6009|1631882|0|2023-06-14 22:45:01
5693|1788429|0|2023-06-14 22:50:00
6009|1631882|0|2023-06-14 22:50:00
5693|1788429|0|2023-06-14 22:55:00
[...]

Ok we have a lot of data, let's use the sensor number we have secured earlier by adding a WHERE clause and filter out sensor IDX 6009 :

sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009;
DeviceRowID|Value|Usage|Date
6009|1631882|0|2023-06-14 22:20:00
6009|1631882|0|2023-06-14 22:25:00
6009|1631882|0|2023-06-14 22:30:00
6009|1631882|0|2023-06-14 22:35:00
6009|1631882|0|2023-06-14 22:40:00
6009|1631882|0|2023-06-14 22:45:01
6009|1631882|0|2023-06-14 22:50:00
6009|1631882|0|2023-06-14 22:55:00
6009|1631882|0|2023-06-14 23:00:01
6009|1631882|0|2023-06-14 23:05:00
6009|1631882|0|2023-06-14 23:10:00
6009|1631882|0|2023-06-14 23:15:00
6009|1631882|0|2023-06-14 23:20:00
6009|1631882|0|2023-06-14 23:25:00
[...]

Ok this is much better, let's try to locate the data we are looking at. From the deletion attempt above, it looks like the value we are looking for is "1632239" and the date is June the 16th ... Let's fine tune the query :

sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";
DeviceRowID|Value|Usage|Date
6009|1632239|0|2023-06-16 00:00:00
6009|1632239|0|2023-06-16 00:00:01
6009|1632239|0|2023-06-16 00:05:00
6009|1632239|0|2023-06-16 00:10:01
[...]

There are actually 102 rows returned, which explain why a simple shift click is not doing the job !

Let's rerun the same SQL query with a delete statement this time : 

sqlite> DELETE FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";
sqlite> SELECT * FROM Meter WHERE DeviceRowID=6009 and Value=1632239 and Date like "2023-06-16%";
sqlite>

Our fancy rows are gone. We can quit sqlite client and restart domoticz!

sqlite> .quit
$ sudo service domoticz.sh start

Back to the webpage and ... "voilĂ " ;-)



No more fancy peak value, all clean !

I hope this helps !










Comments

What's hot ?

ShredOS : HDD degaussing with style

Wallbox : Get The Most Of It (with API)