Easily identifying duplicate records in ServiceNow

 

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.

Column 1

Column 2

Column 3

 

 

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 function groupBy on all three columns, leveraging the COUNT aggregate, to determine if duplicates exist. Below is some sample data to explain the concept:

Column 1

Column 2

Column 3

A

B

C

A

B

C

A

B

D

X

Y

C

X

Y

C

X

Y

E

X

Y

F

 

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.

 

Column 1

Column 2

Column 3

A

B

C

A

B

C

A

B

D

X

Y

C

X

Y

E

X

Y

E

X

Y

F

 

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 = 0i < columns.lengthi++) {

    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 \n\t + gs.getProperty(‘glide.servlet.uri’) + table + ‘_list.do?sysparm_query=’;

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

            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.