Description

The EssbaseRightLog PERL script and related executable parse ANY Oracle Hyperion Essbase® v.5 - v.11.1.1 server or application logs and generates a full, custom delimited, output for enhanced analysis (database, spreadsheet).

EssbaseRightLog is intended to help Essbase® and Planning® database administrators and developers. Any Essbase® generated log may be parsed whatever the delimiter defined in Essbase.cfg config file (please refer to DELIMITEDMSG TRUE, DELIMITER in Oracle’s Essbase® Technical Reference). The idea was to make the logs really readable (one liner) by facilitating their integration and filtering in a spreadsheet (common logical delimiter). Reconciled logs may be inserted as well in a relational database for querying or statistics production.

EssbaseRightLog_vxx.exe is a compiled version of EssbaseRightLog_vxx.pl. The executable is generated since version 2.6.0 with PERL Archive module available on CPAN (PAR 0.980).

Options available

• Advanced date formatting (US, European or standard ISO),
• Headers insertion,
• Detailed message categories (please refer to Essbase Server and Application Log Message Categories in DBAG),
• String filtering,
• Custom separator,
• Export to Excel workbook (filtered and paned) whatever the size of the log file (supports logs with more than 65536 rows on Excel 2000/XP/2003).
• Query output, define your SQL queries in a config file.

Screenshots

http://roux.sebastien.googlepages.com/essbaserightlog

Availability

EssbaseRightLog is available in the downloads section as a single PERL script, which require PERL. It may then be run under Windows/UNIX/Linux environments.

Required PERL module: When running the PERL script the following magical PERL module will be required: Spreadsheet-WriteExcel More information at:

• http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel/
• http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html

EssbaseRightLog is available in the downloads section as a Win32 executable. Installation tip: unzip and copy executable to x:\Windows\System32 for a better integration with command line (DOS).

Instructions of use

USAGE: EssbaseRightLog -i <logfile(s)> [-o <outputfile>, -c, -d <arg>, -t, -s <arg>, -f <arg>, -x <outputfile>, -q, -h]

 -i specify Essbase log(s), args: <logfile1[;logfile2;...]>
 -o specify output file, arg: <outputfile>
 -c specify message categories
 -d specify date format, arg: <ISO|EUR|US>
 -t specify headers on top
 -s specify separator, arg: <*>
 -f specify filter (case sensitive), arg: <regex>
 -x specify Excel output, arg: <outputfile>
 -q specify query output
 -h display usage

Examples

Filtering: lately I just created a few batches with my own rules, find below some useful examples of what can be done.

• Trace calculations based on a regexp filter (-f "1013091.*Calculate|1012550.*Elapsed") from different applications (-i), inserting categories (-c) and headers (-t), and formatting date to European standard (-c EUR):

rightlog.exe -i Y:\App\App1\App1.log;X:\App\App2\App2.log -o c:\calcs_logs.txt -c -d EUR -t -f "1013091.*Calculate|1012550.*Elapsed"

• Trace every connection that has been done on your applications:

rightlog.exe -i Y:\App\App1\App1.log;X:\App\App2\App2.log -o c:\connections_logs.txt -c -d EUR -t -f 1013210

• Trace all Error or Warning strings that are present in your applications' logs:

rightlog.exe -i Y:\App\App1\App1.log;X:\App\App2\App2.log -o c:\ErrWar_logs.txt -c -d EUR -t -f "Error|Warning"

• Process different specified logs, output in Excel errors, warnings, dataload and calculation messages:

rightlog.exe -i Y:\App\App1\App1.log;X:\App\App2\App2.log;X:\App\App3\App3.log -x 
c:\AppEvent_lite.xls -c -d EUR -t -f "1012550.*Elapsed|Error|Warning|1013207|1003024"

Excel Export:

• Export to Excel workbook including headers, categories and ISO date formatting

rightlog.exe -i C:\Hyperion\Essbase93\Essbase.log -x c:\mylogs\Essbase20080326.xls -c -t -d ISO

Query output:

• Output stats to text file. On first run querycfg.xml config file will be createdif not existing, you may then edit it defining your own custom queries!

rightlog.exe -i C:\Hyperion\Essbase93\Essbase.log -q > c:\Essbase_stats.txt

About query output parameter (-q)

This options is made available thanks to SQLite embedded database. Logs are parsed and inserted into a table, queries are then applied to produce custom stats. Once that rightlog.db database file is created you may query it using many clients around (based on a free or a commercial licence). I use SQlite Manager which is a great Firefox plugin to query and manage SQLite database files. An ODBC driver is also available at http://www.ch-werner.de/sqliteodbc/

Current version and release notes

V.2.6 2008/09/14 - Added query output based on SQLite database. Query option forces categories (-c) display. First run with -q option will generate a querycfg.xml file with default SQL queries - fell free to modify it!

- forced Excel extension (.xls) on Excel export arg (-x) - forced header on Excel export arg (-x) - minor corrections

v.2.5.2 2008/03/26 - Added export to Excel paned and filtered workbook feature

- Updated usage text

v.2.2 2007/12/04 - Corrected display error in usage text

- Updated Essbase Server and Application Log Message Categories for Essbase 9.3.1 (see -c option)

To come...

• Stats generation (graphs) using SQLite database and Google Chart API.

From the same author

ssauditmerger - Merge your spreadsheet audit logs for better analysis