Administration Guide › Accessing Collected Events with ODBC and JDBC

Accessing Collected Events with ODBC and JDBC

This section contains the following topics:

About ODBC/JDBC Access in CA User Activity Reporting Module

Creating ODBC and JDBC Queries for Use with CA User Activity Reporting Module

How Queries are Processed

Example: Use an Access Filter to Limit ODBC Results

Example: Preparing to Use ODBC and JDBC Clients with Crystal Reports

Use Crystal Reports to Access the Event Log Store with ODBC

Accessing Events from Crystal Reports with JDBC

Remove the ODBC Client on Windows Systems

Remove the JDBC Client

About ODBC/JDBC Access in CA User Activity Reporting Module

CA User Activity Reporting Module provides read-only ODBC and JDBC access to the event log store to allow you to do the following things:

• Configure custom reports with an external reporting utility, like BusinessObjects' Crystal Reports
• Retrieve selected log information using external applications

These features allow you to create and format your own custom reports using log information already retrieved by CA User Activity Reporting Module. In addition, you can retrieve data for use with your existing correlation engines, malware detection packages, and other functions.

After installing the CA Technologies-supplied client on the system you plan to use to access the event log store, configure a connection to the data source and then begin retrieving data. The subscription service installs the server-side components.

Creating ODBC and JDBC Queries for Use with CA User Activity Reporting Module

Support for ODBC and JDBC in CA User Activity Reporting Module is limited to read-only queries using SELECT statements on the following tables:

• VIEW_EVENT - contains events from the Event Database
• VIEW_INCIDENT - contains incidents from the Incident Database
• VIEW_INCIDENTEVENT_BYID - contains incident component events from the Incident Event Database

Build your SELECT statements using ANSI SQL format and rules. The server-side components contain a SQL parsing engine. The parser implements a large portion of the entry level SQL as defined in the X3.135-1992, "Database Language SQL" specification. The parser also supports SQL features from ANSI SQL99 and commercial databases like Microsoft SQL Server and Oracle. The parser is also compliant with the ODBC minimal grammar specification.

The common event grammar serves as the schema for this table. See the CEG Reference Guide for details on the schema.

SQL Support Limitations

CA User Activity Reporting Module does not support stored procedure calls, data control language (DCL) commands, or data definition language (DDL) commands.

CA User Activity Reporting Module does not support the following data manipulation language (DML) operations and keywords:

• UNION
• JOIN
• Nested SELECT
• INSERT
• UPDATE
• DELETE
• Transaction control operators like COMMIT, ROLLBACK, and so forth

Supported SQL Functions

The following are the supported SQL functions for use in building SELECT statements:

• ABS(numeric_exp)
• ROUND(numeric_exp, integer_exp)
• LCASE(string_exp)
• LOWER(string_exp)
• LENGTH(string_exp)
• LTRIM(string_exp)
• RTRIM(string_exp)
• SUBSTRING(string_exp, start,length)
• UCASE(string_exp)
• UPPER(string_exp)
• IFNULL (expr, default_val)
• ISNULL (expr, default_val)
• NVL (expr, default_val)
• CONVERT (value_exp, data_type)
• CURDATE()
• CURTIME()
• CURTIMESTAMP()
• DATEADD(datepart, number, date)
• TIMESTAMPADD(datepart, number, date)
• DATEDIFF(datepart, startdate, enddate) For datepart, the values Year, Day, and second are allowed.
• TIMESTAMPDIFF(datepart, startdate, enddate) For datepart, the values Year, Day, and second are allowed.
• DAYOFMONTH(date_exp)
• DAYOFWEEK(date_exp)
• DAYOFYEAR(date_exp)
• HOUR(time_exp)
• MINUTE(time_exp)
• MONTH(date_exp)
• NOW( )
• SECOND(time_exp)
• WEEK(date_exp)
• YEAR(date_exp)
• AVG([ALL | DISTINCT]expression)
• SUM([ALL | DISTINCT]expression)
• COUNT({[ALL | DISTINCT]expression] | *})
• MAX([ALL | DISTINCT]expression)
• MIN([ALL | DISTINCT]expression)

