Skip to content

Query Logging (beta)

Introduction

CALUMO runs many queries on behalf of users against OLAP and Relational SQL Server data sources. Query Logging logs these queries for futher analysis where they are over the threshold configured.

The Query Log is enabled using the Beta Feature QueryLogging in the Server Configuration Features interface.

Settings

There are two settings in the CALUMO Server Configuration that are used to set the threshold in milliseconds at which to log a query to the Query Log, one for OLAP queries and one for SQL queries

When a query runs for longer than this amount of time it will write its query information to the Query Log.

Logged Properties

The information recorded in the query log is:

Property Description
CorrelationId This is a unique identifier used for a batch of queries that can be used to correlate these queries across other parts of CALUMO, like other Performance Events that are logged for Reports and Views
Duration This is the time in milliseconds that thequery took to execute and return a result
StartTimeLocal This is the time the query started in Server local time
StartTimeUTC This is the time the query started in UTC
EndTimeLocal This is the time the query ended in Server local time
EndTimeUTC This is the time the query ended in UTC
Error This flags whether or not there was an error associated with this queries execution
ErrorMessage If there was an error flagged, this will display the simplified error message if one exists
QueryContext This is a property that contains more detail on the execution as a JSON object, see Query Context
QuerySource This is the source of the query within CALUMO
QueryTarget This is a property that will be either SQL or OLAP depending on what query was run
QueryText This is the actula query text that was executed
Username This is the username of the user that executed the query

Query Context

The query context is a part of the details logged for a given query and aims to give more targetted information around the query that was run.

It can contain some of the following information:

Property Description
OlapDatasource The name of the OLAP datasource that was queried which will either be an alias name from the Datasources configured in the Server Configuration, or, a physical server name
OlapCatalog The name of the OLAP catalog that was queried
OlapCube The name of the OLAP cube that was queried
OlapCellCount The number of cells returned from the query
OlapRecordsAffected The number of records affected when an update was performed
SQLConnectionString The connection string used to connect to the SQL datasource. This will have the password removed/obfuscated
SQLDatasource The name of the SQL datasource that was queried which will be an alias name from the Connections configured in the Server Configuration
SQLDatabase The name of the database that was queried
SQLInserts Where a change was made, this will be the numebr of records that were inserted
SQLUpdates Where a change was made, this will be the numebr of records that were updated
SQLDeletes Where a change was made, this will be the numebr of records that were deleted
SQLRecordsAffected The number of records affected

Querying the Query Log

The Query Log is only available via direct query of the CALUMO databases QueryLog table.

To find related items from the Performance Events that CALUMO records into the <****>PerformanceEvent tables, use the CorrelationId to matchthe queries with their relevant Performance Event to get a better picture.

Maintenance

The Query Log has a clean up maintenance task that runs as a part of the general log clean up maintenance task to ensure that stored logs older than the configured threshold are removed.