Duplicate Hostname, possibility to delete agent records in All Computers automatically?

[Carlos Mora]

Hello.

Best regards to all.

 

We have a list of approximately 4500 stations with 3 scalability servers, however, there are computers with duplicate hostname.

When we see the agent's last execution date, we validate that these stations are possibly no longer active (out of inventory), however, we need to know if there is a possibility that the tool (DSM Explorer) automatically performs the process of removing the record of this station in inventory, since there are currently 317 computers per hostname duplicated, that is, approximately 18 computers have the information correctly. But it is required in some way to eliminate the others.

Thanks for your possible answers.

Best regards.

[Brian_Fontana]

Step 1: Use this KB to create a query for duplicate by host name:

 

How to Create Dynamic Groups for Identifying Dupli - CA Knowledge 

 

Step 2: Create a query-based policy, using the query from above, and add an action to the query-based policy to delete the asset.

[Brian_Fontana]

From the above KB doc, it sounds like this is the query you will want to be using:

Duplicate Computers by Hostname with Older Last Run Date

select dh.dis_hw_uuid from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (select v.host_name, max(u.last_run_date) as last_run from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.host_name having count(*) > 1) as Keepers on dh.host_name=Keepers.host_name and ca.last_run_date=Keepers.last_run inner join (select v.host_name from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.host_name having count(*) > 1) as Duplicates on dh.host_name=Duplicates.host_name where Keepers.host_name is null and IsNull(ca.agent_type,1)=1

 

This query will identify only the "older" records for each duplicate, based on last run date.  Thus instead of deleting all duplicates, it allows you to retain the one having the newest "last run date".

[Carlos Mora]

Hello Brian

Thanks for your answers

Indeed, the query brings duplicate hostnames with the agent's last execution date, however, in this query 4 computers are brought that the agent has currently executed (for example, the agent executed today).

 

Is this normal?

As a final question, there is an example or procedure that explains how to create the policy based on the query to automatically remove the "obsolete" agents.

Thanks for your comments Brian.

Best regards.

[Legacy User]

Hi, Carlos.

 

check this 

Automating the Removal of Duplicate Assets:

 

Header 1

