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

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/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

Client Automation - All Versions

Resolution

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:

 

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.