Can scheduled reports send personal summaries?

peterraeves · ‎03-25-2016

Before I go out scripting, I was wondering if there was a simple way to do the following. I want to send out reports on a regular interval, that contain a summary of the incidents logged by a certain user. Every time the user should receive a pdf with the list containing all incidents he logged from the last time this report was send.

Therefore, we don't know up front who we will be sending to and we don't want to make a scheduled report for every user, and only sending it when it isn't empty. I haven't tried this yet, but I could probably have a script that loops over all assignees for that period of time, that sets up scheduled reports that would run as that user and that would have the list conditioned on "assigned_to = gs.getUserID()", but I was wondering if there was a more OOTB, cleaner way to do this.

peterraeves · ‎06-01-2016

I did fix this problem. What you need to do is

1) Create the report to use "dynamic = me". For example "opened by - is dynamic - me"

2) Create a template for the schedule you want to run

3) Create a Scheduled Script that will use the template to create new ScheduleOnce schedules for all users or groups you want. The script will set the "run as" field to the specific user and let the schedule run for that user.

In step 2) I originally used a Script Include to create the schedule:

         var scheduled_report = new GlideRecord('sysauto_report');

         //Set the scheduled job to active

         scheduled_report.active = 'true';



        //We add report title and user name to the name for clarity

        scheduled_report.name = ''

                 + 'Scheduled Email Report of '

                 + report.title

                 + ' - '

                 + user.name;



        //Run report as given user to show only his records

        scheduled_report.run_as = user.sys_id;

        //Sent report to same user, so he receives his own report

        scheduled_report.user_list = user.sys_id;

        //Only send the report once

        //It will be rescheduled if needed

        scheduled_report.run_type = 'once';

        scheduled_report.report_title = subject;

       scheduled_report.report_body = body;

       scheduled_report.report = report.sys_id;

       scheduled_report.omit_if_no_records = omit;


        //Now, insert the newly created scheduled job to start running

        //As it is a "once" job, it will run automatically

        var answer = scheduled_report.insert();

The function had parameters like "user" and "report", but I just recently discovered that there is an OOTB script include for this functionality. So it might be better to check that one out as well. It's an object called "ScheduleOnce" and is an extension of the "Schedule" object.

View solution in original post

peterraeves · ‎09-19-2017

Yes. It will create a new scheduled report for every single user.

From a performance pov, it is probably best to limit the number of users (eg: filter out inactive users, only take the users which have open RITMs, etc).

FYI, after a while your scheduled report list will become big, so it might be good to run some kind of cleanup script which removes these 'RunOnce' scheduled jobs after they are finished. But that is all up to you of'course.

FYI, this script does not take into account 'included' reports. If this is something you would need, you should modify the script to make a copy of the included templates as well.

Russ Heard3 · ‎09-19-2017

Great stuff thanks for helping me understand it 🙂

Russ Heard3 · ‎09-20-2017

Sorry to bother you again Peter - if you have time could you see if I'm doing something wrong here?

I have the script working to some extent - a copy of the report is run for each of the number of users extracted by the script, but both reports are Run As myself, and the User_List does not get set

Script:

//var str = "active=true^roles=approver_user";

//var str = "active=true^roles=approver_user^name=russ heard";

var str = "active=true^roles=approver_user^last_nameSTARTSWITHhea"; // limit users to 2 users, myself & 1 other

var gr = new GlideRecord('sys_user');

gr.addEncodedQuery(str);

gr.query();

//Get template

var template = new GlideRecord('sysauto_report');

template.get('6a934c46db154f40af053b2ffe9619a1'); //Scheduled Job - 'Scheduled Execution of RITMS Awaiting Approval'

while (gr.next()) {

template.sys_id = '';

template.user_list = gr.user.sys_id;

template.run_as = gr.user.sys_id;

template.run_type = 'once';

template.insert();

}

Scheduled Jobs List:

2 reports run, as expected - 2 users were returned by the encoded query.

But both were Run As myself

Scheduled Execution:

User distribution is not set?

Thanks in advance.

Russ

peterraeves · ‎09-20-2017

It might be a reference vs value issue in javascript. Try the following code, it's what I am using myself

template.run_as.setValue(gr.getValue('sys_id'));

template.user_list.setValue(gr.getValue('sys_id'));

To be sure you could add the following lines and check the logs after you run the script, to see whether the values are at least correct and/or correctly set

gs.info('User ' + gr.getDisplayValue() + ' with sys id ' + gr.getValue('sys_id'));

template.run_as.setValue(gr.getValue('sys_id'));

template.user_list.setValue(gr.getValue('sys_id'));

gs.info('template ' + template.getValue('run_as') + '/' + template.getValue('user_list'));

Russ Heard3 · ‎09-20-2017

Brilliant! Working as expected now, thanks for your time 🙂

Cheers

Russ