How Queries are Processed

CA User Activity Reporting Module processes an ODBC or JDBC client-initiated query in the following way:

1. A client application sends a SELECT statement through an ODBC connection to the CA User Activity Reporting Module server.
2. The CA User Activity Reporting Module server validates the SELECT statement. If the validation is successful, the CA User Activity Reporting Module server creates a data structure representing the query.

   Any errors encountered are returned directly to the client driver.

3. The CA User Activity Reporting Module server converts the SQL elements into a query that it can use. If the conversion is successful, the CA User Activity Reporting Module server runs the query.

   Any errors encountered are returned to the client driver.

4. The CA User Activity Reporting Module server manages state information, including an expiration timer, for each query so that it can be canceled in the event the session is closed or the query expires.
5. The CA User Activity Reporting Module server translates the query results and sends them back to the ODBC client driver, and the client application then receives the data.

Result Column Alias

As part of its query state management, CA User Activity Reporting Module provides support for aliased result column names. Therefore you can display common event grammar fields in your custom reports using your own labels and headings.

The alias names are persisted and used for proper data mapping when the CA User Activity Reporting Module server transfers a result set back to the client driver.

Result Limits

To manage disk space, CA User Activity Reporting Module limits the number of result rows. CA User Activity Reporting Module uses a subset of the Transact-SQL TOP keyword with only a fixed value. The percentage variant of the keyword is not supported.

The default TOP value used in CA User Activity Reporting Module is 5000 rows, with a maximum value of 50,000 rows.

CA User Activity Reporting Module-specific Error Codes

The following are the ODBC and JDBC error codes that can occur while accessing the CA User Activity Reporting Module event log store. Each error message provides specific error details.

• 88 – do not support

  The accompanying error message provides details about the unsupported functionality.

• 300 – generic error

  The accompanying error message provides details.

• 301 – error to convert multibyte character parameters
• 302 – authentication error
• 304 – invalid expression (column) in OrderBy or GroupBy clause

The following errors are SQL statement execution errors:

• 305 – error to start query
• 306 – error to get query status
• 307 – error to parse query results XML
• 308 – query execution error
• 309 – query executed with errors
• 310 – error to fetch query results
• 311 – query timeout

Example: Use an Access Filter to Limit ODBC Results

You can create an access filter to limit the data returned to an ODBC access request. When members the named application group accesses CA User Activity Reporting Module event data using the ODBC client, they see only the information allowed by the filter.

This example assumes that you have an application group named UNIX_Analysts and that you want to restrict all members of that group to see only UNIX events from the event log store. The filter created in this example limits event data views from within the CA User Activity Reporting Module user interface and external requests through ODBC.

More information about access filters is available in the online help.

To create an access filter

1. Log in to CA User Activity Reporting Module as an Administrator user.
2. Click the Administration tab, then click the User and Access Management subtab.
3. Click New Access Filter . The Access Filter Design wizard starts.
4. Enter UNIX Analysts for the Name field, and the phrase, UNIX Analysts access filter in the Description field, and then click step 2 Identities at the top of the dialog.
5. Change the Type to Application Group and type UNIX in the Name field and then click Search Identities.

   A list of identities matching your search criteria appear in a shuttle control so that you can select the desired identities.

6. Select the UNIX_Analysts application group from the Available Identities list and click the right arrow shuttle control to move the selection into the Selected Identities list.
7. Click step 3 Access Filters at the top of the dialog.
8. Click New Event Filter to add a line, and then click the field area under Column.
9. Select event_logname from the drop-down list, and then click the field area under Value.
10. Select Unix from the drop-down list. Your dialog resembles the following:

    

