Advanced Systems Management Server 2.0 Reporting

Archived content. No warranty is made as to technical accuracy. Content may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Published July 1999

Abstract

This paper introduces the data architecture of Microsoft® Systems Management Server (SMS), and discusses how to create a variety of reports based on SMS queries. Microsoft Access 97 is the primary tool used throughout this paper to create reports based on SMS data.

On This Page

Introduction
SMS 2.0 Data Architecture
Reporting SMS 2.0 Data
Tools for Accessing SMS 2.0 Data
Writing Queries
Creating Reports
Configuration and Performance
Adding WMI Scripting to Your Application

Introduction

This document expands upon the information presented in Chapter 15, "Reporting Options for SMS 2.0," in the Microsoft® Systems Management Server Version 2.0 Resource Guide (referred to later in this paper as SMS 2.0 Resource Guide). It introduces Microsoft® Systems Management Server 2.0 (SMS) data architecture and discusses options for accessing SMS data. The primary tool used in this document is Microsoft Access 97 and the .mdb version of the SMS Query Extract tool. This tool is distributed in versions for both Microsoft Excel and Access on the Microsoft BackOffice® 4.5 Resource Kit CD-ROM.

The topics discussed in this document are:

• SMS 2.0 Data Architecture

• Reporting SMS 2.0 Data

• Tools for Accessing SMS 2.0 Data

• Writing Queries

• Creating Reports

• Configuration and Performance

• Adding WMI Scripting to Your Application

Before You Begin

This document describes how to create a variety of Access 97 reports. Most of the reports described are based on either existing or newly created SMS queries. In order to use the information in this document, you should be familiar with the procedures and related documentation listed here:

Creating and Using SMS Queries

You should know how to create and use SMS queries from within the SMS Administrator console. Refer to Chapter 11, "Managing Collections and Queries," and Appendix E, "Attributes, Attribute Classes, and WBEM Class Names," in the Microsoft Systems Management Server Version 2.0 (SMS) Administrator's Guide (referred to later in this paper as the SMS 2.0 Administrator's Guide), and Appendix B, "SMS Object Properties," and Appendix C, "Hardware Inventory Classes," in the SMS 2.0 Resource Guide.

Using the SMS Query Extract Tool

You should have experience importing SMS queries using the Access version of the SMS Query Extract tool. This tool is distributed on the BackOffice 4.5 Resource Kit CD-ROM. You can find the tool on the CD-ROM at <cd rom drive>\Support\Reskit\Bin\<platform>\Report\SMSExtract.mdb where <platform> is either Alpha or i386. Refer to Chapter 15, "Reporting Options for SMS 2.0," in the SMS 2.0 Resource Guide for more information about using this tool.

Creating Access Tables and Queries

You should be familiar with creating tables and queries in Access 97 and know how to use the Access 97 Report Wizard. Refer to the appropriate Access 97 documentation for more information about these subjects.

Additional References

You can obtain additional information about how to use Windows Management Instrumentation (WMI) and Web-Based Enterprise Management (WBEM) from the following sources:

• The Microsoft SMS 2.0 Toolkit

• The WMI Software Development Kit (SDK)

• Microsoft support at

  https://www.microsoft.com

Some examples in this document use Microsoft Visual Basic® for Applications code, but in-depth knowledge of Visual Basic for Applications and WMI Scripting is not required.

SMS 2.0 Data Architecture

The technology that underlies how SMS organizes and presents information about your SMS site network is completely different than the technology that previous versions of SMS used. SMS uses WMI technology as the data provider source of all information about your site. This technology is also integrated into the Microsoft Windows® 2000 and Windows NT® operating systems.

Web-Based Enterprise Management (WBEM)

Although the actual data is stored in Microsoft SQL Server™ tables on your SMS site database server, SMS gathers, organizes, and presents the data in accordance with the management infrastructure standards set forth by the Web-Based Enterprise Management (WBEM) initiative, in which Microsoft is an active participant. WBEM is an enterprise-grade, industry-supported technology that specifies standards for a unifying data architecture, allowing access to a variety of underlying technologies and platforms and presenting that data in a consistent fashion. This technology is designed to scale well and to target the needs of enterprise-sized organizations. It is important to understand that WBEM is not an actual data repository (such as SQL Server), nor does it provide a user interface (as does, for example, the SMS Administrator console) into the data. WBEM is a set of standards for managing the enterprise network.

WBEM is based on the Common Information Model (CIM) version 2.0 schema definitions that represent managed objects in a Windows environment. Each WBEM class is a template for a type of managed object. For example, the Win32_LogicalDisk class is a template for all disks that can exist on a Windows-based computer. For each physical disk, there exists an instance of the class. WBEM classes contain properties, which describe the data, as well as methods. The methods describe the behavior of the class, such as creation, deletion, and updating behaviors.

CIM is an initiative by a group of industry leaders (the Desktop Management Task Force, or DMTF) created to develop a conceptual data model of the environment that unifies and extends existing standards such as Simple Network Management Protocol (SNMP), Desktop Management Interface (DMI), and others. As you might surmise from the discussion so far, CIM is an object-oriented data model. It offers a reliable, extensible model for enterprise-grade information management. CIM provides a unified view of manageable physical and logical objects in the operating environment. These managed objects represent system resources. CIM is platform-independent, but it supports extensions to the core data model. More information about CIM can be found at:

https://www.dmtf.org

Windows Management Instrumentation (WMI)

To meet your needs as a Windows NT administrator, Microsoft developed WMI to implement WBEM standards in a Windows environment. WMI extends the CIM model with the Microsoft Win32® Extension Schema for the Windows operating system. WMI allows you to monitor, configure and control any manageable object through a common, unifying set of interfaces, regardless of the underlying instrumentation mechanism. WMI provides, among other benefits:

• A richly descriptive, extensible model of Microsoft Windows 95, Windows 98, and Windows NT system operation, configuration, and status.

• A flexible data model that permits vendors to extend the schema to cover new devices and applications by writing WMI providers. The SMS Provider is such a provider, extending the WMI model to SMS-specific operations.

• An event architecture that allows changes in management information to be identified, aggregated, compared to and associated with other management information, and forwarded to local or remote management applications, such as SMS. For example, you can query against the flow of status messages associated with the software distribution feature of SMS.

• A query language, WMI Query Language (WQL). This language uses a SQL syntax that is extended to support event notification and other WBEM-compatible features. SMS queries use WQL statements to obtain data from WMI classes.

• The WMI Scripting Application Programming interface (API), which you can use to query and manipulate the WMI object classes with a variety of tools, such as Microsoft Visual C++®, Access, Perl, JScript®, the Visual Basic family of languages, and others.

• The use of Managed Object Format (MOF) files. MOF files are used to define the structure and contents of the CIM data schema, creating a very extensible data schema. Refer to Chapter 10, "Collecting Hardware and Software Inventory," in the SMS 2.0 Administrator's Guide for information about using MOF files with SMS.

Internal WMI architecture consists of the CIM Object Manager (CIMOM) and the CIMOM Object Repository, as well as WMI providers for the Win32 environment. The CIM Object Manager handles the communication of information between data providers and data consumers such as the SMS Administrator console or a reporting application making calls to the WMI Scripting API. The CIMOM Object Repository holds the CIM schemas, which the Object Manager uses when it services requests that applications make for CIM objects.

Note: Direct querying against the SQL Server tables themselves is not recommended or supported. When reporting, always access your SMS site data by using one of the methods shown in the illustration below.

The following illustration depicts the WMI architecture and the flow of information between data providers into WMI. It depicts both a management solution such as SMS, and querying tools like Access 97, Crystal Info for SMS, and Visual C++.

Figure 1: WMI Architecture

Additional information about CIM, WBEM, and WMI technology is available online from Microsoft at

https://www.microsoft.com/ntServer

Reporting SMS 2.0 Data

This document focuses on using Access 97, alone and with the SMS Query Extract database loaded, to create reports from SMS data. When you link to SMS data using Access 97 alone, during the process of connecting to a data source, you view the Select Data Source dialog box as shown below:

Figure 2: Select Data Source dialog box

If you select the WBEM Source option as illustrated, you are connecting to the data through the Microsoft WBEM ODBC (Open Database Connectivity) driver. This method is convenient, and it is easier and faster to produce reports this way. The driver converts SMS object classes to tables for your queries by mapping WBEM namespaces to ODBC databases and WBEM classes to ODBC tables.

When you load the Access version of the SMS Query Extract tool — Smsextract.mdb — into Access 97, access to the SMS Provider is not handled by the WBEM ODBC driver. Instead, code modules in this database make direct calls to the WMI Scripting API. This offers you the advantage of direct access to WMI data classes, which improves the performance of your queries.

