An accounting firm, Numbers R Us, wants you to distribute Microsoft Excel to its 50,000 accountants across the world. You can distribute the software the old fashioned way by having the systems administrators install Excel on their machines in their respective domains, or you can take advantage of Microsoft's Systems Management Server (SMS) to automate the software distribution process.

With SMS, you can inventory software and hardware configurations, distribute software, perform remote troubleshooting, store information in a centralized database, and customize and integrate SMS with your internal processes. Management Information Format (MIF) files collect inventory information for equipment and personnel for SMS. (This article assumes you are familiar with MIF customization. For the necessary background, see Mark Eddins, "Customizing Systems Management Servers," January 1997, and Mark Eddins, "Customizing Graphics for SMS Custom Inventory Objects," March 1997. Also see Mark Cooper, "Maintaining Oracle SmartClient Workstations with SMS," page 183.)

Microsoft extended SMS's functionality considerably when it included the MIF Form Generator. With the MIF Form Generator, you can create, with no programming, custom forms called MIF Entry Forms. With these forms, you can collect additional inventory attributes that SMS's standard inventory doesn't provide. For example, you can prompt SMS client users to provide information, such as names or phone numbers.

Screen 1, page 176, contains an example of a MIF Entry Form. After a user completes the form, the client puts the information in a NOIDMIF file. (A NOIDMIF file lets you add or update MIF groups in the Personal Computer SMS architecture--MIF architecture tells SMS what data is necessary for a MIF component, such as an Employee component. For more information on architecture and other MIF details, see the sidebar "The MIF: SMS's Workhorse," page 177.) The client then saves the file locally in sms_clientroot\ms\sms\noidmif. During the next inventory collection, SMS's Inventory Agent reads the NOIDMIF and combines it with the standard inventory for later processing into the SMS database.

The MIF Form Generator lets you create forms that query users for all kinds of information. For example, you can create a MIF Entry Form to ask employees their birth date for a calendar on the company's intranet. Or you can ask employees to identify the minor but annoying problems they've been encountering with their computers so that you can get an idea of the types of problems occurring.

However, the MIF Entry Form isn't an effective tool for gathering the information you need to distribute Excel and subsequent upgrades to the 50,000 accountants at Numbers R Us, for two reasons. First, some people will fill out the form incorrectly (even if you give them explicit instructions), and others won't fill out the form at all (they might be on sick leave or vacation). Even if 95 percent of the users complete the form correctly, 2500 users will not receive their software package because their machine won't appear in the SMS query results. And with 47,500 machines in the SMS query results, you'll never discover who is missing.

Second, once SMS saves the information in the MIF Entry Forms, it doesn't prompt employees to update their information. Thus, if an employee moves to another department, changes title, or leaves the company, the SMS database won't reflect this change. Therefore, some Excel upgrades will never reach the correct people.

So if you cannot use MIF Entry Forms to collect the needed information for the SMS database, what can you use? Most companies set up and regularly update a human resources (HR) database that contains information on all employees. If you integrate the HR and SMS databases, you can get complete, accurate information.

Here's the game plan: Instead of extracting information from employees, you will extract the data from the Numbers R Us HR database and process it into a MIF for each employee. You will then place the MIF in the sms\site.srv\isvmif.box and let SMS process the MIF, integrating the new data into the SMS database.

One strategy to implement this plan is to write an IDMIF with a custom Employee architecture. (IDMIFs let you create new or update existing MIF architectures.) Then you can relate the employee attributes to the SMS personal computer attributes by writing queries that incorporate the Employee architecture and the Personal Computer architecture. Although this implementation strategy seems feasible, it is not desirable because SMS offers no way to query more than one architecture at a time. A better strategy is to use the existing Personal Computer architecture and the corresponding Identification group from the SMS database to generate a custom IDMIF for adding the user information. (An Identification group contains attributes that identify the component--e.g., Employee--that the MIF describes.)

The HR and SMS Integration Strategy
Here's an overview of the integration strategy that uses custom IDMIFs. First, you need to match a machine record in the SMS database to the employee record in the HR database. To make this match, you need a unique employee attribute that both the SMS database and HR database reference.

At Numbers R Us, each employee has a unique ID number. Employees' ID numbers are already in the HR database, but not in the SMS database. To add them to the SMS database, you can use the MIF Form Generator to create a MIF Entry Form that prompts the user to enter only his or her employee ID number. This form will generate a NOIDMIF at the client workstation and put the employee ID attribute in a new group, which you can call User Information. SMS will automatically take the resulting MIF containing the new information and incorporate it into the Personal Computer inventory. The resulting record in the SMS database will have the employee ID attribute, which you can match to the employee ID attribute in the HR database.

How to integrate SMS with an existing external database

Because you are entering only a single attribute and pulling the rest from the HR database, the potential data entry problems with the MIF Entry Form discussed earlier are not an issue. The application will flag any employee ID mismatches so that you can identify inaccurate or missing ID numbers, and the information will be up to date because the HR database is up to date.

