I need to send a scheduled report based on changing ticket assignees

christinammarre · ‎02-07-2018

Hello, I am looking for a way to send a scheduled report every morning to the ticket assignees who have not updated their tickets within the past 48 hours. These ticket assignees are part of a group called Service Desk in ServiceNow. I have created the report in the reporting module and have it grouped by ticket assignee, but am stuck as to how to schedule it so that it will send to the folks that the tickets are assigned to. Any suggestions would be much appreciated.

kmolson73 · ‎02-07-2018

Hi Christina,

I did something similar pertaining to Project Managers and their resource plans that were expiring in 30 days.

Try this:

Create a new Report for the desired table, add your conditions and use the dynamic condition for your assigned to field (assigned to is(dynamic) to me)
Create a new Scheduled Report, name it and select your report, set the schedule to run on demand. Leave the Run as and Users blank. Check the box to omit if no records. Right click and save the scheduled report, then right click and copy the sys_id.
Create a new Scheduled Job, select automatically run a script of your choosing. Schedule it to run when you want it to (so every morning at a specific time). Add the below script which does the following; queries your ServiceNow assignment group to get the list of users, gets the template (Scheduled report) and for each user in the group run the report using that user as the Run As and also who the report is sent to.
Test it by Executing your Scheduled Job, then look at your Scheduled Reports you should see that there is one ran for each user in that group, then check your emails log to see which users got the notification.

//Get all users from Assignment group

var gr = new GlideRecord('sys_user_grmember');

gr.query('group','c70154e4b43274008b98891e1937b8a0'); //Use the sys_id of your assignment group here

gr.query();

//Get Template for scheduled report

var template = new GlideRecord('sysauto_report');

template.get('26e5424e4f6bc740539085c98310c7de'); //Use the sys_id of your scheduled report here

//Run the scheduled report as that user, repeated for each user in the group

while (gr.next()){

template.sys_id = '';

template.run_as = gr.user.sys_id;

template.user_list = gr.user.sys_id;

template.run_type = 'once';

template.insert();

}

View solution in original post

yandp · ‎09-03-2020

Hello kmolson, Thank you very much for this sharing.

I tested and working.

I have some question about this, if i have multiple user groups and big number of user group.

how can i managed for multiple group?

I really new to ServiceNow, hope to get some advise.

Thank you very much.

Regards.

sharan16 · ‎06-27-2024

Hi,

I have a similar requirement. I need to send a scheduled report. I have a list of CIs and each Ci has ownedby.manager.

For eg: Each Ci has different Ownedby.manager - Ownedby.manager should only receive CI lists for which he is ownedby's manager. I have written this script. Please check if anything is wrong. It is currently executing and sending me email 12 times of the same extract. I am owned by's manager for 12 CIs.

var reportgr = new GlideRecord('cmdb_ci');

reportgr.addEncodedQuery('owned_by.active=false^install_statusIN1,800^owned_by.managerDYNAMIC90d1921e5f510100a9ad2572f2b477fe');

reportgr.query();

//Get Template for scheduled report

var template1 = new GlideRecord('sysauto_report');

template1.get('8b671aad470b82506aedad22036d4316'); //Use the sys_id of your scheduled report here

//Run the scheduled report as that user, repeated for each user in the group

while (reportgr.next()){

template1.sys_id = '';

template1.run_as = reportgr.owned_by.manager.sys_id;

template1.user_list = reportgr.owned_by.manager.sys_id;

template1.run_type = 'once';

template1.insert();

Lee Nichols · ‎05-02-2019

Excellent this is very helpful thank you.

pooja123 · ‎06-18-2019

I tried this in my personal instance for each assignment group instead of users. But dynamic filter is not showing any results. Could you please help me for the assignment group dynamic filter.