How can I write a query to find machines which installed office 2003 with Sp3 in SCCM 2007?
Thanks,
Best Answer
The easiest way to do this is just to use reports rather than queries. There are built-in reports that will tell you all the machines with a particular version of a particular product installed. Have a look at the reports in the "Software - Companies and Products" category, like "Computers with a specific product name and version".
If however you actually do need a query, if for instance, you need to create a collection of all of these that you can advertise a specific software package to, then the easiest way is to search for a particular file that will be common to all of these machines.
For example, if you know that the install packages used in your company always install Outlook as part of Office, you could use a query like this that looks for the Outlook EXE file, and checks its version.
For example by setting these criteria in a query:
Software Files.File Name is equal to "outlook.exe"
and
Software Files.File Version is greater than or equal to "11.0.8173.0"
Or this is the query language that the above translates into (this can be pasted into the Show Query Language box):
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 = "outlook.exe"
and SMS_G_System_SoftwareFile.FileVersion >= "11.0.8173.0"
For info, file versions for various Office versions and service packs can be found in these Technet pages:
Probably the easiest way to get a simple list would be to take advantage of one of the built-in reports, such as the "Services - Computers running a specific service" report. You can then just input a service common to Hyper-V hosts, such as "Hyper-V Virtual Machine Management" (note that I know more about SCCM than Hyper-V this might not be the right service to look for, I just got it from looking at the running services report from a few of our Hyper-V servers).
You should be able to access this through SCCM's web reports with a URL like:
We have this setup for managing machines (mainly virtuals) in our development environment from our live environment's SCCM infrastructure. These are totally separate forests with a fairly stringent firewall between them. We mainly only use it for OS/app patching via SCCM Software Updates, and limited hardware/software inventory reporting.
Once we'd opened up the required ports in the firewall between the environments, all we needed was to use the correct command line when installing the SCCM client on the dev machines, we use this command line:
Obviously the fallback status point is optional (but highly recommended for troubleshooting, especially if you're currently having problems installing clients), and there's no need to specify the port if you're using the standard port.
We found that installs were failing to connect properly to the site until we properly specified the site code and SLP server in the install string.
Best Answer
The easiest way to do this is just to use reports rather than queries. There are built-in reports that will tell you all the machines with a particular version of a particular product installed. Have a look at the reports in the "Software - Companies and Products" category, like "Computers with a specific product name and version".
If however you actually do need a query, if for instance, you need to create a collection of all of these that you can advertise a specific software package to, then the easiest way is to search for a particular file that will be common to all of these machines.
For example, if you know that the install packages used in your company always install Outlook as part of Office, you could use a query like this that looks for the Outlook EXE file, and checks its version.
For example by setting these criteria in a query:
Or this is the query language that the above translates into (this can be pasted into the Show Query Language box):
For info, file versions for various Office versions and service packs can be found in these Technet pages: