How to link heuristic product and release definitions together in the database.

Document ID : KB000075033
Last Modified Date : 09/05/2018
Show Technical Document Details
Introduction:
How to link heuristic product and release definitions together in the database using SQL.
Background:
Software definitions are stored in the ITCM database using a parent-child relationship.  The parent definitions are known as "product" definitions.  Each product definition will have one or multiple "release" definitions linked to it.  When the AM agent does software discovery, it is specific software releases which are actually discovered.

A hypothetical example might be Notepad++
Versions of Notepad++ have three digits in the version number, e.g. 7.5.1.

The product definition may have 7.5, and each release may be something like 7.5.1, 7.5.2, etc...

Thus the product 7.5 is the parent definition, and the release 7.5.1 is the child definition.

Use this article as directed by CA Support, if there's ever an occasion that requires manual linkage of product-release definitions in SQL.

 
Environment:
Client Automation (ITCM) -- any version.
Instructions:
-- Identify unlinked heuristic (source_type_id=3) product (software_type_id=8) definitions
select * from ca_software_def where source_type_id=3 and software_type_id=8 and sw_def_uuid not in (select primary_sw_def_uuid from ca_link_sw_def)

-- Sample definition (should return two records)
-- Record 1: Heuristic product (source_type_id=3, software_type_id=8)
-- Record 2: Heuristic release (source_type_id=3, software_type_id=3)
select sw_def_uuid, name, sw_version_label, software_type_id, source_type_id from ca_software_def where source_type_id=3 and name='Notepad++ (32-bit x86)' and sw_version_label='7.5.1'
/*
0x323D49B4B3A9604286B67C41B8DD5B18 Notepad++ (32-bit x86) 7.5.1 8 3
0xFC4F63D03D2CFD44B3DB63180D869AFC Notepad++ (32-bit x86) 7.5.1 3 3
*/

-- Sample link record
select * from ca_link_sw_def where primary_sw_def_uuid=0x323D49B4B3A9604286B67C41B8DD5B18
/*
1505833387 0 0xFC4F63D03D2CFD44B3DB63180D869AFC 0x323D49B4B3A9604286B67C41B8DD5B18 3 3 0x00000000001989F7

RELEASE=secondary_sw_def_uuid
PRODUCT=primary_sw_def_uuid
*/

-- Sample insert (we don't fill auto_rep_version column, the dbms will so this for us)
/*
last_update_date= DATEDIFF(s, '19700101', GETUTCDATE()) [this is the current timestamp]
version_number= 0
secondary_sw_def_uuid= sw_def_uuid of the RELEASE (software_type_id=3)
primary_sw_def_uuid= sw_def_uuid of the PRODUCT (software_type_id=8)
link_type_id= 3 (heuristic link)
source_type_id= 3 (heuristic defs)
auto_rep_version --> don't specify column or value. SQL will fill this in for us!
*/
insert into ca_link_sw_def (last_update_date, version_number, secondary_sw_def_uuid, primary_sw_def_uuid, link_type_id, source_type_id)
values (DATEDIFF(s, '19700101', GETUTCDATE()), 0, 0xFC4F63D03D2CFD44B3DB63180D869AFC, 0x323D49B4B3A9604286B67C41B8DD5B18, 3, 3)