How can i compare two tables and identify what users does not exist in one o them?

[Weverton]

I have to campare two tables and identify what users does not exist in one o them but, filter it's not possible to use so i'll have to use script but i have never done this before.

where i have to write the script and what is the script that i should use?

[Brad Bowman]

One way to do this is with a Fix Script.  Let's say I want a list of Names from the user table that have opened an Incident, but have not opened a Change Request.  One version of this script would look like this:

var incArr = [];
var chgArr = [];
var inc = new GlideRecord('incident');
inc.orderBy('opened_by');
inc.query();
while (inc.next()) {
	incArr.push(inc.opened_by.toString()); //create an array of everyone who has opened an incident
}

var chg = new GlideRecord('change_request');
chg.orderBy('opened_by');
chg.query();
while (chg.next()) {
	chgArr.push(chg.opened_by.toString()); //create an array of everyone who has opened a change request
}

var arrayUtil = new ArrayUtil();
var usr = new GlideRecord('sys_user');
usr.addQuery('sys_id', 'IN', arrayUtil.diff(incArr, chgArr)); //sys_ids from incArr that are not in chgArr
usr.query();
while (usr.next()) {
	gs.print(usr.name);
}

[Menna Kearns · Accepted solution]

We had to do something similar with an LDAP import, where we had existing active users in SN that no longer existed in AD. We had to create a new temp import table where we then compared existing users in SN to whether they were in the new temp table, and if not, deactivate them.  We used a fix script, as Brad recommends above.  Logic was something like this:

// Query your user table
var myUserTable = new GlideRecord('my_user_table');
myUserTable.query();

while (myUserTable.next()) {
    //get some kind of attribute to compare to, for example "user_name"
    var userAccount = myUserTable.getValue('user_name');
    
    // Query the other users table (my_other_user_table) for the current main user's account name
    var myOtherUserTable = new GlideRecord('my_other_user_table');
    myOtherUserTable.addQuery('user_name', userAccount);
    myOtherUserTable.query();
    
    // If no corresponding record is found in my_other_user_table, update attributes in main user table
    if (!myOtherUserTable.next()) {
        //do something
        gs.addInfoMessage('user does not exist:' + mainUsers.name);
        myUserTable.update();
    }
}

[ersureshbe]

Hi, you can use database view as the solution and define the left or right outer joins and get the results. If require, you can that db view table in reports and get the results.

https://docs.servicenow.com/bundle/vancouver-application-development/page/use/reporting/task/c_Creat...