11. Click Save and Close.

Example: Preparing to Use ODBC and JDBC Clients with Crystal Reports

Preparing for ODBC or JDBC client access to CA User Activity Reporting Module events using BusinessObjects Crystal Reports involves the following steps:

1. Create a CA User Activity Reporting Module user to allow access to the database.
2. Verify that the ODBC Service is using SSL encryption and port 17002.
3. Install the ODBC client, or copy the JDBC files to the server where Crystal Reports resides.
4. Configure operating system components:
   1. Create and test an ODBC datasource in the Windows Control Panel.
   2. Edit the Crystal Reports configuration file to use the JDBC client.
5. Create events for collection by CA User Activity Reporting Module.

   If you are sure that the event log store already contains events of the type queried, you can omit this step.

Note: This process and the related example assume that you are familiar with creating basic SQL statements and using Crystal Reports. More information about using Crystal Reports is available in the BusinessObjects online help.

Create a CA User Activity Reporting Module User for ODBC or JDBC Access

Use this procedure to create a user account named ELM_Access for use with your JDBC and ODBC clients.

CA User Activity Reporting Module users with the dataaccess permission can use ODBC or JDBC to access event data. Each of the default user roles provided with CA User Activity Reporting Module has this permission. For this example, you could create a user with any of the default CA User Activity Reporting Module roles - Administrator, Analyst, or Auditor.

To create the new user

1. Log in to the CA User Activity Reporting Module server as an Administrator user.
2. Click the Administration tab and the User and Access management subtab.
3. Click the Users button to display the embedded CA EEM user interface.
4. Click New User. The New User dialog opens.
5. Click Add Application User Details and give the account Administrator application privileges.

   Note: In a production environment, you would assign the least permission needed for a user to access the data. You can limit access in many ways including user roles, access policies, and access filters.

6. Complete the user record as needed and assign a password, making a note of it for later use in this example.
7. Save the user and exit the Users window.

Configure the ODBC Service Settings

Use this procedure to configure the CA User Activity Reporting Module ODBC and JDBC service settings.

Note: Changes made to this area cause a restart of the server-side processes that enable ODBC and JDBC communications.

To configure the ODBC Service

1. Log in to the CA User Activity Reporting Module server as an Administrator user.
2. Click the Administration tab and the Services subtab.
3. Click the ODBC Service node.
4. Accept the default settings:
   • Enable Service: True (allows ODBC and JDBC connections on the CA User Activity Reporting Module server)
   • Port: 17002
   • Encrypted (SSL) is selected
   • Session Timeout: 15 minutes
   • Logging Level: accept the default value, NOTSET
5. Click Save.

Create an "elm" ODBC Data Source

Use this procedure to create a data source named "CA-ELM."

Note: Install the ODBC client before you can configure the data source.

To create a data source

1. Access the Windows Control Panel.
2. Open the Administrative Tools folder and start the Data Sources (ODBC) utility.
3. Click Add to display the Create New Data Source dialog.
4. Select the entry, DataDirect OpenAccess SDK 6.0 and click Finish.

   The DataDirect OpenAccess SDK ODBC Driver Setup utility displays a configuration screen.

5. Enter CA-ELM in the Data Source Name field, and provide a text description.
6. Enter the name of your CA User Activity Reporting Module server in the Service Host field. This example uses ca-elm.
7. Enter 17002 in the Service Port field.
8. Select the Encrypted SSL check box.
9. Enter the following Custom Properties:

   querytimeout=600 (seconds);queryfederated=true;queryfetchrows=1000;offsetmins=0;suppressNoncriticalErrors=false
   

10. Click Apply and then click Test Connection.

    If your connection parameters are correct, a successful connection message appears.

11. Click OK to return to the ODBC Data Source Administrator dialog and then click OK again to exit the utility.

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

Edit the Crystal Reports Configuration File

