Query a History of whos used what computer
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.
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.
1 Comment
[ + ] Show comment
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
7 years ago
Top Answer
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.
Posted by:
armasmike
7 years ago
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
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 ?
| 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 ?
Comments:
-
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. - JasonEgg 7 years ago
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. - armasmike 7 years ago