/build/static/layout/Breadcrumb_cap_w.png
10/15/2019 142 views

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


0 Comments   [ + ] Show comments

Comments


All 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





Answered 10/16/2019 by: chucksteel
Red Belt

  • 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.
    • 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.