GlideAggregate for detecting duplicate records

tonypod
Kilo Explorer

‎09-17-2014 08:41 AM

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. }
0 Helpfuls
  • 24,662 Views
15 REPLIES 15

Jason Roiz
Kilo Contributor
In response to Namrata Agrawa1

‎04-14-2021 08:25 AM

Thank you, this was just what I needed to get me to the next step as a new ServiceNow developer!

I'm working on a script to find duplicate CI's and create remediation tasks for each group of them. Here's what I have so far:

var ga = new GlideAggregate('cmdb_ci_hardware');
ga.addAggregate('COUNT', 'serial_number');
ga.addHaving('COUNT', 'serial_number', '>', '1');
ga.query();
while (ga.next()) {
var deviceSN = ga.getValue('serial_number');
var gr = new GlideRecord('cmdb_ci_hardware');
gr.addQuery('serial_number',deviceSN);
gr.query();
while(gr.next()) {
deviceName = gr.getValue('name');
gs.print("Name: " + deviceName + ", Serial Number: " + deviceSN);
}
}

The next step is to incorporate the following sample code...

// where sys-id1 and sys-id2 are sys_IDs of CIs in the cmdb_ci table
var sysIDs = 'sys-id1, sys-id2';
var dupTaskUtil = new CMDBDuplicateTaskUtils();
var deDupTaskID = dupTaskUtil.createDuplicateTask(sysIDs);
gs.info(deDupTaskID);

...I'll post further about what I come up with...

Thanks!
Jason
0 Helpfuls