QRadar: User Behavior Analytics user import does not complete coalescing

Troubleshooting

Problem

User Behavior Analytics (UBA) user import takes a long time to coalesce or never completes.

Symptom

UBA user import completed the import but user coalescing never completed. UBA user import runs continuously and never shows as completed in the GUI. Last poll status displays Coalescing. Customer is unable to use the UBA application.

Cause

UBA taking long to coalesce due to customer's data where users have the identical value in the aliases or attributes that are used for coalescing.

Environment

Any QRadar and UBA version. Issue is with customers data in their Active Directory (AD).

Diagnosing The Problem

Determine how many users have the same value in the aliases or attributes used for coalescing. These steps show you how to investigate the user data inside the app container and confirm whether there are duplicate values.

SSH into the QRadar console.
Run the qappmanager by entering the following:
```
/opt/qradar/support/qappmanager
```
Find the app_id_instance of User Analytics under APP INSTANCES section in IID column. It is highlighted in green in the following example:
From the Console (or App host, if that is where your app is) connect to the app container by using recon. 2655 must be replaced in the following command with your app ID:
```
/opt/qradar/support/recon connect 2655
```

Run the following command for each of the user coalescing aliases or attributes after replacing "<alias>%" with full or partial alias or attribute name. Examples include "account%", "email%", and "alt%".

psql -U postgres -d uba -c "select count(*), attr_value from imported_attributes where attr_name ilike '<alias>%' group by attr_value having count(*) > 2;"

Result
Example output:

psql -U postgres -d uba -c "select count(*), attr_value from imported_attributes where attr_name ilike 'account%' group by attr_value having count(*) > 2;"
 count | attr_value 
-------+------------
 53090 | None
(1 row)

psql -U postgres -d uba -c "select count(*), attr_value from imported_attributes where attr_name ilike 'email%' group by attr_value having count(*) > 2;"
 count | attr_value 
-------+------------
 877 | None
(1 row)

psql -U postgres -d uba -c "select count(*), attr_value from imported_attributes where attr_name ilike 'alt%' group by attr_value having count(*) > 2;"
 count | attr_value 
-------+------------
 41 | None
(1 row)

In this case, the Active Directory data contained the same value "None" for all employees who left the company in the 3 aliases or attributes (account - 53K, email - 877, alternateaccount - 41) they used for coalescing. See the following example for employee "John Smith" "account - None":

psql -U postgres -d uba -c "select *from imported_attributes where imported_entity_id = 2;"
id | attr_name | attr_value | imported_entity_id
----+-----------------+--------------------------+--------------------
17 | account | None | 2
18 | country | United States | 2
19 | department | None | 2
20 | departuredate | 2020-03-12 00:00:00 | 2
21 | displayname | None | 2
22 | domain | None | 2
23 | email | 10001003@FEADremoved.com | 2
24 | employeeid | 10001003 | 2
25 | firstname | None | 2
26 | hiredate | 1966-11-16 00:00:00 | 2
27 | lastname | None | 2
28 | name | John Smith | 2
29 | status | None | 2
30 | terminationdate | 2020-03-31 00:00:00 | 2
31 | title | Programmer Analyst, Sr | 2
(15 rows)

Resolving The Problem

Before you start
Confirm that in your user data:

Alias or attribute exists for each user
Content of aliases or attributes selected are either unique or blank

The Diagnosing The Problem can help you identify the duplicate values.

Steps
Clear the data for the aliases and attributes in QRadar so it can be reset with your corrected user data.

Log in to the QRadar UI.
Open the UBA tab.
Click the User Import icon in the top menu bar on the UBA dashboard.
Delete the current data in User Import by clicking Trash Bin icon next to Pencil icon
Highlight the Delete the configuration and users option, then click Confirm.
On the User Import window, click the Tuning button.
Under User coalescing > Aliases section, click Edit.
Select the aliases or attributes for coalescing.
Click Save.

If you remove an alias or attribute, QRadar detects the change and starts the polling again. But if it does not, you must do a manual poll.

Result
You are now free to import your corrected data.

Optionally, you can verify the user_import_service.log to confirm the amount of time UBA is taking to coalesce. To verify the log, you must connect to the User Analytics container as explained in the Diagnosing The Problem.

Click Import data now.

Run the following commands to verify the log file while coalescing is executing:

cd /opt/app-root/store/log
tail -f user_import_service.log

Result
Example output:

2022-06-17 12:42:48,491 [reference_table_import.process] [INFO] - API poll to reference table took 5.399115085601807 seconds

2022-06-17 12:42:48,890 [reference_table_import.process] [INFO] - Total Number of users ingested from reference table: 117994, 0 is saved

2022-06-17 12:42:48,890 [reference_table_import.process] [INFO] - Ingest reference table processed 117994 identities in 351.48103046417236 secs

2022-06-17 12:42:48,890 [reference_table_import.process] [INFO] - Average ref table poll time: 5.540596897319212 secs

2022-06-17 12:42:48,890 [reference_table_import.process] [INFO] - Average db operation time:  0.36145754587852347 secs

2022-06-17 12:42:48,897 [import_users.run] [INFO] - User polling task <LDAP instancename> finished in 351.5439739227295 seconds, 117994 records were seen, 0 records saved

Related Information

Tuning user import configurations

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"","label":""},"Product":{"code":"SSSJES","label":"IBM QRadar User Behavior Analytics"},"ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Tips