K1000: Add Assigned User to Existing Machine Software Report
Hello, I have this report that polls all computers and grabs whether a certain software package is "Installed" or "Not Installed". What I want to do is add who the Assigned User is (from the Asset table), but I can't figure out how to edit the SQL to add it in from the other table. Any help would be greatly appreciated! Current SQL is below:
select if(sum(if(DISPLAY_NAME like '%My Software Name%', 1, 0)) = 0, "Not Installed", "Installed") as INSTALLED, MACHINE.NAME,MACHINE.IP, MACHINE.LAST_SYNC, MACHINE.USER_LOGGED as USER_LOGGED
from MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
where MACHINE.CHASSIS_TYPE = "Desktop" or MACHINE.CHASSIS_TYPE = "Laptop"
group by MACHINE.NAME
order by INSTALLED, USER_NAME
Answers (1)
Here you go:
select if(sum(if(DISPLAY_NAME like '%My Software Name%', 1, 0)) = 0, "Not Installed", "Installed") as INSTALLED,
MACHINE.NAME,MACHINE.IP, MACHINE.LAST_SYNC, MACHINE.USER_LOGGED as USER_LOGGED,
USER.FULL_NAME as "Assigned User"
from MACHINE
LEFT JOIN MACHINE_SOFTWARE_JT ON MACHINE.ID = MACHINE_SOFTWARE_JT.MACHINE_ID
LEFT JOIN SOFTWARE ON MACHINE_SOFTWARE_JT.SOFTWARE_ID = SOFTWARE.ID
LEFT JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID and ASSET.ASSET_TYPE_ID = 5
LEFT JOIN USER on USER.ID = ASSET.OWNER_ID
where MACHINE.CHASSIS_TYPE = "Desktop" or MACHINE.CHASSIS_TYPE = "Laptop"
group by MACHINE.NAME
order by INSTALLED, USER.USER_NAME
To get to the assigned user you need to first join to the asset table:
LEFT JOIN ASSET on ASSET.MAPPED_ID = MACHINE.ID and ASSET.ASSET_TYPE_ID = 5
Then from there to the user table:
LEFT JOIN USER on USER.ID = ASSET.OWNER_ID
Comments:
-
Thank you, this worked perfectly! I appreciate the thorough explanation.
One other question, why does KACE automatically add a ROLLUP to ORDER BY statements? I would like to run the report and order by a certain field but not roll it up. Any ideas? I tried "without rollup" and that did not work. Thanks. - woody1878 5 years ago-
I'm not sure what you mean by "rollup". If you are referring to the "group by MACHINE.NAME" that is necessary because of the use of SUM in the select clause. - chucksteel 5 years ago
