Effortlessly Identifying Duplicate Records in ServiceNow

[Harsh_Deep]

Understanding the Script

Here’s the script for identifying duplicate records in a table:

var TABLE_TO_FIND_DUPLICATE_IN = "cmdb_ci_vmware_instance"; // Replace with your table
var FIELD_TO_GROUP_BY = "name"; // Replace with your field
var dupRecords = []; 
var gaDupCheck1 = new GlideAggregate(TABLE_TO_FIND_DUPLICATE_IN); 
gaDupCheck1.addAggregate('COUNT', FIELD_TO_GROUP_BY); 
gaDupCheck1.groupBy(FIELD_TO_GROUP_BY); 
gaDupCheck1.addHaving('COUNT', '>', 1); 
gaDupCheck1.query(); 
while (gaDupCheck1.next()) { 
   dupRecords.push(gaDupCheck1.getValue(FIELD_TO_GROUP_BY)); 
}
gs.info(dupRecords);

Let’s break it down step by step.

Step-by-Step Explanation

1. Define the Table and Field

   var TABLE_TO_FIND_DUPLICATE_IN = "cmdb_ci_vmware_instance";
   var FIELD_TO_GROUP_BY = "name";

• Replace TABLE_TO_FIND_DUPLICATE_IN with the table you want to scan for duplicates.
• Replace FIELD_TO_GROUP_BY with the field you want to check for duplicate values.

2. Initialize GlideAggregate

   var gaDupCheck1 = new GlideAggregate(TABLE_TO_FIND_DUPLICATE_IN);

   GlideAggregate is a powerful API for performing aggregate queries such as COUNT, SUM, or GROUP BY.

3. Add Aggregation and Grouping

   gaDupCheck1.addAggregate('COUNT', FIELD_TO_GROUP_BY);
   gaDupCheck1.groupBy(FIELD_TO_GROUP_BY);

• addAggregate('COUNT', FIELD_TO_GROUP_BY) counts the number of records for each value in the specified field.
• groupBy(FIELD_TO_GROUP_BY) groups the results by the field to analyze duplicates.

4. Add a HAVING Clause

   gaDupCheck1.addHaving('COUNT', '>', 1);

This ensures only records with a count greater than 1 (duplicates) are included in the result.

Use Case: Finding Duplicate VMware Instances by Name

Imagine a scenario where duplicate VMware instances (cmdb_ci_vmware_instance) with the same name field are causing discrepancies in your CMDB. The script above helps you identify all duplicate names so you can merge, delete, or address them as needed.

Automate Duplicate Cleanup

If you want to automate duplicate removal, extend the script with a logic to delete records. Always back up data before performing deletions.

Mark  👍 Helpful if you find my response worthy based on the impact.

Regards,

Harsh Deep Singh