QRadar: Upgrading to UBA 4.1.0 can lead to aspects of the app not functioning properly

Created by Curt Wolfson on Thu, 04/15/2021 - 13:55

Published URL:

https://www.ibm.com/support/pages/node/6443753

6443753

Troubleshooting

Problem

Under certain circumstances, customers upgrading to UBA 4.1.0 can experience issues where the app not to function properly due to a migration issue with the database. The upgrade issue is typically caused by data cleared out of the application. When this issue occurs, the user interface can display "Unable to get imports from database" or /opt/app-root/store/log/supervisord.log can display "UndefinedColumn" errors.

Symptom

After an administrators installs UBA 4.1.0, the following symptoms can indicate the application did not upgrade successfully:

The User Imports page fails to load and an 'Unable to get imports from database' error is displayed.

The Rules and ML "Indicator Lights" on the UBA dashboard do not display a successful connection:

The Machine Learning container fails to upgrade and is running on the version prior to the upgrade.

The poll service within the UBA app container constantly restarts.

Cause

Administrators who experience this application upgrade issue likely used the Clear UBA Data Administrative function, then attempted to upgrade their UBA version to 4.1.0.

Click the Admin tab.
Scroll to Apps > User Analytics > Help and Support.

Results
The Clear UBA Data Administrative function, removes entries from a specific UBA database table which prevents the database migration from running during the upgrade. Administrators who cleared UBA data prior to a UBA 4.1.0 app update can experience the symptoms described in this technical note.

Environment

This issue can occur on the following versions:

QRadar V7.3.3 Fix Pack 6 and later, installed with UBA 4.1.0.
QRadar V7.4.1 Fix Pack 2 and later, installed with UBA 4.1.0.

Diagnosing The Problem

On the App Host or Console running the apps, use the recon command to determine the UBA app ID.
```
#/opt/qradar/support/recon ps
```
Connect to the UBA container by using the App ID obtained from the recon ps command.
```
#/opt/qradar/support/recon connect <app_id>
```

After connecting to the UBA container, review the supervisord.log for UndefinedColumn errors.

tail -n30 /opt/app-root/store/log/supervisord.log

2021-04-16 10:39:34,521 INFO success: psql entered RUNNING state, process has stayed up for 
> than 1 seconds (startsecs)
/usr/lib64/python3.6/runpy.py:125: RuntimeWarning: 'app.poll.poll' found in sys.modules after 
import of package 'app.poll', 
but prior to execution of 'app.poll.poll'; this may result in unpredictable behaviour
  warn(RuntimeWarning(msg))
Traceback (most recent call last):
  File "/usr/lib64/python3.6/runpy.py", line 193, in _run_module_as_main
    "__main__", mod_spec)
  File "/usr/lib64/python3.6/runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "/opt/app-root/app/poll/poll.py", line 276, in <module>
    main()
  File "/opt/app-root/app/poll/poll.py", line 262, in main
    user_import_service = start_user_import()
  File "/opt/app-root/app/poll/poll.py", line 100, in start_user_import
    user_import_service = UserImportService()
  File "/opt/app-root/app/poll/user_import/user_import_service.py", line 56, in __init__
    self.clear_status()
  File "/opt/app-root/app/poll/user_import/user_import_service.py", line 66, in clear_status
    all_imports = ImportTasks.fetch_all_tasks(get_db())
  File "/opt/app-root/app/core/dao/import_tasks.py", line 360, in fetch_all_tasks
    cursor.execute(fetch_all_query)
  File "/usr/local/lib64/python3.6/site-packages/psycopg2/extras.py", line 142, in execute
    return super(DictCursor, self).execute(query, vars)
psycopg2.errors.UndefinedColumn: column "last_process_custom_attr_date" does not exist
LINE 4: ..._sync_ref_table_date, last_sync_ref_table_errors, last_proce...
                                                             ^
2021-04-16 10:39:35,196 INFO exited: poll (exit status 1; not expected)

Administrators who use the following database query do not receive results that display a UBA version.
```
psql -U postgres -d uba -c 'select * from versions;'
```

Resolving The Problem

IBM Support, always recommends ensuring a recent backup of App data is present before any app upgrade. App data backups are always stored under /store/apps/backup on the console or App Host running the apps. For more information, see: backing up and restoring app data.

Note: This issue is scheduled to be resolved in an upcoming UBA software release.

Administrators on UBA 4.1.0 who experience these symptoms

On the App Host or Console running the apps, use the recon command to determine the UBA app ID.
```
#/opt/qradar/support/recon ps
```
Connect to the UBA container by using the app ID obtained from the recon ps command.
```
#/opt/qradar/support/recon connect <app_id>
```
After connecting to the UBA container, run the command displayed which inserts an entry in the versions table within the UBA database.
```
psql -U postgres -d uba -c "insert into versions (version) values ('3.8.0');"
```

