Finding Duplicate Assets

[AndyB5000]

I have a script include that I found in the community to assist in finding duplicate records in a table.  In this case I am using it to locate duplicate assets on the alm_asset table.  The script works as expected as it returns a list of duplicated records.  So if I have 2 assets that are in the table twice the script return all 4 records.  I want to add a line that returns one record so I can create a report showing the duplicated assets.

The script is:

function getDupes(alm_asset, asset_tag) {
var q = new GlideAggregate(alm_asset);
//q.addQuery('active', '=', 'true');
q.addAggregate('COUNT', asset_tag);
q.addHaving('COUNT', asset_tag, '>', '1'); (duplicates)
q.query();
var listOfDupes = new Array();
while (q.next()) {
listOfDupes.push(q.getValue(asset_tag));
}
return listOfDupes;
}

The above screenshot shows the returned list.  This is from my PDI as I am testing the script..

thanks in advance for any assistance.

[Ankur Bawiskar]

Hi Bowden,

So are you are not getting unique asset tags after using the above code?

update script as below

1) alm_asset table doesn't have active column so removed that addQuery

2) also in the addHaving() count should be compared with integer 1 and not string '1'

3) getValue() won't work I guess in the GlideAggregate object

the way you are calling in the report looks good

function getDupes(alm_asset, asset_tag) {
var dupRecords = [];
var gaDupCheck = new GlideAggregate(alm_asset);
gaDupCheck.addAggregate('COUNT',asset_tag);
gaDupCheck.addNotNullQuery(asset_tag);
gaDupCheck.groupBy(asset_tag);
gaDupCheck.addHaving('COUNT', '>', 1);
gaDupCheck.query();
while (gaDupCheck.next()) {
dupRecords.push(gaDupCheck[asset_tag].toString());
}

var arrayUtil = new ArrayUtil();

gs.info('getDupes before unique :' + dupRecords);

dupRecords = arrayUtil.unique(dupRecords);

gs.info('getDupes after unique :' + dupRecords);

return dupRecords;
}

Mark Correct if this solves your issue and also mark Helpful if you find my response worthy based on the impact.
Thanks
Ankur

[AndyB5000]

have to be missing something as it is not returning unique values, just returns all duplicate records.

[Ankur Bawiskar]

Hi Bowden,

Did you check in system logs what values are coming?

in left navigation type syslog.LIST and message contains getDupes

please share what values are coming?

Regards

Ankur

[AndyB5000]

It is giving two:

org.mozilla.javascript.EcmaError: "alm_asset" is not defined.
Caused by error in <refname> at line 1

&

org.mozilla.javascript.EcmaError: "getDupes" is not defined.
Caused by error in <refname> at line 1

[ARG645]

Tested and it works for me! 

function getDupes(alm_asset, asset_tag) {
	var q = new GlideAggregate(alm_asset);
	q.addAggregate('COUNT', asset_tag);
	q.groupBy(asset_tag);
	q.addHaving('COUNT', '>', '1'); 
	q.query();
	var listOfDupes = new Array();
	while (q.next()) {
		listOfDupes.push(q.getValue(asset_tag));
	}
	return listOfDupes;
}