Novell Home

GPEXPORT 1.0

Novell Cool Solutions: Feature
By Ty Ellis

Digg This - Slashdot This

Posted: 29 Sep 1999
 

General Purpose ZENworks 2 Inventory Database Export Tool

Introduction

ZENworks 2 includes an inventory scanning process that creates a Sybase database of software and hardware inventory data. The schema for the inventory data follows the CIM specification. It includes multi-valued attributes for certain elements and is stored in many tables and views. This database schema is documented in a Cool Solutions article, ZENworks 2 Inventory Database Schema, that was posted in August 1999.

Many users have expressed the desire to be able to export the inventory data from the Sybase database into a common, well-known format, so that 3rd-party tools can be used to create reports from this data. GPExport 1.0 is the answer. GPExport will allow for the exporting of data into a comma-delimited format for the purpose of importing the data into other tools such as spreadsheets, databases or word processors.

In this first release of GPExport, the exported data is user configurable (within certain constraints), but the tool does not provide any GUI to help setup the configuration. In order to use this tool effectively, you must be capable of supplying technical information such as the IP address of the server where the database is located, the definition of the SQL queries used to extract the data, etc. IS&T professionals with reasonable knowledge about the ZEN 2 database structure should find this tool very helpful.

The GPExport 1.0 zip file, available below for free download, contains all of the files mentioned within this article.

Requirements

ZENworks 2 provides many necessary elements of the execution environment. This includes the following:

  1. The JAVA runtime environment located in the public\zenworks directory.
  2. The jdbcdrv.zip, jgl3.1.0.jar, and Desktop.jar files located in the public\zenworks directory.

The successful execution of this tool will require a mapped drive to a server that has ZEN 2 with the database components installed.

GPExport 1.0 is a single Java jar file containing the necessary classes files, which will execute when augmented with the environment specified above. We also provide a batch file (gpexport.bat) that may be used to start the tool. The application will not require recompilation or modification for the purpose of extracting different data.

This tool uses a configuration file to describe what data is to be exported from the database and how the output is to handle multi-valued data elements. We've provided multiple configuration files for you to use. The appropriate configuration file needs to be specified to the tool via a command line argument, as outlined in Step 3 below.

Running GPExport 1.0

Here's what you do to run this tool:

  1. Set the environment variable ZEN2_DIRECTORY to SYS:public\zenworks directory on a server with the inventory database installed.
  2. Explode the zip file in the directory that GPExport will be run from.
  3. Run GPExport by issuing the following command line: GPExport [-d default_cfg_file] ip_address configuration_file [output_file]

where:

  • -d default_cfg_file is the optional switch for specifying the default configuration file used for export definitions. If this is not specified, then all information needed to define the export must be in configuration_file.
  • ip_address is the IP address or DNS name of the server where the database resides.
  • configuration_file is the configuration file which defines the export definitions.
  • output_file specifies the output file for the exported data. If this is not specified then all output is sent to the screen.

Features

