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

mikekirschen
Giga Contributor

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 

1 ACCEPTED SOLUTION

In that case update the active query line to

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

 

View solution in original post

11 REPLIES 11

dvp
Mega Sage
Mega Sage

Try the below method

Update the report condition as follows as we will be using schedule report Run as field to select the manager dynamically

find_real_file.png

 

and use the below script in Schedule job to Initiate it

 

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('YOUR_SCHEDULE_REPORT_SYS_ID');
	scheduleReport.user_list = managers[i];
	scheduleReport.run_as = managers[i];
	scheduleReport.update();
	SncTriggerSynchronizer.executeNow(scheduleReport);
	gs.sleep(10000);
}

gs.log('Completed');

mikekirschen
Giga Contributor

Many thank you for the information I tried it in our sub instance.  I can see the user_list changing to the groups managers however the the user_list changes are not group by Assignment Group Managers meaning if we have 20 tickets assigned to groups having the same Manager we should see one entry in the user_list for that Manager. The user_list should change when the Manager is different. Also there was no email messages posted in the outbox.

 

Appreciate additional input

The above script update the user_list for every manager and execute the schedule report

 

Couple of questions

Did you update the report condition?

Can you post the script that you are using here and please confirm that you are using a fix script or a schedule script to run the above script.

mikekirschen
Giga Contributor

Here is the the report condition 

find_real_file.png

This is the scheduled job running the script:

find_real_file.png

I can see many duplicate messages for each Group Manager posted to the outbox.