Novell Home

My Favorites

Close

Please to see your favorites.

How to get NAL XML Reporting to work with a Microsoft SQL 2000 Database in ZENworks Desktop Management 6.5

This document (3300294) is provided subject to the disclaimer at the end of this document.

Environment

Novell ZENworks 6.5 Desktop Management - ZfD6.5
Novell Application Launcher (NAL)
Novell ZENworks Desktop Management
Novell Open Enterprise Server (Linux based)
Novell Open Enterprise Server (OES) Support Pack 1 Linux

Situation

How to get NAL XML Reporting to work with a Microsoft SQL 2000 Database in ZENworks Desktop Management 6.5
Instructions to setup Microsoft SQL 2000 Database on a OES Linux Server.

Resolution

1. Follow the instructions on getting XML Reporting with NAL set up and working with Sybase at Novell Documentation.
2. Get the three files msbase.jar, mssqlserver.jar, and msutil.jar and copy them to SYS:\ tomcat\4\webapps\zfdamrServlet\WEB-INF\lib directory. For OES Linux, copy these files into the /var/opt/novell/tomcat/webapps/zfdamrServlet/WEB-INF/lib directory. Also, for OES Linux change the owner to novlwww and the group to www for these jar files.
NOTE: Download these SQL Server 2000 Driver files for JDBC 2.2.0022 from Microsoft's web site.
3. Modify the parameters in your tomcat\4\webapps\zfdamrServlet\WEB-INF\web.xml file (OES Linux directory is /var/opt/novell/tomcat/webapps/zfdamrServlet/WEB-INF/web.xml) to have these settings:
a. dbuser = user you will create later in the DB
b. dbpasswd = password for this user you will create later in the DB
c. dbip = ip address of the server running MSSQL 2000 Server
d. dbport = 1433;
e. dbprotocol = jdbc:
f. dbsubprotocol = Microsoft:
g. dbsubname = sqlserver://
h. dbdriver = com.microsoft.jdbc.sqlserver.SQLServerDriver
Restart both Apache and Tomcat to make the changes effective.
4. Install MSSQL Server on a Windows Server
5. Once MSSQL Server is up and running click on Start -> Programs -> Microsoft SQL Server -> Enterprise Manager

6. Underneath "Microsoft SQL Servers" browse down to your server



7. Browse down to "Databases"
8. Right-Click on the "Databases" folder and choose New Database



9. Click OK
10. Browse down to the NAL database you just created and underneathit right-click on Tables and select "New Table"



11. Set up all the fields of the tables like this


12. There are 3 tables "T_SUCCESS", "T_Failure", and "T_Info". Create them with this information:
a. T_Success contains the following fields along with their associated data types:
- zenEventType varchar(256) Description of the event
- zenDateTime varchar(256) Date and time event occurred
- zenUserTDN varchar(256) User's Tree Distinguished Name (TDN)
- zenWSTDN varchar(256) Workstation's TDN
- zenWSAddr varchar(256) Workstation address (IP)
- zenAppTDN varchar(256) Application object's TDN
- zenAppGUID varchar(256) Application object's GUID
- zenAppVer varchar(256) Application object's version
- zenMajor varchar(256)
- zenMinor varchar(256)
- zenAppFlags varchar(256)
b. T_Info and T_Failure contain the following fields along with their associated data types:
- zenEventType varchar(256) Description of the event
- zenDateTime varchar(256) Date and time event occurred
- zenUserTDN varchar(256) User's Tree Distinguished Name (TDN)
- zenWSTDN varchar(256) Workstation's TDN
- zenWSAddr varchar(256) Workstation address (IP)
- zenAppTDN varchar(256) Application object's TDN
- zenAppGUID varchar(256) Application object's GUID
- zenAppVer varchar(256) Application object's version
- zenMajor varchar(256)
- zenMinor varchar(256)
- zenEventString1 varchar(256) Strings describing reason(s) for failure
- zenEventString2 varchar(256)
- zenEventString3 varchar(256)
- zenEventString4 varchar(256)
- zenEventString5 varchar(256)
- zenAppFlags varchar(256)

13. You can automatically create the tables and fields with this SQL if you want to:

create table T_Info (zenEventType varchar(256), zenDateTime varchar(256), zenUserTDN varchar(256), zenWSTDN varchar(256), zenWSAddr varchar(256), zenAppTDN varchar(256), zenAppGUID varchar(256), zenAppVer varchar(256), zenMajor varchar(512), zenMinor varchar(512), zenEventString1 varchar(512), zenEventString2 varchar(512), zenEventString3 varchar(512), zenEventString4 varchar(512), zenEventString5 varchar(512), zenAppFlags varchar(256))

create table T_Success (zenEventType varchar(256), zenDateTime varchar(256), zenUserTDN varchar(256), zenWSTDN varchar(256), zenWSAddr varchar(256), zenAppTDN varchar(256), zenAppGUID varchar(256), zenAppVer varchar(256), zenMajor varchar(512), zenMinor varchar(512), zenAppFlags varchar(256))

create table T_Failure (zenEventType varchar(256), zenDateTime varchar(256), zenUserTDN varchar(256), zenWSTDN varchar(256), zenWSAddr varchar(256), zenAppTDN varchar(256), zenAppGUID varchar(256), zenAppVer varchar(256), zenMajor varchar(512), zenMinor varchar(512), zenEventString1 varchar(512), zenEventString2 varchar(512), zenEventString3 varchar(512), zenEventString4 varchar(512), zenEventString5 varchar(512), zenAppFlags varchar(256))

14. Create a new user login on the Server. In the SQL Server Enterprise Manager browse down to Security and right click on"Logins" and choose "New Login"



15. In the settings of this user check the radio button for "SQL Server Authentication"
16. Type in the username "naldb" and type in the password "novell" (You can have any username and password here). You may want to set this to "mw_dba" so that the reporting works in ConsoleOne. This is just an example.
17. Choose "NAL" as the database that this user will be a user for.



18. Confirm your password when prompted.
19. Now Right-click on the "Users" section and choose "New User"



20. Create a user named "naldb" (you can name it anything you want really) and make him a member of the db_owner group.You may want to set this to "mw_dba" so that the reporting works in ConsoleOne. This is just an example.



21. Now make sure you have the right authentication mode set. Right click on your SQL Database object and go to properties



22. Click on the "Security" tab and select the radio button called "SQL Server and Windows"



23. Before you send an event make sure that your zfdamrServlet is running by hitting the url http://serverdnsorip:8080/zfdamrServlet/run (or without the 8080) and if it says it is running they you are ok.
24. Send an event to the XLM servlet by running an application that has been configured in step 1 to send XML reporting events.
25. You can view your data in the database by going to the T_Success table for example and right-clicking on it and choosing"Open Table" ->"Return All Rows"



26. You should see something like this:


.

Additional Information

This will also work in ZENworks for Desktops 4.0.1 however it is not officially supported in that platform.

Formerly known as TID# 10094306

Disclaimer

This Support Knowledgebase provides a valuable tool for NetIQ/Novell/SUSE customers and parties interested in our products and solutions to acquire information, ideas and learn from one another. Materials are provided for informational, personal or non-commercial use within your organization and are presented "AS IS" WITHOUT WARRANTY OF ANY KIND.

  • Document ID:3300294
  • Creation Date:19-OCT-06
  • Modified Date:18-JUN-12
    • NovellOpen Enterprise Server
      ZENworks Desktop Management

Did this document solve your problem? Provide Feedback