SQL Query Logging: /Documentation/Archive/21.11

SQL Query Logging

Documentation: Version 21.11

SQL Query Logging

Premium Feature — Available in the Professional and Enterprise Editions of LabKey Server. Learn more or contact LabKey.

You can configure external data sources to log details of each SQL query, including:

the user making the query
impersonation information, if any
date and time
the SQL statement used to query the data source

This topic describes how to use SQL Query Logging

Set Up

To configure a data source to log queries, add a Parameter element to the labkey.xml file. For example, the following template uses a data source named “mySqlDataSource".

<Resource name="jdbc/mySqlDataSource" 
        auth="Container"
        type="javax.sql.DataSource"
        username="myname"
        password="mypassword"
        driverClassName="com.mysql.cj.jdbc.Driver"
        url="jdbc:mysql://localhost:3306/?autoReconnect=true&amp;
          useUnicode=true&amp;characterEncoding=utf8&amp;zeroDateTimeBehavior=CONVERT_TO_NULL"
        maxTotal="20"
        maxIdle="10"
        maxWaitMillis="120000" 
        useInformationSchema="true"
        accessToUnderlyingConnectionAllowed="true"
        validationQuery="/* ping */"
    />

Add the following Parameter element. Note that ":LogQueries" has been appended to the name of the data source.

<Parameter name="mySqlDataSource:LogQueries" value="true"/>

The labkeyDataSource cannot be configured to log queries in this way. Doing so will cause a warning in the server log at startup -- then startup will proceed as normal.