SMS: Inventory Your Desktop Systems, Part 2

This article is the second in a series that discusses procedures for inventorying desktops using Microsoft Systems Management Server (SMS). The first article explained how to collect the inventory information ("SMS: Inventory Your Desktop Systems, Part 1,"Windows NT Magazine, May 1996). This article focuses on developing reports from data in the SMS database. The third article will examine how to extend the SMS database with third-party add-ons.

Depending on your inventory or audit procedures, SMS returns data that you can view in the Personal Computer Properties window in three places: the Packages Object, the Audited Software Object, and the Collected Files Object. If your goal is to look at a specific computer and determine its hardware or software, your task is complete. If you want SMS to do something with this data or if you want to create management reports, you need to understand how to get at that data.

The SMS Administrator's Guide and the Microsoft Educational Services course, Administering Microsoft's Systems Management Server, describe one reporting option: creating queries. These references only touch on the procedures for creating reports with the inventory data or manipulating the information. A lot of functionality is available when you understand SMS queries, reports, and data manipulation techniques.

Basic Queries
SMS stores retrieved information as records in predefined SQL database structures called architectures. Of these, the Personal Computer architecture is the one you see most frequently. (Select a record in the Sites window, and look in the Personal Computer Properties window to view this architecture.)

Each architecture has groups. For example, the Personal Computer architecture has the Disk group, the Processor group, and the Video group. In turn, each group has attributes with values. For example, the Disk group's attributes include Disk Index (e.g., D), File System (e.g., NTFS), and % Disk Full (e.g., 15%).

Once you have SMS information in an architecture, you can query the SMS records. A query is an expression or set of expressions that defines the subset of records you are looking for. For example, you can set up a query to search the SQL Personal Computer architecture for all computers with MS-DOS as the OS. The query will have one expression, which you see in screen 1. In this case, the attribute is Operating System Name.

As Part 1 of this series described, you can perform a query with the attribute Software Name to search for computers that have Word. Remember that you query for records where the attribute Software Name is the same as the name of the package you created (e.g., Inventory winword.exe 6.0) and not the name of the file (e.g., winword.exe).

SMS uses queries mainly for administering jobs. For example, if you want to upgrade all machines running Microsoft Word version 6.0 to version 6.0c, you select the query (by name) in the Job Setup window. As a shortcut, you can select Add to Group from the File menu when you have the Query Results window open. This step will add the machines in the result set to a specified Machine Group, which you can use later to perform jobs. You can even print the result set as a basic report.

Queries are limited in three ways. First, you can't cross architectures. To query the Personal Computer architecture, you can't select attributes from any other architecture. Second, the list of operators is not complete. (As Part 1 explained, you can combine expressions in a query with an Add AND and an Add OR, but not an Add NOT.) This limitation is a problem. For example, suppose you create a package "Inventory winword.exe" to find all machines with winword.exe. If you then query "Software Name is 'Inventory winword.exe'," you'll get a list of all those machines. However, if you query "Software Name is not 'Inventory winword.exe'," you'll get a list of all machines in your database because they all have some file in the inventory that was not winword.exe (e.g., command.com). The absence of an Add NOT operator in the Query Properties dialog prevents you from querying for those machines without winword.exe. Third, you can't create a query that is a comparison of other queries. For example, you can't list the machines in one query result set but not in another. To solve these problems, you need to go beyond basic queries and understand the nature of SQL database tables and records.

You create and name the SQL database while installing an SMS primary site. Suppose, for example, you called the database SMSData in the device smsdata.dat. You can use any Open Database Connectivity (ODBC)-compliant front-end tool to view and analyze the data, but the structure of the SQL database is relatively complex and subject to change. Fortunately, Microsoft provides smsview.exe to expose a stable set of fields. You run this tool only once to create a set of views in the SQL database. You can then attach the view from within a program such as MS Access. Screen 2 shows the views smsview.exe creates.

   Previous  [1]  2  Next