Database Customization: Filter duplicate connection addresses from the Remote Control Viewer.

Document ID : KB000045993
Last Modified Date : 02/03/2018
Show Technical Document Details
Issue:
This document covers a specific request that will not be common to all Client Automation installations.  Using DSM Explorer or the RC Viewer, there may be two computers listing the same IP or ENC IP address in the connection list.  When attempting to take control of one of the PCs, the connection may unexpectedly go to the incorrect/undesired PC.
 
This document will share a database customization that may be used on the domain manager's MDB to prevent duplication of connection addresses when an IP or ENC IP address is reassigned to another PC.
Environment:
Client Automation (ITCM) -- any version.
Cause:
This document was written for a specific issue for an administrator managing ENC connected devices across multiple customer networks.  Each of the ENC connected devices have the same hostname, and potentially the same FQDN, depending on the customer network in which the device resides.  Here's the timeline of events that explains the problem...
 
- Machine A receives ENC IP address 123.
- Machine B receives ENC IP address 456.
- Machine A disconnected from the ENC network, freeing up 123.
- Machine B disconnects and reconnects from the ENC network, and gets assigned 123, the previous address for Machine A.
- In the RC Viewer, both machines list ENC IP of 123 in their connection address lists.
- Taking control of Machine A results in a connection to Machine B.
 
In this example, Machine A is offline from the ENC network, thus never receives a new ENC IP address, leaving the existing ENC IP address stale in the database.  Had Machine A come online and received a new address, the database would be properly updated.
Resolution:
The customization provided is not specific to ENC connection addresses, and can be used for any network address duplication.  To implement the customization, we will modify/alter an existing remote control trigger:
 
USE [mdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER trigger [dbo].[r_i_urc_computer_addr2]
on [dbo].[urc_computer_addr]
    after insert
as
begin
    declare @computer_uid binary(16);
    declare @domain_uuid binary(16);
    declare @new_protocol int;
    declare @new_flags int;
    declare @new_straddress nvarchar(500);
    declare @own_domain_uuid binary(16);
    declare @nTierDomainType int;

    declare cursor_urc_computer_addr cursor local for
        select computer_uuid,domain_uuid,protocol,flags,straddress from inserted;

    open cursor_urc_computer_addr;
    fetch next from cursor_urc_computer_addr into @computer_uid, @domain_uuid, @new_protocol, @new_flags, @new_straddress; -- get first
    while @@fetch_status = 0
    begin
       
        /* Get my Domain UUID */
        set @own_domain_uuid = (select set_val_uuid from ca_settings where set_id=1);

        /* Check my Domain Type */
        set @nTierDomainType = (select domain_type from ca_n_tier where domain_uuid = @own_domain_uuid)

        /* This Operation only applies to Domains */
        if (@nTierDomainType = 0)
        begin
           
            /* Remove Obsolete Records */
            delete from urc_computer_addr where computer_uuid not in (select object_uuid from ca_agent) and domain_uuid=@own_domain_uuid
           
            /* Remove Duplicate Records */
            delete from urc_computer_addr where strAddress=@new_straddress and domain_uuid=@own_domain_uuid and computer_uuid <> @computer_uid

        end;
       
        /* Original Trigger Operation */
        execute p_urc_computer_addr_inserted @computer_uid,@domain_uuid, @new_protocol, @new_flags, @new_straddress;
        fetch next from cursor_urc_computer_addr into @computer_uid, @domain_uuid, @new_protocol, @new_flags, @new_straddress; -- get next
    end; -- while

    close cursor_urc_computer_addr;
    deallocate cursor_urc_computer_addr;

end
GO


This modified trigger will check connection addresses immediately after they are inserted into the database table.  If will verify the database belongs to a domain manager, and do the following:
 
1. Delete orphaned connection addresses that don't belong to any registered agent.
2. Delete any other connection address that is linked to a computer other than the one being inserted.
Additional Information:
The reason this solution is classified as a customization is because it may not apply to all Client Automation customers.  Some are service provides who may have clients across multiple customer networks, and machines having the same network IP address.  Therefore the duplication may be valid, as the address is valid to an RC Viewer user within a smaller subset of the larger organization.