Delete scheduled Events

[MacSWW]

All,

I've followed this article: Creating a Simple Reminder Email - ServiceNow Wiki and created a new Event called change.reminder so that I can trigger an email to remind people when their Change Request 'Planned End Date' has passed. This is working perfectly, but can't figure out how to delete these Events if the Planned End Date is changed before the email is triggered.

For example, if the Planned End Date was originally set on the Change Request for 1pm on 17/07/2014 and the Change is submitted, an Event is logged and sits in the 'Ready' state until that time. If the Planned End Date is then changed to 12:30pm on 17/07/2014, a new Event is created, but the old one stays in the Event queue. Basically, the user will end up getting two emails, one at 12:30pm and one at 1pm.

Is there a way I can delete the first Event when the second one is created? I'm not very good at javascript, so any examples of how to add to the Business Rule to delete or re-schedule these Events would be really helpful!

Thanks,

Mac

[tltoulson · Accepted solution]

Hi Mac,

Deleting scheduled events is just like deleting any other record once you understand how Events work in ServiceNow.   When you queue an event through gs.eventQueue or gs.eventQueueScheduled, it actually creates a record on the sysevent table.   The key fields to note on this table are:

Name: The name of the event

Table: The table from which the event fired

Instance: The sys id of the record from which the event was fired

State: Whether or not the event has processed

Process on: When the event should be processed

The last one is very important.   A scheduled job actually processes the event queue by periodically retrieving all the events whose Process on date/time has already passed and processing the event's actions.   So until the scheduled job processes it, the event record will remain in the Ready state.   Here is how we can leverage this information:

Business Rule

When: After

Update: True

Condition: current.end_date.changes()

Script:

// Self executing anonymous function is not necessary but a good practice in Business Rules

(function() {

         var gr = new GlideRecord('sysevent'); // We need to query the sysevent table

         gr.addQuery('state', 'ready');   // We only want the event in the ready state

         gr.addQuery('name', 'EVENTNAMEHERE'); // Change the event name to the event name you are using

         gr.addQuery('table', 'change_request');

         gr.addQuery('instance', current.sys_id + ''); // Get the event that has been created for this record

         gr.setLimit(1); // Assuming there is only one ready event active for this record at a time, this can improve performance

         gr.deleteMultiple();



         /* Schedule your new event here*/

})();

In theory, you could also simply update the Process on field instead of deleting the event but I would do it this way to be on the safe side.   Also, instead of deleting the event, you could update the state to processed.

[MacSWW]

Travis you're a legend

Thanks for the explanation and script - that's exactly what I was after.

For anyone else who reads this, I added to this a bit more to delete the 'ready' entries when the Change Request gets closed, otherwise they stay in sysevents and people will get reminders for calls already closed.

I have 3 Business Rules now which I hope deal with all eventualities - I'm sure someone will let me know a more streamlined way to do this if there is one

1)       Initial Event creation when the Change gets logged:

                  Name: Change Request End date Reminder

                  Table: Change Request

                  When: after

                  Insert = true

                  Condition: current.end_date.changes() && !current.end_date.nil()

                  Script:

gs.eventQueueScheduled("change.reminder", current, gs.getUserID(), gs.getUserName(), current.end_date);

2)         Rule for when the Planned End Date gets changed:

                  Name: Change Request End Date Reminder -update

                  Table: Change Request

                  When: after

                  Update = true

                  Condition: current.end_date.changes() && !current.end_date.nil()

                  Script:

// Self executing anonymous function is not necessary but a good practice in Business Rules   

(function() {   

   var gr = new GlideRecord('sysevent'); // We need to query the sysevent table   

       gr.addQuery('state', 'ready');   // We only want the event in the ready state   

       gr.addQuery('name', 'change.reminder'); // Change the event name to the event name you are using   

       gr.addQuery('table', 'change_request');   

       gr.addQuery('instance', current.sys_id + ''); // Get the event that has been created for this record 

       gr.setLimit(1); // Assume only one 'ready' event active for this record (can improve performance)

       gr.deleteMultiple();   



// Create new Event when the Current End date changes

gs.eventQueueScheduled("change.reminder", current, gs.getUserID(), gs.getUserName(), current.end_date);

})();

3)         Rule for deleting the 'ready' event when the Change gets closed:

                  Name Change Request End Date Reminder -closed

                  Table: Change request

                  When: after

                  Update = true

                  Condition: current.state.changesTo(3) || current.state.changesTo(4) || current.state.changesTo(7) || current.state.changesTo(9) //Change this to match your equivalent State values obviously

                  Script:

// Self executing anonymous function is not necessary but a good practice in Business Rules   

(function() {   

   var gr = new GlideRecord('sysevent'); // We need to query the sysevent table   

       gr.addQuery('state', 'ready');   // We only want the event in the ready state   

       gr.addQuery('name', 'change.reminder'); // Change the event name to the event name you are using   

       gr.addQuery('table', 'change_request');   

       gr.addQuery('instance', current.sys_id + ''); // Get the event that has been created for this record 

       gr.setLimit(1); // Assume only one 'ready' event active for this record (can improve performance)

       gr.deleteMultiple();

})();

Thanks again Travis and I hope this helps someone else out.

Cheers,

Mac

[ken_tang]

This is amazing.   Thanks so much Travis.

To add onto this a little bit more, due to "table rotations", by default; any event scheduled for longer than 7 days will automatically be deleted.   I went into System Definitions -> Table Rotations -> sysevent and modified the duration so that reminders can be set a little further than 7 days.   Of course, this probably isn't the best in terms of performance on the system, but I don't see any other way around this.

Anyone figured something else out?   Reminders using workflows are do-able, but how can we deal with changes in the reminder date?

[chanikya]

Hi,

Same issue i have here...................

For example, if the Alert Due Date was originally set on the HR Form  Request for 10:04:00AM on 27/04/2018 and the form is submitted, an Event is logged and sits in the 'Ready' state until that time. If the Alert Due Date  is then changed to 10:06:00AM on 27/04/2018, a new Event is created, but the old one stays in the Event queue. Basically, the user will end up getting two emails, one at 10:04:00AM and one at 10:06:00AM.

 still it is triggered two times , why it was happend

To delete old Event queue i have written B.Rule script,, To retrieve this  B.Rule script was written here, by using this it is not creating any mail triggers at least one time too......

BR: Update, after 

Table : u_hr_form

Condition:current.u_alert_due.changes()

(function () {

// Add your code here
var gr = new GlideRecord('sysevent'); // We need to query the sysevent table
gr.addQuery('state', 'ready'); // We only want the event in the ready state
gr.addQuery('name', 'alert_due'); // Change the event name to the event name you are using
gr.addQuery('table', 'u_hr_form');
gr.addQuery('instance', current.sys_id + ''); // Get the event that has been created for this record
gr.setLimit(1); // Assuming there is only one ready event active for this record at a time, this can improve performance
gr.deleteMultiple(); 
/* Schedule your new event here*/

})();