Advanced Reporting Techniques of Dell OpenManage™ IT Assistant
Enterprise Systems Group (ESG)
Dell OpenManage™
Systems Management
Dell White Paper
By Haihong Zhuo
Software Engineer
OpenManage Development
August 2002
Contents
Executive Summary 3
Introduction 4
IT Assistant Database Introduction 5
Inventory Data in IT Assistant Database 5
IT Assistant Database Engine 6
Get Ready for Generating Reports from IT Assistant 8
Discovery Performed 8
Database Attached to Database Engine 8
ODBC Data Source Created for IT Assistant Database (optional) 9
Inventory Reporting and Searching in IT Assistant 10
Inventory Reporting 10
Inventory Searching 11
Customizing Reports from IT Assistant 13
A Simple Report 13
A More Detailed Report 14
System Reports 15
A Complicated Report 16
Tools/Applications To Use 18
SQL Server Management Tools 18
Other Tools 19
Conclusion 20
August 2002 Page 2 Dell Enterprise Systems Group
Section 1
Executive Summary
Dell OpenManage™ IT Assistant keeps information about managed systems as inventory data in its database. Users can query this data and get inventory reports through IT Assistant’s user interfaces. IT Assistant also has the capability for users to create customized reports outside its web-based user interfaces.
For example, a fictitious company, DirectCom, Inc., uses IT Assistant to manage all of its computer systems. At the end of a year, its IT department wants to have several reports about the company’s computer assets in a different format as part of the accounting process output. Desired reports include: 1) a list of the company’s computer systems with name, service tag, type of operating system, and total memory of each system; 2) a report with detailed information on each system for all systems; and 3) a list of systems that have a remote access device and/or array manager installed. With an understanding of the IT Assistant database, as well as general understanding of Microsoft® SQL Server™ queries and tools, these reports can be created.
This paper first describes what users need to know about generating reports from IT Assistant, and then describes the built-in inventory report and search functions offered by IT Assistant. Finally, this paper uses the reports mentioned above as examples to instruct users on how to create reports from IT Assistant.
The content in this article applies to IT Assistant version 6.0 and later.
Section 2
Introduction
Dell OpenManage IT Assistant (ITA) is a systems management console that allows users to monitor and manage many IT assets from a centralized point. After ITA is configured, its Network Monitoring service discovers managed systems – servers, desktops, and workstations – on the network and stores information it gathers about each system into the ITA database. This data is referred as inventory data.
For users to be able to access this inventory data, ITA provides basic inventory report and search functions through its web-based user interfaces. In addition to these built-in functions, the ITA database offers users the ability to customize reports.
This paper first describes the inventory reporting and searching functions provided by ITA, then guides users on how to generate custom reports through specific examples. The examples come from a fictitious company – DirectCom Inc. – and are used to show the reports can be created from ITA. This paper also discusses preparation for generating reports and what tools to use.
Section 3
IT Assistant Database Introduction
ITA uses a database as a logical encapsulation of information that needs to be stored persistently. For detailed information about the ITA database, please refer to the Dell OpenManage white paper “Getting to Know the OpenManage IT Assistant Database” by the same author.
Information on managed systems gathered from the network during discovery is referred as inventory data, and is stored in the database. Inventory data contains relatively stable information on a managed system, including its host name, IP address(es), service tag, asset tag, system’s model, processor’s type, size of memory, etc.
Besides the inventory data, there are other persistent data stored in the ITA database. For a list of ITA database tables and information of each table, please refer to section IT Assistant Database Schema and Appendix B - IT Assistant Database Schema in the ITA Database white paper. For relationships between tables, please refer to section IT Assistant Table Relationships in the same white paper.
Inventory Data in IT Assistant Database
Among all the tables in the ITA database, two are designated to store the inventory data and desire to be elaborated. They are the Node table and the NIC table.
Node Table
The Node table serves as the master repository of information for each managed system found during discovery. Some information about a managed system, including its service tag, asset tag, type and model of the system, OS type, and BIOS version will only have single instance data for each system. Such single-instance data is saved in this table. Some data on a managed system can have multiple instances, like multiple NIC adapters. NIC-related information is stored separately in the NIC table. The name of the system (column Name) serves as the primary key of this table and can be used to join the Node table and the NIC table together. Joining these tables is discussed in the NIC Table section.
The Node table is empty after a fresh ITA installation. During the discovery cycle, rows are inserted into, updated, or deleted from this table accordingly. After an upgrade or re-installation of ITA with the database preserved, existing rows in this table will be exported and re-imported to make them available to the new ITA installation.
NIC Table
The NIC table stores NIC-related information, including IP address, MAC address, and the manufacturer’s name for every NIC adapter located on each managed system that is discovered and stored in the Node table. The name of the managed system (column NodeName) and the IP address of a particular NIC adapter (column IPAddress) constitute the composite key of NIC table.
The NIC table is also empty after a fresh ITA installation. During the discovery cycle, rows are inserted into, updated, or deleted from this table accordingly. After an upgrade or re-installation of ITA with the database preserved, existing rows in this table will be exported and re-imported to make them available to the new ITA installation.
Even though there is no foreign key constraint[1] created between Node table and NIC table, these two tables are logically linked by the name of the managed system. The name of the managed system is denoted as Name in the Node table and NodeName in the NIC table. By using Node.Name = NIC.NodeName in the WHERE clause of any SQL (Standard Query Language) statement, these two tables can be joined.
Additionally, there is a trigger created in the Node table to implement cascade deletion: when a record is removed from Node table, row(s) in NIC table with same system name will also be deleted. All triggers in ITA database are listed in the Triggers section of the ITA Database white paper.
IT Assistant Database Engine
Starting from version 6.0, ITA uses an ODBC-compliant database engine from Microsoft -- Microsoft Data Engine (MSDE), Microsoft SQL Server 7.0, or Microsoft SQL Server 2000. This allows third-party applications to connect to ITA database through native SQL Server interface or ODBC.
When ITA’s service components are to be installed on a system, ITA’s installation program will check if any of these three database engines exists on that system. If none of these databases is found, ITA’s installation program will install MSDE silently on that system and deploy the ITA database to the database engine. If the MSDE or either version of SQL Server is found on the system, ITA’s installation program will deploy ITA’s database to that database engine and integrate ITA’s service components to use the engine. If MSDE is installed and/or used when ITA is initially installed, users can still upgrade the database engine to SQL Server 7.0 or 2000, without having to re-install ITA again or losing data. This provides ITA with scalability.
Section 4
Get Ready for Generating Reports from IT Assistant
Users can do inventory reporting and searches through ITA’s web-based user interfaces, or create custom reports by accessing ITA database directly. Either way, the following prerequisites have to be met before reports can be generated.
Discovery Performed
After a fresh installation of ITA, the Node table and the NIC table are empty. Discovery has to be carried out by ITA’s Network Monitoring service to have managed systems discovered from network and inventory data stored into the Node and NIC tables.
The standard procedure for discovery is as follows:
- After ITA is installed, reboot the system upon request. ITA services, the Connection service and the Network Monitoring service, will be started automatically on system reboot.
- Start the ITA web-based user interface by double-clicking the IT Assistant icon on the desktop.
- When prompted for discovery configuration, follow the instruction to enter subnet(s), host name(s), or IP address(es) for ITA’s Network Monitoring service to discover on.
- Wait for the discovery cycle to be completed.
Database Attached to Database Engine
The database engine used by ITA is MSDE or SQL Server 7.0/2000 from Microsoft, so the database file has to be attached to the database engine before it can be accessed. During ITA installation, the ITA database is deployed to the appropriate database engine, resulting in the database file being generated and attached to the database engine. So, on a system running ITA services, this requirement should already be met.
The database file generated is ITAssist.mdf in ITA’s database directory. The name of the database in the database engine is ITAssist. If SQL Server 7.0 or 2000 is installed, users can start the Enterprise Manager of SQL Server to verify that ITAssist is listed as a database for that database engine, and is therefore accessible.
ODBC Data Source Created for IT Assistant Database (optional)
Third-party applications are allowed to access the ITA database through ODBC. To do so, an ODBC data source has to be created for ITA database ITAssist. After ITA is installed and starts running, an ODBC data source, also named ITAssist, is already created for database ITAssist. Therefore, on a system running ITA services, this requirement should already be met.
Users can start the applet ODBC Data Source Administrator, go to the tab System DSN to verify that ODBC data source ITAssist exists for SQL Server database ITAssist, or create that data source.
Note: it is a prerequisite for ITA services to run, but not for third-party applications to access the database, unless they want to do it through ODBC. Therefore, it is an optional requirement depending on whether or not the user wants to access the database through ODBC.
Section 5
Inventory Reporting and Searching in IT Assistant
ITA stores inventory data in the Node table as well as the NIC table in its database. ITA also offers built-in inventory reporting and searching functions for users to access this data.
Inventory Reporting
In ITA’s user interface, click the Inventory button on the top bar to bring up the Inventory page as shown in Figure 1.
Figure 1: IT Assistant Inventory Page
To the right of the Blue Bar, on the tree of discovered systems, select a group of systems or a particular system. On the right panel of the page, under “Select Columns”, check the items to be included in the report. Click the “Get Inventory” button, and a list of system(s) with inventory data for the selected items will be displayed in the bottom half of the right panel. For example, in Figure 1, IP addresses and service tags of all the discovered and then selected systems are being reported.
Inventory Searching
In ITA’s user interface, click the Search button on the top bar to bring up the Search page as shown in Figure 2.
Figure 2: IT Assistant Search Page
In this page, a user can specify a search against the inventory data in the Node table and the NIC table. Up to two combined criteria are allowed. Each criteria allows a set of Boolean relationships, such as Is, Is Not, etc. Click the “Search” button, and the generated query will be executed against ITA database, and the search result will be displayed in the bottom half of the screen.
As shown in Figure 2, the user wants to find, from all the discovered systems, servers that have Windows 2000 as their operating system. Two systems are found to meet those criteria and are listed with their IP Address, Power Status, OS type, BIOS Version, and Asset Tag information.
Section 6
Customizing Reports from IT Assistant
Even though ITA already provides the built-in inventory reporting and searching functions, ITA has the capacity to allow users to customize the reporting and searching. This section offers scenarios to show different types of reports users can generate. and how.
(DirectCom, Inc. is a fictitious company name used in these examples.)
A Simple Report
While doing standard year-end accounting, the IT department at DirectCom, Inc. wants to have a list of the company’s computer assets. They want the list to include the name and service tag of each system, as well as the type of operating system and the total memory installed on the system. Because DirectCom uses Dell Open Management IT Assistant to manage all of their systems, this task can be done either by using ITA’s built-in inventory reporting function, or by running a simple SQL query against the ITA database.
Looking at the database tables, the system administrator determined that all the required data could be found from the Node table. The corresponding columns are Name, ServiceTag, OSType and TotalMemory, respectively. A SQL query can now be generated to retrieve data from Node table and output the results:
SELECT Name, ServiceTag, OSType, TotalMemory
FROM Node
Executing query produces the output in Table 1:
Name / Service Tag / OS Type / Total Memoryh8vht01 / H8VHT01 / Windows 2000 / 523760
ita-jag1 / 12312 / Windows 2000 Domain Controller / 524287
chlin.us.dell.com / Linux / Red Hat Linux / 524228
5ga2k / 5GA2K / Microsoft Windows 2000 / 131072
w6qhe.aus.amer.dell.com / Unknown / Windows 2000 / 267964416
gx110-c68be / C68BE / Microsoft Windows NT / 261120
htgl901.aus.amer.dell.com / HTGL901 / Microsoft Windows NT / 131072
5YTP501 / 5YTP501 / Windows 2000 / 261672
fgk5r01.aus.amer.dell.com / FGK5R01 / Microsoft Windows 2000 / 524288
jianwen-linux71.us.dell.com / 5MDH101 / Red Hat Linux / 524284
… / … / … / …
Table 1: Simple ITA Database Query Report