During a resent discussion within the mailing list this request was posted.
“I need to run a report of all software installed for each computer in a specific collection (or an AD site..Whichever)… Similar to the “Summary of Installed Software in a Specific Collection” report that comes with SP2, but rather than a summary, I want each computer name listed, with all installed software for each computer”
The report in question would look something like this
The problem I have with this is: Based on the average number or items with in my test database, each PC would have 186 rows! This makes this report unusable! However management and auditor continue to ask for this type of report without knowing what it really means.
What they should be asking for is a high level count of all applications, then be able to drill down to a list of PCs with that application.
So what is your average number of rows per PC?
SELECT Count(v_GS_ADD_REMOVE_PROGRAMS.DisplayName0) AS 'Count0'
FROM v_GS_ADD_REMOVE_PROGRAMS v_GS_ADD_REMOVE_PROGRAMS, v_GS_COMPUTER_SYSTEM v_GS_COMPUTER_SYSTEM
WHERE v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
GROUP BY v_GS_COMPUTER_SYSTEM.Name0
select avg(count0) from #avge
drop table #avge
BTW if really want to kill a few trees here is the query that will produce the huge report requested above.
v_GS_COMPUTER_SYSTEM.ResourceID = v_GS_ADD_REMOVE_PROGRAMS.ResourceID
AND v_GS_COMPUTER_SYSTEM.ResourceID = v_CM_RES_COLL_SMS00001.ResourceID