/build/static/layout/Breadcrumb_cap_w.png

KACE Product Support Question


Installed software for specific users

06/14/2018 458 views
I've been going round and round and can't get it right.  Anyone run a report like this before?  I have a list of users that I need to get a list of installed software per user, if that makes sense.
Answer Summary:
1 Comment   [ + ] Show comment

Comments

  • What is your method for mapping users to devices? last user logged in? Assignee name?
    What if a user is associated with two devices? Do you want a list for each device, or a consolidated list?
    • Assignee name. We have a few instances of dual devices to one individual, consolidated would be fine. Appreciate the comment.

Answer Chosen by the Author

1
This report may be HUGE and take a while, depending on your environment. I would test it in a reporting app before creating a report on the box and/or include a LIMIT clause.
SELECT DISTINCT 
  USER.USER_NAME, 
  SOFTWARE.DISPLAY_NAME, 
  SOFTWARE.PUBLISHER, 
  SOFTWARE.DISPLAY_VERSION
FROM   MACHINE
       JOIN MACHINE_SOFTWARE_JT JT ON MACHINE.ID = JT.MACHINE_ID
       JOIN SOFTWARE ON JT.SOFTWARE_ID = SOFTWARE.ID
       JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID
       JOIN USER ON USER.ID = ASSET.OWNER_ID
WHERE  INVENTORY_RULE IS NULL
You will probably want to add criteria to the WHERE clause in order to get a more meaningful list.

That query will not report software of any computer that doesn't have an assignee. Here is a query to find devices that do not have an assignee:
SELECT MACHINE.ID,
  MACHINE.NAME,
  MACHINE.USER_LOGGED
FROM   MACHINE
       JOIN ASSET ON ASSET.MAPPED_ID = MACHINE.ID
WHERE  ASSET.OWNER_ID = 0
ORDER BY MACHINE.NAME
Answered 06/14/2018 by: JasonEgg
Red Belt

  • This seems to have worked. Thank you! You were right, it is huge and will take some cleanup but follow-up question, how would I add a Where username = user to limit the results to specific people? Would that work?
    • Yep. You can add "AND USER.USER_NAME = 'Jason'" to the end of that query to run it for a specific user
      • Nice, thank you!

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