Duplicate serial number assets report

[_bhishek]

Hi All,

I am able to find the duplicate serial number through background script. I want to create a report with list view in service now  for the duplicate serial number assets(alm_asset table).

Could you please help me how can i do that .I am using below script.

var gaDupCheck = new GlideAggregate('alm_asset'); 


gaDupCheck.addAggregate('COUNT', 'serial_number'); 
gaDupCheck.addNotNullQuery('serial_number'); 
gaDupCheck.groupBy('serial_number'); 
gaDupCheck.addHaving('COUNT', '>', 1); 
gaDupCheck.query(); 

while (gaDupCheck.next()) { 

 var gr = new GlideRecord('alm_asset');
  gr.addQuery('serial_number',gaDupCheck.serial_number);
  gr.query();
  gs.info('Duplicate serial number: ' + gaDupCheck.serial_number);

}

[The Machine]

the comment about using a script include is probably best. 

[Dr Atul G- LNG]

Hi @_bhishek 

Create a report and group by Serial number. 

[kps sumanth · Accepted solution]

Hello @_bhishek ,

You can create a script include for the same and you can use that script include and use the same in your report.

Create a script include:

var Duplicate_Records_serial = Class.create();
Duplicate_Records_serial.prototype = Object.extendsObject(AbstractAjaxProcessor, {
duplicateRecord: function(){
	var numbers = [];
	var gaDupCheck = new GlideAggregate('alm_asset'); 


gaDupCheck.addAggregate('COUNT', 'serial_number'); 
gaDupCheck.addNotNullQuery('serial_number'); 
gaDupCheck.groupBy('serial_number'); 
gaDupCheck.addHaving('COUNT', '>', 1); 
gaDupCheck.query(); 

while (gaDupCheck.next()) { 
numbers.push(gaDupCheck.serial_number.toString());

//  var gr = new GlideRecord('alm_asset');
//   gr.addQuery('serial_number',gaDupCheck.serial_number);
//   gr.query();
//   gs.info('Duplicate serial number: ' + gaDupCheck.serial_number);

}
//gs.info("Serial_records "+numbers)
return numbers;
},

    type: 'Duplicate_Records_serial'
});

and create a report in that call the above script include like this.

Condition: serial number is "javascript:new Duplicate_Records_serial().duplicateRecord()"

Please mark this Accepted and helpful if this worked for you.

[lwhite]

When I try the accepted solution (code is copied and pasted), I get a report that lists out all the records with an empty serial number and no other records, but I know there are other records that have actual values that are duplicated.  Any ideas on why that is or how to correct it?