Disconnected Agents with Last Poll Date Shown in the Future (Violation of UNIQUE KEY)

Products

Carbon Black App Control (formerly Cb Protection)

Issue/Introduction

App control Agent is not able to communicate with the App Control Console.

Dascli status outputs shows:

Client Information
Connection: disconnected (not permitted)
or
Client Information 
Connection: connected (not permitted)

App Control Server logs shows messages like below

[533517] <date> <hour> (8304 Register Thread 0) Statement returned error [2627]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD5AEE82B9'. Cannot insert duplicate key in object 'dbo.hostmain'. The duplicate key value is (36affad1-0525-11ec-80c0-000c292e39fb-6daeef17).
Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
[533518] <date> <hour> (8304 Register Thread 0) Host <domainName>\<hostname> [-1] record created.
[533519] <date> <hour> (8304 Register Thread 0) Error creating host record for computer <domainName>\<hostname> from <IP address>.
[533520] <date> <hour> (8304 Register Thread 0) Error: Extra Result Set: 1, Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
[533521] <date> <hour> (8304 Register Thread 0) Error: Extra SQL Message: #2, Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]The 'RegisterHost' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead., Statement: SET NOCOUNT ON;{CALL dbo.RegisterHost (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
[533522] <date> <hour> (8304 Register Thread 0) SQL Connection was disconnected. Server: localhost
[533523] <date> <hour> (8304 Register Thread 0) Registration of <domainName>\<hostname> from <IP address> failed, returning error 401.

Environment

App Control Server: All Supported Versions
App Control Agent: All Supported Versions

Cause

Duplicated cookie id for endpoint(s)

Resolution

Upgrade the App Control Server to the latest available version - this issue only affects older server versions
- If, after upgrading the server, agents stay disconnected continue to Step 2
Get the duplicated cookie value(s) from the ServerLog.bt9 file, look for "The duplicate key value is" and copy the value found inside the parenthesis, example:
```
The duplicate key value is (36affad1-0525-11ec-80c0-000c292e39fb-6daeef17).
```
Temporarily stop the App Control Server service
Verify that there is a recent Database backup
Open SQL Server Management Studio as the Carbon Black Service Account

Adjust the following script (update line 4 with the cookie value(s) from Step 2):

USE das 
GO
SELECT host_id INTO #tmpDeleteHosts 
FROM dbo.hostmain (nolock) WHERE cookie = 'xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx' --UPDATE the cookie here

UPDATE dbo.config_list SET obsolete=1
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts)

DELETE FROM dbo.host_config_prop
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts) 

DELETE FROM dbo.hostmain 
OUTPUT deleted.host_id, deleted.template_host_id, deleted.deleted, deleted.cookie, deleted.host_group_id, deleted.host_group_is_automatic, deleted.hostname, deleted.display_hostname, deleted.bit9_version_kernel_major, deleted.bit9_version_kernel_minor, deleted.bit9_version_kernel_point, deleted.bit9_version_kernel_build, deleted.bit9_version_kernel_date, deleted.bit9_version_kernel_debug, deleted.ip_address, deleted.operating_system_id, deleted.register_date, deleted.delete_date, deleted.uninstall_date, deleted.users, deleted.mac_address, deleted.description, deleted.os_description, deleted.last_host_group_id, deleted.last_host_group_date, deleted.bit9_version_major, deleted.bit9_version_minor, deleted.bit9_version_point, deleted.bit9_version_build, deleted.bit9_version_debug, deleted.bit9_version_date, deleted.password_seed, deleted.cli_code, deleted.upgrade_state, deleted.host_flags, deleted.refresh_flags, deleted.debug_level, deleted.kernel_debug_level, deleted.debug_duration, deleted.debug_flags, deleted.cc_level, deleted.cc_flags, deleted.comment, deleted.virtual_platform, deleted.virtualized, GETUTCDATE(), deleted.cb_sensor_version, deleted.cb_sensor_flags, deleted.cb_sensor_id 
INTO dbo.hostmain_bak
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts)

DELETE FROM dbo.host_state 
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts) 

DELETE FROM dbo.host_metadata
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts) 

DELETE FROM dbo.duplicate_hosts 
WHERE host_id IN (SELECT host_id FROM #tmpDeleteHosts ) 

DELETE FROM dbo.created_template_lookup 
WHERE template_host_id IN (SELECT host_id FROM #tmpDeleteHosts ) 

DROP TABLE #tmpDeleteHosts

The following error could be displayed when deleting the cookies:

Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD186E4FA6'. Cannot insert duplicate key in object 'dbo.hostmain_bak'. The duplicate key value is (xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx).
The statement has been terminated.

If this happens please run the following query:

use das;
delete dbo.hostmain_bak where host_id in
(
select b.host_id from dbo.hostmain_bak as b
inner join dbo.hostmain a
on b.cookie = a.cookie
)

Execute the query from Step 4 again for each duplicate cookie
Start the App Control Server service

Additional Information

If the 'DailyPruneTask' fails with an error in the Reporter log that contains a message like the below, then please...

Msg 2627, Level 14, State 1, Procedure PruneDeletedHosts, Line 25 [Batch Start Line 2]
Violation of UNIQUE KEY constraint 'UQ__hostmain__4E88F7AD186E4FA6'. Cannot insert duplicate key in object 'dbo.hostmain_bak'. The duplicate key value is (xxxxxxxx-xxxx-xxxx-xxxxx-xxxxxxxxxxx-xxxxxxxx).
The statement has been terminated.

Temporarily stop the App Control Server service

Run SQL Server Management Studio as the Carbon Black Service account, and execute:

use das;
delete dbo.hostmain_bak where host_id in
(
select b.host_id from dbo.hostmain_bak as b
inner join dbo.hostmain a
on b.cookie = a.cookie
)

Confirm there are no more messages seen by running:
```
use das; exec dbo.PruneDeletedHosts
```
Start the App Control Server