/bundles/itninjaweb/img/Breadcrumb_cap_w.png
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:
Cancel
1 Comment   [ - ] Hide 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.
Please log in to comment

Answer Chosen by the Author


Answers

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!
Please log in to comment
Answer this question or Comment on this question for clarity

Answers