Whether you use Access 97 alone or with the SMS Query Extract database, you find that each approach has both advantages and disadvantages. Logging onto the SMS Provider through the WBEM ODBC driver is quick and easy. The driver transparently converts class attribute arrays into ODBC tables and embedded objects resulting from query joins, as well as other aspects of conversion from object class to table. However, because of differences in supported Structured Query Language (SQL) syntax between the WMI Query Language (WQL) and the WBEM ODBC driver, such as those described in the "Query Syntax" section later in this document, you should take care to construct your queries with these differences in mind.

Tools for Accessing SMS 2.0 Data

Your reporting options are not limited to the Crystal Info reports that are distributed on the SMS 2.0 CD-ROM, or to the reports you can create yourself with the Crystal Report Designer. Because SMS utilizes WMI, you can access SMS data with a variety of tools. When you choose a tool, consider the advantages and disadvantages of each tool for the reporting task at hand, as well as your personal preference.

Note: WMI must be installed on any computer from which you want to access your SMS site data. WMI is installed automatically on any computer on which you install a remote SMS Administrator console. You can install WMI from the SMS 2.0 CD-ROM by installing \Support\Reskit\Bin\<platform>\Diagnose\Wbemtest.exe where platform is either Alpha or i386.

Reporting Tools

The following list summarizes some of applications that you can use to access SMS data for reporting purposes, and offers considerations for using each:

Microsoft Access 97

This application can access the SMS data classes through either the WMI Scripting API or through the WBEM ODBC driver. Using Access 97, you can either dynamically link to an SMS object class, or you can import static views of the data into local tables. Access 97 features a powerful and versatile report designer, wizards to assist you with every aspect of creating queries, tables and reports, and is powered by a fully featured relational database engine.

Microsoft Excel 97

This application, which can access the WMI Scripting API directly, offers easy manipulation, graphing, and printing of data, but it lacks a report writer. If you prefer working with Excel, you can export the data to other applications such as Microsoft Word 97. Several third-party application developers, such as Seagate Software, offer reporting engines that can use data and graphs from Excel tables. Excel is based on Microsoft ActiveX® technology, which makes programming business solutions by exposing and accessing application objects as disparate as Excel and, for instance, Microsoft PowerPoint®, particularly seamless.

Seagate Crystal Info for SMS

This tool is distributed on the SMS 2.0 CD-ROM. You can use this tool to create, schedule, and edit Crystal Reports based on your SMS data. Several predesigned Crystal Info reports included with SMS are designed to produce useful information about the operation of your SMS site. You can alter these reports or use them as the basis for your own reports by using the Crystal Report Designer. This tool can help you produce reports that include multiple levels of detail, and makes charts easy to add.

Seagate Crystal Web Report Server

This product is distributed as part of Crystal Info 6.0 and is included on the BackOffice4.5Resource Kit CD-ROM. When it is installed, users can schedule and view reports in Hypertext Markup Language (HTML) format from within their Web browsers.

SMS Query Extract tool

This tool (Smsextract.mdb and Smsextract.xls) is located in the Support\Reskit\Bin\<platform>\Diagnose directory (where <platform> is either Alpha or i386) on the BackOffice 4.5Resource Kit CD-ROM. The SMS Query Extract tool accesses SMS data by making calls to the WMI Scripting API in code to run the queries you import. Two versions of this tool are distributed, an Excel version and an Access version. After importing and running each query, you can save or export the data and analyze it further to meet your reporting objectives. This is the easiest method for creating reports using SMS data, and it is likely that users who are creating reports are familiar with either Excel or Access.

SMS Status Query Viewer

This tool works with the SMS status queries. Although named queries cannot be printed or exported from the Query Viewer interface, you can export status messages from the Status Message viewer as delimited ASCII text, and then import that text into another application such as Microsoft Excel or Word. Compared to the other tools in this list, creating reports in this manner is a tedious procedure.

Scripting Languages that Support Microsoft ActiveX Scripting Technologies

The WMI Scripting API can be called by any scripting language that supports ActiveX Scripting, such as Perl, Jscript, and Visual Basic Script. In addition, you can use the Window Script Host or Microsoft Internet Explorer to write scripts using this API.

Other Tools

Other tools are available to you that, although not used for reporting, can help develop your understanding of WQL query syntax, WMI/WBEM classes, and how SMS utilizes this technology.

SMS Administrator Console

Within the SMS Administrator console, you can examine the properties of each SMS query to learn which classes are being used, and you can examine the WQL statement that is the basis for the query. You can alter the query by altering the WQL statement, and you can also create your own queries. For instance, you can use the Resource Explorer to familiarize yourself with hardware and software inventory classes.

CIM Studio

The WBEM SDK includes several tools that allow you to view and change data in the CIM repository, and configure and view events. The most useful of these applications is CIM Studio. You can use this tool to edit SMS data classes and instances, to edit properties and qualifiers, and to generate and compile Managed Object Format (MOF) files.

Status Message Viewer

With the SMS Status Message Viewer, you can view status messages in a variety of ways. This tool constructs queries against the SMS status message system with whatever parameters you select. By navigating to the View menu and clicking Query Information, you can examine the query's WQL statement. Examining these statements will help you to understand how to query against the status message system and also how to use WQL statements as the basis for your own queries.

Microsoft Query

MS Query is an easy method to access data using the WBEM ODBC driver, but this tool lacks reporting features compared to the other choices in this list.

Writing Queries

If you navigate to any SMS query in the SMS Administrator console and right-click to obtain the properties dialog box of any query, you can view the WQL statement that is the basis for that query by clicking Edit Query Statement. When the statement properties dialog box for that query appears, you can view the actual WQL statement by clicking Show Query Language.

SMS uses the WMI Query Language (WQL) and a superset of the language called Extended WQL. Both languages are retrieval-only languages used to create queries. You cannot create, modify or delete classes or instances using either language. WQL and Extended WQL (hereafter referred to as just WQL) are based upon the American National Standards Institute Structured Query Language (ANSI SQL), but they differ from SQL in that they retrieve data from classes instead of tables and from instances instead of rows.

Query Syntax

To help you select from either of these methods, bear in mind the following considerations when accessing SMS data using Access 97 alone through the WBEM ODBC driver:

• The WBEM ODBC driver does not support the following keywords: UNION, INNER JOIN, the DATADIFF() function.

• SQL Server error messages generated at the SMS site database server are not passed to the WBEM ODBC driver. This might make developing and debugging more complex queries more difficult.

• When a query is submitted by the WBEM ODBC driver to the SMS Provider, the query statement is parsed into a WQL statement that can be understood by the CIMOM. If your query contains unsupported keywords, or for any reason does not run the query successfully, the driver will attempt to reparse the submitted statement into subqueries. The subqueries are then run individually, and the data is then reassembled by the driver. This post-processing of queries by the WBEM ODBC driver permits you to run queries that you otherwise might not be able to run in WQL. For instance, using the WBEM ODBC driver, you can run queries that contain aggregate functions (which are not supported by WQL).

  Sometimes this feature of the WBEM ODBC driver will cause lengthy run times for your query as the driver attempts to parse your query into subqueries and run each one individually.

  Tip If you do not want the driver to attempt to resubmit your query for processing while trying to run the query, select Passthrough only when logging onto the SMS Provider in the Configure Connection dialog box. This generally yields faster processing times for your query by passing your SQL statement directly to the provider, and has the additional advantage of supporting queries that WQL cannot support.

If you create queries to run through the WMI Scripting API, keep the following in mind:

• Aggregate functions such as MIN, MAX, and AVG are not supported in WQL.

• When creating complex queries and dealing with WMI array properties, as the developer you must create additional code to deal with the differences between the WMI class schema and relational database queries that are otherwise handled by the WBEM ODBC driver.

• WQL does not support SQL Passthrough; it cannot merely pass a SQL string to the database engine. The syntax must follow the WQL standard.

As the standards for ANSI SQL have changed over time, different versions of the language have been created, distinguished mainly by syntactical differences between the versions. Each version is referred to by the year the standard was finalized, for example, ANSI SQL 92. The Extended WQL used by SMS supports language elements from ANSI SQL, ANSI SQL 89, and ANSI SQL 92. ANSI SQL 92 is the recommended version for most of your SMS query statements.

Although some elements of ANSI SQL 92 are used by the WBEM ODBC driver, the version of ANSI SQL primarily supported by the WBEM ODBC driver is ANSI SQL 89. When you construct SQL statements to query against the SMS object classes, the driver converts the SQL syntax to conform to the WQL ANSI 92 standards. After conversion, the driver submits the statement to the SMS Provider.

As a result, when you construct queries using a reporting tool that accesses the data through the WBEM ODBC driver, you will find that some queries might not succeed, depending on whether the keyword or function (such as AVG) is supported by WQL.

As previously mentioned, if Passthrough only is selected in the Configure Connection dialog box, and if you use an unsupported syntax (such as an aggregate function) in your SQL statements, your query will fail because the SQL statement is sent directly to the SMS Provider without conversion to WQL. If you want the driver to reparse your SQL statements into WQL and post-process your queries, do not select Passthrough only.

SMS Object Classes

