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

Ozgur Baykal · ‎08-23-2022

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 · ‎08-26-2022

@Ozgur Baykal - Are you using assignment group in interaction if yes, then use the below code snippet.

Please make the necessary filter update and use it

//Get Incident Assignment group
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());
}

//Get Interaction Assignment group
var intGr = new GlideAggregate("interaction");
//Your actual filter in report
intGr.addQuery("active=true");
intGr.groupBy('assignment_group');
intGr.query();
while (intGr.next()) {
    groups.push(intGr.assignment_group.toString());
}

// Consolidated groups
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');

Thanks & Regards,
Vasanth

View solution in original post

Ankur Bawiskar · ‎08-23-2022

Hi,

you can create single scheduled report and dynamically change the user/group via script and send it

Scheduling a report for dynamic Recipients based on the table column of the report

Regards
Ankur

Regards,
Ankur
✨ Certified Technical Architect || ✨ 9x ServiceNow MVP || ✨ ServiceNow Community Leader

Ozgur Baykal · ‎08-24-2022

Thank you for your quick response Ankur. This script is dynamically changing the recipients of the scheduled email, but it is not filtering the report for each recipient only for their relevant data.

Vasantharajan N · ‎08-23-2022

Please refer to the link below

https://community.servicenow.com/community?id=community_question&sys_id=a8168bf6db36eb805d782183ca9619f3

Thanks & Regards,
Vasanth

Ozgur Baykal · ‎08-24-2022

Thank you for your response Vasantharajan. Looks like this script is both dynamically modifying the recipients for the email and filtering the report attached to it based on the manager. but we have more complicated situation. I would like to send these emails to each member of the Assignment Group ( maybe Assignment group’s “group email” can be used) and they should only receive their related data in the email. And we have mangers that manage several Assignment Groups (AG). So, I think if we set the “Run as=[Manager]” as it suggests in that script, then the AG members will see other AG’s data (which are managed by their manager as well) in their emails . Would you happen to have a solution for that by any chance ? Like , is it possible to set the “Run as = Assignment Grp” or possibly set the “Run as = each individual member of Assignment Grp” and I can set the Report filter “Assignment group is (dynamic) One of My Groups”. So, when they click the report link on the email, they only see their related data.