/build/static/layout/Breadcrumb_cap_w.png

Scripting Question


Query a History of whos used what computer

04/28/2017 1130 views
My Code I am using MySQL Workbench.

USE ORG1;
SELECT DISTINCT ASSET_HISTORY.NAME AS Computer_Name, MACHINE.NAME AS MACHINE_Name,  MACHINE.USER AS USER, MACHINE.IP AS IP,
ASSET_HISTORY.TIME AS Time_loged ,ASSET_HISTORY.ASSET_ID as ID
FROM MACHINE
Join ASSET_HISTORY ON (ASSET_HISTORY.ASSET_ID = MACHINE.ID)
JOIN ASSET ON (ASSET_ID = ASSET_HISTORY.ASSET_ID)
Where MACHINE.NAME = "THE COMPUTERS NAME"

So i want to try in a computer and it will fine a history of Who has used that computer.


Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • Nice that worked great. if i change

    Where MACHINE.NAME = "THE COMPUTERS NAME"

    TO

    Where MACHINE.USER = "Users Name"

    Iget a error code :2013 Lost connextion to MySQL server during query.

    I am using MySQL Workbench.

Answer Chosen by the Author

0
Asset ID does not map to Machine ID. There actually is a "MAPPED_ID" field in the ASSET table that would be a better candidate for a JOIN of MACHINE and ASSET, but I have found it is not reliable unless you are maintaining your asset inventory very well. Additionally, you need to get the value of the user from the ASSET_HISTORY table instead of the MACHINE table. So this query should work for your purposes:
SELECT DISTINCT ASSET_HISTORY.NAME AS ASSET_NAME, 
  MACHINE.NAME AS MACHINE_NAME, 
  MACHINE.IP AS IP,
  ASSET_HISTORY.VALUE2 AS Logged_User,
  ASSET_HISTORY.TIME AS Time_logged,
  ASSET_HISTORY.ASSET_ID as ASSET_ID
FROM ASSET_HISTORY
  JOIN ASSET ON ASSET.ID = ASSET_HISTORY.ASSET_ID
  JOIN MACHINE ON (ASSET.NAME = MACHINE.NAME 
    OR ASSET.NAME = MACHINE.BIOS_SERIAL_NUMBER
    OR ASSET.MAPPED_ID = MACHINE.ID)
WHERE MACHINE.NAME = "YOUR-COMPUTER-NAME"
  AND ASSET_HISTORY.FIELD_NAME = 'USER'
This may get some erroneous results if there are any duplicate computer names. 
Answered 05/03/2017 by: JasonEgg
Red Belt

All Answers

0
So i been testing the code on some very old computers. We are still using them they first showed up in 2014


new-T702-9  
new-T702-9  
Bob   192.168.254.44   2014-01-29 16:06:48  
360
new-T702-9  new-T702-9  
Bob  192.168.254.44  
2017-05-03 12:46:34   360

IP number are the same that can't be right.

And if i look in AD for the user bob the account was created in  10/9/2015 12:03:38 PM

so the user Bob couldn't be using the computer in 2014.

So is there a Join missing ?

If i look at the ASSET_HISTORY.FIELD_NAME for USER LOGGED i fine this

'389674', 'Computer', '360', 'new-T702-9', '2014-05-07 10:05:25', '0', 'Reported by Agent', 'Modification', 'USER_LOGGED', 'xxxxx\\jSmith', 'xxxxx\\administrator', 'User Logged'

So am I missing something with joins to get the true history of the computer ?





Answered 05/03/2017 by: armasmike
Senior White Belt

  • I see now: this is because you're selecting user from the MACHINE table instead of the ASSET_HISTORY table. I'll have to paste a full query.
    • i am excited to see how that is written i am new to dell k1000 so the more i see and learn the better
    • Hi Jason any update on this question ?
      • I edited the query in my original answer. If you replace YOUR-COMPUTER-NAME with a valid computer, does it work?
      • Works great

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
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