Sel Limit via sys_properties

[Rooma1]

Hi All,

I have a requirement to create a cleanup job on 'sysapproval_approver' table so that it can clean the records pending for more than a year. I want to set the limit to 100 via system property and want to call that property in the script. I do not want to use setLimit method.

Given below is the snippet code:

var gr = new GlideRecord('sysapproval_approver');

gr.addEncodedQuery('sysapproval.number=RI0439973^state=requested');

gr.query();

while (gr.next()) {

    var RITMgr = new GlideRecord('sc_req_item');

    RITMgr.addQuery('sys_id', gr.document_id);

    RITMgr.addQuery('state', '!=', '5');

    RITMgr.query();

    while (RITMgr.next()) {

        RITMgr.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';

        RITMgr.update();

        RITMgr.stage = 'Request Cancelled';

        RITMgr.state = '5';

       RITMgr.setWorkflow(false);

        RITMgr.update();

        var workflow = new global.Workflow();

        workflow.cancel(RITMgr);

        gs.print('Numbers are '+ RITMgr.number);

    }

[Maddysunil]

@Rooma1 

Could you please apply some log and check where the code is getting failed, also try with below updated code:

cleanup: function() {
    var cleanupLimit = gs.getProperty('housekeeping_cleanup.max_approval_record');

    // Loop through all sysapproval_approver records
    var gr = new GlideRecord("sysapproval_approver");
    gr.addEncodedQuery("state=requested^source_table=sc_req_item^ORsource_table=sc_request");
    gr.setLimit(cleanupLimit);
    gr.query();

    while (gr.next()) {
        if (gr.source_table == 'sc_req_item') {
            // Handle sc_req_item records
            var ritm = new GlideRecord("sc_req_item");
            if (ritm.get(gr.document_id)) {
                ritm.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';
                ritm.stage = "Request Cancelled";
                ritm.state = "5";
                ritm.setWorkflow(false);
                ritm.update();
                var workflow = new Workflow();
                workflow.cancel(ritm);
            }
        } else if (gr.source_table == 'sc_request') {
            // Handle sc_request records
            var req = new GlideRecord("sc_request");
            if (req.get(gr.document_id)) {
                var message = gs.getMessage('Request has been cancelled as the approval was not given within 90 days');
                gs.addInfoMessage(message);
                var workflow = new Workflow();
                workflow.cancel(req);
                req.request_state = 'closed_cancelled';
                req.approval = 'rejected';
                req.state = 5;
                req.update();
            }
        }

        // Cancel the approval request
        gr.state = "cancelled";
        gr.setWorkflow(false);
        gr.update();
    }

    gs.log("End housekeeping", "RI/RQ Housekeeping");
}

Please Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks

[Rooma1]

@Maddysunil Thanks for the suggestion. I want to add one more system property which will store the Approval record age like 365 days and i want to add it in the encoded query.

What is the syntax to get the no. of records from system property.

[Maddysunil]

@Rooma1 

you can use something like this:

var maxApprovalAgeDays = gs.getProperty('housekeeping_cleanup.max_approval_age_days');
var encodedQuery = "state=requested^source_tableINsc_req_item,sc_request^sys_created_on<javascript&colon;gs.daysAgoStart(" + maxApprovalAgeDays + ")";
gr.addEncodedQuery(encodedQuery);

Please Mark ✅Correct if this solves your query and also mark 👍Helpful.

Thanks

[Rooma1]

@Maddysunil I am very thankful to you for all the solutions you have provided till now and it was very helpful for me.

There is one more requirement now where I am getting stuck and not sure why the code is not working.

Here, the requirement is once the RITM's approval and the stage gets cancelled and the worknotes get updated, I need to check if there are any more RITM open for that parent REQ. If none of the RITM is open, then I need to update the REQ's Approval state to Closed Incomplete.

Use Case, it should check:

1. If 1 RITM and it is cancelled, then the REQ state should get updated to Closed Incomplete.

2. If 2 RITMs, one gets closed cancelled and the other is already closed, then the REQ state should get updated to Closed Incomplete.

3. If 2 RITMs, one is closed cancelled and other is still open, then the REQ should not get updated.

 cleanup: function() {

var cleanupLimit = gs.getProperty('housekeeping_cleanup.max_approval_record', 100); //Maximum number of records to be processed by  script include - "JDSHousekeepingRequests" in one go.

var maxApprovalAgeDays = gs.getProperty('housekeepingRequests.maxApprovalAgeDays', 600); //Max Approval record age which has to be processed by the script include - "JDSHousekeepingRequests"

var minApprovalAgeDays = 90; //variable to store min numbers of Approval age days to use when the maxApprovalAgeDays system property value is less than this value.

if (maxApprovalAgeDays < minApprovalAgeDays) {

            maxApprovalAgeDays = minApprovalAgeDays;

            gs.log('Using the Minimum Approval Age days : ' + minApprovalAgeDays + ' as the maxApprovalAgeDays system property value is less than minimum Approval Age days', "RI/RQ Housekeeping");

        }

 var recordsProcessed = 0; // Counter to keep track of processed records

var encodedquery = '';

        encodedquery += 'state=requested'; //Approval State is Requested

        encodedquery += '^source_table=sc_req_item^ORsource_table=sc_request'; //Source Table is either sc_req_item or sc_request

        encodedquery += '^sys_updated_onRELATIVELT@dayofweek@ago@' + maxApprovalAgeDays; //ApprovalFor record last updated maxApprovalAgeDays ago .

        gs.log("Start housekeeping job with Max Cleanup Limit : " + cleanupLimit + " and max Approval Age Days : " + maxApprovalAgeDays, "RI/RQ Housekeeping");

        // Loop through all sysapproval_approver records

        var gr = new GlideRecord("sysapproval_approver");

        gr.addEncodedQuery(encodedquery);

        gr.orderBy('sys_created_on');

        gr.setLimit(cleanupLimit);

        gr.query();

        while (gr.next() && recordsProcessed <= cleanupLimit) {

            if (gr.source_table == 'sc_req_item') {

                // Handle sc_req_item records

                var ritm = new GlideRecord("sc_req_item");

                ritm.addQuery('sys_id', gr.document_id);

                ritm.addQuery('stateNOT IN3,5,4'); //State value 3=Closed,4=Closed Incomplete,5=Closed Cancelled

                ritm.query();

                while (ritm.next()) {

                    ritm.work_notes = 'Cancelled by Scheduled Job - Housekeeping Job for RI/RQ';

                    ritm.update();

                    ritm.stage = "Request Cancelled";

                    ritm.state = "5"; //state - 5 equals to 'Closed Cancelled

                    ritm.setWorkflow(false);

                    ritm.update();

                    var workflow = new global.Workflow();

                    workflow.cancel(ritm);

                    recordsProcessed++; // Increment the counter

                    //Once the RITM is cancelled, it will check if there are any other RITM open for the parent REQ, if not then it will update the REQ to closed incomplete.

                    var TotalCount = 0;

                    var aggrRITM = new GlideAggregate('sc_req_item');

                    aggrRITM.addQuery('request', sys_id);

                    //aggrRITM.addEncodedQuery('stateIN-5,1,2,3,6');//1=Open,2=Work in Progress,3=Closed,'-5'=Pending,6=Resolved

                    aggrRITM.addAggregate('COUNT');

                    aggrRITM.query();

                    TotalCount = aggrRITM.getAggregate('COUNT');

                    if (TotalCount == 0) {

                        var REQgr = new GlideRecord('sc_request');

                        if (REQgr.get(aggrRITM.request)) {

                            REQgr.request_state = 'closed_incomplete';

                            REQgr.work_notes = 'Atleast one of the RITM was cancelled by the Housekeeping Job, hence updating the state of REQ to Closed Incomplete';

                            REQgr.setWorkflow(false);

                            REQgr.update();

                        }

                    }

                }

[Harish KM]

HI @Rooma1 without set limit , you can use system property to set Batch size like below

var batchSize = gs.getProperty('limit.test'); // Set the desired batch size

gs.info(batchSize);

var gr = new GlideRecord('incident');

gr.addEncodedQuery('state=7');

gr.orderBy('sys_created_on');

gr.query();

var processedRecords = 0;

while (gr.next() && processedRecords < 5000) {

    // Update fields as needed

    gr.setValue('short_description', 'one');

    gr.update();

    processedRecords++;

gs.info("process"+ processedRecords);

    // If the batch size is reached, re-query to fetch the next batch

    if (processedRecords % batchSize === 0) {

        gs.info("called");

        gr = new GlideRecord('incident');

      gr.addEncodedQuery('state=7');

      gr.setSkipCount(processedRecords);

      gr.query();

    }

}