Reporting on asset history to determine machine owners
Hi all,
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:
SELECT
ASSET_HISTORY.NAME as 'Machine Name',
ASSET_HISTORY.VALUE2 as 'Username',
Count('Machine Name') as 'Number of Logins',
MACHINE.USER_FULLNAME as 'Last User'
FROM
ASSET_HISTORY
join
MACHINE ON (ASSET_HISTORY.NAME = MACHINE.NAME)
WHERE
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
Limit 50000
-
Do you want the report to show just the top user? - chucksteel 10 years ago
-
Yes, the user with the most logins per machine name. - joecool 10 years ago
Answers (1)
I was only able to do this by using two sub-select statements so it's a bit messy. It also took a long time to run on my KBOX:
SELECT MACHINE.NAME, MACHINE.USER_LOGGED,(SELECT COUNT(ASSET_HISTORY.ID) FROM ORG1.ASSET_HISTORY WHERE ASSET_HISTORY.CHANGE_TYPE like 'Modification' and ASSET_HISTORY.FIELD_NAME like 'USER_NAME' and ASSET_HISTORY.NAME = MACHINE.NAMEGROUP BY ASSET_HISTORY.NAME, ASSET_HISTORY.VALUE2ORDER BY COUNT(ASSET_HISTORY.ID) DESCLIMIT 1) as LoginCount,(SELECT ASSET_HISTORY.VALUE2 FROM ORG1.ASSET_HISTORY WHERE ASSET_HISTORY.CHANGE_TYPE like 'Modification' and ASSET_HISTORY.FIELD_NAME like 'USER_NAME' and ASSET_HISTORY.NAME = MACHINE.NAMEGROUP BY ASSET_HISTORY.NAME, ASSET_HISTORY.VALUE2ORDER BY COUNT(ASSET_HISTORY.ID) DESCLIMIT 1) as UserNameFROM ORG1.MACHINE;
Note that I queried on changing user_name and not user_fullname so it's a little different. You should also keep in mind that depending on your agent inventory period this type of report won't be an accurate measure of who used a computer since the asset_history table is only updated when the computer runs an inventory so every login might not be accounted.
Comments:
-
Worked like a charm! Thanks chucksteel! - joecool 10 years ago
