/build/static/layout/Breadcrumb_cap_w.png

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



0 Comments   [ + ] Show comments

Answers (1)

Posted by: chucksteel 4 years ago
Red Belt
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 4 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 4 years ago

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