Implementation Guide › Installing CA User Activity Reporting Module › Install the ODBC Client

Install the ODBC Client

Installing an ODBC client on Windows systems involves the following steps:

1. Verify that you have necessary permissions and obtain a license key for the ODBC client driver (prerequisites).
2. Install the ODBC client.
3. Create a data source using the Windows Data Source (ODBC) utility.
4. Configure the connection details for ODBC client.
5. Test the connection to the database.

Prerequisites

ODBC access to the event log store is available only in CA User Activity Reporting Module r12.1 and later releases. See the ODBC data source considerations for needed information before you begin the installation.

Users of this feature must belong to a user group that has the dataaccess privilege in the Default Data Access Policy (in the CALM access policies).

For an ODBC client, the following prerequisites apply:

• You must have administrator privileges to install the ODBC client on a Windows server.
• The ODBC client installation requires the Microsoft Windows Installer service, and displays a message if it is not found.
• Configure the ODBC Server service in CA User Activity Reporting Module, ensuring that you select the Enable Service check box
• Configure an ODBC Data Source for Windows systems using the Data Sources (ODBC) utility on the Control Panel.
• You must have rights to create files in the directory where you want to install the client on UNIX and Linux systems.

See the CA User Activity Reporting Module Support Certification Matrix on http://www.ca.com/Support for details on the specific platforms supported for use with the ODBC and JDBC feature.

Configure the ODBC Server Service

You can configure ODBC and JDBC access to the CA User Activity Reporting Module event log store using this procedure.

To configure ODBC and JDBC access

1. Log in to the CA User Activity Reporting Module server as an Administrator user.
2. Access the Administration tab, and then click the Services subtab.
3. Click the ODBC Server service to open the global settings, or expand the node and select a specific CA User Activity Reporting Module server.
4. Set a port value for the Service Port field, if you decide to use a port other than the default value.
5. Specify whether to enable SSL to encrypt data transport between the ODBC client and the CA User Activity Reporting Module server.

   Note: The Service Port and SSL Enabled settings must match on both the server and the ODBC client. The default value for port is 17002, and SSL encryption is enabled. If these settings do not match the settings on the ODBC client, connection attempts fail.

Install the ODBC Client on Windows Systems

Use this procedure to install the ODBC client on a Windows system.

Note: You need a Windows Administrator account to install the ODBC client.

To install the ODBC client

1. Locate the ODBC client directory in the Application DVD or installation image, in the directory \CA\ELM\ODBC.
2. Double-click the application, Setup.exe.
3. Respond to the license agreement and click Next.

   The Choose Destination Location panel appears.

4. Enter a destination location or accept the default location and click Next.

   The Select Program Folder panel appears.

5. Select a program folder or accept the default selection and click Next.

   The Start Copying Files panel appears.

6. Click Next to begin copying files.

   The Setup Status panel displays the progress of the installation. When the installation finishes copying files, the InstallShield Wizard Complete panel appears.

7. Click Finish to complete the installation.

Create an ODBC Data Source on Windows Systems

Use this procedure to create the required ODBC data source on Windows systems. You can create the data source as either a user DSN or a System DSN.

To create the data source

1. Access the Windows Control Panel, and open the Administrative Tools.
2. Double-click the utility, Data Sources (ODBC). The ODBC Data Source Administrator window appears.
3. Click Add to display the Create New Data Source window.
4. Select the entry, CA User Activity Reporting Module ODBC Driver, and then click Finish.

   The CA User Activity Reporting Module ODBC Driver Setup window appears.

5. Enter values for the fields as described in the section on ODBC Data Source Considerations, and then click OK.

ODBC Data Source Considerations

The following are the descriptions of the ODBC data source fields as they relate to CA User Activity Reporting Module:

Data Source Name

Create a name for this data source. Client applications that want to use this data use this name to connect to the data source.

Service Host

Specifies the name of the CA User Activity Reporting Module server which the client connects. You can use either a hostname or an IPv4 address.

Service Port

Specifies the TCP service port on which the CA User Activity Reporting Module server listens for ODBC client connections. The default value is 17002. The value you set here must match the setting for the ODBC Server service or the connection fails.

Service Data Source

Leave this field blank, otherwise the connection attempt fails.

Encrypted SSL

Specifies whether to use encryption on the communications between the client and the CA User Activity Reporting Module server. The default value is to have SSL enabled. The value you set here must match the setting for the ODBC Server service or the connection fails.

Custom Properties

Specifies the connection properties for use with the event log store. The delimiter between the properties is a semi-colon with no space. The recommended default values include the following:

querytimeout

Specifies the timeout value in seconds with no data returned after which the query is closed. The following is the syntax for this property:

querytimeout=300

queryfederated

Specifies whether to perform a federated query. Setting this value to false performs a query only on the CA User Activity Reporting Module server to which the database connection is made. The following is the syntax for this property:

queryfederated=true

queryfetchrows

Specifies how many rows to retrieve in a single fetch operation, if the query is successful. The minimum value is 1, and the maximum value is 5000. The default value is 1000. The following is the syntax for this property:

queryfetchrows=1000

offsetmins

Specifies the offset for the timezone for this ODBC client. A value of 0 uses GMT. You can use this field to set your own timezone offset from GMT. The following is the syntax for this property:

offsetmins=0

suppressNoncriticalErrors

Indicates the Interface Provider’s behavior in case of noncritical errors such as a database not responding or a host not responding.

The following is the syntax for this property:

suppressNoncriticalErrors=false

Test the ODBC Client's Connection to the Database

The ODBC client is installed with a command line interactive SQL Query tool, ISQL. You can use this tool for testing your configuration settings and the connectivity between the ODBC client and the CA User Activity Reporting Module event log store.

To test the client connection to the database

1. Access a command prompt and navigate to the directory where you installed the ODBC client.
2. Start the ISQL utility, odbcisql.exe.
3. Enter the following command to test the client connection to the database:

   connect User*Password@DSN_name
   

   Use the data source name you created for this ODBC connection to the database for the DSN_name value. If your connection parameters are correct, you see a return message similar to following:

   SQL: connecting to database: DSN_name
   Elapsed time 37 ms.
   

Note:If your password contains the @ symbol, the iSQL utility fails to run properly, reading everything after the "@" as the DSN name. To avoid this problem, enter the password in quotes:

Connect User*"Password"@DSN_name

Test Server Retrieval from the Database

Use this test query to determine whether an ODBC client application is able to retrieve data from a CA User Activity Reporting Module event log store using the established database connection. This procedure uses the same ISQL utility you used to test the ODBC connection.

Note: Do not copy and use the SQL queries provided in the CA User Activity Reporting Module queries and reports to test your ODBC connection. Those SQL statements are only for the CA User Activity Reporting Module server to use with the event log store. Build your ODBC SQL queries using standard constructs according to the ANSI SQL standard.

To test the server component data retrieval

1. Access a command prompt and navigate to the directory where you installed the ODBC client.
2. Start the ISQL utility, odbcisql.exe.
3. Enter the following SELECT statement to test retrieval from the event log store:

   select top 5 event_logname, receiver_hostname, SUM(event_count) as Count from view_event where event_time_gmt < now() and event_time_gmt > timestampadd(mi,-15,now()) GROUP BY receiver_hostname, event_logname;
   

More information

SQL Support Limitations

Supported SQL Functions