SMS 2.0 introduces over 200 WMI classes that define the information gathered by SMS about your network. Although the actual data is stored in SQL Server tables, SMS queries and reports don't directly query the SQL Server database tables. SMS queries are based on the SMS object classes, and are accessed by calling the WMI API either directly or through code.

Although there are many SMS object classes, in practice less than 70 are needed for reporting. The following are the six main types of classes associated with reporting:

• System Resource

• Hardware Inventory

• Software Inventory

• Status and Summarizer

• Software Distribution

• Y2K Product Compliance

The first listed class, System Resource, identifies every discovered system in your SMS site. You must join this class with one or more of the other main classes to produce reports.

The System Resource Class

The SMS_R_System class identifies every discovered system in your site. All inventory classes join to SMS_R_System with the ResourceID property. Multiple resource classes can be joined.

The class definition follows:

[CIMWin32Name("SMS_R_System"), DisplayName("System Resource"), dynamic:
class SMS_R_System : SMS_Resource
{
     uint32 ResourceId;
     string AgentName[];
     string AgentSite[];
     datetime AgentTime[];
     uint32 Client;
     string ClientVersion;
     string IPAddresses[];
     string IPSubnets[];
     string IPXAddresses[];
     string IPXNetworkNumbers[];
     string LastLogonUserDomain;
     string LastLogonUserName;
     string MACAddresses[];
     string Name;
     string NetbiosName;
     string OperatingSystemNameandVersion;
     string ResourceDomainORWorkgroup;
     string ResourceNames[];
     uint32 ResourceType;
     string SMSAssignedSites[];
     string SMSInstalledSites[];
     string SMSUniqueIdentifier;
     string SNMPCommunityName;
     string SystemRoles[];
};

([] indicates an array property).

Hardware Inventory Classes

SMS hardware inventory classes can be divided into three types, based on when the data was collected. This division by time allows you to create trending reports and track changes in hardware over time. The naming convention is shown in Table 1.

Table 1 Hardware Inventory Class Prefixes

Prefix	Description
SMS_G	Current inventory.
SMS_GH	Inventory history; can be used for trending information.
SMS_GEH	Extended history; to query on these classes, you must provide a date and a ResourceID.

The following table describes the SMS hardware inventory classes you use to query for information. When you create a hardware inventory query, join SMS_R_System with one or more of the current inventory classes listed below. The list is typical; there might be more or fewer classes depending on your system configuration. In Table 2, note that you are querying current inventory information, as shown by the prefix to each class name.

Table 2 Hardware Inventory Classes

Display Name	SMS Object Class Name	Description
Boot Configuration	SMS_G_System_ BOOT_CONFIGURATION	Information about the client's boot configuration.
CD-ROM	SMS_G_System_CDROM	Information about CD-ROM devices found on the client.
Computer System	SMS_G_System_ COMPUTER_SYSTEM	Information about the client computer, such as the name of the computer manufacturer, the SMS system role, and the computer name.
Display Configuration	SMS_G_System_DISPLAY_CONFIGURATION	Information about the client display driver.
Display Controller Configuration	SMS_G_System_DISPLAY CONTROLLER CONFIGURATION	Information about the video adapter configuration in Win32 systems.
Environment	SMS_G_System_ ENVIRONMENT	Client system environment, such as COMPUTERNAME.
IRQ	SMS_G_System_IRQ	Current interrupt (IRQ) assignments on the client.
Keyboard	SMS_G_System_ KEYBOARD_DEVICE	Information about the client's keyboard.
Lastest Software Scan	SMS_G_System_ LastSoftwareScan	Information about the last software inventory cycle.
Logical Disk	SMS_G_System_ LOGICAL_DISK	Information about the logical disk drives on the client, such as volume names and file systems.
Modem	SMS_G_System_ MODEM_DEVICE	Information about modems attached to the client.
Memory	SMS_G_System_ X86_PC_MEMORY	Information about the client's memory configuration, such as page file space.
Motherboard	SMS_G_System_ MOTHERBOARD_DEVICE	Information about the primary and secondary bus types on the client.
Network Adapter	SMS_G_System_ NETWORK_ADAPTER	Information about the network adapters on the client, such as type of adapter and MAC address.
Network Adapter Configuration	SMS_G_System_ NETWORK_ADAPTER _CONFIGURATION	Information, such as IP address, IP subnet, and default IP gateway.
Network Client	SMS_G_System_ NETWORK CLIENT	The type of network the client runs.
Operating System	SMS_G_System_ OPERATING_SYSTEM	Information about the client's operating system.
Parallel Port	SMS_G_System_ PARALLEL_PORT	Information about the client's parallel ports.
Partition	SMS_G_System_ PARTITION	Information about an area of the physical disk on a Win32 system.
PC_BIOS	SMS_G_System_BIOS	Information about the client's BIOS.
Pointing Device	SMS_G_System_ POINTING_DEVICE	Information about the client's mouse or other pointing device.
Port	SMS_G_System_PORT	Information about port numbers on a Win32 client system.
Printer Configuration	SMS_G_System_PRINTER _CONFIGURATION	Information about printer configuration.
Printer Device	SMS_G_System_ PRINTER_DEVICE	Information about any printer or imaging device connected to a Win32 system.
Processor	SMS_G_System_ PROCESSOR	Information about client processors, including manufacturer and version.
Protocol	SMS_G_System_ PROTOCOL	Information about network protocols used by the client.
SCSI Controller	SMS_G_System_ SCSI_CONTROLLER	Information about SCSI controllers on the client.
Serial Port	SMS_G_System_ SERIAL_PORT	Information about any serial ports on the client.
Serial Port Configuration	SMS_G_System_ SERIAL_PORT_ CONFIGURATION	Information about configuration of the serial ports on a client, such as baud rate and parity values.
Service	SMS_G_System_SERVICE	Information about services running on the client.
Sound Device	SMS_G_System_ SOUND_DEVICE	Information about sound cards present on the client system.
System	SMS_G_System_SYSTEM	Information about the client system, such as the client's domain, name and SMS ID.
System Drivers	SMS_G_System_ SYSTEM_DRIVERS	Information about system drivers for a client.
System Resource	SMS_G_System_ SYSTEM_RESOURCE	Information about a system resource on a Win32 client.
Tape Drive	SMS_G_System_ TAPE_DRIVE	Information about a tape drive on a Win32 client.
Time Zone	SMS_G_System_ TIME_ZONE	The time zone for this client.
Video	SMS_G_System_VIDEO	Information about the client video adapter card and monitor. Information includes installed display drivers, adapter type, chipset used, manufacturer, screen height and resolution.
Workstation Status	SMS_G_System_ WORKSTATION_ STATUS	Information about when inventory was collected on the client.

As an example of the information contained in one of these classes, refer to the following table. It describes attributes defined by the SMS_G_System_LOGICAL_DISK class.

Table 3 SMS_G_System_LOGICAL_DISK

Attribute	Example	Description
GroupID	5	Resource key; SMS group ID.
ResourceID	4	Resource key; unique SMS ResourceID.
Availability	7	The availability and status of the device. Values are: 1=Other 2=Unknown 3=Running/Full Power 4=Warning 5=In Test 6=Not Applicable 7=Power Off 8=Off Line 9=Off Duty 10=Degraded 11=Not Installed 12=Install Error 13=Power Save-Unknown 14=Power Save-Degraded 15=Power Save-Standby 16=Power Cycle
BlockSize	0	Size in bytes of blocks that form this StorageExtent. If unknown or if block concept not valid, value = 1
Caption	"C:"
Description	"Local Fixed Disk"
DeviceID	"C:"
FileSystem	"NTFS"	The type of file system running on the Win32 local disk.
FreeSpace	1788	Size in MB of available disk space.
HotSwappable	0	Values are 0 = no 1 = yes
Name	"C:"
Power Management Enabled	0	Boolean indicating that the device can be power managed.
RevisionID	1	SMS Revision ID.
Size	3200	Size of the volume in MB.
SystemName	"SMS20NTWKS"
TimeStamp	"19980709151505. 000000+***"	Last time this disk was formatted.
VolumeName	"C_DRIVE"

Software Inventory Classes

Files discovered on each client are examined for header information; this data is stored in SMS_G_System_Software Files and SMS_G_System_Software Products. If no header information is found, then the name of the file and the file size are stored in SMS_G_System_Unknown File. For a software inventory query, join SMS_R_System with one or more of the classes displayed in table 4.

Table 4 Software Inventory Classes

Display Name	WBEM Class Name	Description
Software Products	SMS_G_System_ SoftwareProducts	Information about software inventoried on the client, grouped by manufacturer name.
Software Files	SMS_G_System_ SoftwareFiles	Information about software inventoried on the client, grouped by file name.
Unknown File	SMS_G_System_ UnknownFile	Files that do not have identifying header information.

For each row in SMS_G_System_SoftwareProducts there can be from zero to many rows in SMS_G_System_SoftwareFile, linked by ProductID.

Status and Summarizer Classes

SMS status classes contain information about status messages. These messages can originate in client and server components such as the SMS Advertised Programs Client Agent or the SMS Site Component Manager. Status messages can also originate from the SMS Provider and the software metering components of SMS. For more information about SMS status and summarizer messages, refer to Chapter 26, "Status Messages," in the SMS 2.0 Resource Guide.

Summarizer classes contain current event status. Although information contained in these classes does not expire after seven days, you should be aware of the timing of the summarization cycles.

Table 5 Status and Summarizer Classes

WBEM Class	Description
SMS_AdvertisementStatusSummarizer	Displays detailed advertisement status information grouped by site code. Maps to the advertisement items beneath the Package Status item in the SMS Administrator console.
SMS_ComponentSummarizer	Green-Yellow-Red (GYR) indicator related to the total error and warning messages concerning your network's health.
SMS_PackageStatus DistPointsSummarizer	Information about the status of packages on the distribution points.
SMS_PackageStatus DetailSummarizer	Detailed information about status of a given package by site code. Maps to the package console tree item beneath the Package Status item in the SMS Administrator console.
SMS_PackageStatus RootSummarizer	Information about the status of a given package.
SMS_SiteDetailSummarizer	Contains the number of information/error/warning messages based on a given tally interval for a given site.
SMS_SiteSystemSummarizer	GYR indicator that looks at free space to determine health. Maps to the Site System Status item in the SMS Administrator console.

Software Distribution Classes

The SMS software distribution classes define the software distribution process. To troubleshoot software distribution problems, also query the Status classes.

Table 6 Software Distribution Classes

WBEM Class	Description
SMS_Package	A package contains zero or more programs.
SMS_Program	Defines a program.
SMS_DistributionPoint	Defines packages on distribution points.
SMS_Advertisement	For each package and program, describes an advertisement.

Y2K Product Compliance Class

Product compliance information is contained in the SMS_ProductCompliance class. This class lists each software product by name, version, and degree of compliance. This class is joined to the SMS_G_System_SoftwareProduct class, which contains information about software inventoried on your site's resources. For those products that do not contain header information to identify themselves, the SMS_G_System_UnknownFile class is used. As an example of how these classes are used, consider the following WQL statement, which is the basis for the Y2K All Compliant Software in This Site and Its Subsites query.

SELECT DISTINCT compl.ProdName, compl.ProdVer, compl.ProdCompany,
compl.ProdLang, compl.URL, compl.Comment 
FROM SMS_SoftwareProductCompliance as compl 
INNER JOIN SMS_G_System_SoftwareProduct as prod 
ON compl.ResProdName = prod.ProductName 
AND compl.ResProdVer = prod.ProductVersion 
INNER JOIN SMS_G_System_SoftwareFile as prodfile 
ON UPPER(prodfile.FileName) = UPPER(compl.FileName) 
AND prodfile.FileSize = compl.FileSize 
WHERE compl.Category = "Compliant" 
AND compl.Type = "Year 2000 Compliance" 
AND (compl.ResProdLangID = prod.ProductLanguage OR compl.ResProdLangID = 65535) 
AND prod.ProductID = prodfile.ProductID 
UNION 
SELECT DISTINCT compl.ProdName, compl.ProdVer, compl.ProdCompany,
compl.ProdLang, compl.URL, compl.Comment  
FROM SMS_SoftwareProductCompliance as compl 
INNER JOIN SMS_G_System_UnknownFile as unknownfile 
ON UPPER(unknownfile.FileName) = UPPER(compl.FileName) 
AND unknownfile.FileSize = compl.FileSize 
AND unknownfile.ProductId = 0 
WHERE compl.Category = "Compliant" 
AND compl.Type = "Year 2000 Compliance"

As you can see, if you want to query against all the software found at your site, it is necessary to also query against the SMS_G_System_UnknownFile class.

Creating Reports

This section describes several reports that illustrate various approaches to reporting on SMS data. Each report is presented in an order that builds upon the familiarity with the procedure that you attained in the previous report. You might find it advantageous to work through these reports in sequence, although you can also create each report, with the exception of the Rate of Installation for All Advertisements report, without reference to the work you performed in the other reports.

The following kinds of reports are described in this document:

• Advertisement Status Report—This report is based on an Access 97 table linked to an SMS Object class.

• **Y2K Compliance Report—**This report is based on an existing SMS query, which is then imported into the SMS Query Extract tool.

• **Systems with Missing RAM Report—**This report is based on a new query that you create in the SMS Administrator console. After creating the query, import the data into the SMS Query Extract tool. Finally, create an Access query to modify the results by removing extraneous data.

• **Operating Systems in Use by Site Report—**This report is also based on a new query. After creating the query, you can create an Access crosstab query.

• **All Status Messages Over Time for a Single Advertisement Report—**This report involves modifying the SMS Query Extract database. The first step in creating this report is to create a lookup table detailing the codes used with status messages. Then, after modifying the SMS Query Extract tool that allows you to import advertisement messages, you can create a new report of those messages. Finally, you can add a chart to the report.

• **Rate of Installation for All Advertisements Report—**This report is based on the previous report. You can modify the previous report in order to report on the installation progress of all advertisements at your site. First, create a new query, and then import the status messages for successfully run advertised programs into the SMS Query Extract tool. Then create an Access query to track running totals.

Advertisement Status Report

For the Advertisement Status report, you will create a WBEM ODBC link to an SMS object class. Then, you will filter the information obtained to view only the information you are interested in.

In the SMS Administrator console, Advertisement Status contains a status summarizer for each advertisement distributed by SMS, and a summary of advertisement statistics across all sites in the hierarchy.

The Advertisement Status details pane displays the information listed in Table 7.

Table 7 Advertisement Status Field Descriptions

Field	Description
Name	The name assigned to the advertisement when it was created.
Failures	The number of users and/or clients that experienced an error processing the advertisement or its associated package, or that attempted to run the advertised program but failed.
Programs Started	The number of users and/or clients that started running the advertised program.
Program Errors	The number of users and/or clients that reported errors while running the advertised program.
Program Success	The number of users and/or clients reporting that the advertisement ran successfully.
Package	The name of the package being advertised.
Program	The program type in the package being advertised.
Target Collection	The collection to which this advertisement was sent.
Available After	The time after which this advertisement is available to the target collection.
Expires After	The time after which this advertisement is no longer available to the target collection.
Advertisement ID	The ID number assigned to the advertisement when it was created.

You can create a report that contains the above information by creating a report based on a link to the data. Note that when you create an Access link to an SMS data object class, the data is always current. Alternatively, merely importing data into an Access table will also allow you to report, but unlike linked data, imported data is static. It is not refreshed or updated each time you access the table. A linked table is always current.

To create an Advertisement Status report

1. In Access 97, either create a new database or select an existing database. On the File menu, click Get External Data and then click Link Tables. The Link dialog box appears.

   

2. In the Link dialog box, a portion of which is shown below, scroll down the list of choices in the Files of Type box and select ODBC Databases ().

   

3. The Select Data Source dialog box appears. Click the Machine Data Source tab and select WBEM Source. Click OK. The Configure Connection dialog box appears.

   

4. In the Configure Connection dialog box, type your user name, password, and site server name. Click Connect.

5. After connecting to the SMS Provider, the Connect button changes to a Refresh button, and the namespaces available to you appear in the Namespace Selection box. In the Namespace Selection box, navigate to the SMS namespace selection you want. Click OK (not Refresh).

   

6. In the Link Tables dialog box, select SMS_AdvertisementStatusRootSummarizer. Be sure the Save password check box is selected so you do not have to manually connect to the namespace or refresh the link data the next time you access the linked table. Click OK.

   

7. In the Select Unique Record Identifier dialog box that appears, do not choose a field. Click OK to allow the ODBC driver to identify the unique record identifier.

   

   A new linked table appears in your Access 97 Database window. The linked table contains the same name as the data class, SMS_AdvertisementStatusRootSummarizer.

8. To create the actual report, in the Access 97 Database window, click the Reports tab, and then select New.

9. In the New Report dialog box, click AutoReport:Columnar. Then, in the Choose the table or query where the object's data comes from box, select the linked table, SMS_AdvertisementStatusRootSummarizer. Click OK.

In a few minutes the Access Report Wizard creates the report. Note that there are multiple pages for each advertisement, corresponding to different starting dates for the statistics. For this example, select the starting date where DisplaySchedule = "0001128000080008," filtering out all other starting date values. This selects all the statistics "Since site installation". For an explanation of this code, refer to the BackOffice 4.5Resource Kit.

To eliminate the extra pages

1. Press Esc to go to Design view. Or, on the View menu, click Design View.

2. When Design view appears, on the View menu, click Properties. The Report dialog box appears.

3. In the Report dialog box, shown below, click the All tab. Type in the following: DisplaySchedule="0001128000080008" in the Filter field and type or select Yes in the Filter On box, as shown. Then, close the Report dialog box.

   

4. In the header at the top of the report now displayed, change the caption to SMS Advertisement Status by double-clicking the title label and editing the text. Save the report as SMS Advertisement Status. The report should display information similar to this:

   Table 8 Example Data, SMS Advertisement Status

Column	Value
AdvertisementID	ONE2000
AdvertisementsName	a1
AdvertisementsFailed	0
AdvertisementsReceived	160
CollectionID	SMS00008
CollectionName	All Windows NT Workstation 4.0 Systems
DisplaySchedule	0001128000080008

You have just created a table linked to an SMS data class through the WBEM ODBC driver, and then filtered the report. Because you created a linked table instead of importing data, the data will always be current. Because you saved your password with the link, you will not have to reconnect to the namespace the next time you run this report.

Y2K Compliance Report

For the Y2K Compliance report, you create a report based on an existing SMS query, using the SMS Query Extract tool.

To create the Y2K Compliance report

1. In Access 97, load the SMSExtract.mdb file.

2. In the SMS Login dialog box, type the site server name. Do not use leading backslashes. For example, if your site server name is red1, type that, not \\red1. Type a user name that can access the SMS database and password. If you have used this database before, note that the server and user credential information is already present in the dialog box. This information is retrieved from and stored in the registry. When you are finished, click OK.

3. In the Named Queries dialog box, select the query Y2K All Compliant Software by System in This Site and Its Subsites. The statement underlying the query appears in the Query Statement box.

4. Click Run this Query.

5. The Y2K All Compliant Software by System in This Site and Its Subsites table is created and displayed in datasheet view. Note that the name is shortened because 64 characters is the maximum length of a name in Access 97.

6. From the New Object list on the toolbar, select Report. The New Report dialog box appears.

   

7. In the New Report dialog box, select AutoReport: Tabular. Then, in the Choose the table or query where the object's data comes from box, make sure that the Y2K All Compliant Software by System in This Site and Its Subsites query appears. Click OK.

   

   The report then appears in Layout Preview view. You might notice that data appears truncated in some of the columns, especially the URL column. The Access Report Wizard equalized the column width instead of allowing different widths to accommodate the data.

8. Press the Esc key, or on the Access menu, click View, then click Design View to place the report into Design view.

9. In Design view, select all the controls in the Detail section of your report.

10. Right-click your selections in the Detail section and select Properties. The Multiple Selection dialog box appears.

11. In the Multiple Selection dialog box, change the Can Grow property to Yes, and close the dialog box.

12. Next, select the title in the Report Header section of your report. Widen the title label so that the entire title is displayed. Edit the title if necessary.

13. Right-click the report and choose Layout Preview. If you are satisfied with the design, save the report.

You have just created a report based upon an existing SMS query by importing the query into the SMS Query Extract tool.

Systems with Missing RAM Report

Administrators often want to report on all systems where the amount of RAM has decreased. For the Systems with Missing RAM report, you create a new query in the SMS Administrator console and then import it into Access.

To create a Systems with Missing RAM report

1. Open an SMS Administrator console.

2. In the SMS console tree, navigate to and then right-click the Queries item. Select New, then Query. The Query Properties dialog box appears.

3. In the Query Properties dialog box, name the new report "Missing Memory." Then click Edit Query Language.

4. In the Missing Memory Query Statement Properties dialog box, click Show Query Language.

5. A single tab, Query Language, is displayed in the Missing Memory Query Statement Properties dialog box. Type in the following WQL statement into the Query pane:

SELECT DISTINCT ResourceDomainORWorkgroup, NetbiosName,
HM.TotalPhysicalMemory,
M.TotalPhysicalMemory
FROM SMS_R_SYSTEM S INNER JOIN SMS_G_System_X86_PC_MEMORY M ON S.ResourceID = M.ResourceID INNER JOIN SMS_GH_System_X86_PC_MEMORY HM ON M.ResourceID = HM.ResourceID WHERE HM.TotalPhysicalMemory > M.TotalPhysicalMemory

6. Click OK to save the new query. Click OK again to close the Query Properties dialog box.

7. Run the query to make sure it works by right-clicking the Missing Memory report and selecting Run Query. If you receive errors, check your query for misspellings in the query statement.

Now you are going to import your new query into the SMS Query Extract tool.

To import a query into the SMS Query Extract tool

1. In Access 97, load Smsextract.mdb.

2. Select the Missing Memory query to import it into a table.

3. After the query runs, you can print it in grid view directly from Access 97, or use the Access 97 Report Wizard to create a report.

This query lists all cases in which current memory is less than what was reported. However, as the following illustration shows, the exact amount of physical memory reported can vary by a few bytes, which might cause extra systems to appear in the report. Because both the current and the reported values are listed, you can easily determine which systems are missing large amounts of RAM. Alternately, the following steps demonstrate creating a query to filter out the less significant differences in RAM (such as the difference shown in the following figure).

To create a filter query

1. In Access 97, select the Missing Memory table you just created.

2. From the New Object list on the toolbar, select Query. The New Query dialog box is displayed.

3. Select Design View as shown below and click OK. Your query is displayed in Design view.

   

4. In the Design view window of your query, in the qryMissing Memory : Select Query dialog box that is shown after step 9, double-click the asterisk (*) in the Missing Memory table.

5. Double-click TotalPhysical Memory to place this field in the second column.

6. In the query, at the end of TotalPhysical Memory, type:

TotalPhysical Memory1.

7. Clear the Show check box so that the query results do not appear in your report.

8. In the Criteria box, type:

>1000.

9. Save the query as "qryMissing Memory." Remember that a query cannot have the same name as a table.

   Your filtering query should look like this:

   

You have just created a new SMS query, and then created an Access crosstab query to filter out less significant query results. You can now import the crosstab query into the SMS Query Extract tool, and create a report.

Operating Systems in Use by Site Report

For the Operating Systems in Use by Site report, create a new SMS query in the SMS Administrator console, then, as in the previous report, create an Access crosstab query. In this report, the crosstab query summarizes the results.

First you must create a new SMS query.

To create a new SMS query

1. Open the SMS Administrator console.

2. Navigate to the Queries console tree item, then right-click and select New, then Query. In the Query Properties dialog box, name this query Operating Systems in Use by Site.

3. Click Edit Query Statement.

4. In the Operating Systems in Use By Site Query Statement Properties dialog box, click Show Query Language.

5. Type in the following WQL statement:

SELECT OperatingSystemNameandVersion,SMSInstalledSites,Name FROM SMS_R_System zS WHERE (OperatingSystemNameandVersion IS NOT NULL) ORDER BY SMSInstalledSites, OperatingSystemNameandVersion, Name

6. Click OK to save the query.

7. In the Query Properties dialog box, click OK.

8. From the SMS Administrator's console, run the query to make sure it succeeds by right-clicking and selecting Run Query.

9. Run Access 97, and then load the Smsextract.mdb file. Log on at the SMS Login window of the tool.

10. In the Named Queries dialog box, select this new query and click Run This Query to import it into a table in the tool's database.

Next, you create a crosstab query (also known as a pivot table). This query, run against the table you just created, serves as the basis for your report.

To create a crosstab query

1. With the Operating Systems in Use by Site Table still open, from the New Object list on the toolbar, select Query. The New Query dialog box appears.

2. In the New Query dialog box, shown below, select Crosstab Query Wizard. Click OK.

   

3. In the Crosstab Query Wizard page, click Tables if it is not already selected, then select Operating Systems in Use by Site. Click Next.

4. In the next page of the wizard, choose OperatingSystemNameandVersion from the Available Fields box and click the > (greater than) button. Click Next.

5. For Column Headings, choose SMSInstalledSites, and then click Next.

6. In the next page of the wizard, for the Name field, choose the Count function. Click Next, and then click Finish.

7. The crosstab query you have just created appears in grid view. Notice that most of the columns are too wide. To adjust the column widths, select some columns. (Do not select all the columns, or this will not succeed.) On the Format menu, click Column Width, and then click Best Fit.

8. Save the query, and then create your report.

This query summarizes the number of clients by operating system for each site by creating a pivot table based on the results of your previous query.

Note: If clients are assigned to multiple sites, the grand total of clients returned by this query will be higher than the actual number of clients in your network.

For this report, you created a new SMS query using the SMS Administrator console, and then imported that query into a table in Access 97. You then created a crosstab query that displayed the information in a more useful manner.

All Status Messages over Time for a Single Advertisement Report

For the All Status Messages over Time for a Single Advertisement report, you create an area chart for all the messages related to an advertisement. This involves modifying the SMS Query Extract tool, which requires some familiarity with programming Access. Before altering the SMS Query Extract tool, remember to create a backup of the Smsextract.mdb file.

The main steps in creating this report include:

• Creating a lookup table.

• Modifying the SMS Query Extract tool by altering a form and a procedure by choosing a specific advertisement and then importing its messages.

• Importing a query.

• Creating a crosstab (or pivot) query.

• Creating a new Access report and adding a chart based on the query.

In the following diagram, the top three boxes represent SMS status classes joined in a query. The lower two boxes represent the lookup table that will be created and joined to the crosstab query that you already created.

Figure 2: Advertisement status schema

Creating a Lookup Table

First, you create a table of definitions for message identification numbers.

To create a Lookup Table

1. In Access 97, using a new or existing database, in the Access Database window click the Tables tab, and then create a new table by clicking New. In the New Table dialog box, click Design View.

   In Table Design view, create a new field that has the following properties:

   • Field Name = MessageID

   • Data Type = Number

   • Field Size = Long Integer

   Make this field the primary key of your new table by right-clicking the field and selecting Primary Key.

   Create another field and give it the following properties:

   • Field Name = SummarizedAs

   • Data Type = Text

   • Field Size = 20

2. Save this new table as tlkpMessages.

3. Type the values shown in the following table into the tlkpMessages table.

   Table 9 Status Message Ids

   Message ID	Summarized As
   10000	Failures
   10002	Received
   10003	Failures
   10004	Failures
   10005	Programs Started
   10006	Program Errors
   10007	Program Errors
   10008	Program Success
   10009	Program Success
   10018	Rejected
   10019	Rejected
   10021	Failures

4. Close the tlkpMessages table. This table will be a lookup table for message identification numbers.

Modifying SMS Extract Form

Next, you modify a form in the Smsextract.mdb file.

To modify the SMS Query Extract tool to import Advertisements

1. In Access 97, load Smsextract.mdb. In the Database Design view, select the Forms tab, then open frmConnect.

2. In the lower right corner of the form, select the option group, optListType.

3. Change the Visible property to Yes.

4. Save and close the form.

You can now select SMS Advertisements as well as SMS Queries. The SMS Advertisements option will be used later in this example.

Modifying a Procedure

Next, you change a portion of the code in one of the code modules of the SMS Query Extract tool.

To change SMS Query Extract tool code to accept Advertisements

1. If Smsextract.mdb is closed, open it. If the opening form is displayed in form view (awaiting your input), press F11 to go to Design view.

2. Open the module basQuery.

3. Go to the QryString function.

4. Modify case 3 of the Select statement to read as follows:

Case 3 'build a query for the selected AdvertisementID sQryString = "SELECT stat.Time, stat.MessageID, stat.MachineName, att.AttributeValue " sQryString = sQryString & "FROM SMS_StatusMessage stat " sQryString = sQryString & "INNER JOIN SMS_StatMsgAttributes att " sQryString = sQryString & "ON stat.RecordID = att.RecordID " sQryString = sQryString & "WHERE att.AttributeID = 401 " sQryString = sQryString & "AND att.AttributeValue = '" & sID & "'
Case 1, 4

5. Save and close basQuery.

You can now import all the status messages for a selected Advertisement. Remember that status messages are periodically summarized (every seven days, by default), and purged from the database, so plan your queries accordingly.

Importing Advertisement Status Messages

To import SMS Advertisement Status Messages

1. Open frmConnect in form view.

2. Type the site server name, user, and password as shown in the following illustration.

   

3. In the List Type box, select SMS Advertisements, and then click OK.

4. When the Advertisements list appears, select an advertisement.

5. Click Run This Query. A table with the same name as the advertisement you selected will be created.

The SMS Query Extract tool will import all the SMS status messages for the selected advertisement into a table of the same name.

Creating a New Access Crosstab Query

Next, you create a new crosstab query based on the status messages table you have just created.

To create a new crosstab query

1. With Smsextract.mdb still loaded, from the Access Database window, click the Queries tab. Click New to create a new query.

2. In the New Query dialog box, select Design View. The Show Table dialog box appears.

3. In the Show Table dialog box, make sure that the Tables tab is displayed.

4. Add the following two tables: tlkpMessages and the advertisements table you created in step 5 of the previous procedure. Click Close.

5. Change the query to a crosstab query.

6. Design your crosstab query as shown in the following illustration, using your own advertisements table in place of the table shown (a1).

   

7. Run the query. Here is a sample result, with dates along the top.

   Table 10 Sample Query Results

   Summarized As	02/01	02/02	02/03	02/04	02/05	02/06	02/07	02/08
   Program Errors	3	0	1	2	3	3	1	1
   Program Success	2	6	7	3	8	3	2	1
   Programs Started	7	8	7	3	4	7	11	1
   Received	8	15	11	18	6	9	11	2
   Rejected	1	3	2	1	1	0	2	0

8. Save the new query as qryXtab_a1 (substitute your advertisement name for a1).

Creating a New Access Report

Next, you create a report based upon the crosstab query you just created.

To create your report

1. In Access 97, select the Access Database window or press F11.

2. Click the Reports tab, and then click New. The New Report dialog box appears.

3. In the New Report dialog box, select Chart Wizard, and then select the crosstab query you just created, as shown below. Click OK.

   

4. The Report Wizard then displays a dialog box in which you can choose up to six fields for your report. Select the fields and when you are done, click Next.

5. Now you can select a chart type. For this example, choose the Area chart, and then click Finish.

6. Press Esc to view the chart in Design view.

7. Select the chart object, and make it as wide as the page and 3 inches high.

8. Open the crosstab query you just created, then select all the rows and some of the columns and copy to the clipboard. Close the query.

9. Right-click on the chart, select Chart Object, and then click Edit.

10. From within Microsoft Graph, on the Data menu, choose Series in Rows.

11. To fill the datasheet with your sample data, select the upper left corner cell of the datasheet, and paste in the query results from the clipboard.

12. Make the chart object almost as large as it is in the report.

13. From the Chart menu, click Chart Type. In the Chart Type dialog box shown below, select the Custom Types tab.

    

14. Select B&W Area as the chart type, and then click OK. (This choice might give better results on laser printers than a color chart would give.)

15. Select each data series, right-click Format Data Series, and then select the Label tab. Then select Show Label.

16. Close Microsoft Graph by choosing File, and then click Exit & Return to Report.

17. Make the chart object the width of the report.

18. Preview your report. Save it with the name of the advertisement.

You have just created a report that charts all of the status messages for a single advertisement. To update it, just reimport the advertisement data.

The procedure for creating reports with different advertisements is the same. Because there could be a very large volume of data for each advertisement, a new report is created for each advertisement.

Rate of Installation for All Advertisements Report

For the Rate of Installation for All Advertisements report, you modify the Advertisement Summary report by adding a line graph to show the rate at which software distribution is succeeding over time. The main steps in this report are listed here:

• Creating a query in the SMS Administrator console.

• Importing the status messages for programs run successfully.

• Creating a totals query.

• Creating a running totals query.

• Modifying an existing report by adding a graph based on the query.

• Linking the graph by AdvertisementID to the report.

Creating a Query in the SMS Administrator Console

To create a query

1. Open the SMS Administrator console.

2. Right-click the Queries console tree item. Select New Query.

3. In the Query Properties dialog box, name the new query AdvertisementSuccesses. Then click Edit Query Statement.

4. In the AdvertisementSuccesses Query Statement Properties dialog box, click Show Query language.

5. Place the following code in the query:

SELECT stat.Time, stat.MachineName, ad.AdvertisementID FROM SMS_StatusMessage stat INNER JOIN SMS_StatMsgAttributes att ON stat.RecordID = att.RecordID INNER JOIN SMS_Advertisement ad ON att.AttributeValue = ad.AdvertisementID WHERE (stat.MessageID=10008 OR stat.MessageID=10009)

6. Click OK. The Query Properties dialog box reappears.

7. From the Query Properties dialog box, click OK to save the query.

Creating a Totals Query

To create a Totals query

1. Run Access 97 and load Smsextract.mdb.

2. In the Named Queries dialog box, load _AdvertisementSuccesses.

3. Minimize the _AdvertisementSuccesses table, and then on the Window menu, click Unhide. Select smsextract:Database from the list of hidden windows.

4. In the Access Database window, on the Insert menu, click Query. The New Query dialog box appears.

5. From the New Query dialog box, select Design View, and then click OK.

6. On the View menu, click Totals.

7. Set up the columns exactly as shown below:

   

   In the first column, the Format function extracts the date from combined date and time values.

8. In the third column, change the default value of Group By to Count as shown above.

9. Save the query as qryAdvertisementSuccess, and then close it.

Creating a Running Totals Query

In this section, you create a new query that counts the number of computers that have received an advertisement.

To create a Running Totals query

1. In the Access Database window, on the Insert menu, click Query.

2. In the New Query dialog box, select Design View, and then click OK.

3. In the Query Design window, the upper pane displays the query qryAdvertisementSuccesses. From the Database window, drag qryAdvertisementSuccesses to the Query Design window. The same query will then be displayed twice (refer to the illustration below). Access 97 automatically adds a "_1" suffix to the title bar of the second query to distinguish it from the first query (which is actually the same query).

4. Join the two queries by Date and AdvertisementID (drag the Date field from one query to the next, creating a self-join).

5. From the View menu, click Totals.

6. In the lower pane of the Query Design window, illustrated below, set up the query as shown. In the first (left) column, place the Date field from the original query. In the other two fields, place the AdvertisementID and CountofMachineName fields from the duplicate query qryAdvertisementSuccesses_1.

   

If you run the query now, you will obtain a count for each date, similar to the data shown below.

Date	AdvertisementID	SumOfCountOfMachineName
02/01/1999	ONE20000	2
02/02/1999	ONE20000	6
02/03/1999	ONE20000	5
02/04/1999	ONE20000	3
02/05/1999	ONE20000	8
02/06/1999	ONE20000	3
02/07/1999	ONE20000	2
02/08/1999	ONE20000	1

But, to obtain a running count, the SQL statement that forms the query has to be changed.

To create a running total

1. Right-click the upper pane of the Query Design window, and select SQL View.

2. Change the code evaluating the Date field (in the section of code following the AND keyword) by changing the evaluation from "equal to" to "greater than or equal to."

   Here is the resulting SQL statement, with the line of code where you will make the change shown in bold print:

SELECT qryAdvertisementSuccesses.Date, qryAdvertisementSuccesses_1.AdvertisementID, Sum(qryAdvertisementSuccesses_1.CountOfMachineName) AS SumOfCountOfMachineName FROM qryAdvertisementSuccesses INNER JOIN qryAdvertisementSuccesses AS qryAdvertisementSuccesses_1 ON (qryAdvertisementSuccesses.AdvertisementID = qryAdvertisementSuccesses_1.AdvertisementID) AND (qryAdvertisementSuccesses.Date >= qryAdvertisementSuccesses_1.Date) GROUP BY qryAdvertisementSuccesses.Date, qryAdvertisementSuccesses_1.AdvertisementID;

Note that, after this change, the join cannot be represented in Design view of your query, and the query can only be modified in SQL view.

3. Save the query and run it. Your results will differ from your previous results as shown below.

   Date	AdvertisementID	SumOfCountOfMachineName
   02/01/1999	ONE20000	2
   02/02/1999	ONE20000	8
   02/03/1999	ONE20000	13
   02/04/1999	ONE20000	16
   02/05/1999	ONE20000	24
   02/06/1999	ONE20000	27
   02/07/1999	ONE20000	29
   02/08/1999	ONE20000	30

4. Save the query as qryAdvertisementSuccess2. Now the report gives a running total.

You have just created a report about the installation progress of advertisements at your site.

Configuration and Performance

Optimizing the performance of your queries is always a consideration when reporting. This section discusses various tips:

• Optimizing WBEM ODBC Queries

• Tips for Crystal Info for SMS

• Troubleshooting Crystal Reports

• Using Wbemtest

Optimizing WBEM ODBC Queries

This section discusses various strategies for optimizing your queries when using the WBEM ODBC driver.

Writing Efficient Queries

To write efficient queries, you must know where there are bottlenecks and inefficiencies in the tools you are using. Keep the following in mind:

• To avoid a large number of NULLS in your return set, include a WHERE <column name> IS NOT NULL clause in your SQL statement.

• Avoid using the LIKE keyword, which some providers do not support.

• Select only needed properties (columns) when you query. Selecting more properties than you need to report on is inefficient. Make queries very selective to return the minimum number of rows possible.

• Use only as many joins as required for your query.

You should make your queries selective and use as few joins as possible because, when you query against the WBEM classes that define the SMS data, those classes must be converted into individual tables. Because the classes in your query might be translated into many tables, you might find that a seemingly simple SQL statement takes a long time to run and returns a very large number of rows. At times, the number of tables that must be created and joined exceeds the number of joins permitted by SQL Server.

For instance, consider the following WQL statement:

SELECT * from sms_r_system

This simple statement, from one class, translates into the following SQL statement:

SELECT  all
convert(varchar(50),sms_r_system.ItemKey),
sms_r_systemAgents.AgentName,
sms_r_systemDiscItemAgents.AgentSite,
sms_r_systemDiscItemAgents.AgentTime,
sms_r_system.Client0,
sms_r_system.Client_Version0,
__ystemSystem_IP_Address_ARR0.IP_Addresses0,
__ystemSystem_IP_Subnets_ARR1.IP_Subnets0,
__ystemSystem_IPX_Addres_ARR2.IPX_Addresses0,
__em_IPX_Network_Numbers_ARR3.IPX_Network_Numbers0,
sms_r_system.User_Domain0,
sms_r_system.User_Name0,
__ystemSystem_MAC_Addres_ARR4.MAC_Addresses0,
sms_r_system.Name0,
sms_r_system.Name_Context0,
sms_r_system.Netbios_Name0,
sms_r_system.Operating_System_Name_and0,
sms_r_system.Preferred_Server0,
sms_r_system.Preferred_Tree0,
sms_r_system.Resource_Domain_OR_Workgr0,
sms_r_system.ItemKey,
__ystemSystem_Resource_N_ARR5.Resource_Names0,
sms_r_system.DiscArchKey,
__ystemSystem_SMS_Assign_ARR6.SMS_Assigned_Sites0,
__ystemSystem_SMS_Instal_ARR7.SMS_Installed_Sites0,
sms_r_system.SMS_Unique_Identifier0,
sms_r_system.Community_Name0,
__ystemSystem_System_Rol_ARR8.System_Roles0 
FROM System_DISC AS sms_r_system 
LEFT OUTER JOIN DiscItemAgents AS sms_r_systemDiscItemAgents 
ON sms_r_system.ItemKey = sms_r_systemDiscItemAgents.ItemKey  
LEFT OUTER JOIN Agents AS sms_r_systemAgents 
ON sms_r_systemAgents.AgentID = sms_r_systemDiscItemAgents.AgentID  
LEFT OUTER JOIN System_IP_Address_ARR AS __ystemSystem_IP_Address_ARR0 
ON sms_r_system.ItemKey = __ystemSystem_IP_Address_ARR0.ItemKey  
LEFT OUTER JOIN System_IP_Subnets_ARR AS __ystemSystem_IP_Subnets_ARR1 
ON sms_r_system.ItemKey = __ystemSystem_IP_Subnets_ARR1.ItemKey  
LEFT OUTER JOIN System_IPX_Addres_ARR AS __ystemSystem_IPX_Addres_ARR2 
ON sms_r_system.ItemKey = __ystemSystem_IPX_Addres_ARR2.ItemKey  
LEFT OUTER JOIN System_IPX_Network_Numbers_ARR AS __em_IPX_Network_Numbers_ARR3
ON sms_r_system.ItemKey = __em_IPX_Network_Numbers_ARR3.ItemKey  
LEFT OUTER JOIN System_MAC_Addres_ARR AS __ystemSystem_MAC_Addres_ARR4 
ON sms_r_system.ItemKey = __ystemSystem_MAC_Addres_ARR4.ItemKey  
LEFT OUTER JOIN System_Resource_N_ARR AS __ystemSystem_Resource_N_ARR5 
ON sms_r_system.ItemKey = __ystemSystem_Resource_N_ARR5.ItemKey  
LEFT OUTER JOIN System_SMS_Assign_ARR AS __ystemSystem_SMS_Assign_ARR6 
ON sms_r_system.ItemKey = __ystemSystem_SMS_Assign_ARR6.ItemKey  
LEFT OUTER JOIN System_SMS_Instal_ARR AS __ystemSystem_SMS_Instal_ARR7 
ON sms_r_system.ItemKey = __ystemSystem_SMS_Instal_ARR7.ItemKey  
LEFT OUTER JOIN System_System_Rol_ARR AS __ystemSystem_System_Rol_ARR8 
ON sms_r_system.ItemKey = __ystemSystem_System_Rol_ARR8.ItemKey   
ORDER BY sms_r_system.ItemKey

This example was taken from the Smsprov.log file.

Use Passthroughonly

You might prefer to use the Passthroughonly setting for all your queries. The Passthroughonly setting should be used to get the best possible performance by directly passing your SQL statement to the SMS Provider. When developing your queries, you might prefer to know early in the process that you have an unsupported query (for example, one dueto syntax errors), rather than to wait while the driver attempts to reprocess your query.

The Passthroughonly option disables any attempt by the WBEM ODBC driver to process the query if the query is rejected. You cannot use this option if you want the WBEM ODBC driver to process the query, as you might, for instance, if you wanted to use a special ODBC function such as TIMESTAMPDIFF.

Tips for Crystal Info for SMS

The reporting and troubleshooting tips below describe methods of optimizing reports that you create using Crystal Info for SMS.

Quotes

Use double quotes, not single, for criteria in the Report Designer's SQL Query dialog box. Double quotes are for column/property names.

Dates

It is easy to specify date criteria in Crystal Info for SMS. Unfortunately, due to language incompatibilities, these criteria cannot be evaluated on the SMS site database server, which results in much longer processing times.

You can improve performance by ensuring that dates are evaluated in the WBEM ODBC driver instead of the Crystal Reports engine. To attain this improvement, use the ODBC date functions. As an example, the following query takes today's date, subtracts the Agentdate (which is converted from datetime to date with the Convert function), and finds a match if the difference is 1. (In other words, the date found has to be yesterday's date). This query is used in the New Systems Discovered report (filename: Cf_05.rpt) distributed with SMS.

