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

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

book

Article ID: 290783

calendar_today

Updated On:

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

*NOTEPlease make sure a full database backup is taken before executing the given steps*

  1. 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).
  2. Stop the App Control Server service
  3. 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
  4. 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
    )
  1. Please execute the query from Step 2 again for each duplicate cookie
  2. 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