Help with modifying custom report SQL Query for SCCM compliance


  • 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 = '' 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

    Desired Output

    Application name   Version      Status

    Adobe Reader        11.0.1       Compliant

    Adobe Shockwave  10.1.3      Not Compliant

    Adobe Flashplayer   N/A         Missing

    Donnerstag, 20. Juni 2013 13:24