SELECT
    zS."AgentName", zS."AgentSite", zS."AgentTime", zS."IPAddresses",
        zS."IPSubnets", zS."NetbiosName"
FROM
    "SMS_R_System" zS
WHERE
    {fn TIMESTAMPDIFF(SQL_TSI_DAY, 
    {fn Convert(zs."AgentTime", SQL_DATE)},
    {fn curdate()}) } = 1
ORDER BY
    zS."IPSubnets" ASC,
    zS."NetbiosName" ASC,
    zS."AgentTime" ASC

Note that in this case, you must not specify Passthroughonly mode for this query. It cannot be a Passthrough query, because WQL does not support the function.

Special Report Designer Settings

Set the following options before you create any reports

To set the Report Designer options

1. In the Report Designer, on the File menu, click Options.

   

2. Select Convert NULL field value to default. You cannot exclude null values without selecting this check box.

3. Select Keep Date-Time type. If you are only getting dates when you expect to get date and time, make sure this check box is selected.

You can also set the report options for an existing report on the File menu by clicking Report Options, then making the suggested changes (see illustration below):

Troubleshooting Crystal Reports

No Data is Returned in Your Report

Discovery data might not exist in the SMS site database. Refer to the SMS2.0 Administrator's Guide for information about resource discovery.

Report Properties Are the Wrong Datatype, or Reports Do Not Perform Sorting and Grouping Properly.

