Need Help identifying Duplicate records on custom table

[Sunny14]

Hello Team,

We have a custom Table X and for some reason the numbering has started creating duplication at the insert of the record. I need help identifying duplicate records. The number of records are huge 900K+ so Group by will take forever. 

If we can printout the duplicate records via script, that would be good. After that we can work on those duplicates. 

Thanks. 

[Ravi Peddineni · Accepted solution]

@Sunny14 

Try this in the Background Scripts:

gs.info(getDuplicateValues(<table>, <field>));

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;
}

****NOTE: It might take a lot of time to complete the query and might even get you session resources locked for this operation. So I strictly suggest you to run this in the possible lower instance where the criticality is low. ****

Reference: Get Duplicate Records from any table 

[Sunny14]

Thanks @Ravi Peddineni 

Right on target. This is exactly what I was looking for.

One question I have though, Can we add condition to cut down number of records (e.g. Created between Jan 1st  2024 to June 30 th 2024 ] ? How can we add addEncodedQuery () condition with GlideAggregate () ?

Please advise. 

gs.info(getDuplicateValues(<table>, <field>));

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;
}

[Ravi Peddineni]

@Sunny14 

I was about to suggest you to divide the data into chunks but the problem is that it might miss checking the dupes that don’t fall in the same chunk. 

[Sunny14]

Yes, That is very valid point.  Thanks @Ravi Peddineni . 

But otherwise if we have to combine addEncodedQuery() with GlideAggregate, how can we ? 

For e.g. we can first consider only Active = true.