Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
------------------------------------------------------------------------------------------------------------------------------------------------------------
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.