Check the datetime stamp of the WBEM ODBC driver you are using. It should be dated August 1998 or later.

A User Does Not Have Sufficient Access Rights to Run a Report Against a Given Database.

The user account running the report must have the Act as part of operating system advanced user right to impersonate an account that can access the data. To grant this right to a user or user group, run User Manager for Domains.

A Report Fails When It Runs Against a Different SMS Site Database.

If you have received a report from another site, you must first run the Set Location tool to specify the correct database for Crystal Info to report against.

Running Set Location Fails.

Determine whether the user account you are using when you run this tool has access permissions to the report folder. Navigate to \SMS\Cinfo\Samples\SMS and verify that you have access.

A Remote SMS Administrator Console User Receives an Error Message When Clicking the Reports Console Tree Item.

If you install a remote SMS Administrator console on a computer running Windows NT Workstation 4.0 while you are logged on as a domain administrator, the Info APS service will not start automatically. Also, an error message will be generated when a user attempts to access the Reports console tree item.

To correct this problem, on the remote computer, double-click the Services icon in Control Panel. In the list of services shown, select Info APS and then click Startup. Change the Startup Type of this service to Automatic and then click OK. Restart the remote SMS Administrator console.

A Report Takes an Unusually Long Time to Run.

If you use aliases for the WBEM class tables, the aliases cannot be SQL Server-reserved words, or a report based on these aliases might take significantly longer to complete. To correct this problem, modify any alias that is also a SQL Server-reserved word. Refer to the SQL Server documentation for a list of words reserved by SQL Server.