Before you can use Crystal Reports with the CA User Activity Reporting Module JDBC client, you must first provide some configuration settings. After you configure the Crystal Reports XML configuration file, you are ready to prepare and send ANSI SQL standard queries to the CA User Activity Reporting Module event log store.

To configure Crystal Reports settings for JDBC

1. Ensure that you copy the JDBC client JAR files to the Crystal Reports server before you edit the configuration file.
2. Access the server where Crystal Reports resides.
3. Locate the file, CRConfig.xml, and open it for editing.
4. Locate the <DataDriverCommon> tag and the <Classpath> tag section under it.
5. Add the location of the JDBC JAR files for the JDBC client to the classpath.
6. Change the value in the JDBC URL tags to the following:

   jdbc:ca-elm://127.0.0.1:17002;encrypted=1;ServerDataSource=Default;CustomProperties=(querytimeout=600;queryfederated=true;queryfetchrows=1000;offsetmins=0;suppressNoncriticalErrors=false)
   

   See the section on JDBC URL considerations for more explanation of these parameters.

   Refer to the documentation supplied with Crystal Reports for additional information on setting connection parameters in that product.

7. Change the value in the JDBC Classname tags to be the following:

   com.ca.jdbc.openaccess.OpenAccessDriver
   

8. Save the file and exit.

More information:

JDBC URL Considerations

JDBC URL Considerations

When using the JDBC client to access event data stored in CA User Activity Reporting Module, you need both the JDBC Classpath and a JDBC URL. The JDBC Classpath names the driver JAR file locations. The JDBC URL defines the parameters the classes in the JARs use when they load.

The following is a complete, sample JDBC URL:

jdbc:ca-elm://127.0.0.1:17002;encrypted=1;ServerDataSource=Default;CustomProperties=(querytimeout=600;queryfederated=true;queryfetchrows=1000;offsetmins=0;suppressNoncriticalErrors=false)

The following descriptions explain the URL components:

jdbc.ca-elm:

Defines the protocol:subprotocol string that designates the JDBC driver provided with CA User Activity Reporting Module.

//IP Address:Port;

Names the IP address that represents the CA User Activity Reporting Module server whose data you want to access. The port number is the port to use for the communications, and must match the setting in the CA User Activity Reporting Module ODBC Service configuration panel. If the ports do not match, the connection attempt fails.

encrypted=0|1;

Determines whether SSL encryption is used for the communications between the JDBC client and the CA User Activity Reporting Module server. The default value is 0, not encrypted, and does not require specification in the URL. Setting encrypted=1 turns encryption on. Set the connection to encryption explicitly. In addition, this setting must match what you configure in the CA User Activity Reporting Module ODBC Service dialog or the connection attempt fails.

ServerDataSource=Default

Specifies the name of the data source. Set this value to Default for access to the CA User Activity Reporting Module event log store.

CustomProperties=(x;y;z)

These properties are the same as the ODBC custom properties. If you do not specify them explicitly, the default values shown in the example URL apply.

More information

ODBC Data Source Considerations

Create Events for the ODBC Example

For the example query to display, create some relevant events by causing failed activities like the following:

• Log in incorrectly to the CA User Activity Reporting Module server several times.
• Log in incorrectly to an Agent host whose events go to a specific CA User Activity Reporting Module server.
• Access a network or system resource with incorrect credentials.

Use Crystal Reports to Access the Event Log Store with ODBC

You can use the ODBC access feature to query CA User Activity Reporting Module event data from a third party reporting tool like BusinessObjects Crystal Reports. After you complete the required installation and configurations, you are ready to prepare and send ANSI SQL standard queries to the CA User Activity Reporting Module event log store.

The database schema for the event log store is the common event grammar (CEG). The CA User Activity Reporting Module online help contains a CEG reference component to help you create queries. You can also review the underlying SQL statements for the out-of-the-box queries, but use ANSI SQL to access the database from outside CA User Activity Reporting Module.

