Anyone know how to report on users with multiple assets?

[Kim Sullivan]

I know I can do a list view and then Group by user, but I don't then know how to filter out all the users with only (1) asset.   Help.

[Patrick DeCarl1]

Kim,

Not sure if there is an OOTB report type that will support your goal but below script(as an example) will do the trick. Can run script from fix scripts or background scripts.

More about GlideAggregate can be found at GlideAggregate

gatherMultiple();


function gatherMultiple() {
       var gr = new GlideAggregate('alm_hardware');
       gr.addAggregate('COUNT', 'assigned_to');
       gr.groupBy('assigned_to');
       gr.addHaving('COUNT', '>', 1);
       gr.query();


       gs.log("The following " + gr.getRowCount() + " User has multiple assets assigned to them...");
       while (gr.next()) {
               gs.log("multiple assets: " + gr.getAggregate('COUNT', 'assigned_to') + " => " + gr.assigned_to.getDisplayValue());

       }

}

Example output

[Patrick DeCarl1]

Hi Kim,

Did my comments above help you? If so, can you make it complete to help others know your questions was answer?

[raycallan · Accepted solution]

Hi Kim,

Just found this thread as I was looking for the similar solution whereby I wanted to find users assigned multiple computers and I have managed to create a report use Related lists

To get it to work your source table isUsers [sys-user] and under related list conditions select Asset -> Assigned to and change the Greater than or Equal to to 2

Hope this helps

Ray

[o_mcclain]

Hi raycallan! I have tried this approach; however, Users with only one asset appear on the report. Any idea as to why that would happen? I switched to the User table because I was initially using the alm_hardware table.