While Creating or Modifying a Report, Connecting to the SMS Namespace Fails.

If the account you are using does not have permission to access the SMS database, your account must have the Act as part of operating system advanced user right to impersonate an account that the report can run under. Alternatively, simply add your user account to the SMS Administrators user group.

Using Wbemtest

To use Wbemtest to run a query

1. Run Wbemtest.

2. Click Connect. The Connect dialog box appears.

3. In the Connect dialog box, fill in the namespace and credentials. Click Login.

   

4. In the Web-based Enterprise Management Common Information Model Object Manage dialog box that appears, click Query.

   

5. In the dialog box that next appears, type a WQL query.

6. Click Apply.

Even correct syntax can result in a "Generic Error" message.

To see the full error message

1. From the error message, click More Information.

2. On the dialog box that appears, click Show MOF.

3. Look at the MOF to see the error description. For example, SQL Server might have run out of TempDB space.

4. Fix the problem and try the query again. In the example in step 3, expand TempDB, and try the query again.

Adding WMI Scripting to Your Application

This section describes how to add the functionality of the WMI Scripting API to your application. The example shown uses VBScript and requires the Windows Script Host (WSH), although the code can be placed into a Visual Basic or Visual Basic for Applications code module, or even placed into a Web page. As mentioned, several tools are suitable for calling the WMI Scripting API.

