SCCM 2012 and 2007 Custom report/SQL query to find out the details of machines/workstations which has Oracle or IBM related application installed in your organization with Site code and country details. I know, it’s useful to create SCCM custom report for the same but I didn’t get time to create one. Also, I’m not an expert in SQL queries (as mentioned in the previous posts :)). I used SQL “LEFT join” to join the different views of SCCM 2007 and 2012. Make sure you replace the site code and Country names (which I used in CASE statement) as per your requirement.
Following is the SQL query that you can use to find the machine name, Location and country on basis of application installation (oracle and IBM) in this scenario. You can use this query to find out any of application installation details from Add Remove Programs view.
select DISTINCT DisplayName0, ProdID0, Sitecode, Publisher0, v_R_System.Netbios_Name0, dbo.v_FullCollectionMembership.SiteCode, CASE WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' THEN 'India' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'C%' THEN 'Japan' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'D%' THEN 'HongKong' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'E%' THEN 'HongKong' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'F%' THEN 'US' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'G%' THEN 'US' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'H%' THEN 'Belgium' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'I%' THEN 'Germany' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'J%' THEN 'France' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'K%' THEN 'Italy' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'L%' THEN 'PORTUGAL' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'M%' THEN 'SPAIN' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'N%' THEN 'UK' WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'O%' THEN 'UK' ELSE 'Unidentified' END AS 'Country' From v_R_System JOIN v_GS_ADD_REMOVE_PROGRAMS ARP on v_R_System.ResourceID = ARP.ResourceID JOIN v_FullCollectionMembership on v_R_System.ResourceID = v_FullCollectionMembership.ResourceID LEFT JOIN v_GS_WORKSTATION_STATUS HWSCAN on v_R_System.ResourceID = HWSCAN.ResourceID Where ((DisplayName0 like '%IBM%' or DisplayName0 like '%Oracle%' )) Group by v_R_System.Netbios_Name0, DisplayName0, ProdID0, Publisher0, Sitecode
