使用外部 join 建立資料關聯

本文說明外部 join (left join 和 right join)。彙整作業會根據共同欄位值，建立多個來源資料之間的關聯並加以合併。將相關的安全性事件和實體整合至單一、全面的檢視畫面，即可有效偵測及調查威脅。

標準 (內部) join 需要兩個資料來源都有相符的項目，而外部 join 則會從 join 的其中一側擷取所有記錄，即使另一側沒有相符的項目也一樣。另一端不相符的欄位通常會填入 null。這樣可以避免遺失不相符的資料。

外部彙整的運作方式

YARA-L 2.0 的外部彙整概念與標準 SQL 外部彙整相同：

• 左外部 join 會保留 join 左側的所有記錄。

• 右外部 join 會保留 join 右側的所有記錄。

所有查詢 (無論是否包含 match 條件) 都支援外部 join 語法 (left join 和 right join)。外部彙整的基本行為在搜尋、資訊主頁和偵測規則中都相同，但語法會因情境而異：

• 搜尋和資訊主頁：在 events 區段中使用明確的 left join 或 right join 關鍵字。

• 偵測規則：在 events 區段中使用等號 (=) 定義彙整關係，並在 condition 區段中指定無限制條件 (例如，左外部彙整使用 #data_table >= 0，或反彙整排除使用 #data_table = 0)。

瞭解左外部 join

無論您在搜尋中使用 left join 關鍵字，還是在偵測規則中使用無限制條件 (#data_table >= 0)，左外部 join (或左 join) 都會保留 join 左側 (保留) 資料來源的所有記錄。

• 如果左側記錄在「右側」事件或資料表中沒有相符項目，右側欄位會傳回 null。

• 預留位置含義：match 區段中使用的任何預留位置變數，都必須參照左側事件中的欄位，確保整個結果集的資料匯總準確無誤。

事件對事件左連結範例

以下範例示範左外部聯結，將使用者登入事件與在同一主機上發生的後續網路連線事件建立關聯。左連結可確保結果集中保留所有 USER_LOGIN 事件。如果找到相符的 NETWORK_CONNECTION 事件 ($e2)，系統就會彙整其資料。如果找不到相符項目，$e2 的欄位會是 null。

$e1.metadata.event_type = "USER_LOGIN"
$e2.metadata.event_type = "NETWORK_CONNECTION"
left join $e1.principal.hostname = $e2.principal.hostname

定義離開事件

以下查詢範例定義了彙整作業的左側 ($e1)，也就是最終結果中保留的事件集：

$e1.metadata.event_type = "USER_LOGIN"

下表代表查詢結果，找出初始的使用者登入事件組合：

定義合適的事件

以下查詢範例定義了彙整作業的右側 ($e2)，也就是與左側事件比對的事件集：

$e2.metadata.event_type = "NETWORK_CONNECTION"

下表列出可用於比對的網路連線事件組合：

參加活動

包含比對部分

以下範例示範如何使用 principal.hostname 欄位上的 left outer join 執行比對查詢：

  $e1.metadata.event_type = "USER_LOGIN"

  $e2.metadata.event_type = "NETWORK_CONNECTION"

  left join $e1.principal.hostname = $e2.principal.hostname

  $host = $e1.principal.hostname

  match:
    $host over 5m

• left outer join 可確保每個 USER_LOGIN 事件 ($e1) 都包含在最終結果集中。

• 預留位置 $host 會獲派 $e1.principal.hostname 的值。左外部聯結可確保事件 $e1 存在，確保 $host 變數一律填入匯總。

• 規則會依主機匯總 5 分鐘時間範圍內的結果。

Join 結果

產生的資料會顯示這兩個事件的組合。左側資料表 ($e1) 的所有記錄都會保留，如果找不到相符的主機名稱 (例如 server-db-03)，右側資料表 ($e2) 的欄位會設為 null。

左側彙整查詢範例

本節提供左側彙整查詢範例。

使用比對條件進行彙整

• Event-entity

  
  $e1.metadata.event_type = "NETWORK_CONNECTION"
  $g1.graph.metadata.entity_type = "ASSET"
  left join $e1.principal.asset.hostname = $g1.graph.entity.asset.hostname
  $host = $e1.principal.asset.hostname
  
  match:
    $host over 5m
  
  

• Event-datatable

  
  $host = $e1.principal.hostname
  left join $e1.principal.hostname = %all_dt_column_types.hostname
  
  match:
    $host by 5m
  

沒有比對條件的彙整

• Event-event

  
  $e1.metadata.event_type = "USER_LOGIN"
  $e1.principal.ip = "114.241.96.87"
  $e2.metadata.event_type = "NETWORK_CONNECTION"
  left join $e1.principal.hostname = $e2.principal.hostname
  
  

• Event-entity

  
  $e1.metadata.event_type = "NETWORK_CONNECTION"
  $g1.graph.metadata.entity_type = "ASSET"
  left join $e1.principal.asset.hostname = $g1.graph.entity.asset.hostname
  $host = $e1.principal.asset.hostname
  
  

• Event-datatable

  
  $host = $e1.principal.hostname
  left join $e1.principal.hostname = %all_dt_column_types.hostname
  
  

偵測規則中的左側彙整

在偵測規則中，您不會在 events 區段中使用 left join 關鍵字。而是使用等號 (=) 將 UDM 事件連結至 events 區段中的資料表或次要事件，並在 condition 區段中控制彙整行為：

規則中的事件與資料表左連結範例

以下範例使用名為 corporate_asset_directory 的資料表，為事件加入資產重要性和地理中繼資料，同時保留來自非代管主機的事件。

定義資料表

corporate_asset_directory 資料表包含下列資料列，並使用 hostname 做為索引鍵資料欄：

比較內部彙整和左外部彙整條件

• 內部彙整 (#corporate_asset_directory > 0)：如果資料表數量大於 0，則會執行內部彙整。如果事件源自未列在 corporate_asset_directory 中的非代管主機 (例如 unmanaged-byod-09)，系統會篩除該事件，且不會產生偵測結果：

  rule inner_join_drops_unmatched_events {
    meta:
      author = "Google SecOps"
      description = "Inner join example: drops events whose hostname is not in the data table."
  
    events:
      $e.principal.hostname != ""
      $hostname = $e.principal.hostname
  
      // Direct inner join against data table
      $hostname = %corporate_asset_directory.hostname
  
    match:
      $hostname over 1h
  
    outcome:
      $country = array_distinct(%corporate_asset_directory.country_code)
      $asset_tier = array_distinct(%corporate_asset_directory.asset_tier)
      $risk_score = max(%corporate_asset_directory.risk_score)
  
    condition:
      $e and #corporate_asset_directory > 0
  }
  

• 左外部 join (#corporate_asset_directory >= 0)：使用無繫結條件 #corporate_asset_directory >= 0 會執行左外部 join。即使主機名稱與 corporate_asset_directory 中的資料列不符，$e 中的所有事件仍會保留。

  如果事件與資料表格列不相符，%corporate_asset_directory.column 的參照會評估為 null。在 outcome 區段中，使用 strings.coalesce 做為字串欄，並使用條件式 if 運算式做為數值欄，以指派預設備用值：

  rule left_outer_join_enrichment {
    meta:
      author = "Google SecOps"
      description = "Left outer join using #dt >= 0: preserves all events and populates enrichment fields."
  
    events:
      $e.principal.hostname != ""
      $hostname = $e.principal.hostname
  
      // Outer join equality binding
      $hostname = %corporate_asset_directory.hostname
  
    match:
      $hostname over 1h
  
    outcome:
      $country = array_distinct(strings.coalesce(%corporate_asset_directory.country_code, "UNKNOWN"))
      $asset_tier = array_distinct(strings.coalesce(%corporate_asset_directory.asset_tier, "UNMANAGED"))
      $risk_score = max(if(%corporate_asset_directory.risk_score > 0, %corporate_asset_directory.risk_score, 0))
  
    condition:
      $e and #corporate_asset_directory >= 0
  }
  

規則彙整結果

下表比較使用 #corporate_asset_directory >= 0 時，相符和不相符主機的結果值：

規則中的左反聯結 (排除) 範例

您可以使用左外部 join 搭配零計數條件 (#data_table = 0)，執行左反 join。當事件與排除資料表相符時，這個模式會抑制偵測，只有在沒有相符項目時才會觸發警告。

• 單欄排除 (#authorized_scanners_exclusion = 0)：下列規則會將 $e.principal.hostname 與 authorized_scanners_exclusion 資料表彙整，且只有在主機「未」出現在資料表中時才會觸發：

  rule anti_join_scanner_exclusion {
    meta:
      author = "Google SecOps"
      description = "Anti-join exclusion using #dt = 0: triggers only if the host is not in the authorized scanners table."
  
    events:
      $e.principal.hostname != ""
      $hostname = $e.principal.hostname
  
      // Bind join key to data table
      $hostname = %authorized_scanners_exclusion.hostname
  
    match:
      $hostname over 1h
  
    outcome:
      $total_sent_bytes = sum($e.network.sent_bytes)
  
    condition:
      $e and #authorized_scanners_exclusion = 0
  }
  

  或者，您也可以計算 outcome 區段 ($scanner_match_count = count(%authorized_scanners_exclusion.hostname)) 中相符的資料表資料列，並評估 condition 區段中的 $e and $scanner_match_count = 0：

  rule anti_join_outcome_count {
    meta:
      author = "Google SecOps"
      description = "Anti-join via outcome variable count check: triggers when scanner match count is zero."
  
    events:
      $e.principal.hostname != ""
      $hostname = $e.principal.hostname
  
      $hostname = %authorized_scanners_exclusion.hostname
  
    match:
      $hostname over 1h
  
    outcome:
      $total_sent_bytes = sum($e.network.sent_bytes)
      $scanner_match_count = count(%authorized_scanners_exclusion.hostname)
  
    condition:
      $e and $scanner_match_count = 0
  }
  

• 多欄資料列元組排除 (#multi_column_admin_exceptions = 0)： 如要僅在多個欄位的特定組合與例外狀況資料表中的同一列相符時排除事件，請將每個事件欄位繫結至 events 區段中的對應欄，並在 condition 區段中指定 #data_table = 0：

  rule multi_column_tuple_exclusion {
    meta:
      author = "Google SecOps"
      description = "Multi-column row-tuple anti-join: excludes activity matching composite (origin_ip, target_ip, username, action) rows."
  
    events:
      // Bind event fields to match variables
      $e.principal.ip = $src_ip
      $e.target.ip = $dst_ip
      $e.principal.user.userid = $user
      $e.metadata.product_event_type = $action
  
      // Multi-column equality against the same data table row
      $src_ip = %multi_column_admin_exceptions.origin_ip
      $dst_ip = %multi_column_admin_exceptions.target_ip
      $user = %multi_column_admin_exceptions.username
      $action = %multi_column_admin_exceptions.action
  
    match:
      $src_ip, $dst_ip, $user, $action over 5m
  
    outcome:
      $event_count = count($e.metadata.id)
  
    condition:
      $e and #multi_column_admin_exceptions = 0
  }
  

右外部 join

右外部彙整 (或右彙整) 會保留 right join 關鍵字「右側」資料來源的所有記錄。

• 如果右側事件的記錄與左側事件不相符，左側事件的欄位會傳回 null。

• 預留位置含義：match 區段中使用的任何預留位置變數，都必須參照正確事件中的欄位，確保整個結果集的資料匯總準確無誤。

事件對事件右側彙整範例

以下範例示範如何使用 right outer join，將使用者登入事件與後續在同一主機上發生的網路連線事件建立關聯。right join 可確保所有 NETWORK_CONNECTION 事件都會保留在結果集中。如果找到相符的 USER_LOGIN 事件，系統就會彙整其資料。如果找不到相符項目，$e1 的欄位會是 null。

$e1.metadata.event_type = "USER_LOGIN"

$e2.metadata.event_type = "NETWORK_CONNECTION"

right join $e1.principal.hostname = $e2.principal.hostname

定義離開事件

下列查詢定義了 join 的左側 ($e1)，也就是最終結果中的選用事件集：

$e1.metadata.event_type = "USER_LOGIN"

下表代表查詢結果，找出初始的使用者登入事件組合：

定義正確的事件

下列查詢定義了彙整作業的右側 ($e2)，也就是最終結果中保留的事件集。

$e2.metadata.event_type = "NETWORK_CONNECTION"

下表列出可用於比對的網路連線事件。

參加活動

以下範例顯示比對查詢，其中 principal.hostname 欄位採用右外部聯結：

$e1.metadata.event_type = "USER_LOGIN"

$e2.metadata.event_type = "NETWORK_CONNECTION"

right join $e1.principal.hostname = $e2.principal.hostname

$host = $e1.principal.hostname

match:
  $host over 5m

• right outer join 可確保最終結果集包含每個 NETWORK_CONNECTION 事件 ($e2)。

• 預留位置 $host 會獲派 $e2.principal.hostname 的值。右外部聯結可確保事件 $e2 存在，確保 $host 變數一律會填入資料以進行匯總。

• 規則會依主機匯總 5 分鐘時間範圍內的結果。

Join 結果

產生的資料集會顯示這兩項事件的組合。右側資料表 ($e2) 的所有記錄都會保留，如果找不到相符的主機名稱 (例如 vm-unauth-05)，左側資料表 ($e1) 的欄位會設為空值。

右側 JOIN 查詢範例

本節提供右側彙整查詢範例。

使用比對條件進行彙整

• Event-event

  
  $e1.metadata.event_type = "USER_LOGIN"
  
  $e2.metadata.event_type = "NETWORK_CONNECTION"
  
  right join $e1.principal.hostname = $e2.principal.hostname
  
  $host = $e2.principal.hostname
  
  match:
    $host over 5m
  
  

• 實體事件

  
  $e1.metadata.event_type = "NETWORK_CONNECTION"
  
  $g1.graph.metadata.entity_type = "ASSET"
  
  right join $g1.graph.entity.asset.hostname = $e1.principal.asset.hostname
  
  $host = $e1.principal.asset.hostname
  
  match:
    $host over 5m
  
  

• Datatable-event

  
  $host = $e1.principal.hostname
  
  right join %all_dt_column_types.hostname = $e1.principal.hostname
  
  match:
    $host by 5m
  
  

沒有比對條件的彙整

• Event-event

  
  $e1.metadata.event_type = "USER_LOGIN"
  
  $e1.principal.ip = "114.241.96.87"
  
  $e2.metadata.event_type = "NETWORK_CONNECTION"
  
  right join $e1.principal.hostname = $e2.principal.hostname
  
  

• 實體事件

  
  $e1.metadata.event_type = "NETWORK_CONNECTION"
  
  $g1.graph.metadata.entity_type = "ASSET"
  
  right join $g1.graph.entity.asset.hostname = $e1.principal.asset.hostname
  
  $host = $e1.principal.asset.hostname
  
  

• Datatable-event

  
  $host = $e1.principal.hostname
  
  right join %all_dt_column_types.hostname = $e1.principal.hostname
  
  

限制

建立外部 join 時，請注意下列限制：

• 不支援完整外部 join (left join 和 right join 結合)。

• 無相符項目的彙整查詢，日期範圍上限為 14 天。

• 您無法直接彙整兩個情境來源 (例如將實體直接彙整至資料表)。

• 主要統合式資料模型 (UDM) 事件必須是外部彙整的保留端。如果主要事件位於「可為空值」端，則查詢無效。

  • 事件實體彙整必須是左彙整。這樣就能正確保留事件 ($e1)。

  • 實體事件彙整必須是右彙整。這樣就能正確保留事件 ($e1)。

  以下範例無效，因為 UDM 事件 ($e1) 位於左側，但右側彙整會保留右側 ($g1)，這違反了必須保留 UDM 事件的規則：

  
  // Invalid query
  $e1.metadata.event_type = "NETWORK_CONNECTION"
  $g1.graph.metadata.entity_type = "ASSET"
  right join $e1.principal.asset.hostname = $g1.graph.entity.asset.hostname
  
  

• 偵測規則不支援 events 區段中的明確 left join 和 right join 關鍵字，僅 YARA-L 搜尋和資訊主頁支援。如要在偵測規則中執行左外部彙整或反彙整，請在 condition 區段中使用無限制條件，例如 #data_table >= 0 或 #data_table = 0。詳情請參閱「偵測規則中的左側彙整」。

最佳做法

為避免外部 join 查詢效能緩慢和查詢逾時，請使用特定且範圍較小的篩選器。

例如，像下面這樣廣泛的查詢：

$e1.metadata.event_type = "USER_LOGIN"
$e2.metadata.event_type = "NETWORK_CONNECTION"
right join $e1.principal.hostname = $e2.principal.hostname

可透過新增下列特定條件進行最佳化：

$e1.metadata.event_type = "USER_LOGIN"
$e1.principal.ip = "121.121.121.121"
$e1.principal.user.userid = "alex"
$e2.metadata.event_type = "NETWORK_CONNECTION"
$e2.src.hostname = "altostrat.com"
$e1.principal.hostname = $e2.principal.hostname

還有其他問題嗎？向社群成員和 Google SecOps 專業人員尋求解答。