How to remove duplicate relationships on each affected CI ?

[Community Alums]

Hi Everyone,

How can we restrict duplicate relationships on each affected CI and also  Prevention the creation of new duplicate relationships (Patches::Patched by).

Script Include has been written for this, still its effecting with duplicate CI's

Script Include :

var FA_CMDB_DupRelationshipHelper = Class.create();
FA_CMDB_DupRelationshipHelper.prototype = {
   

    validateCIRelationships: function(appNumber,ciSysID,ciDisplayName,trigger) {
        var errorReason = '';
        var grInserOrUpdRel = new GlideRecord('cmdb_rel_ci');
        var relType = gs.getProperty('cmdb.rel.runs_on_runs_type'); // Runs on relationship sys id
        var grApp = new GlideRecord("cmdb_ci_service_discovered");
        grApp.addEncodedQuery("number=" + appNumber);
        grApp.setLimit(1);
        grApp.query();
        if (grApp.next()) {
            var gaRel = new GlideAggregate('cmdb_rel_ci');
            gaRel.addEncodedQuery("child=" + ciSysID + "^type=" + relType + "^parent.sys_class_name=cmdb_ci_service_discovered"); // Query to check any relationships existed with provided appNumber, ciSysID & cmdb_ci_service_discovered table as parent 
            gaRel.addAggregate('COUNT');
            gaRel.query();
            if (gaRel.next()) {
                if (gaRel.getAggregate('COUNT') == 0) { // No records Create a new relationship
                    grInserOrUpdRel.initialize();
                    grInserOrUpdRel.setValue('child', ciSysID);
                    grInserOrUpdRel.setValue("type", relType); // setting Runs on :Runs relationship type
                    grInserOrUpdRel.setValue('parent', grApp.getUniqueValue());
                    grInserOrUpdRel.insert();
                    if (ciDisplayName) {
                        this.checkOSIRel(ciDisplayName, grApp.getUniqueValue(), relType, appNumber, trigger);
                    }
                } else if (gaRel.getAggregate('COUNT') > 1) { // if we have more than one record need to delete all and create one relationship
                    this.delDuplicateRecords(ciSysID, relType);
                    grInserOrUpdRel.initialize();
                    grInserOrUpdRel.setValue('child', ciSysID);
                    grInserOrUpdRel.setValue("type", relType); // setting Runs on :Runs relationship type
                    grInserOrUpdRel.setValue('parent', grApp.getUniqueValue());
                    grInserOrUpdRel.insert();
                    if (ciDisplayName) {
                        this.checkOSIRel(ciDisplayName, grApp.getUniqueValue(), relType, appNumber, trigger);
                    }
                } else if (gaRel.getAggregate('COUNT') == 1) { // if we have one record need to verify any changes based on that update
                    grInserOrUpdRel.initialize();
                    grInserOrUpdRel.addEncodedQuery("child=" + ciSysID + "^type=" + relType + "^parent.sys_class_name=cmdb_ci_service_discovered"); 
                    grInserOrUpdRel.setLimit(1);
                    grInserOrUpdRel.query();
                    if (grInserOrUpdRel.next()) {
                        if (grInserOrUpdRel.getValue('parent') != grApp.getUniqueValue()) {
                            grInserOrUpdRel.parent = grApp.getUniqueValue();
                            grInserOrUpdRel.update();
                        }
                        if (ciDisplayName) {
                            this.checkOSIRel(ciDisplayName, grApp.getUniqueValue(), relType, appNumber, trigger);
                        }
                    }
                }
            }
        } else {
            errorReason = 'Cannot find matching Application Service with the number in Key Value "ApplicationServiceNumber" entry from the VMI: <strong>' + ciDisplayName + '</strong>';
            gs.eventQueue('fa.cmdb.relationship.failed', null, errorReason, trigger);
        }
    },
    
    delDuplicateRecords: function(CI, relType) {
        var grRel = new GlideRecord('cmdb_rel_ci');
        grRel.addEncodedQuery("child=" + CI + "^type=" + relType + "^parent.sys_class_name=cmdb_ci_service_discovered");
        grRel.query();
        while (grRel.next()) {
            grRel.setWorkflow(false);
            grRel.deleteRecord();
        }
    },

    checkOSIRel: function(displayName, appSerID, relType, appNumber, trigger) {
        // Check on Cmdb server table
        var ciClass = '';
        var ciSysID = '';
        var errorReason= '';
        var gtServerNme = new GlideRecord('cmdb_ci_server');
        gtServerNme.addQuery('name', displayName);
        gtServerNme.addQuery('install_status', '!=', '7');
        gtServerNme.setLimit(1);
        gtServerNme.query();
        if (gtServerNme.next()) {
            ciClass = gtServerNme.getValue('sys_class_name');
            ciSysID = gtServerNme.getUniqueValue();
        } else {
            errorReason = 'Cannot find matching OS Server with same name as the VMI: ' + displayName +' server with the inserted/updated Key Value: <strong>' + appNumber + '</strong>';
            gs.eventQueue('fa.cmdb.relationship.failed', null, errorReason, trigger);
        }
        if (ciSysID) {
            var qry = "typeLIKERuns on::Runs^child.sys_class_name!=cmdb_ci_vm_instance^parent.sys_class_name=cmdb_ci_service_discovered^child.sys_id=" + ciSysID;
            var getOSrel = new GlideRecord('cmdb_rel_ci');
            getOSrel.addEncodedQuery(qry);
            getOSrel.query();
            if (getOSrel.hasNext()) {
                while (getOSrel.next()) {
                    getOSrel.setWorkflow(false);
                    getOSrel.deleteRecord();
                }
                getOSrel.initialize();
                getOSrel.setValue('child', ciSysID);
                getOSrel.setValue("type", relType); // setting Runs on :Runs relationship type
                getOSrel.setValue('parent', appSerID);
                getOSrel.insert();
            } else {
                getOSrel.initialize();
                getOSrel.setValue('child', ciSysID);
                getOSrel.setValue("type", relType); // setting Runs on :Runs relationship type
                getOSrel.setValue('parent', appSerID);
                getOSrel.insert();
            }
        }
    },
    type: 'FA_CMDB_DupRelationshipHelper'
}

Thanks and Regards @Ankur Bawiskar , @Mohith Devatte 

Venugopal S

[Community Alums]

Hi,

• Duplicate Patches::Patched by relationships have been removed from the afflicted CI's
• Duplicate Patches::Patched by relationships are no longer being inserted daily
• There are no new duplicate relationships being created on these CI's

does this work for this case ?

Thanks & Regards 
Venugopal S

[suvro]

Points 2 and 3 works well.

FOR

• Duplicate Patches::Patched by relationships have been removed from the afflicted CI's

You either need to manually delete the duplicates or write some different code for the same

[Community Alums]

Hi @suvro
Thanks for the above code for 

• Duplicate Patches::Patched by relationships have been removed from the afflicted CI's

I wrote Fix script, It doesn't seems working properly can you please suggest me with it.


Fix Script:
var rel = new GlideAggregate('cmdb_rel_ci');
rel.groupBy('parent');
rel.groupBy('child');
rel.groupBy('type', 'Patches::Patched by');
rel.addHaving('COUNT', '>', 1);
rel.query();
while (rel.next()) {
    var del = new GlideRecord('cmdb_rel_ci');
    del.addQuery('parent', rel.parent.toString());
    del.addQuery('child', rel.child.toString());
    del.addQuery('type', rel.type.toString());
    del.query();
    del.next();
    while (del.next()) {
        del.deleteRecord();
    }
}

Thanks 

Venugopal S