AppNote: Recording NAL XML Reporting events in a MySQL database
Novell Cool Solutions: AppNote
By Justin Birt
Digg This -
Updated: 8 Jun 2007
Novell's ZENworks for Desktops (ZfD) has for some time provided a means by which application events generated by NAL can be recorded to a central database, either by direct connection, or by way of XML documents submitted to a proxy web server that in turn writes the events to the database.
ZfD installs a Sybase database as standard to support the storage of NAL application events if this option is selected during installation.
TID 10094306 discusses the configuration required to log NAL events to Microsoft SQL Server.
A search of the Novell Knowledgebase and forums, as well as forums posts returned no results for using the MySQL database engine that ships as an optional component of NetWare 6.5 and OES.
This article describes how to configure NAL XML Reporting to write events to a MySQL database. Example scripts are provided to create the database and configure the zfdamrServlet, such that anyone with limited knowledge of MySQL can try this for themselves.
- Creating a NAL database in MySQL
- Installing and configuring the zfdamrServlet
- Querying the MySQL NAL database
- Appendix A - MySQL database create script
- Appendix B - MySQL user create script
- Appendix C - web.xml
- Appendix D - Example SQL Queries
In order to use MySQL to record NAL events, it is first necessary to create a database and the three required tables.
Before starting, you will need to download and install some utilities from the MySQL site.
Versions are available for Windows, MAC OSX and Linux. We shall be using the MySQL Query Browser from this tool set.
To create the database, assuming you have installed the tools mentioned above, launch the MySQL Query Browser and login to your MySQL server using the root account created when you installed NetWare, or when you installed MySQL from the overlay CDs at a later date.
Enter your credentials in the login dialog. For server host, enter the IP address or DNS name of the server hosting your MySQL database. You can leave the Default Schema field blank ...
... but you will get the nag dialog below. Click Ignore.
The MySQL Query Browser window will open. From the menu, select File > New Script Tab as shown below.
Cut and Paste the SQL Script shown in Appendix A into the Script panel and click the green Execute button on the right side of the application icon bar.
The illustration below shows the MySQL Query Browser window just after the script has run. Note that you can see the 'zen' database and the three tables in the Schemata panel on the right hand side of the window.
With the database created, we now have to create an access account to be used by the zfdamrServlet and set some access permissions for this account.
In Appendix B there are two example scripts that you can use to create a user account and grant the required permissions.
- The first creates an account zenusr that can access the database from any remote host address, with a password zenpwd.
- The second example is more secure, limiting the access the zenusr account has to three specific IP addresses. Here, you will configure the IP address to be the address of your server or servers that are running the zfdamrServlet. Additionally you may wish to configure the IP address of a administration workstation from where you will run queries.
Use these example scripts as the basis of your own script, changing the username, password and connection security to suit your needs.
To run your script, clear the Script pane in the MySQL Query Browser and paste your script into the pane. Click the green Execute button on the right side of the application icon bar.
Strictly speaking, you only need insert rights to your database as the zfdamrServlet only appends new records to the database. I included the 'select' permission as well so the same account could be used for reporting. This is discussed later in this article.
Full details of how to install the zfdamrServlet on your chosen platform are covered in the ZfD Administrators Guide. However I found I had to tweak things a little to get it operating on a NetWare 6.5 server.
There are two files that require alteration.
This file configures the Apache web server and tells it where to find zfdamrServlet. On my installation, the alias statement at the beginning of the default file read as follows:
Alias /zfdamrServlet "SYS:/zfdamrServlet" <Directory "SYS:/zfdamrServlet/"> Options Indexes FollowSymLinks AllowOverride None Order Allow, Deny Allow from all <Directory>
As the Novell documentation instructs you to copy the SYS:Public/Reporting/canned/Nal Reports/zfdamrServlet.war file to SYS:/tomcat/4/webapps, this will install zfdmarServlet to SYS:/tomcat/4/webapps/zfdamrServlet when Tomcat is restarted. As such, the alias statement in zfdamrServlet.conf needs modification as shown below.
Alias /zfdamrServlet "SYS:/tomcat/4/webapps/zfdamrServlet" <Directory "SYS:/tomcat/4/webapps/zfdamrServlet/"> Options Indexes FollowSymLinks AllowOverride None Order Allow, Deny Allow from all <Directory>
I copied my file to SYS:/tomcat/4/conf/zfdamrServlet.conf, as this is the standard location adopted by other applications, and updated my Apache include statement with this location accordingly.
Configuration of the zfdamrServlet is via the file SYS:/tomcat/4/webapps/zfdamrServlet/WEB-INF/web.xml. Establishing how to configure this file to allow zfdamrServlet to write events to a MySQL database was the most difficult part of this project.
Firstly, it is necessary to understand what the MySQL jdbc driver expects as a connect string. This is shown below.
jdbc:mysql//[mysql server name or ip address]/[database name]
Referring to the Novell ZfD documentation and TID 10094306 I tried numerous variations and combinations when setting the parameters in web.xml to no avail. I constantly observed Java errors in the server logger screen whenever I ran my test ZEN application.
Making the assumption that the zfdamrServlet was written with connection to the Sybase data store in mind and not to SQL databases in general, in order to better understand how the servlet worked, it was necessary to resort to underhanded tactics and examine the zfdamrServlet.class file using a Java decompiler (sorry Novell, I only had a quick peek!).
What I discovered was that within the zfdamrServlet doGet() function, it was possible to see how the code built the connect string from parameter values set in the web.xml file.I established that the code in zfdamrServlet would malform the connect string from a MySQL perspective, resulting in the Java errors I was experiencing and visible in the server Logger Screen.
It is possible to effectively bypass the connect string formatting occurring in code by setting the majority of the parameters in the web.xml to null, and passing the majority of the connect string in just one parameter.
A second parameter is required because the line of code in the class file that formats the connect string has a hard coded : (colon).
The two required parameters are:
|dbport||mysql://[MySQL server name]/[database name]|
Establishing this was a "eureka moment" !!
A full copy of an example of a working web.xml file can be found in Appendix C.
XML Targets Policy
As documented in the ZfD Administrators Guide, you configure your XML Targets Policy as a sub-component of a Service Location Package.
The only deviation from the procedures documented in the guide centered on URL format.
In the guide text, mention is made of specifying the port number in the URL (8080 or 8180 depending on platform). This was not found to be necessary, indeed, a "The page cannot be displayed" error was received when a port number was included.
As such your Target URL should look something like:
http://[server name or IP address]/zfdamrServlet/run
Now a word about scale. If you are a small site, it is conceivable that you could host your MySQL database and install zfdamrServlet on the same servers Apache/Tomcat instance.
For a larger enterprise with multiple sites, best practice would be to have a server per site running the zfdamrServlet, with the appropriate Search/XML Target Policies configured to route document submissions from workstations at that site to the target server for that site.
The MySQL database can be located on a single central server.
Having gotten data flowing into your database, how do you get it out again? For those who have limited knowledge of SQL, the MySQL Query Browser tool used earlier in this article to create the database can be used again to easily retrieve data.
Launch MySQL Query Browser and authenticate to your database using the user credentials created earlier. You can set the Default Schema to the name of your database and avoid the nag screen.
Once the application window has opened, you will be able to see your database and the three data tables in the Schemata tab panel on the right side of the window.
As an example, if you double click the t_failure table the application will automatically enter the SQL query code to retrieve the contents of the table in the code field.
Click the green Execute button on the right of the tool bar and your table data will be returned to the Resultset tab panel. This is shown in the illustration below.
Once you have retrieved your data, select File > Export Resultset > Export as CSV to save your data as a csv file that can be loaded and analysed by the spreadsheet application you are most familiar with.
I have included a few SQL queries you can cut and paste in the MySQL Query Browser in Appendix D.
Alternatively, MySQL provides ODBC drivers for most platforms allowing you to connect directly to the database from a spreadsheet application.
It is possible to configure zfdamrServlet to connect to and record NAL events in the MySQL database that ships with OES NetWare. This allows organisations to implement NAL XML Reporting without having to introduce an additional database engine.
#First we create our database/schema. Call it what you like CREATE DATABASE zen; #Switch to our new database USE zen; #Create our failure table CREATE TABLE `zen`.`T_Failure` ( `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `zenEventType` TEXT, `zenDateTime` TEXT, `zenUserTDN` TEXT, `zenWSTDN` TEXT, `zenWSAddr` TEXT, `zenAppTDN` TEXT, `zenAppGUID` TEXT, `zenAppVer` TEXT, `zenMajor` TEXT, `zenMinor` TEXT, `zenEventString1` TEXT, `zenEventString2` TEXT, `zenEventString3` TEXT, `zenEventString4` TEXT, `zenEventString5` TEXT, `zenAppFlags` TEXT, PRIMARY KEY(`ID`) ) TYPE = MYISAM; #Create our information table CREATE TABLE `zen`.`T_Info` ( `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `zenEventType` TEXT, `zenDateTime` TEXT, `zenUserTDN` TEXT, `zenWSTDN` TEXT, `zenWSAddr` TEXT, `zenAppTDN` TEXT, `zenAppGUID` TEXT, `zenAppVer` TEXT, `zenMajor` TEXT, `zenMinor` TEXT, `zenEventString1` TEXT, `zenEventString2` TEXT, `zenEventString3` TEXT, `zenEventString4` TEXT, `zenEventString5` TEXT, `zenAppFlags` TEXT, PRIMARY KEY(`ID`) ) TYPE = MYISAM; #Create our success table CREATE TABLE `zen`.`T_Success` ( `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, `zenEventType` TEXT, `zenDateTime` TEXT, `zenUserTDN` TEXT, `zenWSTDN` TEXT, `zenWSAddr` TEXT, `zenAppTDN` TEXT, `zenAppGUID` TEXT, `zenAppVer` TEXT, `zenMajor` TEXT, `zenMinor` TEXT, `zenAppFlags` TEXT, PRIMARY KEY(`ID`) ) TYPE = MYISAM;
GRANT select,insert on zen.* to zenusr@"%" identified by 'zenpwd'; FLUSH PRIVILEGES; GRANT select,insert on zen.* to firstname.lastname@example.org identified by 'zenpwd'; GRANT select,insert on zen.* to email@example.com identified by 'zenpwd'; GRANT select,insert on zen.* to firstname.lastname@example.org identified by 'zenpwd'; FLUSH PRIVILEGES;
<!DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.2//EN" "http://java.sun.com/j2ee/dtds/web-app_2_2.dtd"> <web-app> <display-name>ZENWorks for Desktops, Application Management Reporting Servlet</display-name> <description> This is version 1.0 of The ZENWorks for Desktops Application Management Reporting Servlet. This version takes an XML document as input, and submits the reporting event described by the XML document to a JDBC-enabled database. </description> <!-- The following context parameters must be given values appropriate for your database configuration. If your database has the default Sybase values, then all you need to change is the ip address (dbip). --> <context-param> <param-name>dbuser</param-name> <param-value>zenusr</param-value> <description> The user id to use when accessing the database </description> </context-param> <context-param> <param-name>dbpasswd</param-name> <param-value>zenpwd</param-value> <description> The password to use along with dbuser </description> </context-param> <context-param> <param-name>dbip</param-name> <param-value></param-value> <description> The IP address of the server running the database </description> </context-param> <context-param> <param-name>dbport</param-name> <param-value>mysql://[MySQL server address]/zen</param-value> <description> The port on which the database engine is listening (2638 for Sybase on NetWare) </description> </context-param> <context-param> <param-name>dbprotocol</param-name> <param-value>jdbc</param-value> <description> The protocol (eg, jdbc:) to use when accessing the database </description> </context-param> <context-param> <param-name>dbsubprotocol</param-name> <param-value></param-value> <description> The subprotocol (eg, sybase:) to use when accessing the database </description> </context-param> <context-param> <param-name>dbsubname</param-name> <param-value></param-value> <description> The subname (eg, for Sybase, Tds:) to use when accessing the database </description> </context-param> <context-param> <param-name>dbdriver</param-name> <param-value>org.gjt.mm.mysql.Driver</param-value> <description> The full class of the jdbc driver to use (eg, com.sybase.jdbc2.jdbc.SybDriver) </description> </context-param> <context-param> <param-name>dbinstance</param-name> <param-value></param-value> <description> The instance name of the NAL reporting database </description> </context-param> <servlet> <servlet-name>zfdamrServlet</servlet-name> <description> This servlet takes XML representations of Application Management reporting events and translates them into JDBC, inserting them into the configured database </description> <servlet-class>com.novell.zenworks.zfdamrwebapp.zfdamrServlet</servlet-class> </servlet> <servlet-mapping> <servlet-name>zfdamrServlet</servlet-name> <url-pattern>/</url-pattern> </servlet-mapping> <session-config> <session-timeout>30</session-timeout> <!-- 30 minutes --> </session-config> </web-app>
# Retrieve all distribution/launch failures SELECT * FROM t_failure; # Retrieve all distribution/launch successes SELECT * FROM t_success; #Retrieve distribution/launch successes from a given date. Note that the % character is a wild card SELECT * FROM t_failure WHERE zenDateTime LIKE "13/04/2007%"; # Retrieve failure events for a given application. SELECT * FROM t_failure WHERE zenAppTDN LIKE "[Application object DN]";
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com