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.