How to easily identify duplicate records in ServiceNow

When it comes to data management, maintaining clean and accurate records is one of your top priorities. It’s not unusual for organizations to run into issues with duplicate records – either from inefficient record keeping or duplicate workflows. 

A common need within ServiceNow is to identify duplicate records, according to a set of arbitrary criteria. Though the “list view” of a given table offers some advanced search and filtering capabilities, it does not give you the ability to natively identify records that share the same values across multiple columns (i.e. “duplicates”).

However, ServiceNow’s GlideAggregate object and associated functions allow you to easily identify duplicates via use of the “groupBy” function. This is useful in cases where identifying duplicates is necessary for informational or data-cleanup purposes. For example, say we have a table with Columns 1, 2, and 3, as shown below.

If we want to find “duplicate” records where, for each record, the values in Column 1, 2, and 3 are all respectively the same, we can use the GlideAggregate functiongroupBy on all three columns, leveraging the COUNT aggregate, to determine if duplicates exist. Below is some sample data to explain the concept:

We can see that the value “C” in Column 3 appears multiple times, but we are only concerned with records where Column 1 and Column 2 are also mutually the same. Essentially, we are attempting to construct a composite key, composed of all 3 columns. Below I have highlighted the two rows that we are interested in.

If we were to look at a list view for this table and group by Column 3, we would see a grouping of 3 rows, since “C” is the value for 3 different rows. However, we can not further group by either or both of the other two columns, and that group of 3 records includes a row that is outside of our target data.

Provided below is some code that, as an example, can identify duplicates in our hypothetical table.

var table = ‘u_demo_table’;

var columns = [‘u_column_1’, ‘u_column_2’, ‘u_column_3’];

var pivotColumn = ‘u_column_3’;

var duplicateFinder = new GlideAggregate(table);

for (var i = 0; i < columns.length; i++) {

    duplicateFinder.groupBy(columns[i]);

}

duplicateFinder.addAggregate(‘COUNT’, pivotColumn);

duplicateFinder.query();

while (duplicateFinder.next()) {

    var count = parseInt(duplicateFinder.getAggregate(‘COUNT’, pivotColumn));

    if (count > 1){

        var message = count + ‘ found nt‘ + gs.getProperty(‘glide.servlet.uri’) + table + ‘_list.do?sysparm_query=’;

        for(var i = 0; i < columns.length; i++){

            message += columns[i] + ‘=’ + duplicateFinder[columns[i]] + ‘^’;

        }

        gs.log(message);

    }

}

Running the script, we see that it found duplicates.

If we navigate to the link, we can get access to our duplicates in a list view and action them as needed.

This script could be extended to take action itself, once the duplicates are found. Hopefully this information provides a basis from which you can further explore GlideAggregate independently.

With a deeper understanding of these tools, you can maintain cleaner, more accurate data within your ServiceNow instance, ultimately improving your overall data management and operational efficiency.

While the above technical tip has been provided with care and consideration, it’s important to acknowledge that individual circumstances may vary. Always ensure compatibility and feasibility within your specific ServiceNow environment before implementing any suggestions. Additionally, back up your data and proceed with caution when making any changes to your instance or workflows. 

As with any change in ServiceNow, make sure you test any changes prior to moving to production.