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

[#1]

Environment

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

Symptoms

• 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.

Cause

• Duplicated cookie id for endpoint(s)

Resolution

*NOTE: Please 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
  )

5. Please execute the query from Step 2 again for each duplicate cookie
6. Start the App Control Server service

Additional Notes

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

 

Related Content

App Control: How to take a full backup of the DAS database
App Control: Collecting Historical Logs for Server Troubleshooting (Locally)