Reporting on asset history to determine machine owners
I'd like to know if anyone else has attempted this type of report and if they are aware of how to automatically select the user that has logged into each machine the most often. I've created a report with the information for all logins for each machine and the number of times each user logged in grouped by machine, but am unable to get it condensed into the information I need. An alternative I've come up with is to export to excel and sort by machine name, then user login count, but I'd like to see if it is possible to get it done in the script itself. Thanks!
Here's what I've come up with:
ASSET_HISTORY.NAME as 'Machine Name',
ASSET_HISTORY.VALUE2 as 'Username',
Count('Machine Name') as 'Number of Logins',
MACHINE.USER_FULLNAME as 'Last User'
MACHINE ON (ASSET_HISTORY.NAME = MACHINE.NAME)
ASSET_HISTORY.CHANGE_TYPE like 'Modification'
and ASSET_HISTORY.FIELD_NAME like 'USER_FULLNAME'
GROUP BY ASSET_HISTORY.NAME , ASSET_HISTORY.VALUE2
ORDER BY ASSET_HISTORY.NAME, ASSET_HISTORY.VALUE2