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

In some other context, you might want to change the row colours based on conditions (If AppA version is lower than AppC version, then it’s unsupported > set background row colour to RED)
Advertisements