Create collection based on software presence

[birz]

Hi,

 

I want to deploy IE 8, but first I want to create a collection for computers that don't have IE 8 installed on. Out of 500 PC about 50 have the software already installed (it was installed manually). In my tests what I found out was if I try to deploy IE8 (package with IEAK) on computers that already have IE 8 installed it messes the installation, ex: the windows intergated logon doesn't work until a "gpupdate /force" is made on the computer. So to ease the thing I wanted to create a collection that would contain only the computer that don't have IE8 installed on and assigned the software to that collection. That way for the computers with IE already installed on, it won't reinstall unecesary. Attached you'll find the criteria quey I'm doing, but it doesn't seem to work. The other way around works well though, if I put "is like" I'll only get the 50 or so computers that have IE8. Any help would be great.

 

Thnaks,

[wmmayms]

If you do it like that you will recive all computers in that collection because they all have rows in the v_GS_ADD_REMOVE_PROGRAM view that are not = "Internet Explorer 8"

To get around this you will have to use a subselect statement.

[wmmayms]

If you do it like that you will recive all computers in that collection because they all have rows in the v_GS_ADD_REMOVE_PROGRAM view that are not = "Internet Explorer 8"

To get around this you will have to use a subselect statement.

 

 

Here is an example code:

 

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Mozilla Firefox (3.5.9)")

 

Note i dident have IE 8 in my enviroment so you need to change the line i marked as red in my above example.

 

GL

[Eswar Koneti]

How about if i have Windows 7 machines in the environment which comes IE8 by default and which will not display in add and remove programs.In this case,it might require to go with software file like below one:

 

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 like "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion not like "8%"

[Eswar Koneti]

Hi,

 

I want to deploy IE 8, but first I want to create a collection for computers that don't have IE 8 installed on. Out of 500 PC about 50 have the software already installed (it was installed manually). In my tests what I found out was if I try to deploy IE8 (package with IEAK) on computers that already have IE 8 installed it messes the installation, ex: the windows intergated logon doesn't work until a "gpupdate /force" is made on the computer. So to ease the thing I wanted to create a collection that would contain only the computer that don't have IE8 installed on and assigned the software to that collection. That way for the computers with IE already installed on, it won't reinstall unecesary. Attached you'll find the criteria quey I'm doing, but it doesn't seem to work. The other way around works well though, if I put "is like" I'll only get the 50 or so computers that have IE8. Any help would be great.

 

Thnaks,

 

Look at this post for machines which doesnt have IE8 installed http://solodig.com/some-useful-smssccm-web-reports/create-a-report-to-get-machines-which-has-internet-explorer-7-installed-on-in-sccm

You can use this to create collection similar to.

[birz]

How about if i have Windows 7 machines in the environment which comes IE8 by default and which will not display in add and remove programs.In this case,it might require to go with software file like below one:

 

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 like "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion not like "8%"

 

 

Thanks for all your help. As for this statement I will only limit my collection to Windows XP machine

 

Thanks, again, I'll post the result soon.

[birz]

Great it works!

 

this is the exact SQL statement I used

 

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_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName = "Windows Internet Explorer 8")

 

Again, I limited the memebership rule to the collection "All Windows XP Systems" only. That way I wont pickup Windows 7 nor servers.

 

I'm really greatfull that you guys provided me the answer, but I'd like to be be better for creating those statement myself. How do I get better? where should I start? because at the moment I'm limited to the query design, which looks limited...

 

 

Thanks again,

[Peter van der Woude]

I would start with understanding the difference between WQL Queries and SQL Queries (when to use and how to create one from the other).