How to send weekly scheduled report notifications to each individual assignment group including ONLY their relevant data.

[Ozgur Baykal]

I have created two barchart reports "Aging Tasks" and "Aging Interactions". We have over 100 Assignment Groups and I want to send those two reports, weekly,  to all those Assignment groups individually, including the reports showing only data  related to their own team. I do not want to create 200+ reports (for each assignment group) and 200+ "Scheduled email of reports" for this. Can we write script to do this weekly report notification task in a more automated way? If so, Can someone help/guide me to build that script? 

[Vasantharajan N]

Hi @Ozgur Baykal 

Please update the script like below which get all groups and check for the users who need to get the email. 

Please make changes to the report that assignment group is set to use dynamic filter one of my groups.

var gr = new GlideAggregate("incident");
//Your actual filter in report
gr.addQuery("active=true");
gr.groupBy('assignment_group');
gr.query();
var groups = [];
//Getting unique group details
while (gr.next()) {
    groups.push(gr.assignment_group.toString());
}

var grpList = groups.join();
gs.print(grpList);

var userGr = new GlideAggregate("sys_user_grmember");
userGr.addEncodedQuery('groupIN' + grpList);
userGr.groupBy('user');
userGr.query();

var members = [];
while (userGr.next()) {
    members.push(userGr.user.toString());
}

gs.print("Members " + members.join());
for(var i = 0; i<members.length; i++){
	
	gs.log('Manager: ' + members[i]);

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

gs.log('Completed');

[Ozgur Baykal]

Hi @Vasantharajan N . This is very close to what I ultimately need. Thank you for sharing it with me. I just need couple tweaks to make on it.

So I think I need to make the following modifications (please correct me if I am wrong, I do not know javascript) :

1- Change Line 1 to- var : gr = new GlideAggregate("task"); (With this script. I am sending “Aging Tasks” report to users)

2- Change Line 3 to-  gr.addQuery('state>5'); we consider “Resolved” state as inactive. Per ServiceNow documentation ‘Resolved’ = 6 and anything above 6 is inactive.

3- I also need to include “Aging Interactions” report with the same email ( both are “embedded png” format bar chart reports) . So, somehow I need to take the groups ( and their members ) that may appear in Active Interactions report and add them to the [members] variable in this script (without duplication). This way we don’t ignore assignment groups if they do not have any active tasks, but have active interactions.

Would you please confirm if my modifications suggestions are ok, and help on adding the Assignment groups members of active interactions to the [members] variable? I'd greatly appreciate it.

[Vasantharajan N]

Hi @Ozgur Baykal 

You are right with Step 1 and Step 2.

Step 3 : You can use "Include with" Option in the scheduled report to include the interaction report in the same email

[Wendy20]

Hi Vasantharajan, 

Thank you for sharing this.  I was wondering how can this script be modified for it to distribute from "change request" table and for recipient to be based on "assigned_to" field.  The recipients should only receive Change requests assigned to them instead of the full report.  Can this be achieved?  Thank you in advanced for your time.  

[Ozgur Baykal]

Thank you for confirming me @Vasantharajan N . The "Active Interactions" report is already included in the "include with" option in the scheduled report. (please see screen shot). 

My concern is missing out some of the people who should receive this email with two reports embedded in it. 

For example: Group X does not have any "active tasks" at the time we run this report, but they have "active interactions". So, I am concerned that members of Group X not being included in members variable (Line 32 & 33- scheduleReport.user_list = members[i]; scheduleReport.run_as = members[i];) in your script and not get the email. From what I understand, this script is only listing 'members of the groups with active tasks' not considering, interactions.

I think we need to add more lines somewhere including : var gr2 = new GlideAggregate("interaction"); gr2.addQuery("active=true");  and some more lines to get those groups/members with active interactions. Then, add them to the [members] variable (without duplication).

Please let me know your thoughts. I really appreciate your feedback.