script to remove any active false users from CI

[Hemamani Prabha]

Hello all,

Can someone help me with the script to remove any active false users from the fields of CIs:
Secondary Owner, Business Owners and Business Contacts

Thanks,

Hema

[Viraj Hudlikar]

Hello @Hemamani Prabha 

Create a fix script and use below code after using proper backend field name as setup in your instance. 

var ciGr = new GlideRecord('cmdb_ci');
ciGr.addQuery('secondary_owner', '!=', '');
ciGr.addOrCondition('business_owners', '!=', '');
ciGr.addOrCondition('business_contacts', '!=', '');
ciGr.orderBy('sys_created_on');
ciGr.setLimit(100); // if data is less you can comment this line if data is huge you can increase limit as you need but you will need to rerun this script untill no data is present.
ciGr.query();

while (ciGr.next()) {
    var updateNeeded = false;

    // If your field type is reference field the below format suits you to Process Secondary Owner field.
    if (ciGr.secondary_owner && ciGr.secondary_owner.active == false) {
        ciGr.secondary_owner = '';
        updateNeeded = true;
    }

    // Process Business Contacts in case if the field type is of list use below format of code.
    var businessContacts = ciGr.business_contacts.toString();
    if (businessContacts) {
        var contactsArray = businessContacts.split(',').filter(Boolean);
        if (contactsArray.length > 0) {
            var activeContacts = getActiveUserIds(contactsArray);
            var newContacts = activeContacts.join(',');
            if (newContacts !== businessContacts) {
                ciGr.business_contacts = newContacts;
                updateNeeded = true;
            }
        }
    }

    if (updateNeeded) {
        ciGr.update();
    }
}

function getActiveUserIds(userIds) {
    var activeUsers = [];
    var gr = new GlideRecord('sys_user');
    gr.addQuery('sys_id', 'IN', userIds);
    gr.addQuery('active', true);
    gr.query();
    while (gr.next()) {
        activeUsers.push(gr.sys_id.toString());
    }
    return activeUsers;
}

Note: The code I provided you will need to rerun it if data is huge. And to avoid performance impact use setLimit(xx) properly.
Also, if you are doing this first time try with one record by setLimit(1) and observe update is happening or not.

If my response has helped you hit helpful button and if your concern is solved do mark my response as correct.

Thanks & Regards
Viraj Hudlikar.

[Viraj Hudlikar]

Hello @Hemamani Prabha ,

Thank you for marking my response as helpful. 🙂

If my response helped please mark it correct and close the thread so that it benefits future readers.

Thanks & Regards,

Viraj Hudlikar.

[Robbie]

Hi @Hemamani Prabha,

You can use the below Script as a Fix Script for the initial fix. You should then think about implementing a check and something similar as a business rule on the user table when a user is inactivated so as to keep the data in sync moving forward.

Please note, the fields you mentioned I believe maybe custom on your instance, I've used standard Out Of Box fields ("supported_by" and "owned_by") in the below script which you can simply substitute as required.

Please also note, on line 33, I have purposely commented out the "ciGR.update()" syntax. This allows you to run the script WITHOUT actually performing the update so as to sanity check the correct data and records will be performed.

Once you're happy and chekcing the logs to check the expected CI's and users align, un-comment line 33.

As always, perform this on a non prod instance first.

// Identify all inactive users. We perform this check as we are only intersted in inactive users.
var inactiveUsersByID = [];
var inactiveUsersByName = [];
var userGR = new GlideRecord('sys_user');
userGR.addQuery('active', false);  // Filter only inactive users
userGR.query();

while (userGR.next()) {
    inactiveUsersByID.push(userGR.sys_id.toString());  // Store the inactive user sys_ids
	inactiveUsersByName.push(userGR.name.toString());  // Store the inactive user sys_ids
}

//Check the Configuration Item (CI) records. However, rather than check ALL Ci's, only check CI's which have values against selected fields - more efficient
var ciCounter = 0;
var ciGR = new GlideRecord('cmdb_ci');
var ciGROr = ciGR.addQuery('owned_by', '!=', '');
ciGROr.addOrCondition('supported_by', '!=', ''); //Add / update the fields you require and wish to filter by
ciGR.query();  // Query all CI records (modify this query if you need to limit the results)

while (ciGR.next()) {
	ciCounter++;
    // Loop through the fields where users are stored (Secondary Owner, Business Owner, Business Contact)
    var userFields = ['owned_by', 'supported_by']; //Add / update the fields you require and wish to filter by

    for (var i = 0; i < userFields.length; i++) {
        var userField = userFields[i];
        var userSysId = ciGR[userField];

        // If the user field is populated, check if the user is inactive
        if (userSysId && inactiveUsersByID.indexOf(userSysId.toString()) > -1) {
            // If the user is inactive, remove them from the CI field
            ciGR[userField] = null;
            //ciGR.update();  // Uncomment this to Save the change to the CI record
            gs.info('Removed inactive user Name: ' + inactiveUsersByName[i] + ' - User sys_id: ' + inactiveUsersByID[i] + ' From Field Name: ' + userField + ' on CI Name: ' + ciGR.name + ' - CI sys_id: ' + ciGR.sys_id);
        }
    }
}

gs.info('Background script completed. CIs checked: ' + ciCounter + '. Inactive users removed from CI fields.');

To help others (and for me to gain recognition for my efforts), please mark this response correct by clicking on Accept as Solution and/or Kudos.



Thanks, Robbie

[Ankur Bawiskar]

@Hemamani Prabha 

You only want them to be removed?

If you empty those fields it will be not a clean data.

There will be lot of CIs which won't have this data, not good from reporting point of view.

You should first discuss this with your customer, after removal which user needs to be populated there

If my response helped please mark it correct and close the thread so that it benefits future readers.