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.
Most items in the Attach Tables dialog are native SMS tables, which you should not touch. However, the items with a lowercase "v" prefix are not tables: They're views that smsview.exe creates and that you should use exclusively.
The Administrator's Guide doesn't mention that you must run smsview.exe again if you add an object to an architecture. For example, if you run smsview.exe before you have a Collected Files Object in the Personal Computer Properties architecture, you won't have a dbo.vCollected_Files to attach--you'll have to rerun smsview.exe.
Each dbo_v* table displays SMS SQL database data that you can manipulate, analyze, and report. You can easily query the database with the graphical query tool in Microsoft Access, as screen 3 shows. Note that the linking field is dwMachineID. After you run the query shown in screen 3, you get a record set of data from SQL Server, as you see in screen 4.
Once you create the query, all that remains is to format and present the data. Screen 5 shows a report that the Access report writer generated. Of course, you don't need Access to produce these reports. You can always use your favorite ODBC-compliant report writer.
Inventoried packages and audits are two ways to get inventory information. A third way is to collect files from a desktop. As discussed in Part 1 of this series, you can write a program that runs on the desktop and writes its results to a text file that you then collect.
For example, Part 1 discussed distributing a job that inventories all .exe files and writes the data to a file invlist.txt. A job then tests whether an existing copy of MS Word will load and writes data to a file named wordwork.txt. If you create a package to inventory and collect these files, you can see them in the Personal Computer Properties window, as on screen 6. If you select the entry for the collected file in the Table View, SMS will open the file in the appropriate reader.
This method is fine if you want to view a collected file from one computer, but programmatically summarizing and reporting on the data from all computers is also useful. To do this, your program must access the SQL database to get the filenames and then serially open each file, parse and extract the data, and print the data. The collected file, invlist.txt, for each machine has the same name as the file you collected during the query, and is in a directory below the package number. If SMS finds multiple copies of the file on the target computer, SMS appends a random binary number after the filename--a good reason to keep your results file unique. This naming convention is an improvement from SMS 1.0, but the SMS 1.1 Administrator's Guide hasn't picked up on this improvement. The descriptions in Chapter 6, "Inventory," and Appendix C, "SMS System Flow," are obsolete.
If you look in the SMS SQL view dbo_vCollected_Files, you see that SMS stores the filenames of all collected files in field LocalFileName0. Similarly, SMS stores the original filenames (i.e., the filenames on the workstations) in the field OriginalFileName0.
Now if you use a unique 8.3 filename, you can easily create a program in 16-bit Access to loop through all the filenames and open the text files. If you use a long filename, you need a 32-bit front-end program (e.g., Access95) that recognizes long filenames, or you can create a program to convert the long filenames to short filenames.
Once you have the short filename, you can easily parse the invlist.txt files from every computer to get a consolidated list of .exe files. Assuming you have traversed the SQL database and have the domain name, computer name, and full path for invlist.txt, the Access code snippet, minus error code, for parsing the file will look like listing 1.
This code extracts the filename (e.g., msaccess.exe) and the directory name (e.g., C:\ACCESS\) for all records in dbo_vCollected_Files and passes this information to a user-defined function that adds a record to an Access table.
Although you can get a report of .exe files easier with an audit, the point of this exercise is to show how you can programmatically find and parse collected files. Using these techniques and some ingenuity to craft programs that run on the desktop and write their results to text files, you can find out almost anything about your network's systems. Imagine writing a terminate-and-stay-resident (TSR) program that monitors keystrokes or mouse clicks during the day and writes the statistics to a file.
Who Has No MS Word?
So how do you find out who has no copy of any version of a piece of
software? One way is to create a program in Access that loops through
dbo_vIdentification and dbo_vSoftware, checking for no match of the "Inventory
MS Word" package or audit. This approach lets you create a table in Access
and generate a report. Unfortunately, you can't import that table back into SMS,
so you can't create an SMS job to target software distribution to everyone
without Word.
Another way is to create and distribute a program that writes a small file, C:\yes.txt, on all systems that have the software. The next step is to write and distribute another program:
Ifnot'C:\yes'existsthenecho 1>C:\no.txt. This program puts a file C:\no.txt on all systems that don't have Word. Finally, you create a package to inventory systems with C:\no.txt and target a software distribution job to just those systems. Nasty, but it works.
SMS, Part 3
The next article will examine extending SMS, in particular Digital
Equipment's AssetWORKS, which lets SMS inventory and distribute software to
several UNIX systems. In addition, AssetWORKS extends the standard SMS
functionality in a Windows environment to include functions such as software
metering and a full reporting package.
| AutoCAD Release 13 for Windows NT |
|
System Requirements: Windows NT Workstation 3.5 or later, 32MB of RAM recommended,
Minimum 35MB hard-disk space, 64MB permanent swap file, VGA (or better) display with pointer/mouse Autodesk * 800-964-6432 or 415-508-5000 Web: http://www.autodesk.com Price: CD-ROM: $3750, Disk: $3995 |
