DOCUMENT:Q289877 06-MAY-2001 [sms] TITLE :Create a Query in SMS Server That Returns All Computers PRODUCT :Microsoft Systems Management Server PROD/VER::2.0,2.0 SP1,2.0 SP2,2.0 SP3 OPER/SYS: KEYWORDS:kbConfig kbMMC kbServer kbsms200 kbCollections kbInventory kbQuery kbsmsAdmin ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Systems Management Server versions 2.0, 2.0 SP1, 2.0 SP2, 2.0 SP3 ------------------------------------------------------------------------------- SUMMARY ======= This article describes how to create a query in your Systems Management Server (SMS) version 2.0 administrator console to return information about all computers that do not have a particular file installed. MORE INFORMATION ================ To create a query that returns information about all computers that do not have a particular file installed, you must first create a query that can detect all computers that have a selected file installed. In the following example, the file is named Mynotepad.exe. To create a query that displays all computers that contain the Mynotepad.exe file: 1. In the SMS Administrator console, right-click the Queries node. Click New, and then click Query. 2. Give the new query a name and click the "Edit Query Statement" button. 3. On the General tab, click the Star icon to add a new property. 4. In the Results Properties dialog box, click the Select button, and then specify System Resource for the attribute class and NetBIOS Name for the attribute. (There is no need to specify an alias except for your own readability.) 5. On the General tab, click to select the "Omit duplicate rows" check box so that duplicate entries in the results are not returned. 6. On the Criteria tab, click the Star icon to create a new criteria. 7. In the Criterion Properties dialog box, click the Select button. In Attribute Class, click either Software Files or Software Product. If the attribute class is Software Files, click File Name. Or, if the attribute class is Software Product, click Product Name. (This example only uses the Software Files and File Name settings for this query.) 8. The operator must remain at the "is equal to" default and the value must be the file name. The Values button can pull up all values, but it can be slow if there is an enormous number of values to be returned. 9. Click OK until the query is saved in the administrator console. Right-click the query and click Run Query to view the results. You now have a list of the computers that have the file specified. You are going to be using it to create the list of computers that do not have the specified file installed. If you look at the query syntax in the SMS administrator console, it should resemble the following example: select distinct SMS_R_System.NetbiosName 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 = "mynotepad.exe" To create the query that can display the computers that do not have a particular file installed, begin the same procedure that you used to create a query to find an installed file: 1. In the SMS administrator console, right-click the Queries node. Click New, and then click Query. 2. Give the new query a name and click the "Edit Query Statement" button. 3. On the General tab, click the Star icon to add a new property. 4. In the Results Properties dialog box, click the Select button, and then specify System Resource for the attribute class and Netbios Name for the attribute. (There is no need to specify an alias except for your own readability.) 5. On the General tab, click to select the "Omit duplicate rows" check box so that duplicate entries in the results are not returned. 6. On the Criteria tab, click the Star icon to create a new criteria. 7. In the Criterion Properties dialog box, specify Sub-selected values for the Criterion Type. 8. In the Criterion Properties dialog box, click the Select button. In Attribute Class, click either Software Files or Software Product. If the attribute class is Software Files, click File Name. Or, if the attribute class is Software Product, click Product Name. (This example only uses the Software Files and File Name settings for this query.) The operator must be changed to "is not in". 9. Click the Browse button at this point to select the previously created query that finds all computers that have a file installed. This imports the query syntax directly into the Sub-object window. If you examine the query syntax for this second query in the SMS administrator console, it may resemble the following example: select distinct Name from SMS_R_System where Name not in (select distinct SMS_R_System.Name 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 = "mynotepad.exe") order by Name When you run this query, it returns to the Results window information about all computers that do not have this particular file installed. NOTE: If your software inventory is not set up to detect the file type you are searching for, it may not be returned. A search by product name is more accurate than a search by file name. Additional query words: prodsms mynotepad exe ====================================================================== Keywords : kbConfig kbMMC kbServer kbsms200 kbCollections kbInventory kbQuery kbsmsAdmin Technology : kbSMSSearch kbSMS200 kbSMS200SP1 kbSMS200SP2 kbSMS200SP3 Version : :2.0,2.0 SP1,2.0 SP2,2.0 SP3 Issue type : kbinfo ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2001.