Scheduled Script / Notifications

[MStritt]

I currently have a scheduled script that looks at the last login time (logging into our Support Portal) of Contacts. If they haven't logged in >1 year (365 days), we are locking their account. We would like to send 3 notifications to these Contacts over a period of time leading up to the actually locking of their account.

First notification 30 days (335 days not logged in) in advance of action being taken.
Second notification 2 weeks (351 days not logged in) in advance of action being taken.

Third and final notification 1 day (364 days not logged in) in advance of action being taken.

What's the best way of creating these notifications? 

[MStritt]

Hi Rene,

I've updated the conditions of the query. So, probably changes the code you sent??

locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^last_login_timeISEMPTY^ORlast_login_time<javascript&colon;gs.beginningOfOneYearAgo()^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()

This is what I currently have. I haven't updated the code yet, to reflect your inputs. 

// Build the encoded query for customer_contact
var encodedQuery = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^last_login_timeISEMPTY^ORlast_login_time<javascript&colon;gs.beginningOfOneYearAgo()^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()";

// Query the customer_contact table based on the encoded query
var contactGR = new GlideRecord('customer_contact');
contactGR.addEncodedQuery(encodedQuery);
contactGR.query();

// Loop through the contacts and deactivate them
while (contactGR.next()) {
    contactGR.locked_out = true;
    contactGR.update();
}

Also, I assume where you have "reminder.30 days" will be the actual name of the Event/s I create?

lock_out_reminder.30days

lock_out_reminder.14days

lock_out_reminder.1day

[Rene El Hamzh]

You just need to adjust the query like so. Yes, the event names need to be changed if you didn't name them the same as in my example. 

function sendReminder(days, event) {

    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@" + days + "^last_login_timeRELATIVEGT@dayofweek@ago@" + Number(days + 1) + "^ORlast_login_timeISEMPTY";

    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        gs.eventQueue(event, contactGR, contactGR.getUniqueValue()); 
    }

}


function lockAccount() {
    
    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@365^ORlast_login_timeISEMPTY";


    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        contactGR.setValue("locked_out", true);
        contactGR.update();
    }
}

// Send reminders
sendReminder(30, "reminder.30days");
sendReminder(14, "reminder.14days");
sendReminder(1, "reminder.1day");

// Lock accounts
lockAccount();

[MStritt]

Scheduled job ran about 2 hours ago, but it didn't send out any notifications. Here is my script, one of the events I created (the other 2 are identical except for the event name), and screenshot of the corresponding notification.

function sendReminder(days, event) {

    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@" + days + "^last_login_timeRELATIVEGT@dayofweek@ago@" + Number(days + 1) + "^ORlast_login_timeISEMPTY";

    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        gs.eventQueue(event, contactGR, contactGR.getUniqueValue()); 
    }

}


function lockAccount() {
    
    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@365^ORlast_login_timeISEMPTY";


    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        contactGR.setValue("locked_out", true);
        contactGR.update();
    }
}

// Send reminders
sendReminder(30, "lock_out_reminder.30days");
sendReminder(14, "lock_out_reminder.14days");
sendReminder(1, "lock_out_reminder.1day");

// Lock accounts
lockAccount();

[MStritt]

Also, script doesn't appear to be marking the 29 contacts as locked, that match the condition. I'm showing 1 contact where their last login time is empty (and created more than 1 year ago), 6 where last login time is 01/04/23, and 22 where last login time is 01/05/23 up to the current time today.

[Rene El Hamzh]

I just realized that when posting the code here the "javascript(colon)" gets replaced by "javascript&colon;" which you have probably copied over and invalidates the query. Make sure you replace it with a colon. Also, I've mixed something up with the days passed to the function. See corrected code below.

I have tested this on my PDI, but I recommend that you also validate that the encoded queries return the correct records in the list view. Just replace "YOURINSTANCE" with the name of your instance and also replace the "&colon;" here. 

Not logged in for 335 days:

https://YOURINSTANCE.service-now.com/customer_contact_list.do?sysparm_query=locked_out=false^account...

Not logged in > 365 days:

https://YOURINSTANCE.service-now.com/customer_contact_list.do?sysparm_query=locked_out=false^sys_cre...

function sendReminder(days, event) {

    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@" + days + "^last_login_timeRELATIVEGT@dayofweek@ago@" + Number(days + 1) + "^ORlast_login_timeISEMPTY";

    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        gs.eventQueue(event, contactGR, contactGR.getUniqueValue()); 
    }

}


function lockAccount() {
    
    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^last_login_timeRELATIVELT@dayofweek@ago@365^ORlast_login_timeISEMPTY";


    var contactGR = new GlideRecord("customer_contact");
    contactGR.addEncodedQuery(query);
    contactGR.query();

    while (contactGR.next()) {
        contactGR.setValue("locked_out", true);
        contactGR.update();
    }
}

// Send reminders
sendReminder(335, "lock_out_reminder.30days");
sendReminder(351, "lock_out_reminder.14days");
sendReminder(364, "lock_out_reminder.1day");

// Lock accounts
lockAccount();