Scheduled job is not querying the records as expected

Question

I have a schedulable class(entire code is in schedulable class), which deactivates the users based on thier lastLoginDate(if lastLoginDate > 30 days) We have scheduled the job in production, which runs everyday at 2:00 AM .

Below is the query basically which gets me the users list who needs to be deactivated.

public with Sharing class DisableInactiveUserScheduler implements Schedulable {

private final String DISABLE_NOTIFICATION_WARNING = 'Account Management User Deactivation Warning';
private final string ADMIN_NOTIFICATION = 'Account Management User Deactivation Email to Admins';
private final String PROFILE_ABC_COMMUNITY_USER = 'ABC Community User';
private DateTime notificaficationStartDate = System.today().addDays(-Integer.valueOf(Label.InactiveUserNotificationStartDate));//set to 24
private DateTime notificaficationEndDate = System.today().addDays(-Integer.valueOf(Label.InactiveUserNotificationEndDate));  //set to 30  

public void execute(SchedulableContext sc) {   
    try{
        sendEmailNotifictionToIdleUsers();
        disableUsersSendEmailNotification(); 
    }catch(Exception ex) {
        System.debug('Scheduled run failed : ' + ex.getMessage());          
    }        
}

public void sendEmailNotifictionToIdleUsers(){
    List<user> idleUsersBeforeExclusion = getIdleUsers();
    List<user> idleUsers = exculdeAccountsFromUserList(idleUsersBeforeExclusion);
    if(!idleUsers.isEmpty()){
        sendMail(idleUsers, DISABLE_NOTIFICATION_WARNING);
    }
}

public void disableUsersSendEmailNotification(){
    List<user> usersToInactivateBeforeExclusion = getUsersToDeActivate();
    List<user> usersToInactivate = exculdeAccountsFromUserList(usersToInactivateBeforeExclusion);
    List<user> deactivateUsers = new  List<user>();
    if(!usersToInactivate.isEmpty()){
        for(user userRec : usersToInactivate){
            userRec.IsActive = false;
            deactivateUsers.add(userRec);  
        }
        UserDAO userDA = new UserDataAccessor();
        try{
            if(!deactivateUsers.isEmpty()){
                userDA.updateUsers(deactivateUsers);
            }
        }catch(DmlException e){
            System.debug('Exception in  method(disableUsersSendEmailNotification)of apex class( DisableInactiveUserScheduler) '+e);
            ErrorHandlingUtil.addNewErrorLog('DisableInactiveUserScheduler:disableUsersSendEmailNotification', 'Error occured while updating users: ' 
                                             +e.getMessage()+' -~- StackTrace: '+e.getStackTraceString(), deactivateUsers);     
        }
        sendMail(deactivateUsers, ADMIN_NOTIFICATION);
    }
}

@TestVisible private List<User> getIdleUsers() {
    DateTime notificaficationStartDateWarning = notificaficationStartDate+1;
    List<user> inactiveUserEmailNotificList = [SELECT Id, LastLoginDate, Email, Name, FederationIdentifier, Username, Profile.name
                                    FROM User 
                                    WHERE IsActive = true 
                                    AND Profile.name <> :PROFILE_ABC_COMMUNITY_USER
                                    AND LastLoginDate < :notificaficationStartDateWarning
                                    AND LastLoginDate >= :notificaficationEndDate
                                    AND Username LIKE '%@abc.com%'];
    return inactiveUserEmailNotificList;
}
@TestVisible private List<User> getUsersToDeActivate() {
    List<user> usersTodeactivateList = [SELECT Id, LastLoginDate, Email, IsActive, Name, FederationIdentifier, Username, Profile.name
                                        FROM User 
                                        WHERE IsActive = true 
                                        AND Profile.name <> :PROFILE_ABC_COMMUNITY_USER
                                        AND LastLoginDate < :notificaficationEndDate
                                        AND Username LIKE '%@abc.com%'];
    return usersTodeactivateList;
}  

@TestVisible private List<user> exculdeAccountsFromUserList(list<user> UsersDisableAndNotificList) {
    list<user> users = new list<user>();
    string usrName;
    for(user userrec : UsersDisableAndNotificList){
          if(!(System.Label.ServiceAccountsToExclude.contains(userrec.username.Split('@').get(0)))){ 
            users.add(userRec);
        }
    }
    return users;
}

@TestVisible private EmailTemplate getEmailTemplate(String templateName) {
    EmailTemplate emailTemp = [SELECT Id, Name, HtmlValue, Body, Subject
                               FROM EmailTemplate 
                               WHERE Name = :templateName];
    return emailTemp;
}

public void sendMail(List<user> Users, string emailTemplateName) {
    List<Messaging.SingleEmailMessage> emails = new List<Messaging.SingleEmailMessage>();
    Messaging.SingleEmailMessage email;
    OrgWide_Address_DCMS_Admin__c Address = OrgWide_Address_ABC_Admin__c.getOrgDefaults();
    string FROM_ADDRESS_ID = Address.OrgWide_Address_DMMA_Admin_Id__c;
    if(emailTemplateName == DISABLE_NOTIFICATION_WARNING){
        EmailTemplate emailTempRec = getEmailTemplate(emailTemplateName);
        for(User userRec : Users){
            email = new Messaging.SingleEmailMessage(); 
            email.setTargetObjectId(userRec.Id);
            email.setOrgWideEmailAddressId(FROM_ADDRESS_ID);
            email.setSaveAsActivity(false); 
            email.setTemplateId(emailTempRec.Id);
            emails.add(email);
        }
    }else if(emailTemplateName == ADMIN_NOTIFICATION){
        email = new Messaging.SingleEmailMessage();
        final string SUBJECT_TO_ADMINS = 'Account disabled due to inactivity';
        String[] toAddresses = new String[] {label.DMAAccountAdmins}; 
        string todaysdate = String.valueOf(Date.today()).removeEnd(' 00:00:00');
        String htmlBody = 'Hello,<p/>The following individual accounts were disabled due to inactivity on '+todaysdate+'.<br/>';
        htmlBody += '<table style="width:50%"> <tr> <th style="text-align: left">Name</th>';
        htmlBody += '<th style="text-align: left">Federation ID</th> </tr>';
        for (User userRec : Users){
            htmlBody += '<tr> <td style="width:50%">'+userRec.name;
            htmlBody += '</td> <td style="width:50%">'+ userRec.FederationIdentifier + '</td> </tr>';
        }
        htmlBody += '</table>';
        email.setHtmlBody(htmlBody);
        email.setToAddresses(toAddresses);
        email.setSubject(SUBJECT_TO_ADMINS);
        email.setOrgWideEmailAddressId(FROM_ADDRESS_ID);
        email.setSaveAsActivity(false); 
        emails.add(email);
    }
   Messaging.sendEmail(emails);  
}

}

