How to Create Dynamic Groups for Identifying Duplicate Computers in DSM Explorer.

Products

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

Issue/Introduction

1- Demonstrate how to import a SQL query, as an advanced argument, into the DSM Query Designer, for creating custom asset groups.

2- Demonstrate how to create asset groups for identifying duplicate assets in the database, by hostname, serial number, or both.

Environment

Client Automation - All versions

Resolution

Step 1: Create a new query for Computers:

Step 2: Choose "Advanced Argument" from the query designer:

Step 3: Fill in the advanced argument details:

Pseudo text

Enter text to identify what the query does. This is only a label field, and helpful on the previous screen for managing multiple arguments of your DSM query.

Table:

Be sure to change to "-Any-".  As the query we are importing intends to return a list of computers, this informs DSM to expect a dis_hw_uuid (or equivalent) as the return from your custom query.

Additional WHERE clause:

This is where you provide your custom query, which returns the dis_hw_uuid field back to DSM.

Important Note: Your query must be formatted without any carriage returns or line breaks.

Here are some examples of queries you can paste into the "additional where clause", for identifying duplicate computers by hostname, serial number, or both.

Duplicate Computers by Hostname

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)

Duplicate Computers by Serial Number

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)

Duplicate Computers by Hostname and Serial Number

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

The next three queries return a subset of results from each of the above queries. They exploit the last_run_date column from ca_agent, to remove the newest agent record from each group of duplicates. The end result is a list containing only the "older" duplicates. These will help you automate the cleanup of older duplicates, so you don't have to compare last run dates to choose the older asset.

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

Duplicate Computers by Serial Number 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.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

Duplicate Computers by Hostname and Serial Number 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.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

Step 4: Preview query results, and save.

Step 5: Repeat the process if you wish to add more of the sample queries for identifying duplicate computers.

Step 6: Create a new dynamic groups for each query.

 

It's always a best practice to have a designated engine for query evaluations.  Do not use "All Engines" or the "SystemEngine", as too many query evaluations can impede other tasks assigned to those engines.  Also be sure to set a realistic evaluation period for the query. In most cases, once per day is fine.  Dynamic groups can also be manually evaluated, on demand, by right clicking on the newly created group, and selecting, "Evaluate now".

 

Reference SQL Queries for Duplicate Computers:

-- Duplicate Computers by Hostname

select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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.host_name, ca.last_run_date

-- Duplicate Computers by Serial Number

select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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

-- Duplicate Computers by Hostname and Serial Number

select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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

-- Duplicate Computers by Hostname with Older Last Run Date
select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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

 -- Duplicate Computers by Serial Number with Older Last Run Date

select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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

 -- Duplicate Computers by Hostname and Serial Number with Older Last Run Date
select dh.host_name as 'Hostname',
dh.serial_number as 'Serial Number',
dateadd(s,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