I created the SQL query listed below which will be used as a system compliance report to run against the SCCM database. The problem is that if the software is not on the the system altogether my query filters it out in the where statement. I
want the query to return if the software is on the system and if it's the correct version or not. An example of the desired report is listed at the bottom of this message.
ARP.DisplayName0 AS [Application Name], ARP.Version0 AS Version, CASE WHEN ARP.DisplayName0 = 'Adobe Reader XI (11.0.01)' AND
ARP.Version0 = '11.0.01' THEN 'Compliant' WHEN ARP.DisplayName0 = 'QuickTime' AND
ARP.Version0 = '220.127.116.11' THEN 'Compliant' ELSE 'Not Compliant' END AS 'Application Status'
FROM dbo.v_Add_Remove_Programs AS ARP RIGHT OUTER JOIN
dbo.v_R_System AS SYS ON ARP.ResourceID = SYS.ResourceID
WHERE (SYS.Netbios_Name0 = @Sys_xx) AND (ARP.DisplayName0 LIKE 'Adobe Reader%' OR ARP.DisplayName0 LIKE 'Quicktime%')
GROUP BY ARP.DisplayName0, ARP.Version0
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.