How to fetch list of users where email before @ is not same as UPN (User Principal Name) before @

[Priya Singh 2 2]

How to fetch list of users where email before @ is not same as UPN (User Principal Name) before @

I want to fetch list of users where email before @ is not same as UPN before @ on user table but I am not able to achieve it by background script, it's returning all the users, please advise how I can get this data ?

[Tanushree Maiti]

Hi @Priya Singh 2 2 ,

This way we have fixed it .

( in our case , we are checking with lots of field in sys_user after the data analysis, you also please analyze your data and update the query and data accordingly). You can follow the same.

Create a Before insert,update BR on subscription table, which will  execute at the end (highest order) ..Give proper trigger condition.

Sample screen shot.

Sample code (if any modification is required ,do it):

(function executeRule(current, previous /*null when async*/ ) {
var op = current.operation();

if (op == "update") {
if (current.user_principal_name && current.user == '') {
var upnp = current.user_principal_name;

var userGrp = new GlideRecord('sys_user');
userGrp.addEncodedQuery('email=' + upnp + '^ORuser_nameSTARTSWITH' + upnp );
userGrp.orderByDesc('sys_created_on');
userGrp.query();

if (userGrp.next()) {
current.user = userGrp.sys_id.toString();
}
} else {

var upn = current.user_principal_name;

var userGr = new GlideRecord('sys_user');
userGr.addEncodedQuery('email=' + upn + '^ORuser_nameSTARTSWITH' + upn );
userGr.orderByDesc('sys_created_on');
userGr.query();

if (userGr.next()) {
current.user = userGr.sys_id.toString();
}
}
}
})(current, previous);

Once done, Run the O365 job and  confirm whether your issue fixed or not.

[Tanushree Maiti]

After this let me know what % your are you able to populate user in s/w subscription table.. 

[Priya Singh 2 2]

Hey I tried but it's not giving expected results

[Priya Singh 2 2]

I will give you one example - sys_user

Name Abel tuter               Email - Abel.tuter@example.com                        UPN - Abel.tuter.@esc.com

Name ABLinkan                Email - ABLinkan@example.com                        UPN - ALinkan@esc.com

So in the 2nd example I removed B from UPN which is making Email before @part different from UPN before @ I want to fetch this data in Service Now