Help Finding the "Assignee Name" in the kace database using mysql
Hello Guys.
I am creating a custom report in the k1.
I have an ODBC connection to the database with mysql.
I am trying to find the table with the "Assignee name", but for
the life of me, I can't.
In the "MACHINE" table I only see the "last logged on user", and
the "username" field.
I want to create a report to show that "last logged on user" is different
from the "assagnee name" field.
Answers (3)
Top Answer
select MACHINE.NAME as 'PC Name' ,
ASSET.NAME as 'Asset Name',
USER.USER_NAME as 'Assignee Name'
from MACHINE
join ASSET on ASSET.MAPPED_ID = MACHINE.ID
join USER on USER.ID = ASSET.OWNER_ID
Comments:
-
Thanks KevinG. Great Query - akmagnum 2 years ago
Hey akmagnum,
You can check this out for comparing last logged in user with assigned user report. You may modify the query to your needs. Cheers mate
SELECT
M.NAME,
M.MAC,
M.BIOS_SERIAL_NUMBER,
M.LAST_USER AS LAST_USER,
U.FULL_NAME AS ASSIGNED_USER
FROM MACHINE M
LEFT JOIN ASSET A ON M.ID = A.MAPPED_ID
LEFT JOIN USER U ON A.OWNER_ID = U.ID
ORDER BY M.NAME
Comments:
-
You gave me the answer i needed. Owner_id was the key. Thanks Unsullied. - akmagnum 2 years ago
You are probably struggling as the "Assignee Name" is actually an ASSET field not a MACHINE field, but is shown in the Summary section as dictated by the ASSET_TYPE of DEVICE.
So if you look in the ASSET table you will find the ASSET.OWNER_ID is the link to the USER table wherein you will find the name associated with that ID.
So something like this will show you the assigned user and the assigned owner
SELECT ASSET.NAME, USER.USER_NAME, MACHINE.USER_NAME
FROM (ASSET ASSET
INNER JOIN MACHINE MACHINE ON (ASSET.MAPPED_ID = MACHINE.ID))
INNER JOIN USER USER ON (ASSET.OWNER_ID = USER.ID)
WHERE ASSET.ASSET_TYPE_ID = 5
And the following would show only devices where the assigned name is different to the asset owner
SELECT ASSET.NAME, USER.USER_NAME, MACHINE.USER_NAME
FROM (ASSET ASSET
INNER JOIN MACHINE MACHINE ON (ASSET.MAPPED_ID = MACHINE.ID))
INNER JOIN USER USER ON (ASSET.OWNER_ID = USER.ID)
WHERE ASSET.ASSET_TYPE_ID = 5 AND
USER.USER_NAME != MACHINE.USER_NAME
Comments:
-
Thanks Hobbsy. ITNINJA never dissapoints - akmagnum 2 years ago
