/build/static/layout/Breadcrumb_cap_w.png
04/28/2017 1052 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