/build/static/layout/Breadcrumb_cap_w.png

SCCM 2007 Query

I need to create a collection of machines that have Acrobat reader 11. so that I can patch them.  The query I created used the acrord32.exe and file versions but is not working properly

If I remove the second version it will give me a collection of all machines with a file version less than 11.0.10.32 but some of these machines have version 10 which this patch will not work on.

Any suggestion on how to fix this?

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 = "ACRORD32.exe" and (SMS_G_System_SoftwareFile.FileVersion < "11.0.0.32" and SMS_G_System_SoftwareFile.FileVersion >= "11.0.0.0")

Asked 9 years ago 3866 views
6 Comments   [ + ] Show comments
  • why not try for a start > "11.0.0.0", that should give your target but not the v10 files. Or you could use the ProductID of the v11 family instead of the exe - Badger 9 years ago
  • I am looking to have the collection dynamically update so the idea is (at least what I am thinking anyways) that once the app is patched to the current 11.0.10.320 it will be removed from the collection. I will be able to tell at a glance what is outstanding. - Xpdite 9 years ago
  • Remember that in the SQL/WQL above you're comparing *strings* not *actual version numbers*, so when using comparison operators, the string "11.0.0.0" will always be greater or less than anything which isn't "11.0.0.0".

    I don't know how to parse strings in SQL/WQL in order that you could separate out the various elements of a string into discrete integers, against which you could compare other integers, or even if that's possible. - anonymous_9363 9 years ago
  • Thanks for your help. I have never really been able to find much on how to do queries in SCCM. I usually use Google and try and piece things together and see what works. I wish someone would write a good how to on queries and reports for SCCM (for the beginners like me) - Xpdite 9 years ago
  • I Googled...

    http://www.codeproject.com/Articles/7938/SQL-User-Defined-Function-to-Parse-a-Delimited-Str - anonymous_9363 9 years ago
  • Thanks for the help everyone. It is greatly appreciated. After much googling what I did to tide me over was create a collection of all machines that had the current version of Adobe Reader which at the time was 11.0.10. I then created a second collection with a query that added all workstations that were not in the first collect. Basically by a process of elimination I was able find the machines I was looking for.. I will try the queries below as well as they seem a little cleaner and only require a single collection. - Xpdite 9 years ago

Answers (2)

Posted by: DeQuosaek 9 years ago
Senior Yellow Belt
0
I find that it is much easier to use the "Add/Remove Programs" rather than the inventoried executable files.  By the way, in the GUI, this is listed under "Installed Applications".  Also, this information is collected by the Hardware Inventory rather than the Software Inventory just FYI.

In this case, I would create a query that is something like:

select 
SMS_R_System.Name,
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, 
SMS_G_System_ADD_REMOVE_PROGRAMS.Version
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 like "Adobe Reader%" and (SMS_G_System_ADD_REMOVE_PROGRAMS.Version like "11%" AND SMS_G_System_ADD_REMOVE_PROGRAMS.Version < "11.0.03")

Modify the last part for whichever version you're working with, but maybe try the query with only the like "11%" part first to make sure the version is displayed as you're expecting it to be since the version numbers can vary in the ways they are presented.  

If you include like "11%" and the less than criteria, you'll avoid returning anything that's not version 11.
Posted by: kory 9 years ago
Senior Yellow Belt
0

Hiya.

If you still prefer to use Software Inventory data, have whipped up a query for you.

You can play around with the version number in the query, as well as what you want to see in the output.

Mind you, I only quickly tested this in the Queries (not in collection).

 

select SMS_R_System.Name, SMS_R_System.ResourceDomainORWorkgroup, SMS_G_System_SoftwareFile.FileName, SMS_G_System_SoftwareFile.FileVersion, SMS_G_System_SoftwareFile.FilePath 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 = "ACRORD32.exe" and SMS_G_System_SoftwareFile.FilePath like "C:\\PROGRAM FILES%" and SMS_G_System_SoftwareFile.FileVersion < "12.0" and SMS_G_System_SoftwareFile.FileVersion > "11.0"

 

Good luck!!


Kory.

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