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]

@gunashalini 

Then post what the values of you system property is. I created the custom table and system property in my instance. And post how you have defined the three fields on your custom table. I used the sys_id values for "Hardware,Software,Database" groups for the system property value. Using Group name values in the system property can work too.  the code I have in my scheduled job follows:

var gr = new GlideRecord('sys_user_grmember');
var groupSysIds = gs.getProperty('ITIL role based Groups').split(',');
//gr.addEncodedQuery('group.nameIN'+groupSysIds);  // system property contains group names
gr.addEncodedQuery('group.sys_idIN'+groupSysIds);gr.query();  //system propertiy contains sys_id values
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();
    }
}

which works as expected and doesn't create duplicates in the custom table. It's discouraging that you can't do the same.

[Peter Bodelier]

Hi @gunashalini,

Try it like this:

var gr = new GlideRecord('sys_user_grmember');
gr.addQuery('group.sys_id', 'IN', gs.getProperty('ITIL role based Groups'));
gr.query();

while (gr.next()) {
	var user = gr.getValue('user');
        var group = gr.getValue('group');
        var existingRecord = new GlideRecord('u_itil_license_removal_candidates');
        existingRecord.addQuery('u_user', user);
        existingRecord.addQuery('u_group.sys_id', group);
        existingRecord.query();

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

[gunashalini]

Hi @Peter Bodelier I have used multiple sys_ids in one sys_property. Will this code work in such cases.

[Peter Bodelier]

Yes it should. It splits it into an array with sys_ids

[Vijay Balotia1]

Hi @gunashalini,

In your script you are using license as an object but its not defined any where, so if its a deferent table then u_itil_license_removal_candidates table then define the 'license' object before initialize function. if its the same table then  replace this 

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

Please mark the appropriate response as correct answer and helpful, This may help other community users to follow correct solution.
Thanks
Vijay Balotia