How to manage cmdb lifecycle relationship within ETL integration

[Louis16]

We are building a cmdb integration with an application that is currently managing the application and server inventory (including relationships).

We have successfully mapped the Business Application, Application Service and Server classes but we are struggling with how to manage the relationship lifecycle, specifically when modification to the relationship happens relationship is removed or changed).

I have read that Health & Remediation could be used to identify and potentially remove the relationships but it doesn't seem to cover all use cases.
My understanding that it doesn't manage the relationship directly but is it looks at the staleness of the CI (and not the relationship).

Here is the simple use case:
Application Service A has 2 server relationships and 1 relationship is removed

Data source daily load (Day one)
Application Service A has a "Depends on::Used by" relationship with Server 1
Application Service A has a "Depends on::Used by" relationship with Server 2

Data source daily load (Day Two)
Application Service A has a "Depends on::Used by" relationship with Server 1

I don't see anything in the IntegrationHub ETL/IRE that can help me manage a relationships: My understanding is it only performs inserts.
I looked at the relation record (cmdb_rel_ci) and after the record is inserted, no updates are performed.
The sys_updated_on field is not updated and there is no discovery fields (last_discovered).
So I don't see how I could script a cleanup of the relationship after a data source import.

Any help would be greatly appreciated

Louis

[Guus Bouts1]

Hi Louis,

Ran into your post while experiencing a similar demand; an existing IntegrationHub ETL transform map was only adding new relationships, not even overwriting existing ones.

From what I could determine this seems the way it is, only inserts as you stated.
However, in case you are still searching for a potential work-around, please consider the following I've been able to successfully setup. Our use case was a server import job would have to be updated accordingly to the correct service offering. Before our action it would simply keep adding relationships, never updating.

So to resolve this I've added an After Transform script on the CMDB Integration Studio Application Data Source (alternatively: System Import Sets > Robust Import Set Transformers > Select the one you need to edit and open the Transformer Definition). Mark the "Execute After Script" en provide a script which does what you need, our example below:

(function(ireOutput, ireInput, runId) {

    for (var i = 0; i < ireOutput.length; i++) {

        var output = ireOutput[i];
        var items = output.items;

        for (var j = 0; j < items.length; j++) {

            // check if relations exist for this server we just added
            if (items[j].className == 'cmdb_ci_server') {

                var serverId = items[j].sysId;

                var relationGR = new GlideRecord('cmdb_rel_ci');
                relationGR.addQuery('child', serverId);
                relationGR.orderBy('sys_created_on');
                relationGR.query();

                var rowCount = relationGR.getRowCount();

                // If we found more than one record, multiple relationships exist; delete (all) but the oldest, but leave the last one
                while (rowCount != 1 && relationGR.hasNext()) {
                    if (relationGR.next())
                        relationGR.deleteRecord();

                    rowCount -= 1;
                }
            }
        }
    }
})(ireOutput, ireInput, runId);

Hope this helps you out still.

Regards,
Guus