Note: query is supposed to return 19 rows as per today.

Below are the things i have tried :

1. Setup debug logs in Prod, as per logs above query has returned only 3 rows.
2. Executed the same query in query builder and anynomous window. but both returned 19 rows.
3. No failures in job.
4. I believe those three records that were picked, were recently modified.Rest were not picked eventhough they were meeting the criteria

Any advice or input is really helpful

Answer 1

Date.today() returns a time in your user's time zone, and since you assigned it to a date, it ends up being midnight GMT at the beginning of that day, which basically means that it'll be further out than you expect if your time zone is negative (e.g. GMT-00:00 or West of there, and possibly conditionally because of DST).

For example, in my current org, at the time of this answer, the following code:

DateTime n = Date.today().addDays(-1);
System.debug(n.format());

Outputs:

8/2/2021 6:00 PM

It is currently 8/4/2021 at 6:36 PM in Mountain Standard Time, where I am now. It did not output, as I might expect from your assumptions, 8/3/2021 6:00 PM, 8/3/2021 12:00 AM, or something else resembling "the last day".

To fix this, use DateTime.now() instead.

DateTime n = DateTime.now().addDays(-1);
System.debug(n.format());

Correctly outputs 8/3/2021 at 6:39 PM, which is accurate to when I ran that code above in my org. This is exactly one day, which is what I expect.

Note also that times are in GMT, so make sure you account for that when determining if the user should have been disabled based on your query.

In your case, of course, you'll add -30 days, but you should get more reliable results that way.

Edit:

After seeing the full code, the problem is obviously that the dates are set only once. Scheduled jobs remember their state, and so setting it upon construction means the dates won't update daily.

private DateTime notificaficationStartDate;
private DateTime notificaficationEndDate;

public void execute(SchedulableContext sc) {   
    try{
        notificaficationStartDate = DateTime.now().addDays(-Integer.valueOf(Label.InactiveUserNotificationStartDate));//set to 24
        notificaficationEndDate = DateTime.now().addDays(-Integer.valueOf(Label.InactiveUserNotificationEndDate));  //set to 30  
        sendEmailNotifictionToIdleUsers();
        disableUsersSendEmailNotification(); 
    }catch(Exception ex) {
        System.debug('Scheduled run failed : ' + ex.getMessage());          
    }        
}