You now have an SMS database of Personal Computer inventory records that include a custom group called User Information, which contains the employee ID attribute. Therefore, you can look up the employee ID for each record in the HR database and find the associated SMS Personal Computer inventory record. Once you have the matching record, you can extract the Identification group from the SMS record and use it to build the Identification group in the custom IDMIF. To finish, you need to fill in the employee information attributes. Figure 1 provides an overview of this strategy.

How to Build the Database
You now know the game plan and strategy. Here are five steps to implement them.

STEP 1:
Collect each employee's ID number. Use the MIF Form Generator to create a MIF Entry Form that collects one data value, the employee's ID number. SMS will collect this value when it inventories the workstation through the NOIDMIF process. When you install SMS, you install the MIF Form Generator. An icon will appear in the Systems Management Server group on your SMS Site Server. The MIF Form Generator is intuitive to use. If you review the documentation, you will likely have no problem creating this simple form.

STEP 2:
Develop an integration application that queries the SMS database views that the SMSVIEW program creates. The program will create your custom IDMIF to incorporate the HR attributes into the SMS database. The program needs to extract the Identification group information for each Personal Computer inventory record. You can use Visual Basic (VB), Visual C++, or your favorite development tool to develop this program. Let's call this application HRINT for easy reference.

STEP 3:
Match employee ID numbers in the HR and SMS databases. For each inventory record retrieved in Step 2, have HRINT query the HR database for a record with the matching employee ID number. If HRINT doesn't find a match, it will write an error in the error log, which you need to investigate. Using the information you collect from the SMS inventory database and the HR database, HRINT will create a custom IDMIF that includes the Identification group information from the SMS inventory record. The architecture for the IDMIF is Personal Computer. HRINT will add an Employee Information group that contains all the desired personnel attributes from the HR record to the IDMIF.

