purge_orphan_asset_data.sql

------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Asset Purge v3
------------------------------------------------------------------------------------------------------------------------------------------------------------
SET search_path TO nxsilo_default, nxadmin, nxglobal;
ANALYZE VERBOSE;

\echo  `date`  'Disable constraints'
SET session_replication_role = replica;

------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Start of Version Asset Data purge
------------------------------------------------------------------------------------------------------------------------------------------------------------
 -- Drop a temp table if exists.
 DROP TABLE IF EXISTS temp_orphan_asset;

 -- Create a temp table to store assets that will be purged.
 \echo  `date`  'Calculating orphaned assets to delete'
 CREATE TABLE temp_orphan_asset AS
 SELECT a.asset_id
 FROM asset a
    LEFT JOIN site_devices sd ON a.asset_id = sd.dev_id
 WHERE sd.dev_id IS NULL;

 CREATE INDEX ix_temp_orphan_asset_id ON temp_orphan_asset (asset_id);

 -- Purge Data from tables
 \echo  `date`  'Purging asset_history'
 DELETE FROM asset_history t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);

 \echo  `date`  'Purging device_synopsis'
 DELETE FROM device_synopsis                          t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.dev_id);
 \echo  `date`  'Purging device_nodes'
 DELETE FROM device_nodes                             t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.dev_id);
 \echo  `date`  'Purging device_groups'
 DELETE FROM device_groups                            t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.dev_id);
 \echo  `date`  'Purging vuln_exceptions'
 DELETE FROM vuln_exceptions                          t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.dev_id);
 \echo  `date`  'Purging silo_cp_global_override'
 DELETE FROM silo_cp_global_override                  t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.device_id);
 \echo  `date`  'Purging tag_membership_history'
 DELETE FROM tag_membership_history                   t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging validated_asset_vulnerability'
 DELETE FROM validated_asset_vulnerability            t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging asset_nodes_archive'
 DELETE FROM asset_nodes_archive                      t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging asset_group_membership_history'
 DELETE FROM asset_group_membership_history           t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging tag_criteria_asset_bridge'
 DELETE FROM tag_criteria_asset_bridge                t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging tag_asset_bridge'
 DELETE FROM tag_asset_bridge                         t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);

 \echo  `date`  'Purging version_asset_address'
 DELETE FROM version_asset_address                    t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_name'
 DELETE FROM version_asset_name                       t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_host_type'
 DELETE FROM version_asset_host_type                  t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_unique_id'
 DELETE FROM version_asset_unique_id                  t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_attribute'
 DELETE FROM version_asset_attribute                  t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_service_type'
 DELETE FROM version_asset_endpoint_service_type      t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_database'
 DELETE FROM version_asset_database                   t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_file_attribute'
 DELETE FROM version_asset_file_attribute             t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_file'
 DELETE FROM version_asset_file                       t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_user_attribute'
 DELETE FROM version_asset_user_attribute             t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_user'
 DELETE FROM version_asset_user                       t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_user_group_attribute'
 DELETE FROM version_asset_user_group_attribute       t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_user_group'
 DELETE FROM version_asset_user_group                 t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_operating_system_attribute'
 DELETE FROM version_asset_operating_system_attribute t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_operating_system'
 DELETE FROM version_asset_operating_system           t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_software_attribute'
 DELETE FROM version_asset_software_attribute         t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_software'
 DELETE FROM version_asset_software                   t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_configuration'
 DELETE FROM version_asset_endpoint_configuration     t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_credential_status'
 DELETE FROM version_asset_endpoint_credential_status t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_service'
 DELETE FROM version_asset_endpoint_service           t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_web_application'
 DELETE FROM version_asset_endpoint_web_application   t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint_web_page'
 DELETE FROM version_asset_endpoint_web_page          t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_vulnerability_finding'
 DELETE FROM version_asset_vulnerability_finding      t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_vulnerability_test'
 DELETE FROM version_asset_vulnerability_test         t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset_endpoint'
 DELETE FROM version_asset_endpoint                   t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging version_asset'
 DELETE FROM version_asset                            t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);

 \echo  `date`  'Purging device_asset_bridge'
 DELETE FROM device_asset_bridge                      t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.device_id);
 \echo  `date`  'Purging policy_rule_check_result'
 DELETE FROM policy_rule_check_result                 t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging asset_policy_rule_synopsis'
 DELETE FROM asset_policy_rule_synopsis               t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging asset_policy_synopsis'
 DELETE FROM asset_policy_synopsis                    t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging asset_container'
 DELETE FROM asset_container                          t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);
 \echo  `date`  'Purging cross_site_unmerge_site_assets'
 DELETE FROM cross_site_unmerge_site_assets           t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);

 \echo  `date`  'Purging asset'
 DELETE FROM asset                                    t WHERE EXISTS (SELECT asset_id FROM temp_orphan_asset mav WHERE mav.asset_id = t.asset_id);

 DROP TABLE IF EXISTS temp_orphan_asset;
------------------------------------------------------------------------------------------------------------------------------------------------------------
-- End of data Version Asset Data purge
------------------------------------------------------------------------------------------------------------------------------------------------------------

\echo  `date`  'Enable constraints'
SET session_replication_role = DEFAULT;

ANALYZE VERBOSE;