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
COMPUTER1 App1
COMPUTER1 App2
COMPUTER1 App3
COMPUTER2 App1
COMPUTER2 App2
COMPUTER2 App3
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'
into #avge
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.
SELECT v_GS_COMPUTER_SYSTEM.Name0,
v_GS_ADD_REMOVE_PROGRAMS.DisplayName0,
v_GS_ADD_REMOVE_PROGRAMS.ProdID0,
v_GS_ADD_REMOVE_PROGRAMS.Publisher0,
v_GS_ADD_REMOVE_PROGRAMS.InstallDate0
FROM
v_CM_RES_COLL_SMS00001 v_CM_RES_COLL_SMS00001,
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
AND v_GS_COMPUTER_SYSTEM.ResourceID = v_CM_RES_COLL_SMS00001.ResourceID
Order by
v_GS_COMPUTER_SYSTEM.Name0,
v_GS_ADD_REMOVE_PROGRAMS.ProdID0