- Post History
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
on ‎04-13-2021 11:35 PM
Sometimes it is useful to be able in a report to collect duplicate records.
For example to find duplicates af Assets with the same Asset Tag or CI:s with identical name
Here is an example where you can create a dynamic Script Include that then can be used in reports.
Script Include
Name: getDuplicates
Client callable: true
Description: Get Duplicates from any table in the instance to use in Reports. In Report syntax specify the table and field.
Script:
var getDuplicates = Class.create();
getDuplicates.prototype = Object.extendsObject(AbstractAjaxProcessor, {
getDuplicates: function(table, field) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(table);
gaDupCheck.addAggregate('COUNT', field);
gaDupCheck.addNotNullQuery(field);
gaDupCheck.groupBy(field);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[field].toString());
}
return dupRecords;
},
type: 'getDuplicates'
});
In a report then Example.
Duplicate CI:s on name
Create a report on the cmdb_ci_computer table.
set the filter as: Name is javascript:new global.getDuplicates().getDuplicates('cmdb_ci_computer', 'name')
If you wanna do this on another table just change the the table to report on and change the syntax to that table and fieldname in the condition
Good luck
Anders
- 25,459 Views

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thank you for sharing this! That's a flexible script that probably could be useful for data-auditing in so many different table/field combinations!
Taking the time to share this is much appreciated.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thanks Joseph!
/Anders

- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Have you considered simplifying the script to be something along these lines? I don't know, it just seems to be easier on my eyes and brain in this format, haha.
function getDuplicateValues(table, field){
var dupValues = [];
var ga = new GlideAggregate(table);
ga.addAggregate('COUNT', field);
ga.addNotNullQuery(field);
ga.groupBy(field);
ga.addHaving('COUNT', '>', 1);
ga.query();
while (ga.next()) {
dupValues.push(ga[field].toString());
}
return dupValues;
}
Computer Query: Serial number > is one of > javascript:getDuplicateValues('cmdb_ci_computer','serial_number')
Thank you again for writing this script! It works great!
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Perfect! Exactly what I need to prevent colleagues from downloading to Excel or PowerBI.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Super helpful, thank you!
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Thank you, I was looking for this solution.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Script works for me, however I would like to further filter the Report results by Model Category from the alm_asset table. When I add additional conditions it does not work
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
With that script I am not able to add additional filter conditions in the report, say for Model category or state
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hi , @andersbogsjo
Here is a background script to find duplicate records on cmdb_ci table with their count without using GlideAggreagate or any script include.
///////////////// FETCH ALL RECORDS //////////////////
var ci = new GlideRecord('cmdb_ci');
ci.query();
////////// CREATE AN OBJECT TO STORE ITEM NAME AND COUNTS////////////////
var cmdb = {};
//////////// ITERATE THROUGH GLIDERECORD OBJECT///////////////
while(ci.next()){
var ci_name = ci.name.toString();
if(cmdb[ci_name]){ // If name is already in object , we will increment its count , else keep it 1
cmdb[ci_name]++;
}
else{
cmdb[ci_name] = 1;
}
}
//////////////// Initialze an array and store all duplicate records////////////////
var ditto = [];
for(var name in cmdb){
if(cmdb[name] > 1){
ditto.push(name);
}
}
/////// ITERATE THROUGH DUPLICATE ARRAY AND PRINT VALUES /////////////////////////////
for(var i=0; i < ditto.length; i++){
var names = ditto[i];
var count = cmdb[names];
gs.print('DUPLICATES OF '+ names +' ---------> '+ count);
}
Please mark it as helpful .
Thanks
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Looking for assistance as I had created the script include and followed directions outlined, however I'm only pulling records with blank values for either my serial_number or asset_tag fields. In both my PDI and DEV instance, I have many duplicate records on both the serial_number and asset_tag fields, however whether I try the cmdb_ci_computer table or the alm_asset table, it's always the same results.
Any thoughts on what I might be missing?
Much appreciate anyone's assistance.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Same here. I tried to understand what's the problem with the code but was unable to figure out why this has stopped working.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
@Joel O and @Mika Latvala I ran into the same issue, make sure you check the 'Sandbox enabled' checkbox on your script include. After I made that change, the report I was trying to build worked as expected.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content
Hello
Thank you for the script and the above suggestion. I was able to pull a list of all duplicate records. I tried to convert that to a single score to number of project records with same field value. I was able to do the group by on the field in a list. But when do the Score, I can't make it work. I have 50 projects with three different value for a field that is repeating. when I group by that field value I can tell, in the list it is those three values. But I want to show single score to show count of those distinct three values.
- Mark as Read
- Mark as New
- Bookmark
- Permalink
- Report Inappropriate Content