13.5 Database Backup and Cleanup

The Clean_Database script is 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 is also possible that the identity information may encounter an error (for example, when 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.

13.5.1 Components

$ESEC_HOME/bin/Clean_Database.sh

%ESEC_HOME%\bin\Clean_Database.bat

Main database cleanup script. This calls the other scripts.

$ESEC_HOME/bin/BackupIncidentData.sh

%ESEC_HOME%\bin\BackupIncidentData.bat

Script used to backup Incident data

%ESEC_HOME%\bin\PromptForDatabaseConnectionInfo.bat

Script used to prompt the user for SQL Server database connection information

%ESEC_HOME%\bin\BackupAdvisor.bat

$ESEC_HOME/bin/BackupAdvisor.sh

Script used to backup Advisor data

%ESEC_HOME%\bin\BackupAsset.bat

$ESEC_HOME/bin/BackupAsset.sh

Script used to backup Asset data

%ESEC_HOME%\bin\BackupVuln.bat

$ESEC_HOME/bin/BackupVuln.sh

Script used to backup Vulnerability data

esec_incidents_pkg.delete_incidents_by_query (Oracle)

delete_incidents_by_query (SQL Server)

Stored procedure used to delete Incidents specified by an SQL query

esec_incidents_pkg.delete_incidents_by_rule (Oracle)

delete_incidents_by_rule (SQL Server)

Stored procedure used to delete Incidents created by a specified Correlation Rule

esec_incidents_pkg.delete_incident_by_id (Oracle)

delete_incidents_by_id (SQL Server)

Stored procedure used to delete an Incident with a specified ID

esec_identity_pkg.cleanup_identity (Oracle)

identity_cleanup (SQL Server)

Stored procedure used to delete Identity related data

esec_advisor_pkg.delete_advisor_all (Oracle)

delete_advisor_all (SQL Server)

Stored procedure used to delete Advisor data

esec_vuln_pkg.delete_vuln_all (Oracle)

delete_vuln_all (SQL Server)

Stored procedure used to delete Vulnerability data

esec_assets_pkg.delete_assets_by_id (Oracle)

delete_assets_by_id (SQL Server)

Stored procedure used to delete Asset data based on Asset ID

esec_assets_pkg.delete_assets_all (Oracle)

delete_assets_all (SQL Server)

Stored procedure used to delete all Asset data

13.5.2 Prerequisites

There are several prerequisites for running the Clean_Database script.

  • The user running the script must have permission to execute the cleanup script.

  • The user running the script must have permission to access/execute all of the database tools/utilities.  On Linux systems, this may involve making the esecadm user a member of the "oinstall" group. For example:  

usermod -G esec,dialout,video,oinstall esecadm

  • [Identity Cleanup only] The database must be in a healthy state and in good running condition as the Identity cleanup stored procedure will disable/enable 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 NOT atomic so if one DDL statement execution fails, the script will exit with errors written to the specified log file.  There is no recovery for this scenario and a DBA would be required to run the DDL again.

WARNING:If identity information is cleaned out of the database and then reloaded, the new identity information will not be 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 will not be successful.

Use this option with extreme caution.

13.5.3 Running the Clean Database Script

Linux

  1. Open a console, go to $ESEC_HOME/bin and enter Clean_Database.sh to start the script.

    NOTE:At any time you can abort the execution of the cleanup script 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 Oracle database:

    Database name (Example: ESEC) =>  Ansping will be performed to verify the existing of the specified database instance.
Database username (Press ENTER for default esecdba) 
<username> password =>

    The connection to the database will be verified.

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

  4. If cleaning Incidents, the following things happen:

    1. The following prompt displays:

      Would you like to backup Incidents first? (y or n) =>
Enter "y" to backup the Incident data (recommended) or "n" to skip the Incident data backup.

    2. 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.

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

    3. You will be prompted again to enter the password for the database user previously specified

      NOTE: This prompt is necessary to prevent passing the password via the command line to the database exp command and making the password possibly visible in "ps" commands.

    4. The .dmp files and a log file are created in the specified backup directory.

    5. Choose an Incident cleanup option:

      (1) Delete Incidents By Query – You will be prompted to enter a custom SELECT query. For example: 

      select inc_id from incidents where inc_id=500

      NOTE:The SELECT statement cannot include quotation marks.

      (2) Delete Incidents By Rule – You will be prompted to enter the name of the Correlation Rule(s) that created the Incident(s) For example:

      My Test Rule

      (3) Delete Incidents By Id – You will be prompted to enter the ID of a specific Incident. For example:

      101
(q) Quit without action

    6. At the Incident Cleanup Confirmation prompt, type "start" to start the Incident cleanup (deletion) or "abort" to quit without performing any cleanup.

    7. The results of the Incident Cleanup will be written to the specified log file. 

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

  5. If cleaning Identity, the following things happen:

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

    2. The results of the Identity Cleanup will be written to the specified log file.

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

    3. In addition to deleting the Identity information from the database tables, the script will attempt to delete the Identity Account Map file (identityAccountMap.csv).  So at the prompt

      Please enter the esecadm user password =>

      enter the esecadm user’s password.

      NOTE:NOTE: If you have a distributed Sentinel install, you may need to manually connect to the main Sentinel Server to delete the identityAccountMap.csv file.

Windows

  1. Open a console, go to %ESEC_HOME%\bin and enter Clean_Database.bat to start the script.

    NOTE:At any time you can abort the execution of the cleanup script 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 prompt, enter the following connection information for the SQL Server database.

    • SQL Server database server hostname

    • SQL Server database instance name (Press ENTER to use the default instance)

    • Database port number (Press ENTER to use the default port - 1433)

    • Database name (for example, ESEC)

    • Database authentication option (“1” for Windows Authentication and “2” for SQL Authentication)

    • esecdba password

    NOTE:This option is only required if using SQL Authentication. If using Windows Authentication, you must run the script as the domain user equivalent to esecdba.

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

  4. If cleaning Incidents, the following things happen:

    1. The following prompt displays:

      Would you like to backup Incidents first? (y or n) =>
Enter "y" to backup the Incident data (recommended) or "n" to skip the Incident data backup.

    2. 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.

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

    3. You will be prompted to confirm the Incident backup:

      Backup Incidents to <directory>?

      Type "start" to start the Incident backup (deletion) or "abort" to quit without performing any action. The backed up files will be placed in the specified backup directory.

    4. Choose an Incident cleanup option:

      (1) Delete Incidents By Query – You will be prompted to enter a custom SELECT query. For example: 

      select inc_id from incidents where inc_id=500

      NOTE:The SELECT statement cannot include quotation marks.

      (2) Delete Incidents By Rule – You will be prompted to enter the name of the Correlation Rule(s) that created the Incident(s) For example:

      My Test Rule

      (3) Delete Incidents By Id – You will be prompted to enter the ID of a specific Incident. For example:

      101
(q) Quit without action

    5. At the Incident Cleanup Confirmation prompt, type "start" to start the Incident cleanup (deletion) or "abort" to quit without performing any cleanup.

    6. The results of the Incident Cleanup will be written to the specified log file. 

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

  5. If cleaning Identity, the following things happen:

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

    2. The results of the Identity Cleanup will be written to the specified log file.

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

    3. In addition to deleting the Identity information from the database tables, the script will attempt to delete the Identity Account Map file (identityAccountMap.csv).  So at the prompt

      Please enter username with privileges to delete the identity map file =>

      enter the name of the user who has permission to delete the identity map file (located in %ESEC_HOME%/data/map_data directory).

    4. Enter the user’s password at the next prompt.

      NOTE:NOTE: If you have a distributed Sentinel install, you may need to manually connect to the main Sentinel Server to delete the identityAccountMap.csv file.

13.5.4 Importing the Backup Data to the Sentinel Database

You can import the backup data such as Incidents, Assets, and Vulnerabilities to the Sentinel database. While importing the backup data into the Sentinel database, ensure you import the data in the right sequence to avoid any errors. For more information, see Sequence to Import the Backup Data. However, if you get any Unique Constraint Violation error, ignore the message and proceed with importing the data.

To load the backup data to the database, run the command:

Oracle

  1. Log in to the Sentinel databse as the oracle user.

  2. Set the ORACLE_HOME environment variable to the directory where the Oracle software is installed.

  3. Set the PATH variable to $ORACLE_HOME/bin.

  4. Run the command:

    imp esecdba/<esecdba_password> file = <backup_filename> log = <log_filename> tables = <table_name> ignore = y

    where:

    imp is the command used to import files

    esecdba/<esecdba_password> is the database administrator user name and password

    backup_filename is the back up data that you want to restore to the database

    log_filename is the file in which you want to store the log messages

    ignore = y ignores duplicate rows in the table

MS SQL

  1. Log in to the Sentinel SQL Server database as esecdba user and run the command:

    BULK INSERT <table_name> FROM "<backup_filename>" WITH ( BATCHSIZE =10000, FIELDTERMINATOR = '~$^' )

    where:

    BULK INSERT is the command to insert the backup data to the database

    table_name is the name of the table in the database

    backup_filename is the name of the backup file, which is in the <table_name>.bcp format

Sequence to Import the Backup Data

While importing the backup data into the Sentinel database, ensure you import the data in the following sequence. Otherwise, an error is displayed indicating that Referential Integrity constraint is violated and it does not allow you to import the backup data.

Asset Tables

ENTITY_TYP_LKUP
PERSON
ENV_IDENTITY_LKUP
VNDR
PRDT
ASSET_LOC
SENSITIVITY_LKUP
CRIT_LKUP
ASSET_VAL_LKUP
NETWORK_IDENTITY_LKUP
ORGANIZATION
ASSET_CTGRY
ROLE_LKUP
PHYSICAL_ASSET
ASSET_IP
ASSET_HOSTNAME
ASSET_X_ENTITY_X_ROLE
ASSET

NOTE:In MS SQL, the filename for Asset table is assets.bcp. Therefore, when you import the Asset table you must specify the backup_filename as assets.bcp.

Advisor Tables

ADV_NXS_OSVDB_DETAILS
ADV_NXS_PRODUCTS
ADV_NXS_SIGNATURES
ADV_NXS_KB_PRODUCTSREF
ADV_NXS_KB_PATCH
ADV_NXS_MAPPINGS

Vulnerability

VULN_SCANNER
VULN_SCAN
VULN_RSRC
VULN_RSRC_SCAN
VULN
VULN_SCAN_VULN
VULN_INFO
VULN_CODE

Incidents

INCIDENTS_ASSETS
INCIDENTS_VULN
INCIDENTS_EVENTS
ANNOTATIONS
ATTACHMENTS
EXT_DATA
WORKFLOW_INFO
ASSOCIATIONS
INCIDENTS