How to get the sys ids of the duplicate records as they are referenced in other tables?

[Ksnow]

Hello all,

I need to get a list of records where the duplicate records have been referenced in other tables via background script.

For example:

1st table

Table: Location

Field: Name and records in this tables are:

Record 1: Name : Test , sys_id : xyz

Record 2: Name: Test , sys_id: 123

These 2 records are referenced in other table called "Office" with the field "Location"

I need to know where duplicate records are used and cleanup duplicate value by assigning the correct value in the "Office" table.

Appreciate your help!

Thanks,

Ksnow 

[Vaibhav127]

Hi Ksnow,

you can query your 'office' table with the duplicate location sys id and then update the value to the original one.

var gr = new GlideRecord('office');

gr.addQuery('location',duplicate location sys id);

gr.query();

while(gr.next()) {

gr.location = original location sys id.

gr.update()

}

[shloke04]

Hi @Ksnow 

Please use the below script:

var getRecords = getDuplicates('sys_user','location'); // Replace 'sys_user" with the office table and location with your field which is referring to target table where you want to check for duplicate records
var getDupRecords = getTargetDuplicate(getRecords);

function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
//gs.info(gaDupCheck.location.name);
dupRecords.push(gaDupCheck.location.name.toString());  //Replace "location" with the field Name for which you want to check
}
return dupRecords;
}

function getTargetDuplicate(recordList){
var gr = new GlideAggregate('cmn_location'); // Replace "cmn_location" with your target table you need
gr.addQuery('name',recordList);
gr.addAggregate('COUNT');
gr.groupBy('name'); // Rpelace "name" with the attribute you want to check with in target table
gr.addHaving('COUNT', '>', 1);
gr.query();
while(gr.next()){
gs.info(gr.getAggregate('COUNT') + ' - ' + gr.name); //This will give you the records in you target table
}

}

[Ksnow]

Thanks for the response @shloke04 

I tried the provided script in the background script and when we put GlideAggregrate in the beginning, it is not providing the names of other table while printing.

I am quite beginner in the scripting, would appreciate if you could please help me with the solution which is very close. Hence I am providing the correct table and field names below.

Maybe I was not able to explain what exactly I need. Let me rephrase the situation.

We have duplicate records in the Subnet table (original name) and those records are used in the other table called "Server" in "Subnet" field pointing to Subnet table.

I need to print the duplicate Subnet names + subnet sysId + Server name (in which it is used).

Example:

Test + 123456 + US-H013

Test + abcdef + CN-2016

So that I can segregate the data in the excel like:

Subnet Name | Subnet SysID | Server Name1

                                                  | Server Name2

I have tried the below script as well

var servers = new GlideRecord('cmdb_ci_server');
servers.query();
gs.print(servers.getRowCount());
while(servers.next()){
  var gaDupCheck = new GlideRecord("u_azure_subnets");
gaDupCheck.addQuery("name" , servers.u_snet);
gaDupCheck.query();  
  gs.print("Snet name--" + servers.u_snet.getDisplayValue() + "Snet sys id--" + servers.u_snet + "Server name" + servers.name) 
}

But the result is as below:

Test +  123456  + US-H013

Test +  123456  + US-H014

Test +  123456  + US-H015

Test + abcdef + CN-2016

Test + abcdef + CN-2017

Test + abcdef + CN-2018

It need to show as mentioned above only one SNET name + SNet Sys ID along with multiple servers.

Hope it is clear for you, could you please adjust my script to make it work as expected?

Thanks,

Ksnow