Reports

[Previous] [Next]

SMS 2.0 includes an installation option called Crystal Reports which is an SMS 2.0 snap-in provided by Seagate Corporation. The version supplied with SMS 2.0 is Crystal Info 6.0. Crystal Info is a useful reporting tool that can generate a variety of reports based on information stored in the SMS database such as inventory, product compliance, and system status. Because Crystal Info is integrated as a Microsoft Management Console (MMC) snap-in, it is fully integrated into the SMS Administrator Console. In addition to running the default reports provided with Crystal Info, you can create your own reports or customize the defaults. The reports can be scheduled to run at a particular time or even on a recurring basis. Report types include the following:

• Standard chart
• Form letter
• Form
• Cross-tab
• Subreport
• Mail label
• Drill-down
• Online Analytical Processing (OLAP)

When you choose Crystal Info as an option during setup, SMS installs several default reports. These reports can be found in the Reports folder under Tools in the SMS Administrator Console. The default reports are broken into five categories: Configuration, Hardware Inventory, Product Compliance, Queries, and Status. Each category includes one or more reports that you can run or customize. When you select one of these folders, the reports it contains will be listed in the right pane, preceded by one of three icons, shown in Figure 15-17.

Figure 15-17. Sample reports available in the Product Compliance folder.

The green check mark (an OK indicator) shows that the report is ready to view, meaning that it has run successfully. The yellow triangle with an exclamation point (a Warning indicator) shows that the report is currently unscheduled and so has not yet been run. A red "x" (a Critical indicator) shows that the report experienced a failure and did not run properly. Additional information about the status of each report can be obtained by right-clicking on the report and choosing Properties from the context menu to display the Report Properties window.

Running a Crystal Info Report

Running a Crystal Info report is relatively straightforward and involves telling SMS when the report should run and under what security context. The administrators who will run Crystal Info reports must have access to the WMI data classes used by the SMS Provider, which itself accesses the SMS database. Remember, at no time do you ever query the SMS database directly—you always go through the WMI via the SMS Provider.

To create reports, your user account could be a member of the SMS Admins group, which of course does have appropriate access. If you do not want to add your user account to this group, you must specify an account that does have appropriate access permission. Accessing resources through another user account is known as "impersonation." However, for impersonation to work under Windows NT, your user account must have the Act As Part Of The Operating System user right assigned at your Windows NT workstation.

To run a Crystal Info report through the SMS Administrator Console, follow these steps:

1. In the SMS Administrator Console, navigate to the Reports folder under Tools and expand it.
2. Select the report you want to run, right-click on it, choose All Tasks from the context menu, and then choose Schedule Report to display the Schedule Report Properties window for that report, shown in Figure 15-18.
3. On the General tab, click Refresh to display the report filename in the Description text box. Click Modify if you need to change the appearance of the displayed report. The Modify button launches the Info Report Designer, which we will discuss in the section "Creating a Report" later in this chapter.
4. Click on the Schedule tab, shown in Figure 15-19. On this tab, you specify when the report should be run. You can have it run immediately or at a defined time, and you can also set a recurrence pattern.



Figure 15-18. The Schedule Report Properties window.



Figure 15-19. The Schedule tab.

5. Click on the Accounts tab, shown in Figure 15-20. The name of the user who is logged in at the workstation is displayed in the Logon User Name text box. If this account has appropriate rights to run the report, enter the logon password. If the account does not have appropriate rights, enter the name of an account that does and supply the appropriate password. The Server and Database entries are read-only and can be modified only when you are modifying an existing report or creating a new report.



Figure 15-20. The Accounts tab.

6. Click on the Selection Formula tab, shown in Figure 15-21. On this tab, you can either view or modify the query statement used to generate the report and display data. Click the Edit Selection Formula button if you want to modify the query statement.
7. Click on the Prompts tab, shown in Figure 15-22. On this tab, you can specify the parameters a user must supply before the report can run. Like using prompted queries, this technique provides a more useful report format.
8. Click on OK to save your settings.



