CI relations with empty child is created as part of PrePost processing - Discovery patter - MSSql DB On Windows - Known Error

CI relations with empty child is created as part of PrePost processing - Discovery patter - MSSql DB On Windows Description Discovery of MSSql Instance through pattern "MSSql DB On Windows" leading to duplicate relationships with child as EMPTY. Symptoms like below:

1. Invalid relationships created with child as EMPTY.

2. Duplicate relationships getting created between Windows and MSSql instance.

3. Duplicate SW installs with empty installed on are getting created.

Steps to Reproduce

1. Execute deep discovery on one of the Virtual machines on Azure Cloud to discover the SQL Server installs. 2. Installs created successfully with created by application pattern = true by one of our SAM extension prepost script "". No issue here. 3. Re run deep discovery on the same VM and notice SQL install created with empty installed on and created by application pattern = true; This was happening because there were CI relation "Runs On: Runs" created with empty child data. These entries are created as part of the Pre Post processing script - sa_pattern_prepost_script_b52707fadb0080507ca83c8f9d961949.xml (Create relation between MSSQL Instance to Windows Cluster).

As part of the pattern script Runs on relation is created between MS SQL Instance and Windows Cluster. Here, win cluster is null and hence the child in CI relation is empty. Can we check why is the relation created when win cluster do not exists?

This is leading to duplicate installs and installs with emtpy installed on in SAM when we are processing the above data in our SAM Extension prepost script.

Attached screenshot of the Duplicate or invalid CI relation records with empty child CI.

Workaround As a workaround, modify the post-sensor script "Create relation between MSSQL Instance to Windows Cluster" so that it will check for undefined/empty child CI as below:

Replace the line:

createRelation(msSqlInstanceSysIds[i], parent_cluster_sys_id[0], "60bc4e22c0a8010e01f074cbe6bd73c3"); //Create relation between cmdb_ci_win_cluster to cmdb_ci_db_mssql_instance from type "Runs on::Run"

with the new code:

if (JSUtil.notNil(msSqlInstanceSysIds[i]) && JSUtil.notNil(parent_cluster_sys_id[0])) { createRelation(msSqlInstanceSysIds[i], parent_cluster_sys_id[0], "60bc4e22c0a8010e01f074cbe6bd73c3"); //Create relation between cmdb_ci_win_cluster to cmdb_ci_db_mssql_instance from type "Runs on::Run" }

or import the attached Create relation between MSSQL Instance to Windows Cluster - script XML .

See also PRB1390308/KB0868321: Under some circumstance Discovery/IRE creates orphaned relationships where the parent/child SYS id is null/empty/invalid/undefined .