QRadar: How to determine if your UBA database is corrupted and how to re-create it

Resolving The Problem

You have two options to resolve the problem. Try dropping the data base first, and if that does not work, try method two.

Drop Database

The following procedure drops the entire UBA database from postgresql and restarting the "poll" service re-creates the UBA database. It also maintains your Tuning parameters (Aliases) as well as the LDAP Imports configuration.

Here are the steps to re-create your UBA postgres database:

1. From the Console use qappmanager utility to find the App Instance ID for UBA (1101).

   /opt/qradar/support/qappmanager

   APP INSTANCES (IID=Instance ID, DID=Definition ID, MHN=Managed Host Name, AHT=Application Host Type, SP=Security Profile):
   
   IID | DID | Name | Status | Task Status | Installed | MHN | AHT | Memory | SP | Errors
   
   --------------------------------------------------------------------------------------------------------------------------------------------------------------
   
   1101 | 1101 | User Analytics                                       | RUNNING | COMPLETED   | 2020-04-01 13:35 | <servername> | LOCAL |   1000 |    |   

2. Log in to the UBA docker container 

   /opt/qradar/support/recon connect 1101

3. Stop all services as follows:

   supervisorctl stop flask
   supervisorctl stop poll
   supervisorctl stop userimport

4. Use psql client to drop the UBA database by using the exact command as follows:

   psql -U postgres -c "DROP DATABASE uba"

5. Restart services in this specific order.

   supervisorctl start poll
   supervisorctl start flask
   supervisorctl start userimport

   
   Results
   Once completed, execute a manual import of users from all your LDAP connections. If you get an error that prevents you from dropping the database, follow the next procedure.
   
    

Unable to drop database

If you get a message when you drop the database that there are users still connected, the database might not be dropped.

You can verify with the following statements:

1. Connect to the container from where the apps are running.

   /opt/qradar/support/recon connect 1101

2. Connect to UBA database to run SELECT statement.

   psql -U postgres

   SELECT 
   pid,datname,usename,application_name,client_hostname,client_port,backend_start,query_start,query,state FROM pg_stat_activity WHERE state = 'active';

3. If so, you have to use the following steps to re-create the database. However, you lose the LDAP Imports that are configured. Make sure you take screenshots unless you already know the settings.
4. From the Console Stop the UBA app with qappmanager utility. (Option 24)

   /opt/qradar/support/qappmanager

5. From where the apps are running:

   cd /store/docker/volumes/qapp-1101/ 

6. Type this command exactly as it is written:

   mv psql psql_old_bkp

7. From the Console Start the UBA app with qappmanager utility. (Option 23)

   /opt/qradar/support/qappmanager

   
   Results
   Once completed, reconfigure all your LDAP Import connections and import users.