Administration Guide › Accessing Collected Events with ODBC and JDBC › Accessing Events from Crystal Reports with JDBC › Use Crystal Reports to Access the Event Log Store with JDBC

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."