How to remove duplicates from asset table?

[mdjoseph12]

How can I look at two fields that I am coalescing off of (location & asset tag) to return duplicates or merge them together? Below is the script that I was using to return existing duplicates: 

gs.print(getDuplicates('alm_asset','serial_number'));
function getDuplicates(tablename,val) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(tablename);
gaDupCheck.addQuery('active','true');
gaDupCheck.addAggregate('COUNT',val);
gaDupCheck.addNotNullQuery(val);
gaDupCheck.groupBy(val);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[val].toString());
}
return dupRecords;
}

[Ankur Bawiskar]

Hi Joseph,

Is that above script not working as expected?

what is your requirement? to get assets which are having duplicate serial numbers

Regards

Ankur

[GITAM-Ninja]

Is this still a working script? and if so where are you running it?

[Oleg]

If I correctly understand your question then you want to get some additional properties of records of alm_asset table (for example sys_id, asset_tag, display_name) for duplicates, which you found by your script.

First of all your script has small error. The line gaDupCheck.addQuery('active','true'); should be removed because table alm_asset don't contain the column with the name active.

The modified and extended script could looks like the following:

function getDuplicates(tablename,val) {
    var dupRecords = [];
    var gaDupCheck = new GlideAggregate(tablename);
    //gaDupCheck.addQuery('active','true');
    gaDupCheck.addAggregate('COUNT',val);
    gaDupCheck.addNotNullQuery(val);
    gaDupCheck.groupBy(val);
    gaDupCheck.addHaving('COUNT', '>', 1);
    gaDupCheck.query();
    while (gaDupCheck.next()) {
        dupRecords.push(gaDupCheck[val].toString());
    }
    return dupRecords;
}
function getDuplicateInfos(tablename, valName, val, properties) {
    var dupInfos = [], info, gaDupCheck = new GlideRecord(tablename);
    gaDupCheck.addNotNullQuery(valName);
    gaDupCheck.addQuery(valName, "IN", val);
    gaDupCheck.orderBy(valName);
    gaDupCheck.query();
    while (gaDupCheck.next()) {
        info = { sys_id: gaDupCheck.getUniqueValue() };
        info[valName] = gaDupCheck.getValue(valName);
        if (Array.isArray(properties)) {
            properties.forEach(function (prop) {
                info[prop] = gaDupCheck.getValue(prop);
            });
        }
        dupInfos.push(info);
    }
    return dupInfos;
}
var serialNumbersOfDuplicates = getDuplicates('alm_asset', 'serial_number');
gs.print(serialNumbersOfDuplicates);

var duplicateInfos = getDuplicateInfos('alm_asset', 'serial_number', serialNumbersOfDuplicates,
                                       ["asset_tag", "display_name"]);
gs.print(JSON.stringify(duplicateInfos));