GlideAggregate for detecting duplicate records

[tonypod]

I am trying to detect duplicates across multiple columns using GlideAggregate, but cannot seem to figure it out.   Is it even possible?   Is there a better way?   Here is a snippet for getting unique records when determining uniqueness from single column.   I need to do this by looking at two columns.

1. getDuplicates();  
2. function getDuplicates() {  
3.     var dupRecords = [];  
4.     var gaDupCheck1 = new GlideAggregate('sys_user');  
5.     gaDupCheck1.addQuery('active','true');  
6.     gaDupCheck1.addAggregate('COUNT', 'user_name');  
7.     gaDupCheck1.groupBy('user_name');  
8.     gaDupCheck1.addHaving('COUNT', '>', 1);  
9.     gaDupCheck1.query();  
10.     while (gaDupCheck1.next()) {  
11.           dupRecords.push(gaDupCheck1.user_name.toString());  
12.     }  
13.     gs.print(dupRecords);  
14. }

[Anurag Tripathi]

Please mark the answer helpful if it really helped you

[jochen_larbig]

You can use multiple groupBy statements in GlideAggregate (see GlideAggregate - ServiceNow Wiki)

E.g. to find duplicate relations:

var gaDupCheck = new GlideAggregate('cmdb_rel_ci');

gaDupCheck.groupBy('parent');

gaDupCheck.groupBy('child');

gaDupCheck.groupBy('type');

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

[johnram]

The ServiceNow Wiki content is no longer supported. Updated information about this topic is located here: GlideAggregate
 

Visit http://docs.servicenow.com for the latest product documentation

[martinaparicio]

This background script helped me find duplicate users with same employee number, based on example above.

var gaDupCheck = new GlideAggregate('sys_user');
gaDupCheck.addAggregate('COUNT', 'employee_number');
gaDupCheck.addNotNullQuery('employee_number');
gaDupCheck.groupBy('employee_number');
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
  gs.log(gaDupCheck.employee_number);
}

[Namrata Agrawa1]

Hello,

I have the same requirement .i have written the fix script .it is working also .

var usr = new GlideAggregate('sys_user');
usr.addAggregate('COUNT', 'name');
usr.groupBy('name');
usr.addHaving('COUNT', '>', 1);
usr.query();
while (usr.next())
{
var usr1 = usr.name;
var gr = new GlideRecord('sys_user');
gr.addQuery('name',usr1);
gr.query();
gr.next();
while(gr._next())
{
gr.name = gr.name +' '+'('+gr.user_name+')';
gr.setWorkflow(false);
gr.update();
}

}

Regards,

Namrata Agrawal

kindly correct my answer if it is working for you as well so will helpful for other memebers .