SCCM report/collection for computers with Internet Explorer with different versions

There might be some scenarios where in you would like to fetch computers with IE Versions, how many of them have IE8, IE9 ,how many of them do not have IE8 etc.

Through this post,i wanted to give some useful SQL quires and collections which would help you to create to meet your requirement .

The below is query that can be used to get all IE 7 machines.This assumes that , you have enabled software inventory client agent and by default, *.exe is listed.

Create a new report and paste the following the query.This can be customized as per the requirement.

Report for computers which has IE 7 Installed:

SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [IE Version],
d.Operating_System_Name_and0,
c.FilePath
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE     (c.FileName = 'iexplore.exe') AND (c.FileVersion like '7.00.6000%' )
ORDER BY a.Name0

If you want count the IE versions installed on computers,below is simple report to use.

 select filename as "executable name",left(FileVersion,14)as "File Version", COUNT(*) as Total from v_GS_SoftwareFile where FileName='iexplore.exe' group by FileName,FileVersion

Report for Computers which do not have have IE8 Installed (which may have IE7 or IE9 ):

SELECT DISTINCT a.Name0 AS [Machine Name],
b.SiteCode,c.FileVersion AS [IE Version],
d.Operating_System_Name_and0
FROM v_GS_SoftwareFile c INNER JOIN
v_GS_SYSTEM a ON c.ResourceID = a.ResourceID INNER JOIN
v_R_System d ON a.ResourceID = d.ResourceID INNER JOIN
v_FullCollectionMembership b ON a.ResourceID = b.ResourceID
WHERE     (c.FileName = 'iexplore.exe') AND (c.FileVersion not like '8%' )
ORDER BY a.Name0

Collection for computers which has IE version installed onto it filtered with Vista Computers (OS version 6.0%) :

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name,
SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup,
SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on
SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and
SMS_G_System_SoftwareFile.FileVersion like "8.0%" and
SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 6.0"

Collection for computers which doesn't have Internet Explorer(IE8):

For computers with windows 7  operating system,IE8 will not register in add and remove programs hence you can go with software file.This collection may list IE6,IE7 and IE 9 computers.

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId not in (select SMS_R_System.ResourceId from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "8.%")

SCCM Collection for Computers with Internet explorer 8(IE8) :

select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FileName = "iexplore.exe" and SMS_G_System_SoftwareFile.FilePath like "%prog%internet%" and SMS_G_System_SoftwareFile.FileVersion like "8.%" and SMS_R_System.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 6.0"

 

SSRS report for IE versions refer http://eskonr.com/2014/02/configmgr-ssrs-report-count-of-ie-versions/

