Duplicate entries while running scheduled job

[gunashalini]

The script below is used to take records from sys_user_grmember table and them to u_itil_license_removal_candidates table. However, if the scheduled job runs, all entries sit on the table creating duplicates. How can I avoid it?

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();
    }
}

[Craig Gruwell]

Hi gunashalini,

I'm not seeing anything in the script that would result in duplicates (but you don't need the extra GlideRecord call and it can be simplified to below).  Do you have any business rules executing on u_itil_license_removal_candidates that might be causing the duplicates?  You can try using Script Tracer to investigate more the duplicates are coming from.

var defaultStatus = 'Pending Removal';

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()) {
        existingRecord.initialize();
        existingRecord.u_user = user;
        existingRecord.u_group = group;
        existingRecord.u_license_removal_status = defaultStatus;
        existingRecord.insert();
    }
}

[Vijay Balotia1]

Hi @gunashalini,

user below line in your code and try.

if (!existingRecord.next())

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

[Chetan Mahajan]

Hello @gunashalini ,

                                     To avoid creating duplicate records when running your scheduled job in ServiceNow, you can modify your script to perform a check before inserting a new record into the u_itil_license_removal_candidates table. The check should ensure that a record with the same user and group does not already exist. Here's an updated version of your script:

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();

    // Check if a record already exists with the same user and 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()) {
        // Only insert a new record if it doesn't already exist
        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();
    }
}

Kindly mark correct and helpful if applicable

[Bert_c1]

@gunashalini 

I posted a reply in your other thread for the same here.

https://www.servicenow.com/community/developer-forum/use-multiple-sys-ids-in-one-sys-property/td-p/2...

The following script should work for you, using you system property of groups

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();
    }
}