The following code uses weak (or late) binding, as demonstrated by the commenting out of the As (Type) portion of the Dim statements. The WSH doesn't allow typing of variables — all objects are contained within a Variant. The additional As Type segment of the Dim statements are present in the code shown below to make it easier for you to place this code in a Visual Basic or Visual Basic for Applications code module, which does allow strong or early typing of variables.

Before running the following code, install WMI on your system, and then install the Windows Script Host (WSH). You can obtain WSH from

https://msdn.microsoft.com/scripting

If you are developing Visual Basic applications that access SMS data, after you install WMI, in the Visual Basic Tools menu, select References, and then select the Microsoft WBEM Scripting Library.

In the following code examples, comments in the code are shown in italics for readability.

To add WMI scripting to your application

1. Declare the WMI global objects used to communicate to the SMS Provider.

Option Explicit On Error Resume Next Dim oLocator ' as SWbemLocator Dim oServices ' as SWbemServices Dim eInstances ' as SWbemObjectSet Dim oInstance ' as SWbemObject

2. Set up the connection to the SMS Provider by creating the locator objects required to connect to the WMI namespace.

Set oLocator = CreateObject("WbemScripting.SWbemLocator") ' New SWbemLocator If Err.Number <> 0 Then MsgBox "Windows Management (WMI) is not installed on this computer." & _ vbCrLf & Err.Description WScript.Quit(0) End If

