How to create DSM Explorer Queries to find Agents with Duplicate Host Names or IP Addresses

Document ID : KB000028829
Last Modified Date : 06/11/2018
Show Technical Document Details
Introduction:

How to create  DSM Explorer Queries to find Agents for the following:

  • Duplicate host names
  • Duplicate IP addresses
  • Duplicate Host names without the one with earlier "last run date"

 

Environment:
CA Client Automation - All Versions
Instructions:
Create these Queries in DSM Explorer using Advanced Argument Queries and then create Dynamic Groups based on these Queries.  
Right-click on Queries and select New
Select Computers from the select Target window
Select Advanced Argument from the Query Designer Window:
query1.png


1. For Duplicate Host names :
   Pseudo text:                               hostname
   Table:                                          -Any-
   Additional WHERE clause:         
SELECT dis_hw_uuid FROM ca_discovered_hardware WHERE host_name IN (SELECT host_name FROM ca_discovered_hardware AS ca_discovered_hardware_1 GROUP BY domain_uuid, host_name HAVING (COUNT(host_name) > 1))
 (**** The Additional WHERE clause has to be copied in from one line, 2 or more lines will add an EOL character and cause Query to fail ****)


2. For Duplicate Host IP Address :
   Pseudo text:                               IPAddress
   Table:                                          -Any-
   Additional WHERE clause:         
SELECT dis_hw_uuid FROM ca_discovered_hardware WHERE dis_hw_uuid IN (SELECT object_uuid FROM ca_agent WHERE ip_address IN ( SELECT  ip_address FROM ca_agent AS ca_agent_1 WHERE agent_type =1  GROUP BY ip_address HAVING (COUNT(ip_address) > 1)))
(**** The Additional WHERE clause has to be copied in from one line, 2 or more lines will add an EOL character and cause Query to fail ****)



3. For Duplicate Host names without the one with earlier "last run date"

   Pseudo text:                               Older Duplicate Hostname 
   Table:                                          -Any-
   Additional WHERE clause:         
SELECT object_uuid FROM ca_agent a WHERE a.agent_type=1 and a.agent_name in (SELECT agent_name FROM ca_agent WHERE agent_type=1 GROUP BY domain_id, agent_name HAVING (COUNT(agent_name) > 1)) and a.object_uuid <> (SELECT TOP 1 b.object_uuid FROM ca_agent b WHERE a.agent_name=b.agent_name and b.agent_type=1 ORDER BY b.last_run_date DESC )
(**** The Additional WHERE clause has to be copied in from one line, 2 or more lines will add an EOL character and cause Query to fail ****)




Then click  OK
Save and name the Query


Now you can create Groups, based off those Queries.