KACE Product Support

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
 

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

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