Introduction:
I have read some questions or queries on scheduling reports for dynamic recipients based on the table column of the report. Also, I had a few issues while working on the same requirements. Hence, I have started to write this article. I think it will help community users to work on this type of requirement in the future.
Create a scheduled report which will trigger based on conditions and send it to the dynamic receipts. Dynamic recipients may be assigned to users, the assignee’s manager, etc.
Use cases:
- Create a scheduled report which will trigger based on some conditions and send it to dynamically assigned to users.
- Create a scheduled report which will trigger with conditions and send the incidents report to Managers of the assignment group.
- Create a scheduled report to send to the assigned_to users and his/her managers for pending approval of requests.
Procedure:
Need to follow the following procedure.
Step 1: Create a report and add the “assigned_to” or “assigned_to.manager” columns in the report.
Example- Create a report for example- incidents opened last week with assigned columns in it.
Step 2: Scheduled the same report run as on-demand.
Step 3: Create a scheduled script to update the dynamic assigned to the list.
Here is the 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 tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var gr = new GlideRecord(tablename);
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
}
Step 4: If need to include assigned _to Manager or assignment groups manager, then use the script.
updateUsersListWithManager();
function updateUsersListWithManager(){
var scheduleReport = new GlideRecord('sysauto_report'); // glide the Scheuled report
scheduleReport.get("42f87abc2f37201002454ae72799b66f"); //Sys ID of your schedule Report
var recipients = []; var recipientsManager = [];
var tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var reportgr = new GlideRecord(tablename);
reportgr.addEncodedQuery(query);
reportgr.query();
while (reportgr.next()) {
recipients.push(reportgr.assigned_to.sys_id.toString()); // push the assigned _to users in recipients array
recipientsManager.push(reportgr.assigned_to.manager.sys_id.toString()); // push the assigned _to managers in recipientsManager array
}
// gs.log("recipients array: " + recipients.toString());
// gs.log("recipients manager array: " + recipientsManager.toString());
var arrayUtil = new ArrayUtil();
arrayUtil.concat(recipients, recipientsManager);
var finalRecipients = arrayUtil.unique(recipients); // unique elements
// gs.log("finalRecipients: " + finalRecipients);
scheduleReport.user_list = finalRecipients.join(',');
// gs.log("User list: " + scheduleReport.user_list);
scheduleReport.update();
SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report
}
Step 5: For earlier versions of Orlando, the current object is working in the scheduled job conditional script. we can directly use the below script in the conditional option to dynamically update the recipients.
updateDynamicRecipients();
function updateDynamicRecipients(){
var recipients = [];
// gs.log("current.report.filter:"+ current.report.table);
// gs.log("current.report.filter:"+ current.report.filter);
var scheduledGlideRecord = new GlideRecord(current.report.table);
scheduledGlideRecord.addEncodedQuery(current.report.filter);
scheduledGlideRecord.query();
while (taskQueryGR.next()){
recipients.push(scheduledGlideRecord.assigned_to + '' );
}
// gs.log(recipients.toString());
var arrayUtil = new ArrayUtil();
var finaRrecipients = arrayUtil.unique(recipients); // unique assigned to
current.user_list = finaRrecipients.join(',');
gs.log("User list: " +current.user_list);
current.setWorkflow(false);
current.update();
current.setWorkflow(true);
if (senderArray.length > 0){
return true;
}
return false;
It will update the user's list dynamically and send the reports to recipients.
Check the mail logs.
In addition, to that, we can use the following methods of mail script for reports. We can specify copied and blind copied recipients by using the email object within a mail scripts.
email.setFrom("Servicedesk <servicedesk@testmail.com>"); // to set the setFrom mail address
email.setSubject("Subject of the report"); // to set the mail Subject for notification
email.addAddress("cc","sagar.pagar@testmail.com","Sagar Pagar"); // to add CC users in notification
email.addAddress("bcc","sagar.pagar@testmail.com","Sagar Pagar"); // to add BCC users in notification
Conclusion:
In this way, we can schedule a report with dynamic recipients.
Please provide your inputs and give suggestions if any. I would also like to hear a few use cases which I can build for you.
Feel free to mark helpful and bookmark this article.
Thanks,
Sagar Pagar
Hello Sagar, thanks for responding,
So I need to update the field Users ( the recipients) in the form of one scheduled report to be dynamic values.
I have created the scheduled report with one user because this one will be always as default, but I need to add more users to the list.
I have selected the "conditional" option to create one script that makes a query to a table, I get an array variable with all the sysid for the users as unique.
So when I run the script on background to verify the values, I got the correct sysids :
example:
*** Script: assignedUnique = ed83fc7a1b0633806278edf9bc4bcb12,978178521baa77846278edf9bc4bcbdf,ce47701e1bae77846278edf9bc4bcbee, etc
so when I try to update the current form for the scheuled report to add the new recipients nothing changes and only send the report to the original default user that I put manually.
but when I execute it, nothing happens and is only sending the notification to first user that I enter manually in the field, is not adding/updating the other recipients, its like the script was ignored.
Any idea how can I make this work?
Hello Sagar, thanks for responding,
So I need to update the field Users ( the recipients) in the form of one scheduled report to be dynamic values.
I have created the scheduled report with one user because this one will be always as default, but I need to add more users to the list.
I have selected the "conditional" option to create one script that makes a query to a table, I get an array variable with all the sysid for the users as unique.
So when I run the script on background to verify the values, I got the correct sysids :
example:
*** Script: assignedUnique = ed83fc7a1b0633806278edf9bc4bcb12,978178521baa77846278edf9bc4bcbdf,ce47701e1bae77846278edf9bc4bcbee, etc
so when I try to update the current form for the scheuled report to add the new recipients nothing changes and only send the report to the original default user that I put manually.
but when I execute it, nothing happens and is only sending the notification to first user that I enter manually in the field, is not adding/updating the other recipients, its like the script was ignored.
Any idea how can I make this work?
Hello Cris,
You are on the Quebec version, and the current object in the conditional script is not working. hence it is not going to update the user's list. (Mentioned in above Article step:5)
You need to update the user's list by scheduled script.
1. Change this scheduled report to "On-demand" and save it.
2. Create a new scheduled job with "Automatically run a script of your choosing" with the same scheduling time. Refer: Automatically run a script of your choosing
Add this script and add you're on-demand scheduled report sys_id.
updateDynamicRecipients();
function updateDynamicRecipients(){
var scheduleReport = new GlideRecord('sysauto_report'); // glide the Scheduled report
scheduleReport.get("sys_id of report"); //Sys ID of your ondemand schedule Report
var recipients = [];
var tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var gr = new GlideRecord(tablename);
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());
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
}
Regards,
Sagar Pagar
Hi,
I have tried it but iam getting 2 mails. There is 'Time' field on both scheduled jobs so it might the thing iam getting 2 mails at same time. Could you please help me with this.
schedule report:
scheduled script execution:
Thanks.
The report is called, Expired Due Date, and is returning one record. I have added the assigned_to and assigned_to.manager columns. (note that the assigned_to.manager column header displays as 'Manager'.
This is the schedule for the report:
This is the Scheduled Script (it has the sys_id of the report):
updateUsersListWithManager();
function updateUsersListWithManager(){
var scheduleReport = new GlideRecord('sysauto_report'); // glide the Scheuled report
scheduleReport.get("1d2412021b2cac90499233fccd4bcba5"); //Sys ID of your schedule Report
var recipients = [];
var tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var gr = new GlideRecord(tablename);
gr.addEncodedQuery(query);
gr.query();
while (gr.next()) {
recipients.push(gr.assigned_to.sys_id.toString()); // push the assigned _to users in recipients array
recipientsManager.push(gr.assigned_to.manager.sys_id.toString()); // push the assigned _to managers in recipientsManager array
}
// gs.log(recipients.toString());
// gs.log(recipientsManager.toString());
var arrayUtil = new ArrayUtil();
arrayUtil.concat(recipients, recipientsManager);
finalRecipients = arrayUtil.unique(senderArray); // unique elements
scheduleReport.user_list = finalRecipients.join(',');
// gs.log("User list: " +current.user_list);
scheduleReport.update();
SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report
}
Any help with getting this working would be greatly appreciated.
Thanks!
Hi Sagar, thank you for responding.
Here is the screenshot of the report. The column labeled 'Assigned To' is the the assigned to user, and the column labeled 'Manager' is the assigned_to.manager. The sys_id is 1d2412021b2cac90499233fccd4bcba5
Here is a screenshot of the column configuration showing the Assigned to and Assigned to.Manager:
Here is the full screenshot of the schedule for the report:
Do you notice anything I am doing wrong here that would prevent it from working?
Thanks again for your support!
Hi Sagar, thank your for this article. I want to send the automatic email to the manager of the assignment group. I have created a report, a scheduled email and a script. But I cannot get it work.
The report:
The last column is the name of the manager.... Field from Task table.
The scheduled report:
The script:
updateUsersListWithManager();
function updateUsersListWithManager() {
var scheduleReport = new GlideRecord('sysauto_report'); // glide the Scheuled report
scheduleReport.get("46e8161447ace9100cea3b72e36d4376"); //Sys ID of your schedule Report
var recipients = [];
var tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var gr = new GlideRecord('task');
gr.addEncodedQuery(query);
gr.query();
while (gr.next()) {
recipientsManager.push(gr.assignment_group.manager.sys_id.toString()); // push the assignment group manager in recipientsManager array
}
var arrayUtil = new ArrayUtil();
finalRecipients = arrayUtil.unique(recipientsManager); // unique elements
scheduleReport.user_list = finalRecipients.join(',');
scheduleReport.update();
SncTriggerSynchronizer.executeNow(scheduleReport); //execute schedule report
}
What am I doing wrong?
Hi @Sagar Pagar ,
Thanks for this! it is pretty awesome! Just wondering if you could confirm something for me. This works absolutely perfect for reports that run on the incident table; but when I use it for incident_sla table, I get no values for the Manager column. Is there something I would need to do?
I can confirm that the report (on the incident_sla table) itself contains the assignment_group.manager column:
but when I output to the syslog, I can see no values for the recipients manager array (but there are quite a few commas, which seems like it is looking at something?):
Using the same exact scheduled script, but just updated the report to one that is for the incident table, you can see the recipients manager array populates:
Any ideas?
Thank you!
