Query cmdb_ci table from Change Request

[InfamousSpork]

Hello,

I've been asked to add an additional approval to our change ticket process. It is based on the application that is chosen, and will then need to query 2 tables (cmdb_ci_linux_server and cmdb_ci_win_server) to see if the application chosen is on one of the servers listed that contain Discovery Source = CloudHealth AND Hosted at = AWS. I have created a Business UI script that updates a True/False field if found, however it seems that it's taking too long ("log Slow Business Rule"). Probably due to the large tables. Here is the script:

(function executeRule(current, previous /*null when 'insert'*/) {
    // Function to check servers in a specified table and return true if criteria are met
    function checkServers(tableName) {
        var found = false;
        var appSysId = current.u_application.getDisplayValue(); // 
        var serverGR = new GlideRecord(tableName);
        serverGR.addQuery('u_applications', 'CONTAINS', appSysId);
        serverGR.query();

        if (serverGR.next()) {
            if (serverGR.u_hosted_at == 'AWS' && serverGR.u_discovery_source == 'CloudHealth') {
                found = true; // A matching server is found, set found to true
            }
        }
        return found;
    }

    // Check both Linux and Windows server tables
    var needsSecurityApproval = checkServers('cmdb_ci_linux_server') || checkServers('cmdb_ci_win_server');

    // Set the 'u_security_approval_required' (or your specific field) field based on the check result
    current.u_security_approval_required = needsSecurityApproval;

    if (needsSecurityApproval) {
        gs.addInfoMessage('Additional security approval required for this change request.');
    } else {
        gs.addInfoMessage('No additional security approval required for this change request.');
    }

})(current, previous);

Is there a better option than a business rule, since this doesn't seem to be working? 

[AshishKM]

Hi @InfamousSpork , 

The If condition check can be applied with addQuery().

On which table the BR is trigerring ? 

can you check the correct column name for "u_application" or "u_applications" , if this column configured at cmdb_ci level then it should be same at both place in the below code.

and if there is only one appSysId then use the equale operator instead of contains.

(function executeRule(current, previous /*null when 'insert'*/) {
    // Function to check servers in a specified table and return true if criteria are met
    function checkServers(tableName) {
        var found = false;
        var appSysId = current.u_application.getDisplayValue(); // 
        var serverGR = new GlideRecord(tableName);
        serverGR.addQuery('u_applications', 'CONTAINS', appSysId);
        serverGR.addQuery('u_hosted_at', 'AWS');
        serverGR.addQuery('u_discovery_source', 'CloudHealth');
        serverGR.query();

        if (serverGR.next()) {
            //if (serverGR.u_hosted_at == 'AWS' && serverGR.u_discovery_source == 'CloudHealth') {
                found = true; // A matching server is found, set found to true
            //}
        } // if closed
        return found;
    } // function closed

    // Check both Linux and Windows server tables
    var needsSecurityApproval = checkServers('cmdb_ci_linux_server') || checkServers('cmdb_ci_win_server');

    // Set the 'u_security_approval_required' (or your specific field) field based on the check result
    current.u_security_approval_required = needsSecurityApproval;

    if (needsSecurityApproval) {
        gs.addInfoMessage('Additional security approval required for this change request.');
    } else {
        gs.addInfoMessage('No additional security approval required for this change request.');
    }
})(current, previous);

-Thanks,

AshishKM

[InfamousSpork]

The 2 different column names for the application is correct. One is from the Change Ticket (u_application) and one is from the cmdb_ci tables (u_applications). They're both reference fields. 

The reason for the CONTAINS is because the cmdb_ci field (u_applications) is a List field. 

[Dan_Tracey]

Hi @InfamousSpork 

Personally, I would move this into flow designer.

Create 2 subflows that query each of those tables, and return true or false, and then have a parent flow running both of those subflows and run "if" conditions based upon the output of the sublows.

This should massively reduce the performance impact you are facing.

reply and give me a shout if you need more info.

Thanks,

Dan

[InfamousSpork]

Hello, I ended up having to go with a manual selection for the moment as there just wasn't a great solution for this issue, as we have over 200k records it would need to search through. I'm requesting that they add the necessary information to the application itself (i.e. AWS Application?: Yes / No) instead of having to search within the CMDB server lists. 

Thank you both for your recommendations!