search

Icinga / icingaweb2-module-vspheredb

The easiest way to monitor a VMware vSphere environment.
https://icinga.com/docs/vsphere/latest
GNU General Public License v2.0
100 stars 34 forks source link

Allow to delete all data related to a vCenter #324

Open Thomas-Gelf opened 2 years ago

Thomas-Gelf commented 2 years ago

Expected Behavior

I want to be able to completely wipe data related to a specific vCenter from my DB

Current Behavior

That's not possible

Possible Solution

Implement it :stuck_out_tongue_closed_eyes:

delete_vcenter

(PRO TIP) ask for confirmation:

delete_vcenter-confirm

bobapple commented 2 years ago

ref/IP/42780 ref/NC/763411

willfurnell commented 1 year ago

Hello, is there any update on this issue? It's been open for quite some time, even with no workaround, Thanks, Will.

stevenahmet commented 1 year ago

Hi. Will there ever be an update to this issue? It seems pretty wild that we'd need to drop/delete all our data just because we need to decommission an old vCentre/ESXi hosts. This link https://github.com/Icinga/icingaweb2-module-vspheredb/issues/345 points to #324 as a resolution but there's no resolution here. Thanks.

coredump17 commented 2 months ago

Any update on this issue?

wp-perc commented 1 week ago

We also have this issue. At present moment, we proceed to remove old data with manual queries. If this approach is right, it should be included in the application.

The idea is:

  1. remove associated data when a vcenter is removed
  2. periodically check for remaining data and remove orphans with a dedicated background process

Here the query we run to remove orphan data. If they are right, we can adapt them and implement some code.

DB Schema version is 58.

DELETE FROM alarm_history WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE alarm_history;

DELETE FROM vm_event_history WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_event_history;

# Complementary tables (providing additional data)
DELETE FROM compute_resource WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE compute_resource;

DELETE FROM datastore WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE datastore;

DELETE FROM host_hba WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_hba;

DELETE FROM host_pci_device WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_pci_device;

DELETE FROM host_virtual_nic WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_virtual_nic;

DELETE FROM host_physical_nic WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_physical_nic;

DELETE FROM host_quick_stats WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_quick_stats;

DELETE FROM host_sensor WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_sensor;

DELETE FROM host_system WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE host_system;

DELETE FROM performance_counter WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE performance_counter;

DELETE FROM performance_group WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE performance_group;

DELETE FROM performance_unit WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE performance_unit;

DELETE FROM storage_pod WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE storage_pod;

DELETE FROM virtual_machine WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE virtual_machine;

DELETE FROM vm_datastore_usage WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_datastore_usage;

DELETE FROM vm_disk WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_disk;

DELETE FROM vm_disk_usage WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_disk_usage;

DELETE FROM vm_hardware WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_hardware;

DELETE FROM vm_network_adapter WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_network_adapter;

DELETE FROM vm_quick_stats WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_quick_stats;

DELETE FROM vm_snapshot WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE vm_snapshot;

# Object index
SET SESSION foreign_key_checks=OFF;
DELETE FROM object WHERE vcenter_uuid IN
        (SELECT instance_uuid FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL));
OPTIMIZE TABLE object;
SET SESSION foreign_key_checks=ON;

# Vcenter source data
DELETE FROM vcenter WHERE id NOT IN (SELECT DISTINCT vcenter_id FROM vcenter_server WHERE vcenter_id IS NOT NULL);
OPTIMIZE TABLE vcenter;

On my VSphereDB system the following tables have no data, so I don't perform any cleanup on them.

# Skipped tables
counter_300x5
distributed_virtual_portgroup
distributed_virtual_switch
host_list
host_list_member
host_monitoring_hoststate
monitoring_connection
monitoring_rule_problem
monitoring_rule_problem_history
monitoring_rule_set
perfdata_consumer
perfdata_subscription
performance_collection_interval
vcenter_event_history_collector
vcenter_server
vcenter_session
vcenter_sync
vm_list
vm_list_member
vm_monitoring_hoststate
vspheredb_daemon
vspheredb_daemonlog
vspheredb_schema_migration

@Thomas-Gelf, what do you think?