To access event data from Crystal Reports

1. Complete the prerequisite installation and configuration tasks.
2. Start Crystal Reports and access the Standard Report Wizard.
3. Create an ODBC connection in the Data dialog, and select the ODBC datasource you created in the Windows Control Panel.

   

4. Use the Add Command functionality to create a query in the SQL entry area.

   For example, you could create the following query:

   SELECT source_username as source_username , SUM(event_count) AS FUNC_SUM_event_count FROM view_event WHERE  event_result = 'F' GROUP BY source_username ORDER BY FUNC_SUM_event_count DESC;
   

5. Click OK to complete the query entry.

   A report template appears in which to place the data columns returned by the query.

6. Drag and drop the fields from the Field Explorer, in the upper right corner, into the report template as columns.

   Running the query displays the values associated with the fields. You can use Crystal Reports to create any visualization or customization you need.

7. (Optional) Compare report results against the out-of-the-box report, "Failed Activity by Performer."

Accessing Events from Crystal Reports with JDBC

The following tasks enable you to use JDBC to access the event log store:

1. Copy the JDBC JAR files for the client to the server on which Crystal Reports resides.
2. Edit the Crystal Reports configuration file.
3. Use Crystal Reports to send a query.

Note: This process and the related example assume that you are familiar with creating basic SQL statements and about how to use Crystal Reports. More information about using Crystal Reports is available in the BusinessObjects online help.

Copy the JDBC Driver JAR Files

Before you can use the JDBC driver to access events from a CA User Activity Reporting Module server, copy the related JAR files to the server you want to use for access.

To copy the files

1. Open the ISO image or access the Application installation DVD.
2. Navigate to the directory, \CA\ELM\JDBC.
3. Copy the JAR files to the server on which Crystal Reports resides.

   The report package you are using requires a specific location for these files. Consult the documentation supplied with your application.

4. Make a note of the directory where you place these files for reference path when configuring connections.

Use Crystal Reports to Access the Event Log Store with JDBC

You can use the JDBC access feature to query CA User Activity Reporting Module event data from a third party reporting tool like BusinessObjects Crystal Reports.

The database schema for the event log store is the common event grammar (CEG). The CA User Activity Reporting Module online help contains a CEG reference component to help you create queries. You can also review the underlying SQL statements for the out-of-the-box queries, but use ANSI SQL to access the database from outside CA User Activity Reporting Module.

To access event data from Crystal Reports

1. Start Crystal Reports and access the Standard Report Wizard.
2. Create a JDBC connection in the Data dialog.

   Note: Use the value, Default, for the Database name in the Connection Information dialog when it appears.

3. Use the Add Command functionality to create and run the following query in the SQL entry area:

   SELECT source_username as source_username , SUM(event_count) AS FUNC_SUM_event_count FROM view_event WHERE  event_result = 'F' GROUP BY source_username ORDER BY FUNC_SUM_event_count DESC;
   

4. Drag and drop the fields from the Field Explorer on the right into the report template as columns.

   Running the query displays the values associated with the fields. You can use the Crystal Reports tools to create any visualization or customization you need.

5. (Optional) Compare report results against the out-of-the-box report, "Failed Activity by Performer."

Remove the ODBC Client on Windows Systems

On all Windows platforms, the Remove option of the client installation package deletes product files and entries in the system information.

Important! If you created any IP source files under the Local ODBC Client installation directory, back the files up to a different location before removing the ODBC client on a Windows system.

If you have the Local ODBC Client installed and want to install it in a different location, use the Remove option. Remove the installed the Local ODBC Client, then re-install it in the new location.

To remove the ODBC client

1. Access the Add or Remove Programs utility on the Windows Control Panel.
2. Locate and select the entry, CA User Activity Reporting Module ODBC Driver.
3. Click Remove.

Remove the JDBC Client

To uninstall the JDBC client, remove the installation directory.