Configure QLI for Version 1.5.0 or Newer

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

Core Connector Core connectors are included with all Alation platform tiers (subject to each tier’s connector limits) and are fully supported by Alation.

Important

This section is applicable for Alation version 2024.1 or higher and SQL Server OCF connector version 1.5.0 or higher.

Query log ingestion (QLI) extracts and ingests the query history of a database and powers the lineage, popularity, top user, and join and filter information in the catalog. Explore examples of ingested queries on schema and table catalog pages.

The steps involved in configuring and running QLI are:

1. Select a QLI Method

2. Test the Access

3. Preview Results

4. Run QLI

Select a QLI Method

For SQL Server data sources added using the SQL Server OCF connector, Alation supports the following QLI methods:

• View or Table

• XEvents

• Custom Query-Based

Configure View-Based QLI

To configure a view-based or table-based QLI, perform these steps:

• Create a view or table

• Provide the QLI view or table name in Alation

Create a View or Table

For information on creating a view or table for SQL Server Audit, see SQL Server Audit in SQL Server Connector.

For information on creating a view or table for SQL Server Audit RDS, see see SQL Server RDS.

For information on creating a view or table for Azure SQL Managed Instance (MI), see Azure SQL Managed Instance.

Provide the QLI View Name

Important

The Alation user interface displays standard configuration settings for credentials and connection information stored in the Alation database. If your organization has configured Azure KeyVault or AWS Secrets Manager to hold such information, the user interface will change to include the following buttons adjacent to the respective fields:

By default, you see the user interface for Standard. In the case of Vault, instead of the actual credential information, you must select the source and provide the corresponding key. For details, see Configure Secrets for OCF Connector Settings.

1. On the Settings page of your SQL Server data source, go to the Query Log Ingestion.

2. Click QLI view or table.

3. Enter the QLI view or table name in the View or Table name field.

   Important

   • Ensure that the service account has the permissions to access the view or table.

   • Use the format database_name.schema_name.view_name.

4. Click Save.

Configure XEvents

Before configuring XEvents in Alation user interface, complete the configuration described in the Configuration section of Extended Events in SQL Server Connector.

To configure XEvents in the Alation user interface, perform these steps:

1. On the Settings page of your SQL Server data source, go to the Query Log Ingestion.

2. Click XEvents.

3. Provide the absolute path to the .xel file on the server.

   If left blank, Alation uses the default path: C:\Users\Public\Documents\

   Note

   If you use the SQL user service account, provide sysadmin role to access the XEvents files.

4. Provide the prefix of your .xel file.

   If left blank, Alation uses the default prefix: alation_query_log.

   Important

   • Ensure that you provide a unique prefix. If you use common prefixes, the connector will ingest all the files that match the prefix. This may affect the QLI performance.

   • If you enable the extended events session, the files are created in the <prefix>_0_<creation_timestamp>.xel format. If you alter the file format, Alation will skip those files during query log ingestion.

5. Click Save.

Configure Custom Query-Based QLI

If you cannot create a view or table for QLI, for example, due to access restrictions, you can use a custom QLI query to extract query history into Alation.

To configure custom query-based QLI, you must:

• Use the QLI query template to create a query structure

• Provide the custom query in Alation

Use the QLI Query Template

SQL Server Audit

Use the following query structure:

SELECT
  <userNameColumn> AS userName,
  <textColumn> as queryString,
  <defaultDatabases> AS defaultDatabases,
  <sessionIdColumn> AS sessionId,
  <sessionStartTimeColumn> AS sessionStartTime,
  <StartTimeColumn> AS startTime,
  <QueryCancelledColumn> AS cancelled,
  <milliSecondsColumn> AS seconds
FROM <object_name>
    WHERE startTime BETWEEN 'STARTTIME' and 'ENDTIME'
    ORDER BY sessionId, startTime;

Note

<object_name> can be a view, table or procedure.

SQL Server RDS

Use the following query structure:

SELECT
          -- the following columns and values are required
  cat.server_principal_name as userName,
  cat.session_id as sessionId,
  cat.event_time as startTime,
  TRIM(seq.TextData) as queryString,
  cat.duration_milliseconds / 1000 as seconds,
  cat.database_name as defaultDatabases,
  cat.event_time as sessionStartTime,
  'N' as cancelled
  FROM dbo.alation_audit_logs cat
  -- SQL Server audit stores queries in a NVARCHAR(4000) wide column.
  -- Longer queries are broken up and stored with a sequencer. This JOIN
  -- puts them back together again before importing into Alation so
  -- they don't get discarded.
  INNER JOIN (
          SELECT sequence_group_id,
          STRING_AGG(CONVERT(NVARCHAR(max),statement),'') WITHIN GROUP (ORDER BY sequence_number ASC) AS TextData
          FROM dbo.alation_audit_logs
          GROUP BY sequence_group_id
          ) seq
  ON cat.sequence_group_id = seq.sequence_group_id
  WHERE cat.event_time BETWEEN (STARTTIME) AND (ENDTIME);

