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]

Thanks! I'll make the updates and check later this evening.

[MStritt]

I thought I understood what you were saying, but I guess not after looking at it.

>>> I just realized that when posting the code here the "javascript :" gets replaced by "javascript:"

>>> replace the ":" here. 

Not sure what I need to do here.

Also, what are the queries for the links you provided?  Not sure what the output should show.

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

This is what I'm seeing. I am updating javascript with One Year ago. Is that correct?

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

Again, replacing javascript with One Year Ago

Are these queries used to find the group of Contacts we want to use the reminder emails for?  If so, we'd need to look at all Contacts where User Account Locked is false and Account is not Thales Internal. But, it doesn't matter when the Contact record was created. That's only for Contacts that have never logged in (Last login time is empty). Since they've never logged in, we can't use the 1 year condition against their last login time (if that makes sense). For the ones that have never logged in, I'd like to use the date when they were created to determine whether to lock them out. So, 'Created' condition would only really apply to the Contacts that have never logged in.  If you can think of a better way to handle those Contacts (last login time is empty), I'm open. Should we create a separate scheduled job/script to deal with just the Contacts that have never logged in? 

[MStritt]

Rene,

I thought I had replied to you last Friday. Looks like it didn't save my reply! 

I thought I knew what you were talking about:

"I just realized that when posting the code here the "javascript(colon)" gets replaced by "javascript:" which you have probably copied over and invalidates the query. Make sure you replace it with a colon".

I guess I'm not sure what/where to update with a colon.

Also, what are the 2 links/queries supposed to be showing? The list of Contacts the email reminder code is running against?

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

I'm replacing javascript with 'One year ago'.

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

I'm replacing javascript with 'One year ago'.

The query should just be User Account Locked is false AND

Account is not Thales Internal.

The reason I added Created before One year ago, is specifically for the Contacts where Last login time is empty. Since they've never logged in, we can't use the Last login time is before One year ago since they've never logged in. So, I added a condition that would address those contacts only. If that makes sense??

I'm wondering if we should not include Contacts that have never logged in (Last login time is empty) for this script/code. Maybe create a separate script/code scheduled job to address that use case specifically? I'm open to suggestions.

[Rene El Hamzh]

For some reason when posting code/text here that contains "javascript(colon)" it is changed to "javascript:". 

Code before posting:

Code after posting:

The links were supposed to show what records the queries would return. First one was for contacts not logged in for 335 days + your other conditions. Second one was for contacts not logged in for more than 365 days + your other conditions. 

You could leave everything in the same scheduled job as it needs to check both cases every day. Here is the adjusted code. When copying the code make sure to change the javascript colon in the query of the lockAcount function.

Send out reminders where:

1. Locked out = false
2. Account is not "Thales Internal"
3. Last login time is 335, 351 or 364 days ago
   
   

Lock contacts where:

1. Locked out = false
2. Account is not "Thales Internal"
3. Last login time > 365 days

OR 

1. Locked out = false
2. Account is not Thales Internal
3. Last login time is empty
4. Created on > 365 days

function sendReminder(days, event) {

    var query = "locked_out=false^account!=58ba9ccb4fddb640873b69d18110c744^last_login_timeRELATIVELT@dayofweek@ago@" + days + "^last_login_timeRELATIVEGT@dayofweek@ago@" + Number(days + 1);

    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^last_login_time<javascript&colon;gs.beginningOfOneYearAgo()^NQlocked_out=false^last_login_timeISEMPTY^sys_created_on<javascript&colon;gs.beginningOfOneYearAgo()^account!=58ba9ccb4fddb640873b69d18110c744";


    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();

[MStritt]

Thanks Rene! I really appreciate the time you're spending with me on this. Helps greatly. 

Made the updates in the code and ran the script. I started with the following totals for each query:

103 Contacts

Locked out = false
Account is not "Thales Internal"

Last login time > 365 days

6 Contacts

Locked out = false
Account is not Thales Internal
Last login time is empty
Created on > 365 days

After running the script, the 103 were set to locked, but the 6 for the second query were not locked. They were not updated.

Notifications were successfully sent for 1 day, 14 days, and 30 days.

So, the only thing that didn't seem to work, were the 6 where last login time was empty, not locked, not Thales Internal, and created >365 days. There were not locked.