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

Comments

  • Do you want the report to show just the top user?
    • Yes, the user with the most logins per machine name.
Please log in to comment

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.

 

Answered 04/08/2014 by: chucksteel
Red Belt

  • Worked like a charm! Thanks chucksteel!
Please log in to comment
Answer this question or Comment on this question for clarity