3. Ask the user for the name of the site server. You can also get this from the registry:

HKEY_LOCAL_MACHINE \SOFTWARE \Microsoft \SMS \AdminUI\Connection\Server Dim sSiteServer ' as String sSiteServer = InputBox("Enter name of site server") If sSiteServer = vbNullString Then WScript.Quit(0)

4. Connect to the SMS site server.

Set oServices = oLocator.ConnectServer(sSiteServer, "root\sms") If Err.Number <> 0 Then MsgBox "Could not connect to SMS Site Server on: \" & sSiteServer & _ vbCrLf & Err.Description WScript.Quit(0) End If

5. Ask the site server for the name of the SMS Provider. (The flag of 0 at the end of the first statement below means "return when complete" (the WMI API call wbemFlagReturnWhenComplete)

Set eInstances = oServices.ExecQuery("select Machine, SiteCode from SMS_ProviderLocation where ProviderForLocalSite=True",, 0) If Err.Number <> 0 Then MsgBox "Could not find SMS Provider on SMS Site Server: \" & _ sSiteServer & vbCrLf & Err.Description WScript.Quit(0) End If Dim sSiteCode ' as String Dim sProvMachine ' as String ' There should be only one of these instances For Each oInstance In eInstances sSiteCode = oInstance.SiteCode sProvMachine = oInstance.Machine Exit For Next

6. Connect to the SMS Provider.

Set oServices = oLocator.ConnectServer(sProvMachine, "root\sms\site_" & sSiteCode) If Err.Number <> 0 Then MsgBox "Could not connect to SMS Provider on: \" & sProvMachine & vbCrLf & Err.Description WScript.Quit(0) End If

7. Set the security impersonation level.

oServices.Security_.ImpersonationLevel = 3

8. You are finished with the WMI Locator now, so set the variable to Nothing.

Set oLocator = Nothing

9. Now run some queries.

' List all collections, exclude ones that are in the process of being deleted ' Use flag 48 (wbemFlagReturnImmediately or'd with wbemFlagForwardOnly) to conserve memory on the server ' This is particularly important when a query might return a large number (>1000) of instances. Set eInstances = oServices.ExecQuery("select CollectionID, Name from SMS_Collection where CurrentStatus != 6",, 48) Dim sMessage ' as String sMessage = "Collection ID" & vbTab & "Name" & vbCrLf & vbCrLf For Each oInstance in eInstances sMessage = sMessage & vbCrLf & oInstance.CollectionID & vbTab & _ oInstance.Name Next If Err.Number <> 0 Then MsgBox "Could not execute the query." & vbCrLf & Err.Description Else MsgBox sMessage,, "Collections on site " & sSiteCode End If

Note: Type each line of code as one complete line. Do not break the code onto a second line, as it appears in this text, which is formatted for readability in this document.

For information about the flags used in the above code example, refer to the WMI SDK documentation.