Dynamic and individual Emails based on the Report out to the Assignment groups Managers

[mikekirschen]

We have requirements to send individual emails to the assignment groups managers for opened incidents assigned to their groups. I have scheduled the report to run every Monday and I used the following script to retrieve the Assignment Groups Managers, based on the report outcome and add them to the recipients list. This is working by emailing the report to the respective group managers dynamically. The new requirement is to send separate emails to the groups managers.

----------------

var GroupManager = ''; // to hold Group's Manager for each looped row

// get pre-existing User recipients from definition above and append separator (works with or without existing Users)
var UserList = current.user_list + ',';
var scheduleReport = new GlideRecord('sysauto_report'); // get details directly from this Scheduled Report
scheduleReport.get(current.sys_id);
var tablename = scheduleReport.report.table; // gets the table that the above Report points at
var query = scheduleReport.report.filter; // gets all the Report's predefined clauses and other criteria
var gr = new GlideRecord(tablename); // set up a LOCAL GlideRecord object using the Report's table
gr.addEncodedQuery(query); // apply to it all the Report definitions found above
gr.query(); // execute the Report query into the LOCAL GlideRecord object
// ----------------------------

while (gr.next()) { // loop through each LOCAL record until done
// get the applicable recipients (field names are Report-specific!) and append each to the existing list
GroupManager = gr.assignment_group.manager;
UserList += (GroupManager + ',');
}
current.user_list = UserList; // override the user_list
answer=true;

------------------------------------------

Appreciate the assistance 

[dvp]

Did you happen to run the Schedule job multiple times.

Every time you run schedule job it will send an email to the managers.

[mikekirschen]

It looks like the duplicate records in the outbox, were from previous test attempts. Now I can see the expected result in the outbox. ** This is Excellent **

I have updated requirements to change the report filter to:

What would be the best option to change the filter in the current script:

var gr = new GlideAggregate("incident");

=======================

gr.addQuery("active=true");

=======================
gr.groupBy('assignment_group');
gr.query();
var managers = [];
while (gr.next()) {
managers.push(gr.assignment_group.manager.toString());
}

var arrayUtil = new ArrayUtil();
managers = arrayUtil.unique(managers);

for(var i = 0; i<managers.length; i++){

gs.log('Manager: ' + managers[i]);

var scheduleReport = new GlideRecord('sysauto_report');
scheduleReport.get('adc1ccc7dbf22b80c14d73921f96191b');
scheduleReport.user_list = managers[i];
scheduleReport.run_as = managers[i];
scheduleReport.update();
SncTriggerSynchronizer.executeNow(scheduleReport);
gs.sleep(10000);
}

gs.log('Completed');

[dvp · Accepted solution]

In that case update the active query line to

gr.addEncodedQuery("stateIN1,2,3"); // Update the state values

[mikekirschen]

Worked as expected ... Many thanks

[dvp]

You are welcome 🙂

Can you please mark the response that helped to achieve as a correct answer, so that it will be easy for users to find the solution.