SCCM SQL Query to Find Out IE Version with OS Details of Workstations Configuration Manager ConfigMgr? In this post, I’m going to share the SQL query to fetch IE version details from SCCM/ConfigMgr DB.
Microsoft released a new version of the browser called Microsoft Edge as part of Windows 10. Microsoft (they) announced that Internet Explorer 11 would be the only supported version of IE going forward.
To fetch 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.
SCCM SQL Query to Find Out IE Version with OS Details?
In case, you want to take the inventory of Microsoft Edge then you can collect the information of the “MicrosoftEdge.exe” file from the following location:
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).
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
SCCM Related Posts Real World Experiences Of SCCM Admins (anoopcnair.com)
SCCM Video Tutorials For IT Pros – HTMD Blog #2 (howtomanagedevices.com)
Anoop is Microsoft MVP! He is a Solution Architect in enterprise client management with more than 20 years of experience (calculation done in 2021) in IT. He is a blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. E writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc…
7 thoughts on “SCCM SQL Query to Find Out IE Version with OS Details of Workstations Configuration Manager ConfigMgr”
Nice one (Y)
How about getting this information using Editing .Mof files?
Better approach to collect the inventory !!
When I run the report, it returns blank results. I have software inventory running. What am I doing wrong?
what if i want to know whether the installed version is an RTM one or the definitive one?
I ran into a minor issue with this query and just wanted to share my fix.
With (v_GS_OPERATING_SYSTEM.Caption0 != ‘%server%’) using “!=” seems to not do what was wanted and will return results from servers. This is because != literally looks for %server% and doesn’t use % as a wildcard.
Rather, I had to use: (v_GS_OPERATING_SYSTEM.Caption0 NOT LIKE ‘%server%’) to actually exclude results from servers.
Or another option is to add: AND (dbo.v_FullCollectionMembership.CollectionID = ‘XXXXXXXX’) to the WHERE statement to limit results from a specific collection that just has workstations.
Not sure why but when I copy the above query statement into a new query and click OK I get a syntax error. Any clues?