Scheduled Reports

[oww15]

Hi all, can someone help me with the script on how to send scheduled reports to its group members. The group currently has a group email the reason why the scheduled report is only being sent to the group email. My requirement is to have it sent by the group members. Thank you!

[Ahmmed Ali]

Hello @oww15 

The group table has one field called Include Members. You can update that field to true and the emails where recipient is selected as the group, the email will be sent to both group email and all member's will also be present in the same email.

Note: This applies to all emails which are being sent to that particular group from ServiceNow.

Thank you,

Ali

[oww15]

Hi @Ahmmed Ali, can it be done only to a specific scheduled report as we don't want that all scheduled reports will be sent to the group members. Some can be sent only through their respective group emails.

[Ahmmed Ali]

Hello @oww15 

One solution is that you can set the run as on-demand for your scheduled report. Then create one scheduled job to run a script with the schedule when your report need to be sent. In the scheduled job, you can query the scheduled report need to be sent and update the recipient as group members as required. Then trigger the scheduled report from the script.

Reference: https://www.servicenow.com/community/platform-analytics-articles/scheduling-a-report-for-dynamic-rec... 

Example scheduled job script:

updateDynamicRecipients();

function updateDynamicRecipients(){
	var scheduleReport = new GlideRecord('sysauto_report');  // glide the Scheduled report
	scheduleReport.get("sys_id of report"); //Sys ID of your schedule Report

	var recipients = [];
	var groupSysId = 'YOUR_GROUP_SYS_ID';
	var query = 'group=' + groupSysId;

	var gr = new GlideRecord('sys_user_grmember');
	gr.addEncodedQuery(query);
	gr.query();
	while (gr.next()) {
		recipients.push(gr.assigned_to.sys_id.toString());  // push the assigned _to users in recipients array
	}
	// gs.log(recipients.toString());
	// gs.log(recipientsManager.toString());

	var arrayUtil = new ArrayUtil();
	finalRecipients = arrayUtil.unique(recipients);   // unique elements

	scheduleReport.user_list = finalRecipients.join(',');
	//	gs.log("User list: " +current.user_list);
	scheduleReport.update();

	SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report

}

Thank you,

Ali

[Riya Verma]

Hi @oww15 

Hope you are doing  great.

To achieve the requirement of sending scheduled reports to individual group members rather than just the group email:

1. Define a new table to store the mapping between the group and its members. This table will hold records indicating which user belongs to which group.

2. Modify the existing script responsible for sending scheduled reports. Instead of sending the report directly to the group email, we will retrieve the members of the group from the new table and send individual emails to each group member.

3. Let's assume we have a table named "Group_Membership" with the following fields:

   • Group (reference to the Group table)
   • User (reference to the User table)

4. The script should look something like this:

// Retrieve the Group record based on the provided group name.
var groupName = 'Group Name'; // Replace 'Group Name' with the actual name of the group.
var group = new GlideRecord('Group');
if (group.get('name', groupName)) {
    // Find all users belonging to this group in the Group_Membership table.
    var groupMembership = new GlideRecord('Group_Membership');
    groupMembership.addQuery('Group', group.sys_id);
    groupMembership.query();
    
    // Loop through the group members and send the scheduled report to each member.
    while (groupMembership.next()) {
        var userSysID = groupMembership.getValue('User');
        var user = new GlideRecord('User');
        if (user.get(userSysID)) {
            // Send the scheduled report to the individual group member.
            // Replace 'reportToSend' with the actual report query or function to generate the report.
            var reportToSend = getReportQuery(); // You should define a function that generates the report.
            // Replace 'user.email' with the appropriate field representing the user's email address.
            sendEmailWithReport(user.email, reportToSend);
        }
    }
} else {
    gs.error('Group not found: ' + groupName);
}