Page 1 of 1
Cleanup unused devices
Posted: Monday 09 March 2020 15:04
by peter8wun
Hi,
My domoticz installation received quit a large number of divices from my RFlink hardware (>3000) that are not mine and I think this makes my domotics slow. For instance when accessing the list of divices.
is there a problem executing "delete from DeviceStatus where used =0" on the database to get rit of these devices?
Peter
Re: Cleanup unused devices
Posted: Monday 09 March 2020 15:56
by manjh
peter8wun wrote: Monday 09 March 2020 15:04
Hi,
My domoticz installation received quit a large number of divices from my RFlink hardware (>3000) that are not mine and I think this makes my domotics slow. For instance when accessing the list of divices.
is there a problem executing "delete from DeviceStatus where used =0" on the database to get rit of these devices?
Peter
In the list of devices, click on "not used devices" at the left top. After this, select "all" for "show entries.
This will give you a complete list. Now select the little selectbox right next to the garbage bin, then click the garbage bin itself.
This gets rid of all unused devices.
Re: Cleanup unused devices
Posted: Monday 09 March 2020 16:24
by waaren
peter8wun wrote: Monday 09 March 2020 15:04
My domoticz installation received quit a large number of divices from my RFlink hardware (>3000) that are not mine and I think this makes my domotics slow. For instance when accessing the list of devices.
Is there a problem executing "delete from DeviceStatus where used =0" on the database to get rit of these devices?
Not really but
-
to be safe make sure you have a backup and only do this when domoticz is not active.
- If you have the allow new hardware set to yes they will come back.
- These devices will also have data in other tables.
You can identify the tables (potentally) with data from these devices
Code: Select all
select name as Tables_containing_deviceRowid from sqlite_master where type='table' and sql like '%devicerowid%';
to delete records with devicerowid's from unused devices from these tables
Code: Select all
delete from LightingLog where devicerowid in (select id from devicestatus where used = 0);
delete from Rain where devicerowid in (select id from devicestatus where used = 0);
delete from Rain_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Temperature where devicerowid in (select id from devicestatus where used = 0);
delete from Temperature_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Timers where devicerowid in (select id from devicestatus where used = 0);
delete from SetpointTimers where devicerowid in (select id from devicestatus where used = 0);
delete from UV where devicerowid in (select id from devicestatus where used = 0);
delete from UV_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Wind where devicerowid in (select id from devicestatus where used = 0);
delete from Wind_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Meter where devicerowid in (select id from devicestatus where used = 0);
delete from Meter_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from MultiMeter where devicerowid in (select id from devicestatus where used = 0);
delete from MultiMeter_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Notifications where devicerowid in (select id from devicestatus where used = 0);
delete from LightSubDevices where devicerowid in (select id from devicestatus where used = 0);
delete from DeviceToPlansMap where devicerowid in (select id from devicestatus where used = 0);
delete from SceneDevices where devicerowid in (select id from devicestatus where used = 0);
delete from SharedDevices where devicerowid in (select id from devicestatus where used = 0);
delete from Percentage where devicerowid in (select id from devicestatus where used = 0);
delete from Percentage_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Fan where devicerowid in (select id from devicestatus where used = 0);
delete from Fan_Calendar where devicerowid in (select id from devicestatus where used = 0);
delete from Pushlink where devicerowid in (select id from devicestatus where used = 0);
and lastly
Code: Select all
delete from devicestatus where used = 0;
Re: Cleanup unused devices
Posted: Monday 09 March 2020 16:29
by peter8wun
Thanks for the quick and complete answer. this helps tremendously.
Re: Cleanup unused devices
Posted: Monday 09 March 2020 16:34
by waaren
manjh wrote: Monday 09 March 2020 15:56
In the list of devices, click on "not used devices" at the left top. After this, select "all" for "show entries.
This will give you a complete list. Now select the little selectbox right next to the garbage bin, then click the garbage bin itself.
This gets rid of all unused devices.
Not sure when the fix was applied but on older versions this approach (which should be the preferred one) caused domoticz to 'hang' when > 5 devices where selected for deletion.