andersbogsjo
Tera Guru

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

 

 

Comments
JosephW1
Tera Guru

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
Tera Guru

Thanks Joseph!

/Anders

JosephW1
Tera Guru

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
Tera Contributor

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

ahohnstein
Tera Contributor

Super helpful, thank you!

Subrat Jain
Tera Contributor

Thank you, I was looking for this solution.

Adam M
Tera Contributor

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
Tera Contributor

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

deep_singh
Tera Contributor

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
Mega Sage

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. 

 

JoelO_0-1741874518016.png

 

Mika Latvala
Tera Contributor

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
Tera Contributor

@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.

beccintech_0-1745961349830.png

 

PraveenaS
Tera Expert

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. 

Version history
Last update:
‎04-13-2021 11:35 PM
Updated by: