14.5 Database Cleanup

The Clean_Database.sh scripts are used to purge incidents, identities, assets, advisor data, and vulnerabilities from the Sentinel database. For example, an improperly configured Correlation rule might create hundreds of unwanted incidents in the database. It’s also possible that the identity information might encounter an error. For example, if someone attempts to delete the IdentityAccountMap.csv file.

WARNING:Because these scripts are designed to delete information from your database, they should be used very carefully and only after understanding the implications.

14.5.1 Components

<install_directory>/bin/Clean_Database.sh

Main database cleanup script. This calls the other scripts.

<install_directory>/bin/BackupIncidentData.sh

Script used to back up Incident data.

delete_incidents_by_query

Stored procedure used to delete incidents specified by an SQL query.

delete_incidents_by_rule

Stored procedure used to delete incidents created by a specified correlation rule.

delete_incidents_by_id

Stored procedure used to delete an incident with a specified ID.

identity_cleanup

Stored procedure used to delete identity-related data.

<install_directory>/bin/BackupAdvisor.sh

Script used to back up Advisor data.

delete_advisor_all

Stored procedure used to delete Advisor data.

<install_directory>/bin/BackupAsset.sh

Script used to back up Asset data.

delete_assets_all

Stored procedure used to delete all Asset data.

delete_assets_by_id

Stored procedure used to delete Asset data based on Asset ID.

<install_directory>/bin/BackupVuln.sh

Script used to back up Vulnerability data.

delete_vuln_all

Stored procedure used to delete Vulnerability data.

14.5.2 Prerequisites

There are several prerequisites for running the Clean_Database script.

  • The user running the script must be a novell user, and each script must have the permission set so that only the novell user is allowed to execute the cleanup script.

  • The user running the PostgreSQL script must have permission to access/execute all of the database tools and utilities. Run the script as a dbauser.

  • (Identity Cleanup only) The database must be in a healthy state and in good running condition because the Identity cleanup stored procedure disables and enables foreign key constraints. 

  • (Identity Cleanup only) All Identity/Account loaders and Collectors, such as the Identity Vault Collector, should be stopped.

  • (Identity Cleanup only) Reports that are running queries against the Identity tables should be stopped.

The Identity cleanup DDL operations are  atomic, so if one DDL statement execution fails, the script exits with errors written to the specified log file. 

WARNING:If identity information is cleaned out of the database and then reloaded, the new identity information is not synchronized with any past events that had identity information injected. Therefore, attempts to perform identity lookups on past events (received before the cleanup) or run reports on past events with identity information is not successful.

Use this option with extreme caution.

14.5.3 Running Clean_Database.sh

  1. Open a console, go to <install_directory>/bin and enter Clean_Database.sh to start the script.

    NOTE:You can cancel the execution of the cleanup script at any time by entering q at any prompt.

  2. At the prompt, indicate which objects you want to remove from the database:

    Which objects would you like to cleanup?
(1) Incidents
(2) Identities
(3) Assets
(4) Advisor
(5) Vulnerabilities
(6) Incidents and Identities
(7) All

  3. At the prompts, enter the following information to connect to the PostgreSQL database:

    Database server hostname (Press ENTER for default localhost)=>
Database name (Press ENTER for default SIEM) =>  
Database username (press ENTER for default dbauser)

    The database connection is verified before proceeding to the next step.

  4. (Conditional) If you selected to clean incidents:

    The following prompt displays:

    Would you like to backup Incidents first? (y or n) =>

    1. If you select y to back up the incidents, enter the destination directory (a full path or a path relative to the location of the cleanup script) for the backup files.

      The user running the script must have permission to write to this directory.

    2. Select an incident cleanup option:

      • Delete Incidents By Query: You are prompted to enter a custom SELECT query. For example: 

        select inc_id from incidents where inc_id=500

        The SELECT statement cannot include quotation marks.

      • Delete Incidents By Rule: You are prompted to enter the name of the Correlation rules that created the incidents. For example:

        My Test Rule

      • Delete Incidents By Id: You are prompted to enter the ID of a specific incident. For example:

        101
(q) Quit without action

    3. At the Incident Cleanup Confirmation prompt, enter start to start the incident cleanup or enter abort to quit without performing any cleanup.

      The results of the incident cleanup are written to the specified log file. 

      You should review the log file for any errors before continuing.

  5. Conditional) If you selected to clean identity:

    1. At the Identity Cleanup Confirmation prompt, enter start to start the Identity cleanup or enter abort to quit without performing the identity cleanup.

      The results of the Identity Cleanup are written to the specified log file.

      You should review the log file for any errors before continuing.

    2. In addition to deleting the Identity information from the database tables, the script attempts to delete the Identity Account Map file (identityAccountMap.csv).

      If you have a distributed Sentinel install, you might need to manually connect to the main Sentinel server to delete the identityAccountMap.csv file.

    3. At the prompt, enter the novell user’s password.