Analyze Spotfire® Server SQL Logs in Spotfire®

Introduction

Spotfire® Server outputs many useful logs that can be hard to analyze.  The SQL log files output one is reading data from JDBC data sources using Information Services has useful timing and query information.  The log files are in the <install directory>/tomcat/logs and called sql.log, sql.log.1, sql.log.2, etc.  The most recent log is sql.log with the logs rolling over to sql.log.1, sql.log.2, etc.

Oftentimes, it would be useful to analyze the timings and other information in these log files, but, since the logs include SQL query information on multiple lines, they can be difficult to convert into a data table for use in Spotfire.

The included Spotfire Analysis file, ProcessSQLLogFiles.dxp, includes an IronPython script that reads selected SQL Log files, converts them to a data table, and then shows that data in some simple visualizations.  The analysis files will not work in the Web Player because the "Select SQL Logs..." button uses the Windows FileOpenDialog which cannot open in the Web Player. 

The data table includes all the information from the SQL Log files including the queries and columns.  Each complete job is a single row in the resulting data table.  Since the SQL Logs can roll over, one log file may not have all the information for a single job.  For example, the job starts information may be in the sql.log.1 file, but the job was completed in the sql.log file.  If both files are selected, then the information will be complete, but, if only one of the files is processed, then the information will not be complete.

Using the ProcessSQLLogFiles Analysis File

In order to use the attached analysis file, one needs to have Spotfire 7.11 or above.  The following is a short guide on how to use it:

Step 1: Open the Analysis and trust the IronPython scripts

Step 2: On the Load Data tab, enter the name of the Spotfire Server from which the logs come.  Click the Select SQL Logs... button to browse and select logs to be listed in the "Selected SQL Logs to Process" multi-line textbox:

Step 3: After selecting the log files, click the "Process Selected SQL Log Files" and the logs will be read in, converted to a datatable, and the second tab "Queries over Time" will become the active tab.

Hopefully, others will find this processing of SQL Logs helpful.  One can look at particular Information Links over time and see if their timings change.  The Action Logs have some of the timing information as well, but one may not always have the Action Logs available.

References

• Spotfire Server Action Logs

Revision History

• Original Version July 2018
• Fixed ProcessSQLLogFiles Dxp script May 2019

processsqllogfiles_1.dxp