HELP! How to dynamically set recipients of an existing Scheduled Report to email addresses that are in the report's output?

[scottbjorke]

I have a lot of Scheduled Reports that run a given Report whose output includes email addresses. Presently, my Scheduled Reports get emailed only to whoever I have pre-defined as the STATIC list of email addresses, per the Scheduled Reports UI (in this case, 'bob@mycomany.com'). From there, those recipients have to open the attached report, spot the email address on each row, and FW the report attachment directly to those email addresses from the report output. This seems unnecessary -- I'd rather "cut out the middle man" and have SN set the recipients equal to the email addresses I find within the report.

I assume this means:

- I can simply 'utilize' the same Report that is already defined in my Scheduled Report

- set Conditional = TRUE then insert my scripting logic into the Scheduled Report definition

- walk through some kind of GlideRecord that essentially IS my predefined Report

- get each record's email address (which is one of its report columns) -- but be aware I don't know exactly how to get its exact name because I'm dot-walking and don't have admin rights to utlize the SN log

- appending it to something like "current.address_list" (with a trailing comma to separate each email address)

- that current.address_list can hold a long comma-delimited list of emails as a string value and is not an array[] instead

- retuning TRUE to indeed trigger the report.

Of course, any or all the above assumptions may be really lousy     And of course, there's probably a much easier, simpler way to do what I want to do.

Please note I do not have admin privileges. I'm just a Change Manager trying to reduce operational waste and learn more about leveraging SN's capabilities myself.

Here's header and the script of my latest attempt.   Would really appreciate it if someone could take a look and reply with ideas/comments that are indeed helpful.   That also includes giving me code examples that essentially replace mine (ie, starts over).

Please note I have confirmed the below approach does successfully send my report output to the STATIC recipient from the script AND to the email address predefined in the UI below.   I just can't figure out how to glean additional email recipients from the addresses found in the report output.

---

// REMINDER: THE BELOW SCRIPT EXECUTES ONLY IF "CONDITIONAL" IS CHECKED ABOVE

// Include the Script Include
gs.include('ReportEmailList');

// Set the STATIC recipients first
var email_recipients = 'john.doe@mycompany.com';
var next_email = ' ';

//Execute the scheduled report within a local GlideRecord
var rec = new GlideRecord('sysauto_report');
rec.get('name', 'Scott some rows to return various Release Owners'); // this is same as what's in the header
rec.query();

// cycle through each report row (if any), get the DYNAMIC recipient value, and append it to the recipients string with a leading comma
if (rec.getRowCount() > 0) {
      while (rec.next()) {
                next_email = rec.GetValue('u_owner.email');    
                email_recipients += ',' + next_email;
      }
}

current.address_list = email_recipients;       // FYI - I have verified this line works to affect email recipients manually -- just need help setting 'email_recipients' variable using report output
// Need to set this true for condition script to run
answer = true;

---

PS   Here's how my Report is defined:

[palmen]

You might want to change the following

next_email = rec.GetValue('u_owner.email');

replace with rec.getValue('u_owner.email');

I did a test with the follwoing code and it worked for me.

The GlideRecord on incident need to have querys the same way as the scheduled report is build up (just using number in this test to narrow it down to 1 incident)

// Include the Script Include
gs.include('ReportEmailList');

// Set the STATIC recipients first
var email_recipients = 'john.doe@mycompany.com';

//Execute the scheduled report within a local GlideRecord
var gr = new GlideRecord('incident');
gr.addQuery('number', 'INC0010079');
gr.query();
while (gr.next()) {
   email_recipients += ',' + gr.opened_by.email;
}
current.address_list = email_recipients;
current.update();


// Need to set this true for condition script to run
answer = true;

[Rakesh Mamidi]

Try below script

var scheduleReport = new GlideRecord('sysauto_report');

  scheduleReport.get('b24f055edb04f600cda070f5bf96193b'); //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.opened_by.email.toString()];

  }

  var arrayUtil = new ArrayUtil();

  var finalRecipients = arrayUtil.unique(recipients);

  scheduleReport.address_list = finalRecipients.toString(); //get unique recipients

  scheduleReport.update();

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

[palmen]

Didn't know you could get report.table to get the desired table and report.filter for the filter. Thanks for info

[palmen · Accepted solution]

I modified your solution a bit and came up with the following to fit the requirement.

I'm not sure why you set a static recipient in the code, this could be written in the Email addresses field directly.

Instead of adding the users to the Email addresses field I added them to the Users field instead (see below script)

var scheduleReport = new GlideRecord('sysauto_report');
scheduleReport.get(current.sys_id); //Sys ID of your schedule Report

var tablename = scheduleReport.report.table;
var query = scheduleReport.report.filter;
var users = '';

//Execute the report as a GlideRecord with the set table and condition
var gr = new GlideRecord(tablename);
gr.addEncodedQuery(query);
gr.query();
while (gr.next()) {
   users += ',' + gr.opened_by;
}

current.user_list = users;
current.update();

// Need to set this true for condition script to run
answer = true;

This script works fine for me when testing in my developer instance.