Use multiple sys ids in one sys_property

[gunashalini]

I have added sys id of two groups in the system property - ITIL Role based Groups.

The scheduled job code should check the users added to the two groups and insert them to another table (u_itil_license_removal_candidates). Also it will check if records are already present in the another table. It;s working if I directly mention the name of groups in encodedQuery. But not working in this format.

What is the mistake I'm doing?

var gr = new GlideRecord('sys_user_grmember');
var groupSysIds = gs.getProperty('ITIL role based Groups').split(',');
gr.query();

while (gr.next()) {
	var user = gr.user.toString();
    for (var i = 0; i < groupSysId.length; i++) {
        var groupSysId = groupSysIds[i];

        var existingRecord = new GlideRecord('u_itil_license_removal_candidates');
        existingRecord.addQuery('u_user', user);
        existingRecord.addQuery('u_group.sys_id', groupSysId);
        existingRecord.query();

        if (!existingRecord.hasNext()) {
            license.initialize();
            license.u_user = user;
            license.u_group = groupSysId;
            license.u_license_removal_status = 'Pending Removal';
            license.insert();
        }
    }
}

[Bert_c1]

Hi,

if 'u_group' on the custom table is a Reference field to the sys_user_group table, then change line

        existingRecord.addQuery('u_group.sys_id', groupSysId);

to

        existingRecord.addQuery('u_group', groupSysId);

But then you haven't stated what part above is not working.

[gunashalini]

I'm using multiple sys_ids in one property and but the code is not working. Also I have another version of this code that works perfectly fine, but is too clumsy. So created a sys_property and trying to use the sys_ids of groups into querying.

Another version of code

var gr = new GlideRecord('sys_user_grmember');
gr.addEncodedQuery('group.name=INGL-ASSET-END-OF-SUPPORT-USERS');
gr.query();

while (gr.next()) {
    var user = gr.user.toString();
    var group = gr.group.toString();

    var existingRecord = new GlideRecord('u_itil_license_removal_candidates');
    existingRecord.addQuery('u_user', user);
    existingRecord.addQuery('u_group', group);
    existingRecord.query();

    if (!existingRecord.hasNext()) {
        var license = new GlideRecord('u_itil_license_removal_candidates');
        var defaultStatus = 'Pending Removal';
        license.initialize();
        license.u_user = user;
        license.u_group = group;
        license.u_license_removal_status = defaultStatus;
        license.insert();
    }
}

The encoded query works fine even if there are multiple groups added to the query

[Bert_c1]

Hi @gunashalini,

I added a system property in my instance to test. I used the following.

var gr = new GlideRecord('sys_user_grmember');
var groupSysIds = gs.getProperty('ITIL role based Groups').split(',');
gr.addEncodedQuery('group.nameIN'+groupSysIds);
gr.query();
gs.info("sys_user_grmember records = " + gr.getRowCount());

while (gr.next()) {
    var user = gr.user.toString();
    var group = gr.group.toString();
	gs.info("user = " + user + ", group = " + group);
/*
    var existingRecord = new GlideRecord('u_itil_license_removal_candidates');
    existingRecord.addQuery('u_user', user);
    existingRecord.addQuery('u_group', group);
    existingRecord.query();

    if (!existingRecord.hasNext()) {
        var license = new GlideRecord('u_itil_license_removal_candidates');
        var defaultStatus = 'Pending Removal';
        license.initialize();
        license.u_user = user;
        license.u_group = group;
        license.u_license_removal_status = defaultStatus;
        license.insert();
    }
*/
}

A combination of some logic from your first post and that above. To support multiple groups based on the value of the system property. I commented out the custom table logic as I don't have that, but that logic looks good. The value in my system property used to test is "Hardware,Software,Database".

[gunashalini]

The code isn't working though