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 Comments
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.
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.
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
what have you tried to achieve this ? may be i can help you to check your query.
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
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.
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.
Not possible direct way but you can create script to pipe the information into wmi and use regkey2mof to report this info sccm server but again,IE addon are per user but not system.You should consider this as well while working on scripts. refer this link https://social.technet.microsoft.com/forums/systemcenter/en-US/cecb8c11-b5d8-47d5-9938-bea9d8e22af9/sccm-report-internet-explorer-add-ons
Thank you so very Much Eswar!!
Hi Eswar ,
It works.Thanks a lot !
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
add condition with and D.name0 like 'UA%'
Thank you Eswar, worked like a charm. Just changed 8.0 to 11.0 for a IE11 collection. /Martin
Glad it helped 🙂
Hi Eswar,
Could you please give me a query to find machine names with user name. I need to find computer name for bunch of users.
you use this report to find user with list of machines http://eskonr.com/2012/08/sccm-configmgr-report-find-computers-for-the-list-of-selected-users/
for custom query,use the tables ,v_gs_computer_system/V_r_system to get the list of computers or users.
Please provide me to find out MS office version with service pack.
Please do the needful.
Thanks
did you try this report ? http://eskonr.com/2012/05/sccm-linked-report-ms-office-version-with-service-pack-installed-machines/
I really like your blog.. very nice colors & theme. Did you create this website yourself or did you
hire someone to do it for you? Plz respond as I'm looking to design my own blog and would like to find out where u got this from.
appreciate it
its wordpress theme .you can get it online for free.
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.
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
Pingback: SCCM report/collection for computers with Internet Explorer with different versions | MS Tech BLOG
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.
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.
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.%"
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.
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.
my bad , I forgot to see its comparing the last reboot with current time..
Well the last reboot query, I doubt if its giving the correct data.. will try it again though,
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?
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 ?
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
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.
Hi, I dnt understad what u mean 🙁
what you did not understand ? SQL Query ?
Hi, Its possible that u can send me all clients that not installed scom 2012 agent ?
Many Thx....
you can replace the Program name from ARP what is avilable into the query ? Did you try that ?
Thanks Eswar. The query helped me a lot.
Hi Eswar,
Many Thanks for the reply. But getting syntax error when tried with the above collection query. Pls do the needful.
Rajeev.
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]
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.
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)
I have attempted to use the above query to list all the computers in our customers estate that have any version of Access.
what is the result ? did u get what is required ?
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
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 ?
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.
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.
yes you can do that using net start servicename
did you try that ?
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.
sccm service ? U mean SMS agent host...you can use simple command line net start sms agent
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.
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.