Note

For Automated QLI, the stored procedure must be run once before performing the QLI.

Azure SQL MI

Use the following query structure:

SELECT
  server_principal_name AS userName,
  event_time AS startTime,
  [statement] AS queryString,
  session_id AS sessionID,
  event_time AS sessionStartTime,
  duration_milliseconds AS milliseconds,
  'N' AS cancelled,
  database_name AS defaultDatabases
FROM sys.fn_get_audit_file('<qli_URL>', default, default);

Note

When using the QLI query template, do not substitute the STARTTIME and ENDTIME parameters in the WHERE filter. These are mandatory parameters. These parameters are not actual column names and should stay as is. They are expected by the connector and will be substituted with the start and end date of the QLI range selected in the user interface when QLI is run manually or on schedule.

Provide the Custom Query

1. On the SQL data source, go to the Query Log Ingestion tab of the Settings page.

2. Under the Select a QLI method step, go to Alternatively, use a custom SQL query.

3. In the Custom QLI Query field, provide a custom query to retrieve the query history.

4. Click Save.

Optimize Query Log Ingestion Volume

High QLI volume can slow down ingestion and lineage processing. Apply filters at any of the following layers to reduce the volume of ingested queries while preserving the data Alation needs for lineage, popularity, query history, and join and filter extraction.

Layer	Where to Configure	When Events Are Filtered
XE session	Extended Events session definition on SQL Server	Before events are written to .xel files (most efficient)
QLI view	The SQL view or table the connector queries	At query time during QLI
Custom QLI query	Connector user interface, in the custom query field	At query time during QLI

Exclude System Database Queries

This filter applies to the QLI view, custom QLI query, and XE session.

Queries against system databases (master, tempdb, msdb, model) do not contribute to lineage, popularity, or join and filter extraction.

The following example excludes system databases in a QLI view or custom query.

WHERE DatabaseName NOT IN ('master', 'tempdb', 'msdb', 'model')

The following example excludes system databases using an XE session predicate.

WHERE ([sqlserver].[database_name] <> N'master'
   AND [sqlserver].[database_name] <> N'tempdb'
   AND [sqlserver].[database_name] <> N'msdb'
   AND [sqlserver].[database_name] <> N'model')

Note

This filter has no lineage impact.

Filter by Application Name

This filter applies to the QLI view, custom QLI query, and XE session.

Exclude high-volume non-business applications such as monitoring agents, SQL Agent jobs, or reporting services.

The following example excludes applications in a QLI view or custom query using the ApplicationName column.

