need help in creating report which shows assets assigned to users

[karan15]

in servic enow for some users its showing they have been assigned 2 or more mobile phones , ideally it should be only 1 

I want to run report and find out those active users who have multiple mobile phones assigned to them so that we can contact them and update status of these phones in CMDB 

Please help in query and table i need to use to create this report 

thanks 

[anurag92]

Hi Karan,

You can create a report on the User ("sys_user") table and add a Related list condition on Assets related list. You can select all users where assets are >=2 and further filter on Model / Model category. Something like this:

[karan15]

hi thanks how to add this condition which is greater than 2 ? 

[anurag92]

Click on the blue link "Greater than or Equal to 2" and it will allow you to add your own condition.

[Swapna Abburi]

HI@karan15 

one more way to get the report directly on asset table is to use a script include to find the users who have more than one asset on their name, then call this script include in the report. 

Example Script include:

var multipleAssets = Class.create();
multipleAssets.prototype = Object.extendsObject(AbstractAjaxProcessor, {

mAssets: function() {
var duplicates = [];
var ga = new GlideAggregate("alm_asset");
ga.addEncodedQuery("model_category.name=Mobile Device");
ga.addAggregate('COUNT', "assigned_to");
ga.groupBy("assigned_to");
ga.addHaving('COUNT', '>', 1);
ga.query();
while (ga.next()) {
duplicates.push(ga.assigned_to.toString());
}
return duplicates;
},

type: 'multipleAssets'
});

Report Filter: