Installed software for specific users
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.
1 Comment
[ + ] Show comment
Answers (2)
Answer Summary:
Please log in to answer
Posted by:
JasonEgg
5 years ago
Top Answer
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
Comments:
-
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? - hcjdm 5 years ago
Posted by:
BRUNOPEREIRA
1 year ago
Hello! I'm looking for something along those lines, but I'm following the code above and it doesn't work.Thanks
What if a user is associated with two devices? Do you want a list for each device, or a consolidated list? - JasonEgg 5 years ago