WHERE ApplicationName NOT IN (
    'SQLAgent - TSQL JobStep',
    'Microsoft SQL Server Management Studio',
    'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
  AND ApplicationName NOT LIKE 'Report Server%'

The following example excludes applications using an XE session predicate with the client_app_name action.

WHERE ([sqlserver].[client_app_name] <> N'SQLAgent - TSQL JobStep'
   AND [sqlserver].[client_app_name] <> N'Report Server')

Note

This filter has no lineage impact for non-business queries. If an excluded application runs ETL or data pipeline queries, for example SSIS, excluding it loses those lineage paths. Verify the application does not produce lineage-relevant queries before you exclude it.

Skip Short-Duration Queries

This filter applies to the XE session and QLI view.

Filter out very fast queries, which are typically metadata lookups or health checks. SQL Server measures duration in microseconds in both XEvents and native Profiler tables.

The following example skips queries shorter than 100 milliseconds, which is 100,000 microseconds, using an XE session predicate.

WHERE ([duration] > 100000)

The following example applies the same threshold in a QLI view or custom query.

WHERE Duration > 100000

Note

This filter has minimal lineage impact. Adjust the threshold based on your workload. Lineage-producing statements almost always exceed 100 milliseconds.

Exclude DDL Statements

This filter applies to the QLI view and custom QLI query.

If your use case focuses on query analytics rather than schema change tracking, you can exclude DDL statements.

The following example excludes a safe subset of DDL statements while preserving CREATE TABLE AS SELECT lineage.

WHERE queryString NOT LIKE 'ALTER%'
  AND queryString NOT LIKE 'DROP%'
  AND queryString NOT LIKE 'TRUNCATE%'

To also exclude CREATE statements, add the following condition.

AND queryString NOT LIKE 'CREATE%'

Important

• Do not exclude MERGE. It is a DML statement that produces lineage.

• CREATE TABLE ... AS SELECT and SELECT INTO produce lineage. Excluding CREATE statements loses that lineage.

• The NOT LIKE condition does not catch queries with leading whitespace or comments, for example /* comment */ CREATE TABLE.... Normalize the query text in the view if this applies to your environment.

Note

Excluding DDL statements removes schema creation and modification events from lineage. Alation preserves all DML lineage, including SELECT, INSERT, UPDATE, DELETE, and MERGE statements.

Caution

Do not aggregate QLI rows. Alation expects one row per query execution. For example, do not use GROUP BY queryString, userName with COUNT(*). Aggregating queries breaks ingestion because it loses per-execution metadata such as session ID, duration, and timestamp.

Do not sample QLI data. For example, avoid techniques such as WHERE session_id % 10 = 0. Sampling permanently excludes the same sessions. This produces incomplete lineage and undercounted popularity metrics. Use the targeted filters described in this section instead.

Note

Apply filters as close to the source as possible. XE session predicates stop SQL Server from writing events to disk, which reduces both storage and QLI processing time. After you change QLI filters, monitor lineage completeness in Alation to confirm the filters did not exclude critical query patterns. Review filters periodically as workload patterns change.

Test the Access

Before you perform the QLI, you must validate that the service account has access to the QLI view and gauge the approximate size of the query history metadata.

To test the access and find out the approximate size of the query history metadata, perform these steps:

1. On the Settings page of your SQL Server data source, go to the Query Log Ingestion tab.

2. Under the Test access section, click Test.

   A dialog box appears displaying the access validation result. Upon successful validation, the size of the query history is displayed.

   Available from connector version 1.8.3:

   When you configure a custom QLI query or a custom QLI table or view, the preflight check validates that the query returns the expected column aliases. Column alias matching is case-insensitive.

   • If a mandatory column is missing or misspelled, the check fails with an error. The error identifies the issue and suggests corrections.

   • If optional columns are missing, the check shows a warning. The warning explains which catalog features may be affected.

   • If neither a custom query nor a table or view name is configured, the connector skips this validation.

   • If the configured table uses the native SQL Server QLI format, the connector automatically retries the validation against the native column layout before reporting the result.

   Note

   If you have large query volumes, consider configuring QLI to run on a daily or more frequent schedule rather than attempting to process all data at once. This prevents timeout issues and ensures better performance.

Preview Results

Before performing the QLI, perform these steps to preview the queries:

1. On the Settings page of your SQL Server data source, click go to the Query Log Ingestion tab.

2. Under the Preview Results section, enter the date range for which you want to generate the preview of the query history.

3. Click Preview.

4. Click View Results to view the generated preview.

   The Preview dialog appears displaying the total number of query statements per user under the User Queries tab and a detailed query statement under the Statements tab. Click Download to download the detailed query statement as a JSON file.

Note

You can use this option to run default QLI using just the date-range.

Run QLI

You can either run QLI manually on demand or configure it to run automatically on a schedule.

Run QLI Manually

To perform QLI manually on demand:

1. On the Settings page of your SQL Server data source, go to the Query Log Ingestion tab.

2. Under the Run QLI section, turn off the Enable QLI Schedule toggle.

3. Specify the desired date range using the Date Range calendar widgets. You will need to specify the start date and the end date separately.

4. Click Import.

   A query log ingestion job is initiated.

Schedule QLI

1. On the Settings page of your SQL Server data source, go to the Query Log Ingestion tab.

2. Under the Run QLI section, turn on the Enable QLI Schedule toggle.

3. Specify values for the job recurrence and time. The values are set in your local time.

   

Note

Here are some of the recommended schedules for better performance, especially when dealing with large query volumes:

• Schedule QLI to run for every 12 hours at the 30th minute of the hour

• Schedule QLI to run for every 2 days at 11:30 PM

• Schedule QLI to run every week on the Sunday and Wednesday of the week

• Schedule QLI to run for every 3 months on the 15th day of the month

For databases with high query activity, use more frequent scheduling (every 12 hours or daily) to avoid processing large volumes of data in a single operation, which can lead to timeout issues.

4. Click Import.

The next QLI runs on the set schedule.

View the Job Status

To view the QLI job status after you run the QLI manually or after Alation triggers the QLI as per the schedule, go to Query Log Ingestion > QLI Job Status.

The Query log ingestion job status table logs the following status:

• Succeeded - Indicates that the query ingestion was successful.

• Partial Success - Indicates that the query ingestion was successful with warnings. If Alation fails to ingest some of the objects during the QLI, it skips them and proceeds with the query ingestion, resulting in partial success. Similarly, if all the queries are ingested by a single user, QLI results in a partial success.

• Failed - Indicates that the query ingestion failed with errors.

Click the View Details link to view a detailed report of query ingestion. Click the View Details link to view a detailed report of metadata extraction. If there are errors, the Job errors table displays the error category, error message, and a hint (ways to resolve the issue). Follow the instructions under the Hints column to resolve the error.