SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager

SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Managerr? SCCM Custom report/SQL query IBM and Oracle application.

SCCM Custom report/SQL query to find out the details of machines/workstations which have Oracle or IBM related application installed in your organization with Site code and country details. I know, it’s useful to create an 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.

SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager?

SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager
SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager

SQL Query – SCCM SQL Query IBM or Oracle Custom Query

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 the application installation details from Add Remove Programs view.

SCCM SQL Query IBM or Oracle Custom Query Along with Location Country Details Configuration Manager?

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  

Resources

SCCM Related Posts Real World Experiences Of SCCM Admins (anoopcnair.com)

1E Nomad

SCCM Video Tutorials For IT Pros – HTMD Blog #2 (howtomanagedevices.com)

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.