Query extended network traffic logs

Important

This feature is in Private Preview. To try it, reach out to your Azure Databricks contact.

This article describes how to query extended network traffic logs to analyze outbound network traffic from serverless compute. For an overview of what the logs record and how the tables relate, see Extended network traffic logs.

Requirements

  • Your Azure Databricks account must be enrolled in the extended network traffic logs Private Preview. To request enrollment, contact your Azure Databricks account team.
  • After your account is enrolled, an account admin must turn on the preview from the account console Previews page. See Manage account-level previews.
  • You must have access to query system tables. See System tables reference.

Extended network traffic logs are available on any platform tier.

Query allowed network access events

The following query returns allowed outbound network access events from the last 24 hours, including the destination and its resolved IP addresses:

SELECT
  event_time,
  workspace_id,
  network_source_type,
  destination_type,
  destination,
  CASE
    WHEN destination_type = 'DNS'
      THEN dns_event.rdata
    WHEN destination_type = 'STORAGE'
      THEN storage_event.rdata
  END AS resolved_ips
FROM system.access.outbound_network
WHERE event_time >= current_timestamp() - INTERVAL 1 DAY
  AND access_type = 'ALLOWED'
ORDER BY event_time DESC;

Query traffic volumes by destination IP

The following query returns the total bytes sent and received through a private network gateway over the last 24 hours, grouped by workspace and destination IP address. Use it to find the destinations that receive the most outbound data.

SELECT
  workspace_id,
  destination_ip,
  connection_type,
  connection_method,
  SUM(sent_bytes) AS total_sent_bytes,
  SUM(received_bytes) AS total_received_bytes
FROM system.access.outbound_traffic
WHERE period_start_time >= current_timestamp() - INTERVAL 1 DAY
GROUP BY
  workspace_id,
  destination_ip,
  connection_type,
  connection_method
ORDER BY total_sent_bytes DESC;

Correlate access events with flow logs

To connect hostnames to traffic volumes, join system.access.outbound_network to system.access.outbound_traffic on account ID, workspace ID, and resolved destination IP address, where the access event's event_time falls within the flow log's interval. For details on how the tables relate, see How the tables relate.

Find allowed requests with matching private network gateway traffic

The following query adds a has_matching_png_traffic column to outbound network access events from the last 24 hours:

WITH params AS (
  SELECT
    current_timestamp() - INTERVAL 24 HOURS AS start_time,
    current_timestamp() AS end_time
),

network_events AS (
  SELECT n.*
  FROM system.access.outbound_network n
  CROSS JOIN params p
  WHERE n.event_time >= p.start_time
    AND n.event_time < p.end_time
),

allowed_ips AS (
  SELECT
    n.event_id,
    n.account_id,
    n.workspace_id,
    n.event_time,
    resolved_ip
  FROM network_events n
  LATERAL VIEW EXPLODE(
    CASE
      WHEN n.destination_type = 'DNS'
        THEN n.dns_event.rdata
      WHEN n.destination_type = 'STORAGE'
        THEN n.storage_event.rdata
      WHEN n.destination_type = 'IP'
        THEN ARRAY(n.destination)
      ELSE ARRAY()
    END
  ) e AS resolved_ip
  WHERE n.access_type = 'ALLOWED'
),

png_matches AS (
  SELECT DISTINCT
    n.account_id,
    n.workspace_id,
    n.event_id
  FROM allowed_ips n
  JOIN system.access.outbound_traffic t
    ON t.account_id = n.account_id
    AND t.workspace_id = n.workspace_id
    AND t.destination_ip = n.resolved_ip
    AND n.event_time >= t.period_start_time
    AND n.event_time < t.period_end_time
    AND t.connection_method = 'PRIVATE_NETWORK_GATEWAY'
  CROSS JOIN params p
  WHERE t.period_end_time > p.start_time
    AND t.period_start_time < p.end_time
)

SELECT
  n.*,
  CASE
    WHEN n.access_type <> 'ALLOWED' THEN NULL
    ELSE m.event_id IS NOT NULL
  END AS has_matching_png_traffic
FROM network_events n
LEFT JOIN png_matches m
  ON m.account_id = n.account_id
  AND m.workspace_id = n.workspace_id
  AND m.event_id = n.event_id;

The has_matching_png_traffic column has the following values:

Value Description
TRUE A matching private network gateway traffic record was found.
FALSE The allowed access event didn't have a matching private network gateway traffic record.
NULL The access event wasn't an ALLOWED event.

A value of FALSE doesn't necessarily mean the request used public or other private connectivity. A matching flow can also be missing because of logging limitations or differences in event timing. If two hostnames resolve to the same IP address, both are TRUE.

Estimate traffic volume by destination hostname

The following query estimates the traffic volume associated with each destination hostname over the last 24 hours. It matches the resolved IP addresses from access events to flow log destination IP addresses, then sums the bytes sent and received.

WITH params AS (
  SELECT
    current_timestamp() - INTERVAL 24 HOURS AS start_time,
    current_timestamp() AS end_time
),

allowed_ips AS (
  SELECT
    n.account_id,
    n.workspace_id,
    n.destination,
    n.event_time,
    resolved_ip
  FROM system.access.outbound_network n
  CROSS JOIN params p
  LATERAL VIEW EXPLODE(
    CASE
      WHEN n.destination_type = 'DNS'
        THEN n.dns_event.rdata
      WHEN n.destination_type = 'STORAGE'
        THEN n.storage_event.rdata
      WHEN n.destination_type = 'IP'
        THEN ARRAY(n.destination)
      ELSE ARRAY()
    END
  ) e AS resolved_ip
  WHERE n.access_type = 'ALLOWED'
    AND n.event_time >= p.start_time
    AND n.event_time < p.end_time
),

matched_flows AS (
  SELECT
    t.event_id,
    t.workspace_id,
    t.connection_method,
    MAX(t.received_bytes) AS received_bytes,
    MAX(t.sent_bytes) AS sent_bytes,
    COLLECT_SET(n.destination) AS destinations
  FROM allowed_ips n
  JOIN system.access.outbound_traffic t
    ON t.account_id = n.account_id
    AND t.workspace_id = n.workspace_id
    AND t.destination_ip = n.resolved_ip
    AND n.event_time >= t.period_start_time
    AND n.event_time < t.period_end_time
  CROSS JOIN params p
  WHERE t.period_end_time > p.start_time
    AND t.period_start_time < p.end_time
  GROUP BY
    t.event_id,
    t.workspace_id,
    t.connection_method
)

SELECT
  workspace_id,
  destinations,
  connection_method,
  SUM(received_bytes) AS total_received_bytes,
  SUM(sent_bytes) AS total_sent_bytes
FROM matched_flows
GROUP BY
  workspace_id,
  destinations,
  connection_method
ORDER BY total_sent_bytes DESC;

The results include the following columns:

  • workspace_id: Workspace where the traffic originated.
  • destinations: Destination hostnames or storage locations correlated with the traffic. This list has more than one entry when multiple hostnames share a destination IP address.
  • connection_method: Connectivity method used. The value is PRIVATE_NETWORK_GATEWAY.
  • total_received_bytes: Total bytes received by serverless compute across matching flow records.
  • total_sent_bytes: Total bytes sent by serverless compute across matching flow records.

To avoid counting a flow more than one time when multiple hostnames resolve to the same IP address, the query groups those hostnames into one array and counts each flow record's bytes one time. The results are an approximation and don't attribute exact bytes to each hostname when hostnames share an IP address. Traffic without a matching access event is excluded.

  • Last updated on