Figure 15-21. The Selection Formula tab.



Figure 15-22. The Prompts tab.

The report will run at the scheduled time. To view the report, select the report entry under its report folder. The report is initially displayed in the right pane of the SMS Administrator Console. Viewing of this report (as shown in Figure 15-23) was facilitated by suppressing the view of the console tree. By resizing the screen, changing the zoom percentage, and using the scroll bars, you can view various parts of the report. You can also print the report if you want.

Figure 15-23. Sample report generated using Crystal Info.

TIP

---

To generate a Crystal Info report, you must have installed a printer driver on your workstation. The reports display much better—and print better too—if you install a high-resolution print driver such as a PostScript printer.

Creating a Report

The three main tasks involved in creating a new Crystal Info report are as follows:

• Creating the new report
• Creating a report folder if no appropriate folder exists in the SMS Administrator Console
• Adding the report to the folder

Unlike SMS queries, to create a Crystal Info report you need a more extensive knowledge of the WMI object classes that compose the SMS database. To create a new report, you should start the Info Report Designer, which enables you to control the layout, data, and style of the report. While the Info Report Designer is a point-and-click interface, you must still select items from lists of classes and attributes. (Appendix E of the Systems Management Server Administrator's Guide will prove useful for you in this regard, as will the Microsoft Systems Management Server Resource Guide, part of the Microsoft BackOffice 4.5 Resource Kit.)

To create a report, follow these steps:

1. In the SMS Administrator Console, navigate to any report in the Reports folder, right-click on the report, choose All Tasks from the context menu, and then choose Design New Report to launch the Info Report Designer. The Info Report Designer welcome dialog box will display, as shown in Figure 15-24.



Figure 15-24. The Info Report Designer welcome dialog box.

2. Click New Report to create a new report (or click Open Report to modify an existing report). The Report Gallery dialog box will appear, as shown in Figure 15-25.



Figure 15-25. The Report Gallery dialog box.

3. In the Report Gallery dialog box, select the type of report you want to generate. The Create Report Expert opens to guide you through the process of laying out the report, as shown in Figure 15-26.



Figure 15-26. The Create Report Expert window.

4. On the Data tab, specify a data source by clicking SQL/ODBC to display the Log On Server dialog box, shown in Figure 15-27. In this dialog box, select ODBC-WBEM Source as the data source and click OK.



Figure 15-27. The Log On Server dialog box.

5. In the Configure Connection dialog box (shown in Figure 15-28), which appears automatically after you've selected the data source from the Log On Server dialog box, enter the name of the user account that has access to the data (as described in the previous section). Enter or browse for the SMS database server name, and click Connect. If the connection is successful, the Namespace Selection box on this screen will display a tree structure that begins with the entry Root. Expand this structure until you find your site-code entry, and then select it.



Figure 15-28. The Configure Connection dialog box.

6. Click OK. If the logon is successful, the Choose SQL Table dialog box is displayed, as shown in Figure 15-29.



Figure 15-29. The Choose SQL Table dialog box.

7. Select each table you want to use for your report, and then click Add. When you have finished, click Done to return to the Create Report Expert window.
8. The Create Report Expert now displays the Links tab, shown in Figure 15-30. On this tab, the fields that are common to the tables you selected are displayed as links. Select the link that is most appropriate for your report, such as Resource ID, and delete the others by selecting each link, and then clicking Delete.



Figure 15-30. The Links tab.

9. After selecting the link most appropriate for your report, click Options to display the Link Options dialog box, shown in Figure 15-31. Choose the type of join you want to create between the tables you selected. (Crystal Info help describes these joins in detail, including examples.) These joins are somewhat different from those involved with query creation. A Left Outer join is the recommended option for most reports. It returns all records in which the linked value in both tables is an exact match, as well as a separate row for every entry that does not have an exact match. Click OK to return to the Links tab.
10. Click on the Fields tab or click Next to display the Fields tab, shown in Figure 15-32, and select the fields you want to display when the report is run.
11. Click on the remaining tabs—Sort, Total, TopN, Graph, Select, and Style—to complete any additional settings for your report. You can use these options to sort, sum, or filter the data that is displayed; choose a graph style and layout style; and preview the report layout. The Style tab is shown in Figure 15-33.
12. Click Preview Report to see what your report will look like. Make any changes you want by choosing Format from the Info Report Designer menu, and then choosing Report Style Expert. You can also use the preview screen to modify how the report will look—by changing headers, removing totals, and so on.

Save the report template you just created by clicking Preview Report, File, and then choosing Save from the Info Report Design menu. By default, your report will be saved in the \SMS\CInfo\Winnt folder on the site server.

Figure 15-31. The Link Options dialog box.

Figure 15-32. The Fields tab.

Figure 15-33. The Style tab.

Creating a Report Folder

Your new report needs to be added to a folder under Reports in the SMS Administrator Console. You can create a report folder to organize your reports. To create a new folder, follow these steps:

1. In the SMS Administrator Console, navigate to the Reports folder, right-click on it, choose New from the context menu, and then choose Folder to display the Seagate Crystal Info dialog box, shown in Figure 15-34.
2. Enter a name for your new folder, and then click OK. The new folder will be displayed under Reports.



Figure 15-34. The Seagate Crystal Info dialog box.

Adding a Report to the Folder

To add your report to the new folder or to an existing folder, follow these steps:

1. In the SMS Administrator Console, navigate to the folder to which you want to add the report you created or modified, right-click on it, choose New from the context menu, and then choose Report Object to display the New Report Object Properties window, shown in Figure 15-35.



Figure 15-35. The New Report Object Properties window

2. In the Report File text box, enter the full path to the report you want to add.
3. Enter a descriptive title for the report as well as any additional comments, and then click OK.

NOTE

---

Remember to close the report in the Info Report Designer before you add it to a report folder; otherwise a sharing violation will occur.

Modifying Reports

To modify an existing report, follow these steps:

1. Right-click on the report, choose All Tasks from the context menu, and then choose Modify Report to launch the Info Report Designer tool. The report will be loaded.

You must connect to the WBEM data source server before you make any changes to the report data. For details on how to connect to the WBEM data source server, refer to the section "Creating a Report" earlier in this chapter.

2. Make whatever modifications you want. Before saving the report, choose Options from the File menu to display the File Options Properties window. On the Reporting tab, shown in Figure 15-36, verify that Save Data With Report is not selected, and then save the report.



Figure 15-36. The Reporting tab of the File Options Properties window.

3. In the SMS Administrator Console, right-click on the report and choose All Tasks from the context menu, and then choose Schedule Report to display the Schedule Report Properties window. On the General tab, click Refresh to update the report with your changes.
4. Schedule and run the report to generate a new report with the design changes you implemented.

MORE INFO

---

Refer to the Systems Management Server Administrator's Guide and the online help for the Info Report Designer for more details on creating, modifying, and updating reports.

Other Reporting Tools

You can use any reporting tool you want to extract information from the SMS database. The most important requirement is that your tool be WBEM ODBC-compliant—in other words, the reporting tool must use the WBEM ODBC driver installed with the SMS Administrator Console to access the SMS database via the SMS Provider and WMI. Microsoft Access 97 and later is one such tool; another is Microsoft Excel 97 and later using the Query Extract tool included in the Microsoft BackOffice 4.5 Resource Kit. Let's look first at the Excel 97 Query Extract tool as an alternative way of creating and generating reports.

Microsoft Excel 97 Query Extract Tool

As mentioned, the Query Extract tool is provided as part of the Microsoft BackOffice 4.5 Resource Kit utilities. This tool consists of an Excel workbook and template that contain macros designed to use the WMI Scripting API to access the database via the SMS Provider. You provide the name of the site server and a valid access account, and Query Extract in turn finds the SMS database server. Here's the really neat part: Query Extract displays for you a list of all the current SMS queries to choose from so you don't have to worry about learning a query language. You select the query or queries that you want to execute. The results of each query are placed on a separate workbook page. You are then free to analyze this data as you see fit. You can create charts, pivot tables, or export the data to another database or application format such as Microsoft PowerPoint.

As mentioned, Excel contains both a workbook version and a template version of this tool. If you import the workbook version, it functions as a stand-alone file—meaning that when you import SMS data into this tool and save the file, you need to import another Query Extract worksheet for the next set of reports you want to create. However, if you use the template version to generate a new report, you can simply open a new workbook based on the template, without the need to import another Query Extract worksheet.

The Query Extract tool workbook and template files are named SMSExtract.xls and SMSExtract.xlt. When the Microsoft BackOffice 4.5 Resource Kit utilities are installed, these files can be found under the Resource Kit\SMS\Report directory. Copy the two files into the appropriate folder on your SMS Administrator Console computer—for example, C:\Program Files\Microsoft Office\Templates.

Let's walk through the basic steps for using the Query Extract tool with Excel 97:

1. Start Excel, and either open the SMSExtract.xls workbook file, or create a new workbook using the SMSExtract.xlt template.
2. Depending on how your installation of Excel handles macros, you may see a message asking whether you want to enable macros. Click the Enable Macros button to initiate the tool.
3. The SMS Login dialog box appears, as shown in Figure 15-37. Enter the name of the site server in the Server text box, enter the name and password of a user account that has access to the database, and then click OK. These values will be saved in the Windows NT registry and will be displayed automatically the next time you run the tool.
4. In the Select Site Query To Import dialog box that appears automatically (Figure 15-38), you will see all the currently available SMS queries. Check the query you want to run. You can select two or more queries to import by holding down the Ctrl key and clicking on the queries.



Figure 15-37. The SMS Login dialog box.

5. Click OK. If the query requires user input, a dialog box is displayed asking you to enter the appropriate value. Close the dialog box after entering the value.
6. The results of each query will be displayed on a separate workbook page, as shown in Figure 15-39.
7. Save the workbook, and work with the data as you please.

NOTE

---

A version of this tool, named SMSQuery.xlt, is included with SMS 2.0 and can be found in the Support\Reskit\Bin\platform\Diagnose folder on the SMS 2.0 CD or in the same folder when you install the Supportability Tools with SMS 2.0 Service Pack 1. Aside from some formatting differences, this version works the same way as the Resource Kit tool; however, it does not support prompted queries.

Figure 15-38. The Select Site Query To Import dialog box.



Figure 15-39. A sample results workbook page.

Microsoft Access

Using Access to extract SMS database information requires that the WBEM ODBC driver first be installed on the Access workstation. If you installed the SMS Administrator Console on this computer, you have already installed this driver by default. If not, you can install it from the SMS 2.0 CD or from the SMS_SiteCode share (the \SMS folder) on the primary site server. On the CD, it can be found in \SMSSetup\Bin\platform\Wbemsdk.exe; on the site server, it can be found in \SMS\bin\platform\Wbemsdk.exe. When you launch this program, a simple wizard is displayed that will walk you through the installation. You will need to restart your computer when the installation is complete.

You can then use Access configuration options to point to the SMS database using the WBEM ODBC driver. You will need to have a valid account and password, as described earlier. Refer to your Access documentation for instructions regarding the configuration of Access to import data from SQL databases.

MORE INFO

---

Chapter 15 of the SMS 2.0 Resource Guide (part of the Microsoft BackOffice 4.5 Resource Kit, available through Microsoft Press) offers several approaches to extracting information from the SMS database using Microsoft Access, such as creating links to the SMS data and creating SQL pass-through queries. Refer to that chapter for a detailed discussion of these approaches.