Confirm the value has been updated in the table.

psql -U postgres -d uba -c "select * from versions;"
 id | version |         updated_on
----+---------+----------------------------
  2 | 3.8.0   | 2021-04-16 15:18:39.013583

Stop the flask and poll services within the UBA container.
```
 supervisorctl stop flask
 supervisorctl stop poll
```
Navigate to the /opt/app-root directory.
```
cd /opt/app-root
```

To run the database migration script within the application container, type:

python3 -m app.core.migrate_psql_new_tables

If successful, a 'Database migration is complete' is displayed to administrators:

[appuser@labtesting app-root]$ python3 -m app.core.migrate_psql_new_tables
Checking if database migration is needed...
Database migration is needed, beginning migration process...
Starting task: [Add UBA offenses table]
Finished task: [Add UBA offenses table]
Starting task: [Add generated_cases table]
Finished task: [Add generated_cases table]
Starting task: [Migrate timeline_config table]
Finished task: [Migrate timeline_config table]
Starting task: [Update for new peer group reference sets]
Finished task: [Update for new peer group reference sets]
Reindex of tables completed successfully
Starting task: [Update import_tasks table]
Finished task: [Update import_tasks table]
Starting task: [Add custom attributes table]
Finished task: [Add custom attributes table]
Starting task: [Add support for deleting users on import task removal]
Finished task: [Add support for deleting users on import task removal]
Starting task: [Migrate ML module install path]
Finished task: [Migrate ML module install path]
Database migration is complete.

Review the versions table includes the initial and upgraded UBA versions. For example, if you were upgrading from UBA 3.8.0 to 4.1.0, both versions are expected in the table after you run the 'app.core.migreate_psql_new_tables' command.
```
 psql -U postgres -d uba -c "select * from versions;"
 id | version |         updated_on
----+---------+----------------------------
  2 | 3.8.0   | 2021-04-16 15:18:39.013583
  3 | 4.1.0   | 2021-04-16 15:23:52.376909
(2 rows)
```
If errors are reported when you run the 'app.core.migreate_psql_new_tables' command, this can indicate you are missing a UBA version. Type the following commands:
- ```
psql -U postgres -d uba -c "insert into versions (version) values ('4.0.0');"
```
- ```
python3 -m app.core.migrate_psql_new_tables
```

If errors continue to report when migrating the PSQL database, type the following command to add UBA 4.1.0:

psql -U postgres -d uba -c "insert into versions (version) values ('4.1.0');"

python3 -m app.core.migrate_psql_new_tables

If no errors are reported, type the following command to start the application:
- ```
supervisorctl start flask
```
- ```
supervisorctl start  poll
```

After several minutes, the UBA poll.log file indicates maching learning is upgrading. Administrators can tail the logs to confirm the app upgraded successfully.

tail -f /opt/app-root/store/log/poll.log
2021-04-16 11:34:23,555 [ml_app_def_upgrade.run] [INFO] - ml_app_upgrade: ML app def found - starting upgrade process
2021-04-16 11:34:23,567 [ml_app_def_upgrade.run] [INFO] - ml_app_upgrade: ML app upgrade is available, modified time: 1618583753, version 3.8.0 -> 4.1.0
.
.
.
2021-04-16 11:34:40,270 [task.run_task] [INFO] - DONE task "Check - ML app installed and running", after 12.18 sec.

How to avoid this issue before you upgrade to UBA 4.1.0

Procedure

Use SSH to log in to the QRadar Console.
Optional. If UBA is running on an App Host appliance, open an SSH session to the App Host.
To locate the UBA app ID, type:
```
#/opt/qradar/support/recon ps
```
Connect to the UBA container with the app ID obtained from the the 'recon ps' command.
```
#/opt/qradar/support/recon connect <app_id>
```
Check the versions table within the UBA database to see if it has entries or not. If the query returns results, administrators can safely upgrade UBA to 4.1.0.
```
psql -U postgres -d uba -c "select * from versions;"
```
If the previous query returns no results, administrators should use this command to insert the version in the UBA database.
```
psql -U postgres -d uba -c "insert into versions (version) values ('3.8.0');"
```

Results
The psql command ensures that the database migration script runs when UBA 4.1.0 is installed.

Document Location

Worldwide

[{"Type":"SW","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSBQAC","label":"IBM Security QRadar SIEM"},"ARM Category":[{"code":"a8m0z000000cwt3AAA","label":"QRadar Apps"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"7.4.2"}]