Update email addresses for all users

[adidosc]

Hi All,

We recently noticed that some users were not receiving resolution notifications after incidents were resolved. ServiceNow support correctly identified that somehow many users did not have an email address listed on the cmn_notif_device table. The business rule "Update Email Devices" does work to update the cmn_notif_device table for any user who has an email address that is changed, but I need assistance with an efficient way to update all of the email addresses.

The goal is to have a script or something run so that if a user has an email address listed on the sys_user table, it is copied over or to the cmn_notif_device table for their name if there is no email address present. This is going to just be a one time thing, or maybe on-demand if needed in the future.

I appreciate any feedback, and thank you in advance!

- Ben

[Mike Allen · Accepted solution]

In Background Scripts, I would run the following script:

var email = new GlideRecord('cmn_notif_device');

email.addQuery('email_address', '');

email.query();

while(email.next()){

         var user = new GlideRecord('sys_user');

         user.addQuery('sys_id', email.user);

         user.query();

         if(user.next()){

                   email.email_address = user.email;

                   email.update();

         }

}

Or something like that.   As always, play around with it in subprod.

[adidosc]

You are the man Mike... I ran it in dev and it worked like a charm. I plan to run in test next before moving to production and expect the same great results. Thank you!

[lilianm_vel]

Hello,

I faced the same issue and script sent by Mike is correct but I went further to find the root cause of such behaviour.

In my case, our users are imported via a daily scheduled import, and you may have unchecked the "Run Business rule" checkbox which avoid aligning your sys_user table with your cmn_notif_device and cmn_notif_message tables.

Having said that, Mike's script will solve your issue at one time but will not solve the issue on the long term and you will face again the same problem.

You may be interested in the blogpost I have written that talk about the root cause and the best way to DEFINITIVELY solve it:

https://fruitionpartners.eu/blog/2016/08/10/notifications-not-correctly-sent-end-users/

Thanks Mike, your answer also helped me

Cheers

[lilianm_vel]

Hello,

I faced the same issue and script sent by Mike is correct but I went further to find the root cause of such behaviour.

In my case, our users are imported via a daily scheduled import, and you may have unchecked the "Run Business rule" checkbox which avoid aligning your sys_user table with your cmn_notif_device and cmn_notif_message tables.

Having said that, Mike's script will solve your issue at one time but will not solve the issue on the long term and you will face again the same problem.

You may be interested in the blogpost I have written that talk about the root cause and the best way to DEFINITIVELY solve it:

https://fruitionpartners.eu/blog/2016/08/10/notifications-not-correctly-sent-end-users/

Thanks Mike, your answer also helped me

Cheers