Microsoft’s Systems Management Server (SMS) is a solution to one of the large general problems facing organizations today—managing large numbers of computers, especially personal computers. SMS is also an excellent architecture on which to
build solutions to specific problems. Sometimes these solutions require extending SMS, and other times they involve using other applications interfaced to SMS. This chapter discusses a variety of approaches to programming such solutions with SMS.
SMS is composed of various elements, but central to most of them is the SMS database, stored by Microsoft’s SQL Server. This chapter starts by exploring this database and how a particularly appropriate tool for accessing the database, Microsoft
Access, interfaces with SMS. You will then look at how you can extend the SMS database to include additional information that a typical organization might require. Microsoft Word will then be used to bridge the gap between all this technical data and the
various worlds of your users. The chapter finishes by reviewing some less commonly used programming interfaces to SMS.
SQL Server includes a variety of tools that prove very useful for exploring the databases it stores, presuming you have the privilege to do so. The most appropriate starting point for SQL Server version 6.5 is the SQL Enterprise Manager. You may need to
register the server where your SMS database resides, by taking the Server menu’s Register Server item. Once you have your server listed in the Server Manager window, you can dig down to the databases on that server and look at the SMS database. For
now you are particularly interested in the Objects, Tables subtree. You probably have over 50 tables listed, a rather intimidating prospect. However, you'll soon make sense of these tables.
If a list of all columns in the tables is desired, the SQL Enterprise Manager’s SQL Query Tool (under the Tools menu) can be used to execute the following SQL statement:
SELECT sysobjects.name Table_Name, syscolumns.name Column_Name from syscolumns, sysobjects where syscolumns.id=sysobjects.id and syscolumns.id>100
Technical specialists looking to gain the greatest benefit from SMS will have two primary expectations for the SMS database. The first is to extract data, usually for reporting. The second is to store additional data, in order to serve needs specific to
their organizations. From these points of view, the center of the SMS database design is the MachineDataTable table. This table contains pointers to all the data for every machine (computers and other devices you keep track of SMS with). Every machine has
a unique identification number (dwMachineID) and is categorized as being part of an architecture. The architecture for our purposes will usually be 5, which the ArchitectureMap table refers to as "Personal Computer ". Every machine will have
multiple records within the MachineDataTable, one for each pointer to data stored in other tables. You can tell which table these records point to by referring to the GroupKey column. The numeric GroupKey values can be used with the ArchitectureKey to look
up a descriptive name for the group in the GroupMap table.
SMS’s machine data is stored in a series of tables appropriate to each group of machine characteristics. There are tables for disk, IRQ, network card, operating system, memory, and similar information. Most of these categories have two tables, one
for common data and another for specific data. For instance, you may have many PCs that have NTFS-formatted SCSI Fixed Drives, CD Roms, FAT16-formatted DoubleSpace Drives, and so on. You will have only one, however, that has a drive with a serial number of
A049-6B6D and 370M used of 532M. The former kind of data goes in the common tables, and the latter goes in the specific tables, thus maximizing the efficiency with which information is stored. In this case, the common data goes into a table called
Disk_COMM and the specific data goes into a table called Disk_SPEC.
Going back to the MachineDataTable, you can use the GroupKey and ArchitectureKey to look in the GroupMap table and figure out the table names for the common and specific tables. You can then use MachineDataTable’s SpecificKey to find the data in
the specific table and the CommonKey to find the data in the common table. In some cases, a machine may have several instances of a specific group, and the InstanceKey distinguishes between these. A good example, again, is disks—each PC will probably
have a floppy drive, hard disk, and a CD-ROM, each of which is an instance of a disk.
You can see samples of the actual data contained in the tables by starting the SQL Enterprise Manager’s SQL Query Tool (under the Tools menu) and then entering a query of Select * From MachineDataTable, for example (note that case is not
significant). Then click on the Execute Query button (the one with a green right arrowhead icon).
For many of the machine data groups (disk, operating system, and so forth), previous data is kept for each machine as new, changed data is recorded by SMS. The pointers to this information are kept in the MachineDataHistoryTable, which has an identical layout to the MachineDataTable and is used in the same way. In order to determine which groups have historical data, you can refer to the GroupMap table, HistoryFlag column.
Understanding the SMS database is important, enabling you to explore the database and find information you need. However, producing reports using the database as it is would be very tricky. Microsoft has addressed this issue by
providing the SMSVIEW.EXE program, which can be found in the site’s executables directory, most commonly called \SMS\SITE.SRV\X86.BIN. If you execute this program, SQL Server is provided with views that will probably be more useful for producing
reports. You have to provide logon information to access the database, but otherwise SMSVIEW will automatically execute without intervention, pausing only to announce success or failure. If you want, it can be run from the command line, by providing the
logon information on switches. As an example, SMSVIEW /s:NT4_Server /l:sa /p: /d:SMS runs SMSVIEW on the SMS database on the NT4_Server, using the sa logon and no password.
Views are literally just views on the database—they do not contain data and thus do not consume any disk space. SMSView creates views that essentially flatten and amalgamate the machine detail tables. As an example of an SMSView view, for disks
there is a view called vDisk, which contains both specific and common data on all disks for all machines. The only information missing is machine identification details, which are available in the Identification_SPEC table.
Microsoft reserves the right to change the layout of the SMS database. The views are likely to remain stable, but if you work with the tables then you should be prepared to adjust your code when new versions (especially major versions) appear.
Beyond the machine-related tables, the SMS database contains tables for the other architecture elements, the things that can be used or controlled by the SMS Administrator and Security programs, and the inevitable overhead details.
The tables for the other architecture elements are the following: Jobs and JobDetails for jobs; Packages, PackageLocations, PackageDistributions, Platforms, and WCLs for packages; SMSEvent for events; SNMP_Traps and SNMP_Varbinds for SNMP traps; and
UserGroups for user groups.
The tables for other items that the SMS Administrator program can manipulate or use are the following: Alerts and AlertRecipients for alerts; MachineGroups and MachineGroupDetails for machine groups; ProgramGroups, ProgramGroupProgramItems,
ProgramGroupUserGroups, and ProgramItems for program groups; Queries, QueryExpressions, QueryFormatDefaults, and QueryFormats for queries; and SiteGroups and SiteGroupDetails for site groups.
The tables for things that the SMS Security program can manipulate are stored in the SecurityObjAccessList, SecurityObjAssocation, SecurityObjects, and SecurityUserTemplates tables.
With any complex system there are various minor details and interior elements that the system must keep track of. For SMS, these are kept in the AttributeMap, DeletedMachines, Domains, DomainTypes, PropertyDisplayGroups, PropertyDisplayPanes,
PropertyDisplayUsage, Requests, SchedulerSafeDelete, ServerShares, SetupInfo, SiteControl, SiteUses, SiteWork, SMSData, SqlStoreTable, SyncModification, and WorkstationStatus tables. The SMSData table is particularly central to many other tables in that it
contains the next values for many keys, as well as other data.
Microsoft Access is often thought of as a small database system (like SQL Server but less serious) that has some easy-to-use programming features included. This is true, but it don't forget that other databases can serve up the database functionality,
and Access can be used to easily access that data (thus the name). In this case, the Access database is just used to store queries, reports, and so on. Using Access with SMS’s SQL Server database is a good example.
When installing Access for Windows 95, the custom install should be chosen, and it should be ensured that the SQL Server ODBC driver was installed as part of the Data Access options. If so, using the 32-bit ODBC control panel, a data source can be added
for the SMS database. It will use the SQL Server driver, of course. The Data Source Name can be anything, but let's call it SMS. The Server will be the computer name of the SMS SQL Server, and under Options you can specify the Database Name as SMS.
Using Access, a new blank Access database can be opened and the SMS database can be linked in, using the File, Get External Data, Link Tables… options. In the Link dialog box, the ODBC Databases option should be chosen from the Files of type
drop-down listbox. The SQL Data Sources dialog box will appear, and the SMS database defined in the ODBC control panel can be selected. You will then be able to link in the tables you need (you may wish to link in all of them, for future needs). For most
tables, you have to clarify for Access what the key is; this is usually fairly simple, because the column name will end in the word "key" and be appropriate for that table.
At this point, Access knows where the data is and how to look it up, but it’s not clear to Access how the tables relate to each other. If information is needed from two tables, how does Access ensure that the same machine is referred to by the
selected records in each table? This is where relationships come in, and these are defined using the Relationships menu item under the Tools menu. The relationships are specified by choosing the appropriate tables and then dragging the field that relates
to a corresponding field in the other table onto it. The end result will be something like what we see in Figure 44.1.
FIGURE 44.1. Setting up the table relationships in Access.
Creating a query to collect the data needed for a report is fairly straightforward using Access. A new query based on the Simple Query Wizard can be created within minutes. The appropriate fields (columns) from the desired tables must be chosen with the
wizard. Typically, these will be from the SMS view tables, such as dbo_vDisk, and dbo_Identification_SPEC. The wizard can then finish up and will present the basic query results. It may be necessary to use the Design View for the query and specify
additional details—for example, if multiple tables are used then the relationship might involve the MachineDataTable. For each machine in the MachineDataTable, however, there are multiple records—at least one for each group for which the machine
has data. Thus, it would be necessary to specify which record should be used to establish the relationship, based on the appropriate GroupKey value. The Design View can also be used to specify some formatting details, calculations, and so forth.
An even more attractive and powerful Access report can be created by generating a new report using the report wizard and choosing the query as the source of the data. All the fields could be selected and various formatting options chosen.
A common use for the data collected by SMS is to report on who has what—for instance, who has what disks, and how full they are.
As per the discussion, Access must be set up to link in the SMS data via ODBC, with the appropriate relationships among tables. Then a query can be created using the Simple Query Wizard. The Disk_Index0, File_System0, Storage_Type0,
Free_Storage__Mbyte_0, and Storage_Size__Mbyte_0 fields from the dbo_vDisk table will be selected, along with GroupKey from the dbo_MachineDataTable and Name0 from dbo_Identification_Spec. This will be a detail query, called Disk Query, and it will be
necessary to modify the query design after you finish with the wizard.
In the design view, you can specify that you don’t want to show the GroupKey, but you want it to have a value of 1 as the criteria so that you select the Identification table record when making the relationship between the vDisk and
Identification_SPEC tables (via the MachineDataTable). You also specify that you don’t want to see records where the Disk_Index0 is equal to A, because you presume every machine has a diskette drive (whose characteristics you already know). With
inserting columns, cutting, and pasting, the fields can be moved into a reasonable order. You can also specify that the query results should be sorted by the Name0 and then Disk_Index0 fields. More descriptive names can be given by right-clicking on each
column and giving it a description in its Properties dialog box. A percentage of disk space free column can be added with the Expression Builder, which can be invoked by right mouse-clicking on the Field row of a new column and taking the Build…
option (and yes, there is a percent free column in the vDisk table already, but for the purposes of demonstration, let's create our own expression). The expression can be given a format property of percent to make it more readable. The design view should
look like Figure 44.2.
FIGURE 44.2. Design view of a query in Access.
The SQL view will be much like the following:
SELECT DISTINCT dbo_Identification_SPEC.Name0, dbo_vDisk.Disk_Index0, dbo_vDisk.Storage_Type0, dbo_vDisk.File_System0, dbo_vDisk.Storage_Size__MByte_0, dbo_vDisk.Free_Storage__MByte_0, [Free_Storage__MByte_0]/[Storage_Size__MByte_0] AS Expr1 FROM (dbo_vDisk INNER JOIN dbo_MachineDataTable ON dbo_vDisk.dwMachineID = dbo_MachineDataTable.dwMachineID) INNER JOIN dbo_Identification_SPEC ON dbo_MachineDataTable.SpecificKey = dbo_Identification_SPEC.datakey WHERE (((dbo_vDisk.Disk_Index0)<>"A") AND ((dbo_MachineDataTable.GroupKey)=1)) ORDER BY dbo_Identification_SPEC.Name0, dbo_vDisk.Disk_Index0;
The query in the end will be in the form of Table 44.1—columns widths may need adjustment, and the data will vary, of course.
| Computer Name | Disk | Disk Type | Type | Size | Free | % Free |
| ABOYD | C | Fixed Disk, CMOS Type 65 | FAT16 | 602 | 365 | 60.63% |
| AFULTON | C | Fixed Disk, CMOS Type 0 | FAT16 | 1215 | 469 | 38.60% |
| AHALVERSON | B | Floppy Drive, 5.25 1.2M |
A report preview based on this query (but with different data) can look like Figure 44.3.
FIGURE 44.3. Report based on the sample Access query.
As we have seen, the SMS database is a wealth of information, but nonetheless it’s not omniscient—it doesn’t know everything about everything. Additional details that may be required can be collected and stored in the SMS database, and
they can then be reported on or manipulated along with all the other information. The data that becomes the machine details in the SMS database starts out at the clients, where it is collected by the software and hardware inventories. The inventories write
their findings to Management Information Format (MIF) files, in the \MS\SMS\NOIDMIF’s directory. This data is then collected and eventually stored in the SMS database. Every SMS Administrator should be comfortable with this process, which is well
detailed in Appendix C, SMS System Flow, of the SMS v1.1 Administrator’s Guide. Other programs can also write MIF files to this directory, enabling collection of additional needed data.
Management Information Format files are ASCII text files with a fairly straightforward format. Listing 44.1 is a typical MIF.
Listing 44.1. A representative Management Information Format (MIF) file.
Start Component Name = "Data" Start Group Name = "Data Information" ID = 1 Class = "Your Organization Name|Data Information|1.0" Start Attribute Name = "Files" ID = 1 Type = String(5) Storage = Specific Value = "23" End Attribute Start Attribute Name = "Word Documents" ID = 2 Type = String(5) Storage = Specific Value = "15" End Attribute End Group End Component
The formatting of this MIF should be fairly intuitive. The Name just after Start Group is the group name, as it will appear in the Personal Computer Properties window of the SMS Administrator program. This will also become the table name in the SMS
database, except that spaces will be replaced with underscores, and _COMM and _SPEC will be suffixed to the common and specific tables, respectively. The attributes are then the columns of the table. The Name after Start Attribute is the column name. The
IDs must be unique and increment appropriately. The Type can either be Counter or String, and if it’s a string, the maximum field length must be specified in parentheses and must not exceed 255. Counters are 32-bit integers. Storage specifies whether
the value ends up in the common or specific tables. Value is the data to be recorded; strings must be within quotation marks.
Other parts of the syntax may be less intuitive. Class is the source of the data, as in who provided it, from which process, and which version. This is useful for analysis down the line if MIFs are found at a stage where the source is not obvious. In
addition to ID, groups can also have a Key. This is necessary in those situations where there may be multiple instances of the group for a machine (disk drives being the example discussed earlier in this chapter).
An unsupported tool is provided by Microsoft to check for MIF file syntax validity. It can be found in the \PSSTools directory tree of the SMS CD-ROM and is called MIFCHECK.EXE. If there are any doubts about the syntax of a MIF, this tool should be used
to check for problems.
MIFs can include many other details, useful in only the most advanced situations, which space does not allow discussion of here (see the BackOffice SDK discussion at the end of this chapter). In particular, MIFs can include details about architectures other than PCs, including completely new architectures. In these cases, the MIFs have to go in the IDMIFS directory of the client PCs. With no-ID MIFs, the architecture defaults to the current architecture. With ID MIFs, the architecture must be specified. If it is a new architecture, that architecture is automatically added in the SMS database.
Adding groups to the inventory for an architecture is very simple. When the first MIF for that group is collected, the Inventory Data Loader will create the appropriate data structures to support that group—namely, specific and/or common tables and
an entry in the GroupMap table. Once the group is added, any further MIFs for that group must be compatible with the first MIF’s definition. For instance, if the first MIF defines the datatype as a string of 10 characters, all further MIFs must also
provide strings. If they’re longer than 10 characters, they will be trimmed to 10 characters.
Once the group is added, it will be displayed with the default icon. This may be acceptable, but if you want to use a more appropriate icon, one must be provided in a Dynamic Link Library (DLL), with appropriate changes to ensure it can be found by the
SMS Administrator program when it is started. The icon can be created as a 32-pixel-by-32-pixel icon using an appropriate program, such as Visual C++’s Microsoft Developer Studio, or Visual Basic’s IMAGEDIT.EXE. Then a stub C program (see Listing
44.2) and resource definition files (see Listing 44.3) must be created and compiled to produce the DLL. Finally, the appropriate registry keys must be adjusted so that the DLL can be found.
Listing 44.2. A stub DLL source program for the resource DLL.
// resdll.c - defines the minimal DLL entry point
#include <windows.h>
// stub resource DLL entry point
BOOL WINAPI DllEntryPoint(HINSTANCE hinstDLL, DWORD fdwReason, LPVOID lpvReser_
{
// Nothing to initialize.
return TRUE;
}
Listing 44.3. The resource compiler source file for a resource DLL.
// resdll.rc - defines a resource for an SMS resource DLL. #include <windows.h> // a Program Display Group icon (as seen in Machine Property windows) PDG_Personal_Computer_Disk ICON test.ico
The files are then compiled with the following commands, presuming Microsoft’s Visual C++ is appropriately installed:
cl -DLL -c resdll.c rc docs.rc link -DLL resdll.obj docs.res
It’s necessary to run REGEDT32 to set up the registry so that the SMS Administrator will find this resource DLL—that is, on Windows NT 3.x. Use REGEDIT on Windows 95. You can use either REGEDT32 or REGEDIT on Windows NT 4.x.
Then, under the HKEY_LOCAL_MACHINE hive, find SOFTWARE\Microsoft\SMS and add a new key (use Edit, Add Key) with a keyname of ResDLLs, no class name. With this new key selected, add a value with a value name of Installed and data type of REG_MULTI_SZ. With
the Multi-String Editor dialog box, you can enter as many lines as there are DLLs to register. Each one must be given a unique name, such as ResDLL1. Then it will be necessary to add keys for each of the DLL names, spelled exactly the same as per the
Multi-String Editor dialog box, and with no class. Finally, with each key selected in turn, these keys must have values added with the value name of PathName and Data Type of REG_SZ. The string for each will be the full path where the resource DLL file
exists, such as C:\SMSADMIN\RESDLL.DLL.
If you want this special icon loaded only for yourself, when registering the key be sure to do it under the HKEY_CURRENT_USER hive while you’re logged in.
Organizations spend a lot of money providing their staffs with computers, trusting that they are providing a useful tool to the workers. The computers may be used for a variety of tasks, such as client-server applications, but one of the primary uses
will probably be office systems, as provided by Microsoft Office. The organization may want to collect information on how many documents users are producing with Office—not to monitor productivity necessarily, but to see how valuable Office is to the
organization. This information could be critical to making decisions relating to training, upgrades, standardization, and so on.
With the popularity of Microsoft Office within large organizations, it is reasonable to expect that users will keep their documents within the My Documents directory tree, as recommended by Microsoft. If so, the batch procedure in Listing 44.4 quickly
provides totals of the various kinds of directories.
Listing 44.4. Batch file to do the directory listings for the sample program.
@ECHO OFF rem Get lists of the appropriate files, in all subdirectories, wide format rem (thus quicker to read), don't stop after each set of 24 lines, and rem don't include commas in the totals (easier to parse) DIR "C:\MY DOCUMENTS\*.DO?" /S /W /-P /-C > WORD_DIR.TXT DIR "C:\MY DOCUMENTS\*.XL?" /S /W /-P /-C > EXCL_DIR.TXT DIR "C:\MY DOCUMENTS\*.*" /S /W /-P /-C > ALLD_DIR.TXT rem turn the lists into a MIF SMS_DOCS rem erase the temporary files ERASE WORD_DIR.TXT ERASE EXCL_DIR.TXT ERASE ALLD_DIR.TXT
Listing 44.5 is a program that analyzes the directory listings and produces a MIF file. The program can be compiled with the command CL SMS_DOCS.C.
Listing 44.5. C program to analyze directory listings and create a MIF file.
/* SMS_DOCS.c - Office document count .MIF file creation program (for use on SMS PC clients)
- Paul Thomsen, August 20th, 1996
- the idea is that we want to collect a count of all Word, Excel and similar
documents under the C:\My Documents directory tree. Then we
also want to store this info in SMS, with all the similar data
*/
#include <stdio.h>
#include <string.h>
#include <time.h>
FILE *mif; // the mif file (the output)
FILE *tempnes; // the directory listing file (the input)
char input_buffer[100];
void get_numbers( char filename[40] );
void create_mif();
void read_line();
void write_line();
int files, sizes;
void main()
{
mif = fopen( "C:\\MS\\SMS\\NOIDMIFS\\SMS_DOCS.MIF", "w" );
create_mif();
fclose( mif );
}
void create_mif()
{
int total_files=0, total_sizes=0;
char temp_buffer[100];
// start the file
write_line( "Start Component\n" );
write_line( " Name = \"MS Office Documents\"\n" );
write_line( " Start Group\n" );
write_line( " Name = \"MS Office Documents\"\n" );
write_line( " ID = 1\n" );
write_line( " Class = \"your organization|MS Office Documents|1.0\"\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Word Files\"\n" );
write_line( " ID = 1\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
get_numbers( "WORD_DIR.TXT" );
sprintf( temp_buffer, " Value = %i\n", files );
write_line( temp_buffer );
total_files += files;
// finish this value and start the next one
write_line( " End Attribute\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Word File Sizes\"\n" );
write_line( " ID = 2\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
sprintf( temp_buffer, " Value = %i\n", sizes );
write_line( temp_buffer );
total_sizes += sizes;
// finish this value and start the next one
write_line( " End Attribute\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Excel Files\"\n" );
write_line( " ID = 3\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
get_numbers( "EXCL_DIR.TXT" );
sprintf( temp_buffer, " Value = %i\n", files );
write_line( temp_buffer );
total_files += files;
// finish this value and start the next one
write_line( " End Attribute\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Excel File Sizes\"\n" );
write_line( " ID = 4\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
sprintf( temp_buffer, " Value = %i\n", sizes );
write_line( temp_buffer );
total_sizes += sizes;
// finish this value and start the next one
write_line( " End Attribute\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Other Files\"\n" );
write_line( " ID = 5\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
get_numbers( "ALLD_DIR.TXT" );
sprintf( temp_buffer, " Value = %i\n", files - total_files );
write_line( temp_buffer );
// finish this value and start the next one
write_line( " End Attribute\n" );
write_line( " Start Attribute\n" );
write_line( " Name = \"Other File Sizes\"\n" );
write_line( " ID = 6\n" );
write_line( " Type = Counter\n" );
write_line( " Storage = Specific\n" );
sprintf( temp_buffer, " Value = %i\n", sizes - total_sizes );
write_line( temp_buffer );
// finish this value and the file
write_line( " End Attribute\n" );
write_line( " End Group\n" );
write_line( "End Component\n" );
}
void get_numbers( char filename[40] )
{
char number[15];
files = 0;
sizes = 0;
if ( ( tempnes = fopen( filename, "r" ) ) == NULL )
printf( "Couldn't open the %s directory listing\n", filename );
// and if successful then do what we need to and close the file
else {
// get to the grand totals - while string not found and not end of file
while ( ( strncmp( " Total Files Listed:", input_buffer, 24 )!=0 )
&& ( feof( tempnes )==0 ) )
read_line();
// get the actual totals file
if ( feof( tempnes )==0 ) {
read_line();
strncpy( number, input_buffer+6, 10 );
number[11]=0;
// printf( "number: %s\n", number );
files = atoi( number );
strncpy( number, input_buffer+29, 10 );
number[11]=0;
// printf( "number: %s\n", number );
sizes = atoi( number );
}
fclose( tempnes );
}
}
void read_line()
{
char character=0;
int i=0;
// make sure the buffer is a null string before we start
input_buffer[0]=0;
// fill the buffer until end of file or end of line
while ( !feof( tempnes ) && ( character != 10 ) ) {
character = fgetc( tempnes );
input_buffer[ i++ ] = character;
}
// make sure we know where the end of this line was
input_buffer[i]=0;
// for testing
// printf( "reading: %s", input_buffer );
}
void write_line( char buffer[100] )
{
int i=0;
while ( buffer[i] != 0 )
fputc( buffer[ i++ ], mif );
// for testing
// printf( "writing: %s", buffer );
}
A typical MIF file produced by the SMS_DOCS program is provided in Listing 44.6.
Listing 44.6. Output from the SMS_DOCS program.
Start Component Name = "MS Office Documents" Start Group Name = "MS Office Documents" ID = 1 Class = "your organization|MS Office Documents|1.0" Start Attribute Name = "Word Files" ID = 1 Type = Counter Storage = Specific Value = 31 End Attribute Start Attribute Name = "Word File Sizes" ID = 2 Type = Counter Storage = Specific Value = 803804 End Attribute Start Attribute Name = "Excel Files" ID = 3 Type = Counter Storage = Specific Value = 29 End Attribute Start Attribute Name = "Excel File Sizes" ID = 4 Type = Counter Storage = Specific Value = 295332 End Attribute Start Attribute Name = "Other Files" ID = 5 Type = Counter Storage = Specific Value = 129 End Attribute Start Attribute Name = "Other File Sizes" ID = 6 Type = Counter Storage = Specific Value = 4555055 End Attribute End Group End Component
A resource icon is also supplied with the sample program, in the ICON subdirectory (details the same as in the previous discussion). Figure 44.4 shows the data collected by SMS_DOCS program when viewed with the SMS administrator (once it has had a
chance to go through the inventory collection system).
FIGURE 44.4. Information about Office documents, as collected by the SMS_DOCS program.
Intuitively, one might think that word processing has little to do with managing PCs. Traditionally, this may have been true, but with a little imagination, SMS administrators can get Word to do a lot of their legwork for them. The most significant Word
feature is mail merging, which can automatically produce paper or electronic mail based on data that SMS has collected, notifying users of various things SMS administrators think are important. If SMS administrators also have the Office Development Kit
(ODK), they can write programs in Word Basic to further enhance their SMS reporting.
SMS administrators might use Microsoft Word with SMS to notify users that have certain software packages that training is soon going to be available. They might advise users that have more than 90 percent of their disks full that their disks are getting
full, and thus it would be advisable to review their disk usage. SMS administrators might also include various common solutions to the problem. If users are found that are using nonstandard (or banned) software, administrators can send a stern message to
them. By using Word, such tasks can be accomplished with a minimum of coding, and the message can include formatting, graphics, and effects to communicate the message and intent most effectively.
The important aspect of Word mail merging for the SMS Administrator is that it can use SQL statements to collect the data to be merged with the document. The Word mail merge feature is based on the Microsoft Query engine, so the SQL syntax must be legal
for Microsoft Query. Microsoft Query should be used to generate the SQL commands to collect the data and to test it to ensure that the correct data is collected.
Programming a mail merge-based report should start with creating a new template. A new document can then be created, based on this template, that contains the message to be sent to the users, complete with whatever formatting may be desired. Some of the
text will vary from user to user, such as user name, software used, disk capacity, or whatever else the requirement may be. The points where the variable text (fields) will go should be left blank and bookmarks must be placed there. This is done by
selecting the Bookmark item under the Edit menu. A bookmark can be added wherever the cursor is currently sitting. The Go To button of this dialog box can also be used to find the bookmarks later and to ensure they exist in the correct places.
Programming in Microsoft Word is done with Word Basic in routines called macros. The macros can be created by selecting Tools, Macro from the menu. For simplicity, the program should be split into two routines—one to ask the user where the database
is and how to log into it, and another to actually do the work.
The first routine should probably be automatically executed when the master document is opened, and thus must be called AutoOpen. It should display a dialog box asking for the ODBC data source, the SQL Server user identification, and the corresponding
password. See the Using ODBC section earlier in this chapter for a discussion of setting up an ODBC data source.
The code that actually does the work should start with a MailMergeMainDocumentType 0 statement in order to tell the mail merge to activate the loaded (and currently visible) document. The collection of the data is done with a MailMergeOpenDataSource
command, with the significant switch being .SQLStatement, which is the SQL command generated and tested in Microsoft Query. It is then necessary to ensure that the main document is ready for mail merging, with a MailMergeEditMainDocument command.
Associating the collected data with the bookmarks previously set up can be done by first going to the bookmarks with an EditBookmark .name="name of bookmark", .SortBy=-1, .Goto command, and then inserting the field name with
InsertMergeField .MergeField="column name", .WordField=-1. Finally, the merging of data and document is done and printed by executing MailMergeToPrinter.
One common problem is that SQL statements can be very lengthy, but Word Basic has limits on how long strings (which contain the SQL statements) can be. This is solved by using additional .SQLStatement switches on the MailMergeOpenDataSource command, such as .SQLStatement1. The strings in the .SQLStatement switches must still be a valid SQL command when they are concatenated.
Mail merging can also send the generated documents to the user via MAPI-based mail, such as Microsoft Exchange or Microsoft Mail. The SQL statement used to collect the data in the MailMergeOpenDataSource command should be extended to include fields from
the SMS database that contain the e-mail address of the users. The MailMerge command can be used to send the documents via e-mail, and it has additional switches to associate the appropriate column with the e-mail address. Check out the ODK for details on
this and other mail merge features.
With the template (and its macros) and the main document saved, the program is ready to run. The simplest way is to open the main document, which will activate the initial routine, prompting the administrator for login details. With these entered, the
link will be made, the data collected, and the print initiated. This process may take a minute or so, depending on the data being collected and the size of the database being scanned. For development and testing purposes, it may not be desirable to
repeatedly close and open the document in order to initiate the program, so the Run button from the Macro dialog box (Tools, Macro) or from the macro toolbar can be used.
Word Basic, which is much like Visual Basic, is a powerful, easy-to-program language. With the powerful benefits of mail merging and word processing, as just discussed, SMS Administrators may want to do significant programming using it. The only
significant problem they will discover is accessing the SQL Server database where the SMS data resides. Word Basic doesn’t have commands (other than mail merging) to do this. This problem is rectified with a dynamic link library (DLL) called
WBODBC.WLL (WLLs are Word DLLs).
WBODBC.WLL is an unsupported library distributed with the Office Development Kit, and is documented in an appendix of the ODK’s documentation. There is also a copy of this program included with the BackOffice SDK; however, that version is a 16-bit
version, and thus only works with Word for Windows, in the Windows 3.x environment.
WBODBC.WLL provides functions to do various things with ODBC databases, such as open and close links, submit queries, retrieve the associated data, figure out how much data is available, and so forth. With Word Basic, the programmer can then list data
collected in dialog boxes, allowing the user to review it or make selections.
This program will demonstrate how to use Microsoft Word’s mail merging feature to prepare documents to advise users who are running low on disk space. As an example main document we’ll use the following letter:
Dear , A review of records finds that your personal computer’s C: disk
drive is % full. This could soon lead to problems when your disk does
fill up. You may not be able to create or save documents that you are
working on, or you may not be able to install new programs that you
may require. We would like to suggest, when you get a few minutes, that you
review what is on your hard disk to see whether anything can be
removed. Common possibilities are old programs or old documents. If
you are uncertain as to which files to remove then please don’t
hesitate to contact the helpdesk. Thank you for your attention to this matter.
Notice that there is no word after Dear, and the percent sign has no number before it. These are where the SMS data will be substituted, so there are bookmarks at each of these points, called Username and Percent, respectively.
The AutoOpen macro prompts the user for login information (see Listing 44.7):
Listing 44.7. AutoOpen macro for sample Word interface.
'mail merging using SMS dat
Sub MAIN
UID$ = "sa"
PWD$ = ""
DSN$ = "SMS"
Begin Dialog UserDialog 660, 216, "Connection Information"
Text 12, 46, 196, 13, "ODBC Connection Name: ", .Text5
TextBox 218, 44, 160, 18, .DSN$
Text 14, 9, 447, 13, "Fill in the following information and press OK when finished", .Text6
Text 14, 76, 113, 13, "SQL Logon ID:", .Text7
TextBox 217, 75, 160, 18, .UID$
Text 14, 106, 73, 13, "Password", .Text8
TextBox 217, 106, 160, 18, .PWD$
OKButton 529, 23, 88, 21
CancelButton 529, 47, 88, 21
End Dialog
Dim GetUserInfo As UserDialog
GetUserInfo.UID$ = UID$
GetUserInfo.PWD$ = PWD$
GetUserInfo.DSN$ = DSN$
If Dialog(GetUserInfo, - 1) = 0 Then
MsgBox("Operation Canceled")
Else
UID$ = GetUserInfo.UID$
PWD$ = GetUserInfo.PWD$
DSN$ = GetUserInfo.DSN$
ODBCstr$ = "DSN=" + DSN$ + ";UID=" + UID$ + ";PWD=" + PWD$ + ";DATABASE=SMS"
Call SMS.MAIN(ODBCstr$)
End If
End Sub
Listing 44.8 is the SMS macro, called at the end of AutoOpen.
Listing 44.7. The SMS macro.
Sub MAIN(ODBCstr$) MailMergeMainDocumentType 0 'the Microsoft Query style SQL statement to get the data statement$ = "SELECT Name0,__Disk_Full0 FROM Disk_COMM,Disk_SPEC,MachineDataTable,vIdentification WHERE SpecificKey=Disk_SPEC.datakey AND CommonKey=Disk_COMM.datakey AND MachineDataTable.dwMachineID=vIdentification.dwMachineID" 'continuation of statement$ statement1$ = " And Disk_Index0='C' AND __Disk_Full0>90 AND GroupKey=8" 'get the data for the mail merge MailMergeOpenDataSource .Name = "", .ConfirmConversions = 0, .ReadOnly = 0, .LinkToSource = 0, .AddToMru = 0, .PasswordDoc = "", .PasswordDot = "", .Revert = 0, .WritePasswordDoc = "", .WritePasswordDot = "", .Connection = ODBCstr$ + ";APP=Microsoft Query", .SQLStatement = statement$, .SQLStatement1 = statement1$ 'indicate where the data should go in the document MailMergeEditMainDocument EditBookmark .Name = "Username", .SortBy = 0, .Goto InsertMergeField .MergeField = "Name0", .WordField = - 1 EditBookmark .Name = "Percent", .SortBy = 0, .Goto InsertMergeField .MergeField = "__Disk_Full0", .WordField = - 1 'do it! MailMergeToPrinter End Sub
The attentive reader will notice the use of MachineDataTable in addition to the SMSVIEW views in the SQL statement. This was done because the SMSVIEWS use double outer joins, which Microsoft Query does not have good facilities to handle (the HAVING
clause does help in many cases). It is tricky to ensure that records are not collected that are not appropriate. Partially bypassing the views can avoid this problem. Microsoft Access has a more sophisticated SQL syntax and thus doesn’t have this
problem.
When the main document is opened, AutoOpen gets the login information from the user, and then calls the SMS macro, producing letters such as the following for all the users that have more than 90 percent of their C: drives full:.
Dear JGilbert, A review of records finds that your personal computer’s C: disk
drive is 95% full. This could soon lead to problems when your disk
does fill up. You may not be able to create or save documents that you
are working on, or you may not be able to install new programs that
you may require. We would like to suggest, when you get a few minutes, that you
review what is on your hard disk to see whether anything can be
removed. Common possibilities are old programs or old documents. If
you are uncertain as to which files to remove then please don’t
hesitate to contact the helpdesk. Thank you for your attention to this matter.
SMS offers several other programming interfaces that may be of interest to you. They provide some powerful options; however, they are much more complicated to implement than those you have looked at so far. They also rely upon the BackOffice Software
Development Kit (discussed in the Summary of this chapter) due to the requirement for headers, libraries, and so forth. The BackOffice SDK includes fairly good documentation on these programming interfaces. Given these factors, you should check this out if
you require these options. Only a brief description is provided here.
There is a powerful application programming interface for SMS that provides the opportunity to view and manipulate data objects in much the way that SMS itself manipulates them. The programmer can manipulate jobs and packages, as well as work with
architectures, security details, and all the other data objects of SMS.
The SMS API requires learning the terminology of SMS’s internals and how they relate to each other. Programs can be developed in C or Visual Basic. Approximately eight sample programs are supplied, many of which are potentially useful or
interesting in themselves. You may want to explore this area of SMS programming even if no particular applications are currently in mind.
Preparing packages to install programs is usually fairly straightforward, as any experienced SMS administrator will know. The SMS Administrator provides a nice facility to create packages and set appropriate options. There are also additional package
options available—you may have observed this from more advanced packages provided by Microsoft or other vendors.
The BackOffice SDK does include a discussion of creating packages to do installations. This mostly involves details relating to package definition files and the considerations under which these files are used.
The network monitor is a powerful utility, useful for resolving some kinds of computer management problems, but it is not strongly related to SMS. The SMS Administrator can be used to initiate the network monitor for a particular computer, but otherwise
the two are separate products. Recognizing these factors, Microsoft has now made the network monitor available with Windows NT 4.0.
The BackOffice SDK provides the resources necessary to extend the Network Monitor, regardless of whether it is used with SMS. The extensions enable the Network Monitor to understand protocols that Microsoft has not yet provided parsers for (an example
of which is DECnet). A new parser can be used by creating and registering a DLL, which is called at predefined functions and which should use various functions and data structures that Network Monitor provides. Note that DLLs are normally created in the C
programming language.
This chapter provided you with many details on programming solutions with SMS. With a good understanding of the SMS database, interfacing SMS with Word or Access provides the programmer powerful yet easy-to-use facilities to use the data collected by
SMS. The SMS administrator can also readily extend the SMS database to collect other data that may be required. Other programming options enable the programmer to extend the network monitor, use packages more effectively, or manipulate SMS objects.
Combined with reviewing the sample code provided on this book’s CD-ROM, you should be able to work out any details needed to implement your own solutions. Microsoft’s BackOffice Software Development Kit (SDK) is an excellent source for a more in-depth discussion of these topics. The BackOffice SDK (at least the SMS-related materials) are good for reference purposes, but often presume details that may not be obvious to the novice SMS programmer. BackOffice Unleashed is a good source to start on this advanced topic, and the BackOffice SDK can be used to build on that knowledge. The BackOffice SDK has been included with the Microsoft Developers Network subscriptions on occasion and is also an important part of the Microsoft Solutions Development Kit. See http://www.microsoft.com/catalog/products/msdk/default.htm for more details.