STEP 4:
Save the IDMIF. Have HRINT save each completed IDMIF with the name empl_id.mif (where empl_id is the employee's ID number) and place it in the \SMS\SITE.SRV\ISVMIF.BOX subdirectory on the SMS Site Server.

STEP 5:
Update the Personal Computer inventory. SMS's Inventory Processor will automatically process the IDMIFs and update the Personal Computer inventory so that the inventory includes the Employee Information group and the new attributes from the HR database.

Piece of cake, right? Not quite. If the client workstation runs MS-DOS, Windows 3.x, Windows 95, or Windows NT, the IDMIF will work. You can query the updated SMS database to get the information you need to distribute the Excel program to the intended 50,000 users. And because you regularly update the SMS database, you can use it to send future upgrades as well.

But if the client workstation runs Macintosh or OS/2, the inventory records will suddenly start showing null values for all groups except the Identification and Employee Information groups. And after a while, the inventory records will start showing null values for the Employee Information groups, but display values for all the other groups. To find out why, you have to examine how SMS collects and stores data.

The Data Collection and Storage Process
This strategy tacks on additional information to the Personal Computer inventory through an external process after the standard inventory process creates the record in the SMS database. The standard inventory process continues to periodically collect inventory for each workstation and update the SMS database to reflect any changes that might occur. You need to consider how this collection process might affect any additional groups that you add through the IDMIF.

When SMS's standard inventory process collects inventory, it always creates a MIF containing the complete inventory of the workstation. SMS maintains a history file, which contains the last version of the standard inventory MIF that SMS collected. SMS's Maintenance Manager eventually transfers the new MIF to the SMS Site Server, where the Inventory Processor compares the new MIF to the old one and creates a Delta-MIF containing only the net changes in inventory. This process results in several possibilities:

  • If a previously recorded group is not present in the new MIF, SMS assumes you have deleted the group. The Delta-MIF contains instructions that delete the group from the workstation's current inventory. The group icon will remain in the Personal Computer Properties window because the history for the group is present but all the current attributes will be null.
  • If a new group is present in the new MIF, the Delta-MIF adds that group to the workstation's inventory.
  • If a new attribute is present for an existing group, the Delta-MIF appends that attribute to the group in the database.
  • If a previously recorded group attribute is not present in the current MIF, SMS assumes the missing attribute is null.

Consider that your IDMIF contains the Architecture, Identification, and Employee Information groups, but none of the other groups (e.g., Network Card) that are usually present. If the Inven-tory Processor compares your current IDMIF to the previous standard inventory MIF, the Delta-MIF will delete the original groups, causing null values to appear in the Personal Computer Properties window for these groups. The opposite occurs when the Inventory Processor compares a new update of the standard inventory MIF to a previous IDMIF. Null values will appear for the Architecture, Identification, and Employee Information groups.

A History Lesson
To overcome the problem of null values, you need to understand how SMS stores the histories of prior MIFs. As mentioned previously, the Inventory Processor maintains a record of the last MIF received for each client workstation. SMS stores this history in the sms_install_drive:\sms\site.srv\inventry.box\history subdirectory on the SMS Site Server.

Listing 1 shows an example directory listing of the history subdirectory. Let's examine how the history process works. This history subdirectory has not only a Personal Computer architecture, but a Printer architecture. Specifically, it contains the Personal Computer architecture that you created for the Excel distribution, plus a Printer architecture that two previous SMS articles discussed (Mark Eddins, "Customizing Systems Management Servers," January 1997, and Mark Eddins, "Customizing Graphics for SMS Custom Inventory Objects," March 1997). Listing 2 contains an excerpt from the Personal Computer architecture, and Listing 3 contains an excerpt from the Printer architecture.

If SMS uses a *.raw binary file (which Microsoft clients use) to collect a history for a standard inventory MIF (i.e., if SMS uses a *.raw MIF), SMS maintains the history in a *.hms file. This file will have the name workstation_smsid.hms, where workstation_smsid is the unique SMSID from the identification group. (When you install the SMS client software, it assigns each workstation an SMSID, which is a unique ID number.) For example, one history file in Listing 1 has the name S0001000.hms.

SMS maintains the history for an IDMIF in two related files. First, SMS creates a *.smh file, which contains all the groups in the MIF except the Architecture and Identification groups. SMS generates a unique 8-digit sequential number for the name of the history file and adds the extension .smh. The filename 00000001.smh in Listing 1 is an example of this type of file.

Then, SMS creates a history.map file, a shared file that contains only the Architecture and Identification groups. This file contains a record for each unique MIF component. (SMS determines uniqueness by the Identification group entries.) SMS segregates the entries by architecture.

For example, the following is an excerpt from the history.map file for the IDMIFs:

\[Printer\]
Printer ID:P1000000|=00000000.smh
\[Personal Computer\]
Name:USNE01W01|NetCardID: 00:40:05:1a:4f:11|SMSID:S0001000|=00000001.smh

In this instance, SMS lists two distinct architectures: the Printer architecture and the Personal Computer architecture. The single record in the Printer architecture listing tells you that SMS collected inventory through an IDMIF for only one printer. This record shows that SMS collected information keyed on the Identification group attribute of Printer ID, which had the value P1000000. The pointer (represented by =) tells you that SMS has stored the history information for the other groups in the 00000000.smh file.

The single record in the Personal Computer architecture listing specifies that SMS collected inventory through an IDMIF for only one personal computer. The record shows that SMS collected information keyed on the Identification group attributes of Name (value of USNE01W01), NetCardID (value of 00:40:05:1a:4f:11), and SMSID (value of S0001000). These are the standard key attributes for the Personal Computer architecture provided by SMS. The pointer tells you that SMS has stored the history information for the other groups in the 00000001.smh file.

When SMS collects a new IDMIF, it compares the newly collected Identification group key attributes to those in the records within the history.map file. This comparison lets SMS determine whether the IDMIF includes a new inventory component or is updating an existing one.

Technically speaking, you can consider a *.raw file as an IDMIF because the file includes Architecture and Identification groups. For simplicity, the IDMIFs mentioned here are referring to ASCII MIF files for Macintosh and OS/2 clients.

Why These Custom IDMIFs Work with Microsoft Clients, but Not Others
Because SMS compares a new IDMIF file to the *.smh and history.map files and because SMS compares a new *.raw MIF file to the *.hms file, the Delta-MIF process does not compare the IDMIF file's and *.raw MIF file's unique groups with each other. So when SMS compares a new *.raw MIF to the *.hms file, the comparison doesn't affect the Employee Information group because that group isn't in the *.HMS history file.

Similarly, when SMS compares a new IDMIF file to the *.smh and history.map files, the comparison doesn't affect the standard inventory Personal Computer groups. As a result, you don't get any null values when using IDMIFs with MS-DOS, Windows 3.x, Win95, and NT client workstations.

For Macintosh and OS/2 clients, SMS does not use *.raw files. Rather SMS creates IDMIFs and places them on the ISVMIF.BOX on the Logon Server. From this point, SMS processes them as IDMIFs, complete with *.smh and history.map files.

So when SMS compares your custom IDMIF to the *.smh and history.map files created through standard inventory, you'll get null values for all groups except the Identification and Employee Information groups. If you then have SMS compare a new standard inventory file to the *.smh and history.map files, you'll get a null value for the Employee Information group, but display values for all the other groups.

Fortunately, you can fix this problem in Macintosh and OS/2 clients. If you change the file extension for the IDMIF file to *.nhm, SMS will skip the delta processing for the MIF and immediately update the current inventory record in the SMS database with the data in the IDMIF.

This solution has one drawback. When you use a *.nhm MIF for the custom IDMIF, SMS will not maintain a history for the Employee Information group. However, all standard inventory groups will continue to have a history.

A Special Delivery
You are now ready for your special delivery. You have integrated HR data into the SMS database so that it includes employee ID numbers. You have queried the updated SMS database to get the information you need to distribute the Excel program to the intended 50,000 accountants. And you have fixed the file extension for the custom IDMIFs for those employees on Macintosh and OS/2 client workstations. Now all you have to do is sit back and relax while 50,000 accountants automatically receive their Excel software.