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

Document ID : KB000009565
Last Modified Date : 05/03/2018
Show Technical Document Details
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 (ITCM) -- any version.
Instructions:
Step 1: Create a new query for Computers:
 
1.png
 
 
Step 2: Choose "Advanced Argument" from the query designer:
 
2.png
 
 
Step 3: Fill in the advanced argument details:
 
3.png
 
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.
 
4.png
 
 
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.
5.png
 
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".
 
6.png7.png
 
 
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