Here are the specific features for the exporting of inventory data supported by GPExport 1.0.

  1. Comma Delimited Output
  2. The output of this tool is in comma-delimited format capable of being imported into Microsoft Access (or other applications) without modification. The first line of the file lists all column headings by enclosing each individual column heading in double quotes. Commas separate the column headings. All subsequent lines correspond to rows of data, with each field enclosed in double quotes and all fields separated by commas.

    For example, here is the output for two rows of data each containing the computer name and file system size:

    "Computer Name","File System Size"
    "adminWINNTTELLIS4137_65_60_52.TelAviv.ISRAEL","2047"
    "adminWINNTTELLIS4137_65_60_5290.Tel Aviv.ISRAEL","2047"

  3. Multi-valued Data
  4. Depending on how certain data elements are extracted from the database, the output may result in multi-valued data for a given row. When this occurs, a set of columns will be generated and populated corresponding to each potential multi-value attribute. Each column name will simply have a number appended to it according to the configuration file information. Each row of the report will be comprised of exactly the same number of fields, regardless of the existence of real data.

    For example, this is what would happen to the output if the same example used above were changed to include two fields of File System Size. Notice the change in the header row as well as the additional column of data (identified in bold).

    "Computer Name","File System Size 1","File System Size 2"
    "adminWINNTTELLIS4137_65_60_52.TelAviv.ISRAEL","2047","1023"
    "adminWINNTTELLIS4137_65_60_5290.Tel Aviv.ISRAEL","2047","1023"

  5. Null Values
  6. If a null value is retrieved from the database, or real data simply does not exist in the database, an empty string will be exported enclosed by double quotes.

    For example, here is what would happen to the output if the same example used above resulted in empty fields because the inventory only contained a single hard disk but the report requested two columns of hard-drive data. Notice that the column corresponding to the second hard drive contains empty data.

    "Computer Name","File System Size 1","File System Size 2"
    "adminWINNTTELLIS4137_65_60_52.TelAviv.ISRAEL","2047",""
    "adminWINNTTELLIS4137_65_60_5290.Tel Aviv.ISRAEL","2047",""

  7. Configuration File Format
  8. The heart of this program is the ability to configure what is exported from the database without any changes to the code. This is done via a configuration file that contains the needed information to build the report. Here are details about each of the three sections of the configuration file:

    1. Report Content
    2. Column Description
    3. View Definition

    Important: The order in which statements appear in the configuration file is not important, but the configuration file must be complete or program execution will be terminated.

    The file is case sensitive, and comments are indicated by a "#" character at the beginning of the line.

    1. Report Content

      A single line in the configuration file is used to identify each column of data in the export. This line is identified by the keyword "Report." It is followed by a comma delimited set of column identifiers, and is formatted as follows:

      Report=FirstColumn, SecondColumn

      In this example we simply defined a report with two columns described by FirstColumn and SecondColumn. Each of the column identifiers becomes a new keyword that describes the column.

    2. Column Description

      Each column identifier in the Report line becomes a new keyword that supplies more information about the column. For each column identifier in the report, additional lines need to be present in the configuration file. These lines describe

      • the query
      • the data
      • column multiplicity (since you can have multiple columns for multi-valued fields)
      • column title

      For example, if we were producing an export which should have a single column containing the distinguished name of each workstation, and the report line consisted of a single column identifier, DN, we would need the following report line and four lines describing the column.

      Report=DN

      DN.view=BIOS
      DN.field=u.Name
      DN.count=1
      DN.title=Computer Name

      • DN.view specifies yet another keyword in the configuration file used to identify the query used to gather the data.
      • DN.field provides the selection portion of the query used as column data.
      • DN.count specifies the column multiplicity.
      • DN.title specifies the title of the column.

      Advanced Column Description

      There are also some advanced column descriptions supported by GPExport 1.0. The advanced features include:

      • Column Multiplicity
      • Column Calculations

      Column Multiplicity

      Since certain data fields may be multi-valued (such as NIC Card), GPExport can generate multiple columns for each value of the multi-valued field. The number of columns generated is defined by supplying the ".count" suffix to the column identifier in the configuration file. For example, the following configuration information would generate output similar to that above, under Null Values.

      Report=DN, FileSystemSize

      DN.view=BIOS
      DN.field=u.Name
      DN.count=1
      DN.title=Computer Name

      FileSystemSize.view=MWLocalFileSystem
      FileSystemSize.field=m.filesystemsize
      FileSystemSize.count=2
      FileSystemSize.title=File System Size

      If column multiplicity is not specified, a single column will be generated.

      Column Calculations

      In the event that a calculation needs to be applied to the raw column data before it is exported, a field calculation may be applied. This calculation is specified by appending the ".calculation" suffix to the ".field" suffix of the column identifier keyword.

      For example, given the configuration information above, under Column Multiplicity, it may be desirous for the filesystem size to be converted to Gigabytes instead of the Megabytes format. Specifying the following additional line in the configuration file would do this.

      FileSystemSize.field.calculation=m.filesystemsize/1024.0

      Important: the ".field" suffix of the column identifier (e.g. FileSystemSize.field in the above example) must also be present in the configuration file.

      Currently only calculations that are based on constants and the field specified by the ".field" suffix are supported. Make sure that only the field specified appears in the calculation.

      Data Aggregation

      This tool supports combining multi-valued data into a single column by specifying an aggregation formula instead of the specific data field. This is done by replacing the ".field" suffix with the ".aggregate" suffix in the configuration file. Given the example above, under Column Multiplicity, it may be that all the filesystem sizes are to be summed together to produce a single column containing the total size (in Gigabytes) of all filesystems on the computer. Specifying the following configuration file lines would do this:

      FileSystemSize.view=MWLocalFileSystem
      FileSystemSize.aggregate=sum(m.filesystemsize)/1024.0
      FileSystemSize.title=File System Size

      Notice that we have replaced "FileSystemSize.field" with "FileSystemSize.aggregate" keyword and that we have included a calculation to convert Megabytes to Gigabytes. Note also that we do not need to specify the multiplicity.

      If both the ".field" and the ".aggregate" suffixes are specified, the ".field" suffix will take precedence.

      Null Value Representation

      When an empty field is exported from the tool, its representation can be specified. This is done by supplying the ".null" suffix to the column identifier keyword. Therefore, given the example above, under Column Multiplicity, if "NONE" is to be exported when no data is available for the second filesystem then specify the following line:

      FileSystemSize.null="NONE"

    3. View Definition

      The final information needed to perform the export is the query description. Each of the column identifiers has a keyword specified by the view keyword. The view keyword identifies the query used to obtain the data. The view (or query information) lists the database tables (or views) used in the query, along with how the tables are joined and which table contains the computer system for a given row.

      In the example above, under Column Multiplicity, there would need to be a set of keywords in the configuration file corresponding to the DN.view=BIOS line. Specifically, the following lines must also appear in the configuration file:

      BIOS.from=InstalledBIOS a, UnitaryComputerSystem u, BIOS b
      BIOS.where=u.id$=a.groupComponent and b.id$=a.partComponent
      BIOS.computerSystem=u

      • BIOS.from specifies the tables and view used in the SQL query and aliases for each table or view
      • BIOS.where specifies how the tables are joined
      • BIOS.computerSystem specifies which table contains the computer system ID.
        Critical: The computer system ID must correspond to the unitaryComputerSystem view which must have the id$ field valued.

    Assumptions and Limitations

    Primary join

    When specifying the export content using the "Report" keyword, it is implied that the first column identifier is used as the primary table for joining queries used to extract the data. If the primary query results in a single row per computer then a single row per computer will be exported. On the other hand, if multiple rows per computer system are generated, each computer system will be exported multiple times.

    Query Grouping

    Adjacent column identifiers using the same view (or query) result in a single database query for each of the applicable columns. Therefore, multiple columns may be grouped into a single query.

    Limited SQL

    This tool uses a limited set of SQL statements to extract the data. Only those specifically addressed in this document are supported.

    Error reporting

    If the configuration file is incomplete or is not usable, errors are reported indicating the problem as well as where the problem was encountered. SQL and Sybase errors are reported to the user via the Java exception printing mechanism.

    Default Configuration File

    To get you started, we've provided a default configuration file for GPExport. It is a good representation of all the views that are available. It contains a predefined set of view definitions that a report may use. It does not contain the report definitions or column identifiers, which you will supply to meet your own needs. Use this for the default (-d) option, and simply supply your own configuration file containing your desired Report Contents and Column Descriptions.

    Examples

    Included in the zip file you'll find six example configuration files, which are listed here in order of complexity. They illustrate the variety of things you can do with this tool.

    1. computers.cfg lists the DN of the computers which are in the inventory database.
    2. cpuFamily.cfg lists the DN and the type of CPU installed in the computer.
    3. nic.cfg exports the first two NIC cards installed in the computer.
    4. filesystem.cfg uses an aggregation formula to create a column containing the total hard disk space.
    5. software.cfg exports each computer and its install software information. This export contains a line for each installed software package per computer.
    6. hardware.cfg exports most hardware information for every computer in the inventory database. This includes hard drive sizes and free space, modems, NIC cards, memory, BIOS information, computer model, video and monitor information, etc.

    Download GPExport 1.0

    Download gpexport.zip.

    About the Author

    Ty Ellis graduated in 1984 with a BS in Electronic Engineering. He's been with Novell for six years as a software engineer responsible for NDS administration tools and NDS-enabled network management. Before coming to Novell, he worked at TRW for nine years in the Electronic Systems Division developing real-time flight simulation systems for the F-4 and F-16 aircraft. He is currently the lead engineer on some new features in the works for future releases of ZENworks.


    Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com

© 2014 Novell