PCs with Either of two applications installed.

Use this query to determine if a PC has either or both applications installed.

For more details, please see

http://social.technet.microsoft.com/Forums/systemcenter/en-US/6c4650c7-3246-4e13-a78d-05691c13c89d/duplicate-rows-when-finding-pcs-with-installed-software?forum=configmgrreporting

 

SELECT DISTINCT 
    R.Netbios_Name0, 
    R.User_Domain0+'\'+    R.User_Name0 as 'User Name', 
    OS.Caption0 AS 'Operating System Caption', 
    CASE isnull(WIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application1', 
    CASE isnull(MIR.ResourceID,0 ) 
        When 0 THEN ' ' 
        ELSE '*' 
    END AS 'Application2'
 FROM    
    dbo.v_R_System_Valid AS R 
    INNER JOIN dbo.v_GS_OPERATING_SYSTEM OS ON R.ResourceID = OS.ResourceID 
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Warranty Information Reporting%') as WIR ON R.ResourceID = WIR.ResourceID
    Left outer join (Select ARP.ResourceID as 'ResourceID' from dbo.v_Add_Remove_Programs ARP Where arp.DisplayName0 like '%Monitor Information Reporting%') as MIR ON R.ResourceID = MIR.ResourceID
 ORDER BY 
    R.Netbios_Name0

Leave a Reply

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