When Date is Reached Automatically Send Email Notification to Requestor

[eric987dc]

We currently have a solution which uses the Service Catalog - Items. After a requestor requests this item and it goes through the entire approval process. At the end, it will have a start date. Whatever that start date may be, we are trying to somehow record that date and set a timer so that 2.5 years from that start date, an email will be sent to that same requestor asking for their review. This same process on will repeat again 5.5 years and 8.5 years if the end date is long enough. We will need to condition this process to repeat only the number of times required by the length of the request. 

We did not want to put a wait for condition in the workflow because we wanted that request to be closed/complete.

We also need the ability to ask a few questions and obtain metrics on the number of reviews sent/replied/and their answers. 

What/How is the best method of completing this? 

Thanks for any help!

[eric987dc]

Michael,

I agree. That's why I had tried to put a workflow condition so that the workflow does not launch until the 2.5 marker was met.

I will take a look in to scheduling a job - would that mean just writing a script to read from the custom table, calculate the date difference, then if it has surpassed send an e-mail?

Can the script within the job do all of that as well as create a catalog task?

Also importantly, are scheduled jobs trackable both in terms of metrics and when they were sent.

[Michael Ritchie]

Correct you will need to write a script that will

1. Query your custom table for records where Date <= Current Time or 30 days from current time, or whatever.  You can easily get the encoded query for this condition using the condition builder in a list and then right click on the end of the condition and choose copy query and use this in your script.

2. In your while loop (if records are found by the query), then you can send a notification, create a task, launch a workflow, really whatever you want since scripting is very flexible.

If you just want to send a notification, you may want to review Chuck Tomasi's Scriptless Scheduled Jobs solution as this fits very well in its use case and can provide example script too:

https://community.servicenow.com/community?id=community_blog&sys_id=ae9caee1dbd0dbc01dcaf3231f96193d

[Giles Lewis]

Your scheduled job will look something like this

var gr = new GlideRecord(your_custom_table);
// look for records whose time has arrived
gr.addQuery(some_date_field, '<', gs.nowDateTime());
// ... and which have NOT already been processed
gr.addQuery(some_condition_to_exclude_processed_records);
gr.query();
while (gr.next()) {
    // trigger an event
    gs.eventQueue(...);
    // mark this record as processed so that it will not get picked up
    //  next time this job runs
    gr.some_field = some_value;			  
    gr.update();			  
}

The trick is that you run the job every night, but it only processes records whose "date has arrived" and which have not already been processed. Inside the script you mark these records as processed so that they will not get picked up again the following night.

If you need to do other updates you can do them inside the loop. If you only want a notification, it can be triggered from the event.

[eric987dc]

I am starting to develop the event based on the script you provided.

The outstanding question is, will these events be reportable and auditable?

I am still trying to figure out how to trigger an email notification to the original requestor of the requested item.

Thanks for the tips so far!

[Giles Lewis]

As to the notification recipient:

The second argument to gs.eventQueue is a glide record. In the example above, I should have written gs.eventQueue('name_of_event', gr, parm1, parm2). The name_of_event is something you define when you add the event to the event registry. The glide record (2nd arg) should be of a type that matches the table name in the event registry. (The other 2 fields in the event registry, description and fired_by, are strictly informational).

To define a notification based on an event you must be in "Advanced view". Once you specify the name of your event, the system will know the table type.  When you define the message "Who will receive" and "What it will contain", you can reference fields from this table. Suppose your table type is your custom table, and it contains a field named u_req_item.  Then in "Users/Groups in fields" you should be able to dot-walk your way up to the requestor (u_req_item -> request -> requested_for).

When you call gs.eventQueue(), you can put anything you want in parm1 and parm2 (or omit them). Sometimes people use them for the sys_id of the message recipient. On the Notification form under "Who will receive" there are check boxes for "Event parm 1 contains recipient" and "Event parm 2 contains recipient". This allows the javascript that generated the event to control the recipient. However, I normally prefer the other method because it is clearer to anyone reviewing the notification definition.

Events are cool because they make it really easy to test your notifications. Suppose you have a record and you know the sys_id and you want to generate a notification. You just run a 3-line background script as follows.

var gr = new GlideRecord('my_table_name');
gr.get(sys_id);
gs.eventQueue('my_event_name', gr);

This code generates an event which in turn will trigger the notification. 

As to reporting:

Records in the sysevent table are not retained for very long because there are so many of them. They typically get deleted after a few days. They can be useful for a system administrator to troubleshoot (did an event happen last night?) but they should not be used for reporting.

For reporting you should make sure you write any relevant information to a table that you have control over, such as sc_req_item, or your custom table.