Enabling Auditing

While previous versions of Crystal Enterprise wrote varying levels of information into various text file logs and Windows NT Event logs (as this version still does), it was hard, if not impossible , to have a concise , single-location source for system usage and load information. Version 10 adds this capability with auditing. Auditing allows you to write various pieces of usage information to a standard database that can then be queried and reported on (with Crystal Reports , of course). You can determine such things as license usage, report and folder access, which CE servers may be overloaded, and so forth.

The auditing process involves individual CE servers (such as the CMS, Cache Server, Report Job Server, and so forth) maintaining audit logs on their own as they process various report, logon, and viewing requests . These logs are written as text files onto the server s local hard disk in real time. At a pre-determined interval (every five minutes is the default), the CMS will poll each of these servers for their auditing data. Each server will send its audit logs to the CMS, which then organizes all audit data into a common format and writes it to a previously defined database. That database can be queried or reported against at will to gather Crystal Enterprise performance metrics.

Setting up and using auditing requires a few simple steps:

• An empty database must be created on a database server supported by the CMS.

• The CMS must be configured to write audit data to the newly created database.

• All servers in the CE environment that you wish to collect audit data must be configured to collect the specific events you wish to track.

• Reports must be created to extract data from the auditing database in a meaningful fashion (some sample reports are provided on the CE program CD).

Creating the Auditing Database/Configuring the CMS

The first step to enable auditing is to create a blank database on an available SQL database server for the CMS to use. The CMS will use the same type of database connection to write auditing data as it does to write its other data (such as folders, objects, user , and so on). However, you do not have to create the auditing database on the same server or using the same driver as the main CMS database.

As such, you must create the database on a supported CMS database server. These are Microsoft SQL Server via ODBC, Oracle via a native driver, DB2 via a native driver, Sybase via a native driver, or Informix via ODBC. Create the database using the database server s particular administrative tool (you may need to work with your Database Administrator to create the database). Size the database as you see fit, depending on how much auditing data you believe will be tracked, and how often you may choose to purge old audit data. You may give the database any name you choose, but don t define any tables in the database ”the CMS will do this automatically when it first connects to the database.

To configure the CMS to use the auditing database, you must use the Crystal Configuration Manager, or CCM (the CCM is discussed in more detail later in the chapter, under Managing Servers ). The CCM is a Windows/Unix application that is placed on the same physical computer as the CMS (and all other CE servers, for that matter). You must run the CCM from that physical server. In a Windows environment, the CCM can be started from the Crystal Enterprise 10 program group .

You must first stop the Crystal Enterprise CMS before you can configure the auditing database connection. Ensure that no critical users and processes are making use of the CMS. Then, select the Crystal Management Server in the CCM and either click the Stop button in the CCM toolbar or right-click the Crystal Management Server and choose Stop from the pop-up menu. Watch the status column for the CMS to show Stopped. Then, click the Specify Auditing Data Source toolbar button, or double-click the Crystal Management Server, and click the Configuration tab. If you display the Configuration tab, check Write Server Audit Information to Specified Data Source, and then click the Specify button. The Select Database Driver dialog box will appear.

Choose the desired database driver to connect to the auditing database you already created. Specify necessary information, such as server name, database name, user ID, password, and so forth. When finished, close any open dialog boxes. Restart the CMS by clicking the Start button in the CMC toolbar, or by right-clicking the Crystal Management Server and choosing Start from the pop-up menu. After a short delay, the CMS should start. You may then close the CMC. The CMS will connect to the auditing database and create an empty table named CMS_AUDITTABLE.

Selecting Audit Events on Various Servers

Now that the CMS auditing database is set up, you must specify which auditing events you wish the various servers in the CE environment to write to it. The first task you face when determining what to audit is determining which of the CE servers performs the desired task. For example, auditing of logon and logoff requests will be done by the CMS. Keeping track of scheduled reports that fail or succeed is the responsibility of the Report Job Server. And, while auditing of reports viewed with the DHTML viewer is done by the Page Server, the Report Application Server will audit reports viewed with the Interactive DHTML Viewer.

One way to familiarize yourself with which server performs what action is to review the Crystal Enterprise Architecture section earlier in this chapter. Also, a helpful table that breaks down auditable tasks by server can be found in the online Administrator s Guide. Look in the index for Auditing User and System Actions. You may also simply browse the Auditing tab on various servers within the Crystal Management Console to see which actions can be audited .

Once you re ready to turn auditing on for various actions and events, begin by clicking Servers in the Organize area of the main CMC screen. A list of all servers in your CE system will appear. Click the server you wish to set up auditing for. The Properties tab will appear. Click the Auditing tab to set up auditing.

Ensure that Auditing Is Enabled is checked. Typically, the default location for the server s local audit files should be sufficient. However, if you wish to customize the location where the server writes its temporary local audit files, make the necessary change in the Audit Log File Directory text box. Then, check all desired actions that you wish an audit entry to be made for. When you are finished, click Update.

The server will start writing auditing events to its local text files (you, or other users, will need to perform some auditable actions to force the server to write auditing information). At regular intervals (five minutes is the default), the CMS will query other servers for their audit data and will populate the audit database with the server s data. You may write reports against or query the audit database as you would any other production database.

Reporting Against the Auditing Database

Once you ve configured the processing servers and CMS to audit system activity, you ll want to set up a querying or reporting strategy to gather necessary data from the auditing database as needed. As the auditing database is based on an industry-standard SQL database, you can report against it with Crystal Reports as you can any other production database. You may either start with some sample reports supplied on the Crystal Enterprise CD, or design your own reports.

Look on the CE program CD in \samples\reports\AdminReports. You ll find sample reports that provide some basic sets of auditing data. You may open the reports in Crystal Reports and then use Database Set Datasource Location to point the reports to your particular auditing database. You may wish to modify these reports to your needs, or create your own new auditing reports from scratch.

Auditing data is contained in a single table, CMS_AUDITTABLE. The database schema for this table is described in the online Administrator s Guide. Check the index for Auditing Database Schema. You may simply use report design techniques described in Part I of this book to design your own auditing reports that provide you with metrics specific to your environment and auditing needs.

Tip  

Two idiosyncrasies of the Timestamp field in the auditing database may make reporting difficult. First, the field is defined as a string, not a date/time value. Second, the date/time stored in the string is in Coordinated Universal Time (five hours ahead of Eastern time). You may prefer to adjust the time for your local time zone, as well as converting the string field to a true date/time field. For example, you could create a formula similar to the following to create a date/time field in Mountain time (seven hours behind Coordinated Universal Time).

 DateTime(DateValue({CMS_AUDITTABLE.Timestamp}[1 to 10]), 
 TimeValue({CMS_AUDITTABLE.Timestamp}[12 to 19])) 
 - 7 / 24