Advertisement

SCCM ConfigMgr 2012 SQL Query to Find Out IE Version with OS Details of Workstations

In this post, I’m going to share the SQL query to fetch IE version details from SCCM/ConfigMgr DB. Microsoft released new version of browser called Microsoft Edge as part of Windows 10 and they announced that Internet Explorer 11 would be the only supported version of IE going forward. To fecth the information of IE versions from SCCM/ConfigMgr, we need to enable Software Inventory. However, SCCM software inventory is not very popular among MVPs and even I don’t recommend enabling SCCM Software Inventory for inventorying all the exe file versions rather I would enable selective inventory on selective folders as you can see in the following screen capture. In case, you want to take the inventory of Microsoft Edge then you can collect the information of “MicrosoftEdge.exe” file from the following location “%windir%\SystemApps\Microsoft.MicrosoftEdge_8wekyb3d8bbwe\

SCCM 2012 Custom Report SQL Query to IE Version with OS Details 1

In the following SQL query, I used SQL Inner Join and Left Join along with CASE (SQL CASE is a very unique conditional statement providing if/then/else logic for any ordinary SQL command, such as SELECT or UPDATE).

SCCM 2012 Custom Report SQL Query to IE Version with OS Details

The following SQL query fetches Internet explorer version details of the workstations from SCCM/ConfigMgr DB. In addition, this SQL query for IE explorer provides SCCM site code, Location Details and Operating System details of the workstations.

SELECT DISTINCT 
dbo.v_R_System.Netbios_Name0,
ARP.FileName,
ARP.FileVersion, 
CASE
WHEN ARP.FileVersion LIKE '4.%' THEN 'IE 4'
WHEN ARP.FileVersion LIKE '5.%' THEN 'IE 5'
WHEN ARP.FileVersion LIKE '6.%' THEN 'IE 6'
WHEN ARP.FileVersion LIKE '7.%' THEN 'IE 7'
WHEN ARP.FileVersion LIKE '8.%' THEN 'IE 8'
WHEN ARP.FileVersion LIKE '9.%' THEN 'IE 9'
When ARP.FileVersion LIKE '10.%' Then 'IE 10'
When ARP.FileVersion LIKE '11.%' Then 'IE 11'
ELSE '??' END AS 'Internet Explorer Ver',
dbo.v_FullCollectionMembership.SiteCode,
CASE
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'B%' THEN 'India'
WHEN dbo.v_FullCollectionMembership.SiteCode LIKE 'A%' 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',
v_GS_OPERATING_SYSTEM.Caption0
FROM dbo.v_R_System 
 INNER JOIN dbo.v_GS_SoftwareFile AS ARP ON dbo.v_R_System.ResourceID = ARP.ResourceID 
 INNER JOIN dbo.v_FullCollectionMembership ON dbo.v_R_System.ResourceID = dbo.v_FullCollectionMembership.ResourceID 
 LEFT JOIN dbo.v_GS_WORKSTATION_STATUS AS HWSCAN ON dbo.v_R_System.ResourceID = HWSCAN.ResourceID
 Inner Join v_GS_OPERATING_SYSTEM ON v_GS_OPERATING_SYSTEM.Resourceid=v_R_System.Resourceid
WHERE (ARP.FileName = 'iexplore.exe') AND (ARP.FilePath LIKE '%:\prog%internet%') 
 and (sitecode != 'NULL') and (v_GS_OPERATING_SYSTEM.Caption0 != '%server%')
GROUP BY ARP.FileName, ARP.FileVersion, 
dbo.v_FullCollectionMembership.SiteCode, dbo.v_R_System.Netbios_Name0,ARP.FilePath,v_GS_OPERATING_SYSTEM.Caption0

About Author 

Anoop is Microsoft MVP and Veeam Vanguard ! He is a Solution Architect on enterprise client management with more than 13 years of experience (calculation done on the year 2014) in IT. He is Blogger, Speaker and Local User Group Community leader. His main focus is on Device Management technologies like SCCM 2012,Current Branch, Intune. He writes about the technologies like SCCM, SCOM, Windows 10, Azure AD, Microsoft Intune, RMS, Hyper-V etc...

    Find more about me on:
  • googleplus
  • twitter
  • facebook
  • linkedin
  • youtube
Posted in: ConfigMgr (SCCM), SCCM 2007, SCCM 2012, Script, SQL

4 Comments

  1. Dan B says:

    When I run the report, it returns blank results. I have software inventory running. What am I doing wrong?

Leave a Comment and Contact Anoop