/build/static/layout/Breadcrumb_cap_w.png

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


1 Comment   [ + ] Show comment
  • 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)

Posted by: chucksteel 10 years ago
Red Belt
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
 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