What is the average number or items within the Add/Remove Per PC?

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

Leave a Reply

Your email address will not be published. Required fields are marked *