56 Responses to "SCCM report/collection for computers with Internet Explorer with different versions"

  1. I need query for windows 7 with service pack & IE 11 versions & another without IE 11 installed. I tried many solution queries nothing I achieve via SCCM 2007 does anyone help me.

    Reply
    1. for windows 7 SP1,you can use v_gs_operating_system view along with v_r_system view ,for IE11 and without IE11,you can go with software inventory what is explained in the IE blog post. i will post it later when time permits.

      Reply
  2. I need query for windows 7 with service pack & IE 11 versions & another without IE 11 installed. I tried many solution queries nothing I achieve via SCCM 2007

    Reply
  3. Hi,

    I am trying to deploy IE 11 through software updates(SCCM 2012 R2) but many machine shows as compliant even though they are running IE8. I believe it might be reporting compliant since there are registry entry present for IE11. Is there any way to fix that?

    I am not sure if this is the correct link for asking some help.
    Please forgive me for that.

    Thanks
    Amit G

    Reply
    1. complaint means,the client do not require the patches means,they are not applicable.You can ignore the clients that says complaint and focus on unknown and non-compliant.

      Reply
  4. I need to make a inventory for plugins/ add ins/ extensions in IE/Chrome/Firefox. Any customized scripts please ?. Say for an instance i need all systems list with Adobe plugin installed in IE.

    Reply
  5. Hi Eswar ,

    Hope you are fine .
    I have tried "Report for computers which has IE 7 Installed" and it works fine but final list
    contains more than 10000 records .Could you please help to modify it so that the final report reflects only PC's which names begin with UA letters , for example
    Thanx

    Reply
  6. Hi Eswar,

    When I am running SQL query to get IE versions installed on all systems based on software file (iexplore.exe, v_GS_SoftwareFile); it fetches all the iexplore.exe available on a particular system not the currently installed version of iexplore.exe .Hence we get multiple entries for a particular system.

    Could you please help me out to get the current installed IE version on all system.

    Reply
    1. what do you meant by currently installed ? this report will get you count of all computer with different version of IE.Each computer will have one version of IE which is based on iexplore.exe

      Reply
  7. Hi Eshwar,

    I would like to add new set of favourites on IE 8 & IE 9 via SCCM, I know it can be done via GPO, but i need to know how this can be implemented via SCCM and your help with the scripts.

    Secondly, please provide reference to few good site, if you know where I can lean these scriptings.

    Reply
    1. I would suggest to go with GPO for easy tracking and to function it correctly.
      If you still want to go with Script via SCCM,you can try this script.

      Option Explicit
      dim path
      path=CreateObject("WScript.Shell").ExpandEnvironmentStrings("%UserProfile%\Favorites")
      dim objFSO
      set objFSO=CreateObject("Scripting.FileSystemObject")

      objFSO.CopyFile "\\servername\favourites\eskonr.url", path & "\"

      change if you need something more.

      I tested it manually and it worked for me.

      Reply
  8. I found that using the query below which is slightly modified from Eswar's worked at finding more computers with whatever specific version of IE I was looking for... Notice the "%" after Program Files...

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like "%\\Program Files%\\Internet Explorer\\" and SMS_G_System_SoftwareFile.FileName like "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "8.%"

    Reply
  9. I am not even sure if this can be done. But I need to know if there is a way in SCCM 2012 to report on IE version usage? Not like IE 7 or 8, but as in 32 or 64 bit. I am trying to convince our IT manager that Java 64 bit is not necessary because most people in our firm dont even use IE 64 bit.

    Reply
    1. never tried before about IE Usagae and no point in tracking Usage of IE as this is the default browser with windows and everyone uses it.
      may be you can try to add iexplore.exe for metering usage.

      Reply
  10. Hi,

    I used the "Report for computers which has IE 7 Installed" query, I just added "and Operating_System_Name_and0 LIKE 'Microsoft Windows NT Workstation%'" in order to scope only Windows workstations.
    The problem is this query lists all IE7 versions installed, some computers appears a multiple times in my reports.
    Is there a way to make computers appear only once even if multiple IE7 versions are installed?

    Reply
    1. I just ran the query but i dont see any duplicate columns.Can you take close look at the double computer names if there any any difference in the results columns ?

      Reply
  11. Hi, I'm having a problem generating an SCCM report where we want to confirm that IE6 is not running on Windows 7 machines. Since IE 6 is not in Add/remove programs I running into difficulty coming up withan accurate report. Any suggestions would be appreciated. Thanks

    Reply
    1. by default windows 7 machines do not come up IE6 and it is IE 7 or more(havent checked). To find out the Version of IE6,you will have find it using 'shdocvw.dll'file from C:\windows\system32 Ref: http://support.microsoft.com/kb/969393
      Here is the report to to list IE6 machines using the DLL. http://myitforum.com/cs2/blogs/cstauffer/archive/2009/12/22/how-to-find-ie6-or-older-in-your-environment-with-sms-or-sccm.aspx
      but ensure to add this dll in inventory else you will see empty results.

      Reply
    1. The query which i posted is Collection not report.
      To get report,try the below .Replace the quotes with original as the blog convert's them to fancy.

      SELECT TOP (100) PERCENT dbo.v_R_System.Name0, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, DATEDIFF(Day,
      dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) AS [Days since last boot], dbo.v_GS_OPERATING_SYSTEM.Caption0
      FROM dbo.v_GS_OPERATING_SYSTEM INNER JOIN
      dbo.v_R_System ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID
      WHERE (DATEDIFF(Day, dbo.v_GS_OPERATING_SYSTEM.LastBootUpTime0, GETDATE()) > 7) AND (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%xp%') OR
      (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%Windows 7%') OR
      (dbo.v_GS_OPERATING_SYSTEM.Caption0 LIKE '%vista%')
      ORDER BY [Days since last boot]

      Reply
  12. HI Eswar,

    Can you help me to write an SCCM query for number of computers being loggedoff but not shutdown for 1 week.

    Any help will be highly appreciated

    Rajeev.

    Reply
    1. Hi Rajeev,
      Logged off information will not be avilable in sccm and if you need it ,you may have get it somehow using registry information or status messges..never tried it before But
      you can try this report for computers not restarted since 1 week

      SELECT DISTINCT SMS_R_System.Name, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, SMS_G_System_OPERATING_SYSTEM.Caption
      FROM SMS_R_System INNER JOIN SMS_G_System_OPERATING_SYSTEM ON SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceId
      WHERE (SMS_G_System_OPERATING_SYSTEM.Caption like "%xp%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%vista%" or SMS_G_System_OPERATING_SYSTEM.Caption like "%windows 7%") and (DateDiff(day, SMS_G_System_OPERATING_SYSTEM.LastBootUpTime, GetDate()) >7)

      Reply
  13. Hello Eswar,

    I have tried to use the SCCM query 'Collection for Computers which doesn't have IE8' but I keep getting a syntax error when I hit OK in the dialog box of the query statement it simply states : "This query has a syntax error. Are you sure you want to save it?"

    I am assuming that the query is not meant to be run on the SQL server using the SQL query manager... but in SCCM console yes?

    Please let me know because I am in need of a query to create a collection of machines that do NOT have IE8 installed so I can target them for IE8 deployment and your query cam up in the Microsoft blog and it looked like the one I needed.

    Any help gratefully accepted.

    Regards,

    Graham

    Reply
    1. query works correctly but the only change u need is replace the quoates(') around Iexplore and 8 as they do not work in sccm reports.
      btw.,did you enable software inventory agent looks for *.exe files ? without any exclusions to c:\program files ?

      Reply
  14. HI Eswar,

    i have tried but its not working i have run the command from command prompt and run also.
    its not working. can you please guide me if i am doing wrong.

    Reply
  15. HI Eswar,

    Thanks for the info.

    I want to start the SMS Agent service in our organization users machine. Is there any script for that.

    Regards,
    Srinivas.

    Reply
  16. Hi Eswar,

    Can you please guide me to run SCCM service in client machines. Is there any script available for it.

    Please help me out.

    Regards,
    Srinivas.

    Reply
  17. Neither:
    Report for Computers which doesnt have IE8 Installed (which may have IE7 or IE9 ):
    OR:
    Collection for computers which doesn’t have Internet Explorer(IE8):

    Is NOT working, it is not a valid SQL statment.

    Reply
    1. Mark,
      what does the error message gives you when you run report Report for Computers which doesnt have IE8 Installed (which may have IE7 or IE9 ) ? Its working for me and i can see all computers with IE versions except IE8.

      Collection for computers which doesn’t have Internet Explorer(IE8): Yes,it is not a SQL(Reports uses SQL) and it is WQL (collection uses WQL language). Query is correct but that doesnt list computers with IE8 because ,not like function will not return the correct restuls though. I have updated the collection query with subslected function. This should make you clear on this.

      Reply

Leave a Reply to Alex Cancel reply