Configure Query Log Ingestion

The Azure SQL DB data source supports custom query-based query log ingestion (QLI). QLI requires that auditing should be enabled in Azure.

Important

To use the QLI functionality, ensure that the service account has the CONTROL permission as it needs to access the sys.fn_get_audit_file. For details, refer to the corresponding Azure documentation: sys.fn_get_audit_file (Transact-SQL).

Important

Azure SQL Database Log Character Limit

Azure SQL Database audit logs store only the first 4,000 characters of a query or log entry. Queries or entries exceeding this limit will be truncated, and only the truncated data will appear in the logs or query history.

Enable Auditing in Azure

To enable auditing in Azure:

  1. Log in to the Azure portal and navigate to your Azure resource.

  2. Click on Auditing under Security.

  3. Switch Enable Azure SQL Auditing to ON.

  4. Select the Storage checkbox.

  5. Select your Subscription.

  6. Select a storage account. The storage account is a container to store the audit files. We recommend creating a new storage account for Alation. Select general purpose v1 as account kind.

  7. Expand Advanced properties.

  8. Set the appropriate retention period. For example, you can change the retention period to 1. This assumes that Alation ingests audit files daily—ensure that you select the corresponding configuration in the data source settings in Alation.

    • With the retention period set to 1, Azure will log one day of query data in the storage. When configuring query log ingestion in Alation, set the automatic QLI to run daily to ingest this batch of query history. We recommend a low value for the retention period to avoid filling up the storage account with logs and to reduce the cost for the resource. Keeping this number as low as possible also helps reduce the amount of logs to retrieve into Alation per each query log ingestion job.

    • If the retention period is set to 0, Azure will store the logs until they get manually deleted (0 is indefinite storage). This is not a recommended setting.

  9. Click on Save. Auditing is now enabled and audited data will be streamed to a binary flat file format (.xel).

    Note

    The .xel file is stored in the container that was selected in step 6.

Configure QLI in Alation

QLI can be configured on the Query Log Ingestion tab of the data source Settings page. The template for the QLI query is given below. You can customize it by adding, removing, or changing the filter, but the columns and their aliases must remain as is since Alation expects this query structure.

Note

  • When using the example below, substitute the placeholder <container_URI> with the container URI you find in your container properties, for example: https://testaccountvipra.blob.core.windows.net/sqldbauditlogs/my_database/.

  • Do not substitute the STARTTIME and ENDTIME parameters in the WHERE filter. 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.

To configure query-based QLI:

  1. Go to the Query Log Ingestion tab of the Settings page of your OCF data source.

  2. Under Connector Settings > Query Extraction, in the Custom QLI Query field, specify the QLI query.

  3. Click Save.

QLI Query Template

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('<container_URI>', default, default)
WHERE event_time BETWEEN (STARTTIME) AND (ENDTIME);

Perform QLI

You can either perform QLI manually on demand or enable automated QLI:

  1. To perform manual QLI, under the Automated and Manual Query Log Ingestion section of the Query Log Ingestion tab, ensure that the Enable Automated Query Log Ingestion toggle is disabled.

    Note

    Metadata extraction must be completed first before running QLI.

  2. Click Preview to get a sample of the query history data to be ingested.

  3. Click the Import button to perform QLI on demand.

  4. To schedule QLI, enable the Enable Automated Query Log Ingestion toggle.

  5. Set a schedule under Automated Query Log Ingestion Time by specifying values in the week, day, and time fields. The next QLI job will run on the schedule you have specified.

    Note

    The hourly schedule for automated QLI is not supported.