Get Duplicate Records from any table

[andersbogsjo]

Sometimes it is useful to be able in a report to collect duplicate records.

For example to find duplicates af Assets with the same Asset Tag or CI:s with identical name

Here is an example where you can create a dynamic Script Include that then can be used in reports.

Script Include

Name: getDuplicates
Client callable: true
Description: Get Duplicates from any table in the instance to use in Reports. In Report syntax specify the table and field.

Script:

var getDuplicates = Class.create();
getDuplicates.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getDuplicates: function(table, field) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(table);
gaDupCheck.addAggregate('COUNT', field);
gaDupCheck.addNotNullQuery(field);
gaDupCheck.groupBy(field);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[field].toString());
}
return dupRecords;
},

type: 'getDuplicates'
});

In a report then Example.

Duplicate CI:s on name
Create a report on the cmdb_ci_computer table.

set the filter as: Name is javascript:new global.getDuplicates().getDuplicates('cmdb_ci_computer', 'name')

If you wanna do this on another table just change the the table to report on and change the syntax to that table and fieldname in the condition

Good luck

Anders

[JosephW1]

Thank you for sharing this! That's a flexible script that probably could be useful for data-auditing in so many different table/field combinations!

Taking the time to share this is much appreciated.

[andersbogsjo]

Thanks Joseph!

/Anders

[JosephW1]

Have you considered simplifying the script to be something along these lines? I don't know, it just seems to be easier on my eyes and brain in this format, haha.

function getDuplicateValues(table, field){
	var dupValues = [];
	var ga = new GlideAggregate(table);
	ga.addAggregate('COUNT', field);
	ga.addNotNullQuery(field);
	ga.groupBy(field);
	ga.addHaving('COUNT', '>', 1);
	ga.query();
	while (ga.next()) {
		dupValues.push(ga[field].toString());
	}
	return dupValues;
}

Computer Query: Serial number > is one of > javascript:getDuplicateValues('cmdb_ci_computer','serial_number')

Thank you again for writing this script! It works great!

[Stephan van Bee]

Perfect! Exactly what I need to prevent colleagues from downloading to Excel or PowerBI.

[ahohnstein]

Super helpful, thank you!

[Subrat Jain]

Thank you, I was looking for this solution.

[Adam M]

Script works for me, however I would like to further filter the Report results by Model Category from the alm_asset table.  When I add additional conditions it does not work

[Adam M]

With that script I am not able to add additional filter conditions in the report, say for Model category or state

[deep_singh]

Hi , @andersbogsjo 

Here is a background script to find duplicate records on cmdb_ci table with their count without using GlideAggreagate or any script include. 

///////////////// FETCH ALL RECORDS //////////////////
var ci = new GlideRecord('cmdb_ci');
ci.query();
////////// CREATE AN OBJECT TO STORE ITEM NAME AND COUNTS////////////////
var cmdb = {};

//////////// ITERATE THROUGH GLIDERECORD OBJECT///////////////
while(ci.next()){
	var ci_name = ci.name.toString();
	if(cmdb[ci_name]){  // If name is already in object , we will increment its count , else keep it 1
		cmdb[ci_name]++;
	}
	else{
		cmdb[ci_name] = 1;
	}
}
//////////////// Initialze an array and store all duplicate records////////////////
var ditto = [];
for(var name in cmdb){
	if(cmdb[name] > 1){
		ditto.push(name);
	}

}
/////// ITERATE THROUGH DUPLICATE ARRAY AND PRINT VALUES /////////////////////////////
for(var i=0; i < ditto.length; i++){
    var names = ditto[i];
	var count = cmdb[names];
	gs.print('DUPLICATES OF '+ names +' ---------> '+ count);
}

 Please mark it as helpful .

Thanks 

[Joel O]

Looking for assistance as I had created the script include and followed directions outlined, however I'm only pulling records with blank values for either my serial_number or asset_tag fields. In both my PDI and DEV instance, I have many duplicate records on both the serial_number and asset_tag fields, however whether I try the cmdb_ci_computer table or the alm_asset table, it's always the same results. 

Any thoughts on what I might be missing?

Much appreciate anyone's assistance. 

[Mika Latvala]

Same here. I tried to understand what's the problem with the code but was unable to figure out why this has stopped working.

[beccintech]

@Joel O and @Mika Latvala I ran into the same issue, make sure you check the 'Sandbox enabled' checkbox on your script include. After I made that change, the report I was trying to build worked as expected.

[PraveenaS]

Hello 

Thank you for the script and the above suggestion. I was able to pull a list of all duplicate records. I tried to convert that to a single score to number of project records with same field value. I was able to do the group by on the field in a list. But when do the Score, I can't make it work. I have 50 projects with three different value for a field that is repeating. when I group by that field value I can tell, in the list it is those three values. But I want to show single score to show count of those distinct three values. 

[Drew Carpenter]

https://www.servicenow.com/community/developer-blog/search-for-duplicates-delete-based-on-2-columns/...

[VenkannaG]

var ci = new GlideRecord('cmdb_ci');

ci.query();

var cmdb = {};

while (ci.next()) {

    var name = ci.name.toString();

    cmdb[name] = (cmdb[name] || 0) + 1;

}

for (var name in cmdb) {

    if (cmdb[name] > 1) {

        gs.info('DUPLICATE: ' + name + ' → ' + cmdb[name]);

    }

}