Primary Email Mismatch

[LiamO]

Hello! 

It has been discovered that there is an email address mismatch between sys_user email and cmn_notif_device email_address for some of our users. 

If we manually update an email address within the sys_user table, the business rule 'Update Email Devices' runs perfectly to update the cmn_notif_device email_address field. 

We have found the below post from quite a few years ago, that is very similar to what we are experiencing but their script doesn't solve our problem as email_address from cmn_notif_device is not empty.

Solved: Re: Update email addresses for all users - ServiceNow Community

Unfortunately scripting is not part of our skill set (it's on the to-do list)...

Hoping someone will be able to assist with a script or a workaround to get cmn_notif_device to match sys-user email addresses. 

And sorry if I post this on the wrong board... 

- Liam

[Murthy Ch · Accepted solution]

@LiamO 

You can try the below code in your background script:

var grCmn = new GlideRecord("cmn_notif_device");
grCmn.addEncodedQuery('active=true');
grCmn.query();
while (grCmn.next()) {
    var cmnUser = grCmn.getValue('user');
    var cmnEmail = grCmn.getValue('email_address');
    var grUser = new GlideRecord("sys_user");
    grUser.addQuery("sys_id", cmnUser);
    grUser.query();
    if (grUser.next()) {
        if (grUser.getValue('email') != cmnEmail) {
            grCmn.email_address = grUser.getValue('email');
            grCmn.update();
        }
    }
}

 (=tested)

[AshishKM]

Hi @LiamO , 

So sys_user is main source and cmn_notif_device table has emails but not correct one for the same user or all ?

Have you done  data analysis, how many such record where this email is mismatch.

Check if all records in cmn_notif_device table has emails or some empty email record becuase based the data count we can plan email update code from sys_user to cmn_notify_device table.

Let us know the more data details with some screenshots .. we can write code which you are execute as Fix Script or  Background.

-Thanks,

AshishKM 

[LiamO]

Hi AshishKM, 

The cmn_notif_device table has roughly 600 entries (1750 total)  where the email is not the same as the sys_user table. All records in the cmn_notif_device table have data in the email addresses field. 

We had to modify the domain name a few years ago and this was done via a script. Analysing the data the entries with the email mismatch look to be from when this happened... 

-Liam 

[Ankur Bawiskar]

@LiamO 

Did you check any of those 600 email is present in sys_user?

Possibly that user got deleted from sys_user

What's your ask?

You can check if the email address for those 600 records is present in sys_user or not, if not then delete that cmn_notif_device record as it doesn't correspond to any user in sys_user

Something like this

var grCmn = new GlideRecord("cmn_notif_device");
grCmn.addEncodedQuery('active=true');
grCmn.addQuery('email_address', '!=', ''); // email not empty
grCmn.query();
while (grCmn.next()) {
    var cmnUser = grCmn.getValue('user');
    var cmnEmail = grCmn.getValue('email_address');
    var grUser = new GlideRecord("sys_user");
    grUser.addQuery("sys_id", cmnUser);
    grUser.addQuery('email_address', cmnEmail);
    grUser.query();
    if (!grUser.hasNext()) {
        // If the user record is not found, delete the cmn_notif_device record
        grCmn.deleteRecord();
    }
}

If my response helped please mark it correct and close the thread so that it benefits future readers.