Adding OU to a report for MS Office installations

SELECT DISTINCT
 dbo.v_RA_System_IPSubnets.IP_Subnets0,
 dbo.v_RA_System_SystemOUName.System_OU_Name0,
 dbo.v_R_System.Netbios_Name0,
 dbo.v_R_System.User_Name0,
 dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0
FROM
 dbo.v_GS_ADD_REMOVE_PROGRAMS LEFT OUTER JOIN dbo.v_CM_RES_COLL_SMS00004 ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_CM_RES_COLL_SMS00004.ResourceID
 INNER JOIN dbo.v_R_System ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_R_System.ResourceID
 INNER JOIN dbo.v_RA_System_IPSubnets ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_IPSubnets.ResourceID
 INNER JOIN dbo.v_GS_SoftwareFile ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_GS_SoftwareFile.ResourceID
 left JOIN dbo.v_RA_System_SystemOUName ON dbo.v_GS_ADD_REMOVE_PROGRAMS.ResourceID = dbo.v_RA_System_SystemOUName.ResourceID
WHERE
 dbo.v_RA_System_IPSubnets.IP_Subnets0 NOT LIKE ‘%.0.0’
 AND ((dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%microsoft%office%pro%’
 OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%microsoft%office%stan%’)
 AND NOT (dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%visio%’
 OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%Proof%’
 OR dbo.v_GS_ADD_REMOVE_PROGRAMS.DisplayName0 LIKE ‘%Project%’))
ORDER BY
 dbo.v_RA_System_IPSubnets.IP_Subnets0,
 dbo.v_R_System.Netbios_Name0

Leave a Reply

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