While the previous reports you will see here were based on the Tabular format model, a post on the Sccm community forum brought my attention.
Here is the layout that was requested:
App A Version App B Version App C Version App D Version
Machine X 6.0.5 1.2 1.0 2.8
Machine Y 6.05 1.0 1.0 2.6
Machine Z 6.0.5 1.1 doesn’t exist 2.8
This would of course be very usefull when dealing with groups of applications that would have to interact with each-other.
For example purpose, I will create a report that shows some Adobe products, which also means that some filters in the query would be different for other products.
Here is a query we can use for the base extract:
SELECT Distinct ‘Product’=
Case
When b.displayname0 like ‘Adobe Acrobat%’ Then ‘Adobe Acrobat’
When b.displayname0 like ‘Adobe Photoshop%’ Then ‘Adobe Photoshop’
When b.displayname0 like ‘Adobe Dreamweaver%’ Then ‘Adobe Dreamweaver’
When b.displayname0 like ‘Adobe Framemaker%’ Then ‘Adobe Framemaker’
Else b.displayname0
End,
‘Version’ =
Case
when b.version0 like ’12%’ then ’12′
when b.version0 like ’3%’ then ’3′
when b.version0 like ’11%’ then ’11′
When b.version0 like ’10%’ then ’10′
When b.version0 like ’9%’ then ’9′
when b.version0 like ’8%’ then ’8′
when b.version0 like ’7%’ then ’7′
when b.version0 like ’006%’ then ’6′
when b.version0 like ’6%’ then ’6′
when b.version0 like ’5%’ then ’5′
when b.version0 like ’4%’ then ’4′
End,
‘Publisher’=
Case
When b.publisher0 like ‘Adobe%’ Then ‘Adobe Corporation’
End,
a.Netbios_Name0
FROM v_R_System AS a INNER JOIN
v_Add_Remove_Programs AS b ON a.ResourceID = b.ResourceID INNER JOIN
v_FullCollectionMembership AS c ON a.ResourceID = c.ResourceID
WHERE
((b.displayname0 like ‘Adobe Acrobat%’) or (b.displayname0 like ‘Adobe Photoshop%’)
or (b.displayname0 like ‘Adobe Dreamweaver%’) or (b.displayname0 like ‘Adobe Framemaker%’))
And (b.version0 <> ’1.0′)
order by netbios_name0
When creating the report, we choose the Matrix format, and set The product for the Columns and the Netbios_Name0 for the rows, with Version in the details
Here is how it looks
