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.
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.
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.
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
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.
If cleaning Incidents, the following things happen:
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.
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.
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.
The .dmp files and a log file are created in the specified backup directory.
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
At the Incident Cleanup Confirmation prompt, type "start" to start the Incident cleanup (deletion) or "abort" to quit without performing any cleanup.
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.
If cleaning Identity, the following things happen:
At the Identity Cleanup Confirmation prompt, type "start" to start the Identity cleanup or "abort" to quit without performing the Identity cleanup.
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.
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.
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.
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
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.
If cleaning Incidents, the following things happen:
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.
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.
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.
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
At the Incident Cleanup Confirmation prompt, type "start" to start the Incident cleanup (deletion) or "abort" to quit without performing any cleanup.
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.
If cleaning Identity, the following things happen:
At the Identity Cleanup Confirmation prompt, type "start" to start the Identity cleanup or "abort" to quit without performing the Identity cleanup.
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.
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).
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.
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:
Log in to the Sentinel databse as the oracle user.
Set the ORACLE_HOME environment variable to the directory where the Oracle software is installed.
Set the PATH variable to $ORACLE_HOME/bin.
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
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
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.
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.
ADV_NXS_OSVDB_DETAILS ADV_NXS_PRODUCTS ADV_NXS_SIGNATURES ADV_NXS_KB_PRODUCTSREF ADV_NXS_KB_PATCH ADV_NXS_MAPPINGS
VULN_SCANNER VULN_SCAN VULN_RSRC VULN_RSRC_SCAN VULN VULN_SCAN_VULN VULN_INFO VULN_CODE
INCIDENTS_ASSETS INCIDENTS_VULN INCIDENTS_EVENTS ANNOTATIONS ATTACHMENTS EXT_DATA WORKFLOW_INFO ASSOCIATIONS INCIDENTS