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

*NOTE: Please make sure a full database backup is taken before executing the given steps*

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, e.g:
```
The duplicate key value is (36affad1-0525-11ec-80c0-000c292e39fb-6daeef17).
```
Stop the App Control Server service

Open SQL Mgmt Studio and run the bellow script (update line 3 with the cookie value(s) from Step 1):

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
)

Please execute the query from Step 2 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.

Stop the App Control Server Service

Run the 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
)

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