Duplicate Computer Automation   Purpose: Identify raw SQL queries that can be used to identify duplicate computers based on varying criteria. Provide steps to import the SQL queries into ITCM, for the purpose of creating dynamic groups to monitor on duplication of computers. Provide automation for removing duplicate computer assets, based on the monitoring groups.   SQL Queries for Identifying Duplicate Computers:   Query #1: All duplicates, based on serial number. Query #2: All duplicates, based on host name. Query #3: All duplicates, based on serial number & host name matches. Query #4: Older duplicates, based on serial number. Query #5: Older duplicates, based on host name. Query #6: Older duplicates, based on serial number & host name matches.   Note: The “older duplicates” queries use the last report date of each computer to determine which asset is the newest in any n-tuple of duplicate assets. The results returned will be all duplicates, less the asset having the most recent last report date.   -- ALL Duplicates by Serial Number Only select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and agent_comp_id=5 where dh.serial_number in (        select distinct(serial_number)        from ca_discovered_hardware        group by serial_number        having count(*)>1) order by dh.serial_number, ca.last_run_date     -- ALL Duplicates by Host Name Only select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and ac.agent_comp_id=5 left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid where dh.host_name in (        select distinct(host_name)        from ca_discovered_hardware        group by host_name        having count(*)>1) order by dh.serial_number, ca.last_run_date     -- ALL Duplicates by Host Name & Serial Number Matches select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh inner join (        select host_name, serial_number        from ca_discovered_hardware        group by host_name, serial_number        having count(*) > 1) as Duplicates on dh.host_name=Duplicates.host_name and dh.serial_number=Duplicates.serial_number left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and ac.agent_comp_id=5 left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid order by dh.serial_number, dh.host_name, ca.last_run_date       -- OLDER Duplicates to Delete based on Serial Number ONLY select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (        select v.serial_number,        max(u.last_run_date) as last_run        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.serial_number        having count(serial_number) > 1) as Keepers on dh.serial_number=Keepers.serial_number and ca.last_run_date=Keepers.last_run inner join (        select v.serial_number        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.serial_number        having count(serial_number) > 1) as Duplicates on dh.serial_number=Duplicates.serial_number left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and ac.agent_comp_id=5 left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid where Keepers.serial_number is null and IsNull(ca.agent_type,1)=1 order by dh.serial_number, dh.host_name, ca.last_run_date     -- OLDER Duplicates to Delete based on Host Name Only select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (        select v.host_name,        max(u.last_run_date) as last_run        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.host_name        having count(*) > 1) as Keepers on dh.host_name=Keepers.host_name and ca.last_run_date=Keepers.last_run inner join (        select v.host_name        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.host_name        having count(*) > 1) as Duplicates on dh.host_name=Duplicates.host_name left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and ac.agent_comp_id=5 left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid where Keepers.host_name is null and IsNull(ca.agent_type,1)=1 order by dh.host_name, ca.last_run_date     -- OLDER Duplicates to Delete based on Host Name & Serial Number Matches select dh.dis_hw_uuid, dh.asset_source_uuid, dh.host_uuid, dh.host_name, dh.label, dh.serial_number, dh.disc_serial_number, dh.primary_mac_address, dh.asset_tag, dh.system_id, dateadd(second,ca.last_run_date,'1970-01-01') as last_run_date, ac.agent_component_version as agent_version, nt.label as source_domain from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (        select v.serial_number,        v.host_name,        max(u.last_run_date) as last_run        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.serial_number, v.host_name        having count(*) > 1) as Keepers on dh.serial_number=Keepers.serial_number and dh.host_name=Keepers.host_name and ca.last_run_date=Keepers.last_run inner join (        select v.serial_number,        v.host_name        from ca_agent u        left outer join ca_discovered_hardware v        on u.object_uuid=v.dis_hw_uuid        where u.agent_type=1        group by v.serial_number, v.host_name        having count(*) > 1) as Duplicates on dh.serial_number=Duplicates.serial_number and dh.host_name=Duplicates.host_name left outer join ca_agent_component ac on ca.object_uuid=ac.object_uuid and ac.agent_comp_id=5 left outer join ca_n_tier nt on dh.domain_uuid=nt.domain_uuid where Keepers.serial_number is null and IsNull(ca.agent_type,1)=1 order by dh.serial_number, dh.host_name, ca.last_run_date       Monitoring Duplication in DSM Explorer:   Open DSM Explorer and navigate to Queries -> My Queries.   Create a new query and select, “Computers” as the target return type.   Select, “Advanced Argument”, wait for “add argument” window to load.   Enter the following information depending on the query you want to register:   Important: When entering the “additional where clause”, the contents of the query MUST be entered without line breaks. Essentially the entire query should be pasted as a single line, unless the query designer itself word wraps the text. If you attempt to break up the query onto multiple lines, you will receive an error when attempting to run the query later.   ALL Duplicates by Serial Number Only Pseudo Text: ALL Duplicates by Serial Number Only Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh where dh.serial_number in (select distinct(serial_number) from ca_discovered_hardware group by serial_number having count(*)>1)     ALL Duplicates by Host Name Only Pseudo Text: ALL Duplicates by Host Name Only Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh where dh.host_name in (select distinct(host_name) from ca_discovered_hardware group by host_name having count(*)>1)     ALL Duplicates by Host Name & Serial Number Matches Pseudo Text: ALL Duplicates by Host Name & Serial Number Matches Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh inner join (select host_name, serial_number from ca_discovered_hardware group by host_name, serial_number having count(*) > 1) as Duplicates on dh.host_name=Duplicates.host_name and dh.serial_number=Duplicates.serial_number     OLDER Duplicates to delete based on Serial Number ONLY Pseudo Text: OLDER Duplicates to delete based on Serial Number ONLY Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (select v.serial_number, max(u.last_run_date) as last_run from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.serial_number having count(serial_number) > 1) as Keepers on dh.serial_number=Keepers.serial_number and ca.last_run_date=Keepers.last_run inner join (select v.serial_number from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.serial_number having count(serial_number) > 1) as Duplicates on dh.serial_number=Duplicates.serial_number where Keepers.serial_number is null and IsNull(ca.agent_type,1)=1                OLDER Duplicates to delete based on Host Name Only Pseudo Text: OLDER Duplicates to delete based on Host Name Only Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (select v.host_name, max(u.last_run_date) as last_run from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.host_name having count(*) > 1) as Keepers on dh.host_name=Keepers.host_name and ca.last_run_date=Keepers.last_run inner join (select v.host_name from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.host_name having count(*) > 1) as Duplicates on dh.host_name=Duplicates.host_name where Keepers.host_name is null and IsNull(ca.agent_type,1)=1     OLDER Duplicates to delete based on Host Name & Serial Number Matches Pseudo Text: OLDER Duplicates to delete based on Host Name & Serial Number Matches Table: -Any- Additional WHERE clause:   select dh.dis_hw_uuid from ca_discovered_hardware dh left outer join ca_agent ca on dh.dis_hw_uuid=ca.object_uuid left outer join (select v.serial_number, v.host_name, max(u.last_run_date) as last_run from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.serial_number, v.host_name having count(*) > 1) as Keepers on dh.serial_number=Keepers.serial_number and dh.host_name=Keepers.host_name and ca.last_run_date=Keepers.last_run inner join (select v.serial_number, v.host_name from ca_agent u left outer join ca_discovered_hardware v on u.object_uuid=v.dis_hw_uuid where u.agent_type=1 group by v.serial_number, v.host_name having count(*) > 1) as Duplicates on dh.serial_number=Duplicates.serial_number and dh.host_name=Duplicates.host_name where Keepers.serial_number is null and IsNull(ca.agent_type,1)=1   After completing the “add argument”, press ‘OK’ to return to the query designer. Select, “preview” in the designer tree to test the query. If there is a problem with how the query was entered, you will receive an error from the query designer.   Save the query with a meaningful name. For example, “All duplicate assets by serial number only” or “older duplicate assets based on serial number only”.   After creating the queries, now you can create dynamic computer groups to monitor the duplication of assets.   Notes:   Be mindful of the evaluation engine and period when creating the dynamic group. In larger environments, it’s favorable to have an engine dedicated towards query evaluations, rather than choosing the “All Engines” option.   Also, after initially creating the groups, you will need to right click and select, “evaluate now” from the context menu to see a list of duplicate assets immediately after dynamic group creation.   Once the dynamic groups are created, you can manually start to cleanup duplicate computers from the database. If you would like to automate this process, follow the steps in the next section to setup an ITCM engine task that activates a custom stored procedure in the database for deleting these computers.     Automating the Removal of Duplicate Assets:   The automation of removing duplicate computers builds upon the previous section above on monitoring for duplication. The steps below will guide you through the process of creating a database stored procedure, executed by an ITCM engine task, for automatically deleting assets that are members to the duplicate computer group.   Using the steps in the previous section, select one of the following queries to identify the proper set of duplicates you wish to automate the deletion.   Older duplicates based on serial number matches only. Older duplicates based on host name matches only. Older duplicates based on host name & serial number matches.   After making a choice, register the appropriate query in ITCM and create a dynamic group based on the query. You will need to make note of the name of your dynamic group for use later in this procedure.     Open SQL management studio and connect to your domain manager SQL server.   Important: Computers, duplicate or otherwise, cannot be deleted directly from an enterprise manager. All assets need to be deleted from the domain manager level, and deletions will get replicated to the enterprise manager. Hence, this automation should not be present on the enterprise manager.   Additional Note: While the automation of removing duplicates should not be placed on the enterprise manager, you may register the queries and dynamic groups with the enterprise manager to automate the distribution process of the queries and groups to each domain manager. This will circumvent the process of registering the queries on each domain manager as replication can create them automatically on each domain manager for you.     In SQL management studio, expand Databases -> mdb. Right click on ‘mdb’ and select “New Query” from the context menu.     If you are replacing a previous version of this stored procedure, you need to execute the following in the query window before continuing:   drop procedure dbo.ca_support_delete_duplicate_computers         Copy and paste the following SQL script into the query window:   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   CREATE PROCEDURE dbo.ca_support_delete_duplicate_computers   AS BEGIN   delete from usd_applic where target in (   select member_uuid from ca_group_member where group_uuid in (     select group_uuid from ca_group_def where label='DuplicateGroup')   and member_uuid not in (select dis_hw_uuid from ca_server)   and member_uuid not in (select user_uuid from ca_link_dis_user_sec_profile)   and (member_type=1 or member_type=4))   declare @uuid int declare uid cursor for   select member_uuid from ca_group_member where group_uuid in (   select group_uuid from ca_group_def where label='DuplicateGroup') and member_uuid not in (select dis_hw_uuid from ca_server) and member_uuid not in (select user_uuid from ca_link_dis_user_sec_profile) and (member_type=1 or member_type=2 or member_type=4)   open uid fetch next from uid into @uuid while @@FETCH_STATUS=0 begin   delete from ca_group_member where member_uuid=@uuid delete from ca_agent where object_uuid=@uuid delete from ca_link_dis_hw_user where dis_hw_uuid=@uuid delete from ca_link_dis_hw_user where user_uuid=@uuid delete from ca_discovered_user where user_uuid=@uuid delete from ca_discovered_hardware where dis_hw_uuid=@uuid   fetch next from uid into @uuid end close uid deallocate uid   update usd_applic set activity = 0x00000000000000000000000000000000 where activity <> 0x00000000000000000000000000000000 and activity not in (select objectid from usd_activity) delete from usd_applic where target in (select target from usd_applic where target not in (select object_uuid from ca_agent)) delete from usd_jcappgr where jobtarget in (select jobtarget from usd_jcappgr where jobtarget not in (select object_uuid from ca_agent)) delete from usd_link_act_cmp where comp not in (select object_uuid from ca_agent) delete from usd_link_grp_cmp where comp not in (select object_uuid from ca_agent) delete from usd_target where objectid not in (select object_uuid from ca_agent) delete from ca_agent where agent_type=2 and object_uuid not in (select user_uuid from ca_link_dis_hw_user) and object_uuid not in (select user_uuid from ca_link_dis_user_sec_profile) delete from ca_agent where agent_type=4 and object_uuid not in (select link_dis_hw_user_uuid from ca_link_dis_hw_user) delete from ca_link_dis_hw_user where dis_hw_uuid not in (select object_uuid from ca_agent) and dis_hw_uuid not in (select dis_hw_uuid from ca_discovered_hardware) delete from ca_link_dis_hw_user where user_uuid not in (select object_uuid from ca_agent) and user_uuid not in (select user_uuid from ca_discovered_user) and user_uuid not in (select user_uuid from ca_link_dis_user_sec_profile) delete from ca_group_member where member_uuid not in (select object_uuid from ca_agent) and member_uuid not in (select link_dis_hw_user_uuid from ca_link_dis_hw_user) and (member_type=1 or member_type=2 or member_type=4) delete from ca_discovered_user where user_uuid not in (select user_uuid from ca_link_dis_hw_user) and user_uuid not in (select user_uuid from ca_link_dis_user_sec_profile) and user_uuid not in (select object_uuid from ca_agent) delete from ca_discovered_hardware where dis_hw_uuid not in (select object_uuid from ca_agent) and dis_hw_uuid not in (select dis_hw_uuid from ca_server) and dis_hw_uuid not in (select dis_hw_uuid from ca_link_dis_hw_user)   END GO         Update the highlighted areas of the script, specifying exactly the name of the older duplicates computer group you wish all assets to be deleted.          Execute the query to create the stored procedure in the MDB.          Expand Databases -> mdb -> Programmability -> Stored Procedures. Right click on “dbo.ca_support_delete_duplicate_computers” and select “properties”.   Note: You will need to refresh the list of stored procedures on the tree before the new procedure will be available.          In the properties window, switch to the permissions view. Click the “Search” button. Type “ca_itrm” in the search and press ‘OK’.        In the explicit permission tab, put a check mark in the “Grant” column for the “Execute” permission. Press OK. This will give the ca_itrm account permission to execute the new stored procedure.          Open DSM Explorer. Expand Domain -> Control Panel -> Engines -> All Engines. Select an engine name that you would like to execute this task.          On the selected engine, right click and select, “Add new task..” from the context menu.           Select “SQL Script” for the task type and give the task a meaningful name. For example: “Delete Duplicate Computers”.             Enter the following for the SQL script text: exec dbo.ca_support_delete_duplicate_computers          Set the scheduling for the engine to launch the stored procedure as often as you like and click finish.          Upon execution of the engine task, all members of the duplicate computers group identified in the script will be deleted from ITCM.