How to report on users who have more than one asset assigned?

[donatella_sagli]

Hello,

is there a way I can extract the count of the assets assigned to a user?

One of our users is trying to trace the people who have more than one asset assigned to them, but we don't seem to reach the result.

From the table alm_asset I can set a condition on Assigned to is not empty but I would need to exclude those users who have only 1 asset.

The field Quantity on the asset table is not being filled, so I don't think it has anything to do with the Assigned to.

Do you think we need to build a db view in order to join these information? Is it even possible to join them?

Thank you so much,

Donatella

[Ankur Bawiskar]

@donatella.saglioccolo@solve 

you can try this in reporting

Table - sys_user

In related list condition select this

Asset -> Assigned to

Greater than 1

Regards
Ankur

[donatella_sagli]

This would help if I didn't have to set conditions also on the type of asset (model category) and display the details of the assets in the output.

From the user table, I can't use the asset fields as the columns selection (or rows & columns for pivot) does not allow to select related list fields.

In the attachment see what I am trying to achieve. In the pivot I would like to be able to see only the user who has 5 assets assigned and exclude all others who have only 1.

Thank you!

[Ankur Bawiskar]

Hi,

the report example shared above was just to help you get started.

The above report will help you knowing which users are having more than 1 assets assigned.

You can also give the conditions such as Model Category is XYZ in related list conditions.

I believe that is correct you won't be able to get the column in pivot from asset table

In that case you can try to use script include approach

Table - sys_user

Condition: sys_id is one of javascript: new getassetsforusers().findUserswithMoreThan1Assets();

Sample Script Include below:

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

    findUserswithMoreThan1Assets: function() {
       
  var arr = [];
		var user = new GlideRecord('sys_user');
                user.addQuery('active', true);
		user.query();
		while(user.next()){
			var gr = new GlideRecord('alm_hardware');
			gr.addQuery('assigned_to', user.sys_id);
			gr.query();
			if(gr.getRowCount() >1){
				arr.push(user.sys_id.toString());
			}
		}
return arr.toString();

    },

    type: 'getassetsforusers'
});

Regards
Ankur

[Ashutosh Munot1]

HI,

You can check  my blog for this:

Scripting in Reports

I have created few use cases for this.

Thanks,
Ashutosh