외부 조인을 사용하여 데이터 상관관계 파악

다음에서 지원:

이 문서에서는 외부 조인(왼쪽 조인 및 오른쪽 조인)을 설명합니다. 조인 작업은 공통 필드 값을 기반으로 여러 소스의 데이터를 상호 연결하고 결합하는 데 사용됩니다. 관련 보안 이벤트와 항목을 하나의 포괄적인 뷰로 결합하면 효과적인 위협 감지 및 조사를 제공할 수 있습니다.

두 데이터 소스에 일치하는 항목이 필요한 표준(내부) 조인과 달리 외부 조인은 다른 쪽에 일치하는 항목이 없더라도 조인의 한쪽에서 모든 레코드를 검색합니다. 다른 쪽의 일치하지 않는 필드는 일반적으로 null로 채워집니다. 이렇게 하면 일치하지 않는 데이터가 손실되지 않습니다.

외부 조인 작동 방식

YARA-L 2.0의 외부 조인 개념은 표준 SQL 외부 조인과 동일합니다.

  • 왼쪽 외부 조인은 조인의 왼쪽에 있는 모든 레코드를 보존합니다.

  • 오른쪽 외부 조인은 조인의 오른쪽에 있는 모든 레코드를 보존합니다.

외부 조인 문법(왼쪽 조인 및 오른쪽 조인)은 match 조건이 있는 경우와 없는 경우 모두 모든 쿼리에 지원됩니다. 외부 조인의 기본 동작은 검색, 대시보드, 감지 규칙에서 동일하지만 문법은 컨텍스트에 따라 다릅니다.

  • 검색 및 대시보드: events 섹션에서 명시적 left join 또는 right join 키워드를 사용합니다.

  • 감지 규칙: events 섹션에서 등호(=)를 사용하여 조인 관계를 정의하고 condition 섹션에서 제한 없는 조건을 지정합니다(예: 왼쪽 외부 조인의 경우 #data_table >= 0, 안티 조인 제외의 경우 #data_table = 0).

왼쪽 외부 조인 이해하기

왼쪽 외부 조인(또는 왼쪽 조인)은 검색에서 left join 키워드를 사용하든 감지 규칙에서 무제한 조건(#data_table >= 0)을 사용하든 조인의 왼쪽(보존됨) 측에 있는 데이터 소스의 모든 레코드를 보존합니다.

  • 왼쪽의 레코드가 오른쪽 이벤트 또는 데이터 테이블에서 일치하지 않으면 오른쪽의 필드가 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"

다음 표는 사용자 로그인 이벤트의 초기 집합을 식별하는 쿼리 결과를 나타냅니다.

이벤트 유형 기본 호스트 이름 IP 주소
USER_LOGIN workstation-01 192.168.1.101
USER_LOGIN laptop-hr-02 192.168.1.102
USER_LOGIN server-db-03 10.0.0.50
USER_LOGIN kiosk-4 192.168.1.104

적절한 이벤트 정의

다음 쿼리 예시는 왼쪽 이벤트와 일치하는 이벤트 집합인 조인($e2)의 오른쪽을 정의합니다.

$e2.metadata.event_type = "NETWORK_CONNECTION"

다음 표는 일치에 사용할 수 있는 네트워크 연결 이벤트 집합을 나타냅니다.

이벤트 유형 기본 호스트 이름 IP 주소
NETWORK_CONNECTION workstation-01 192.168.1.101
NETWORK_CONNECTION laptop-hr-02 192.168.1.101
NETWORK_CONNECTION kiosk-4 203.0.113.3

이벤트 참여하기

일치 섹션 포함

다음 예시에서는 principal.hostname 필드에 왼쪽 외부 조인을 사용하는 일치 쿼리를 보여줍니다.


  $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

  • 왼쪽 외부 조인은 모든 USER_LOGIN 이벤트($e1)가 최종 결과 집합에 포함되도록 합니다.

  • 자리표시자 $host에는 $e1.principal.hostname의 값이 할당됩니다. 왼쪽 외부 조인은 이벤트 $e1의 존재를 보장하여 $host 변수가 항상 집계를 위해 채워지도록 합니다.

  • 이 규칙은 5분 동안 호스트별로 결과를 집계합니다.

조인 결과

결과 데이터에는 두 이벤트의 조합이 표시됩니다. 왼쪽 테이블($e1)의 모든 레코드가 유지되고 일치하는 호스트 이름이 없으면 오른쪽 테이블($e2)의 필드가 null로 설정됩니다(예: server-db-03).

이벤트 유형($e1) 기본 호스트 이름($host) IP 주소($e1) 이벤트 유형($e2) IP 주소($e2) 일치 상태
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION 192.168.1.101 일치 항목 있음
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION 192.168.1.101 일치 항목 있음
USER_LOGIN server-db-03 10.0.0.50 null null 일치하는 항목 없음
USER_LOGIN kiosk-4 192.168.1.104 NETWORK_CONNECTION 203.0.113.3 일치 항목 있음

왼쪽 조인 쿼리 예시

이 섹션에서는 왼쪽 조인 쿼리 예시를 제공합니다.

일치 조건으로 조인

  • 이벤트 항목

    
    $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
    

일치 조건이 없는 조인

  • 이벤트-이벤트

    
    $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
    
    
  • 이벤트 항목

    
    $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 키워드를 사용하지 않습니다. 대신 등호(=)를 사용하여 events 섹션에서 UDM 이벤트를 데이터 테이블 또는 보조 이벤트에 연결하고 condition 섹션에서 조인 동작을 제어합니다.

조인 유형 events 섹션 condition 섹션 일치하지 않는 이벤트에 대한 동작
내부 조인 $key = %data_table.key $e and #data_table > 0 일치하지 않는 이벤트는 감지 스트림에서 삭제됩니다.
왼쪽 외부 조인(보강) $key = %data_table.key $e and #data_table >= 0 $e의 모든 이벤트가 보존되고 일치하지 않는 데이터 테이블 필드는 null입니다.
안티 조인(제외) $key = %data_table.key $e and #data_table = 0 데이터 테이블에 이벤트 키가 없는 경우에만 알림을 트리거합니다.

규칙의 이벤트-데이터 테이블 왼쪽 조인 예시

다음 예시에서는 corporate_asset_directory라는 데이터 테이블을 사용하여 관리되지 않는 호스트의 이벤트를 삭제하지 않고 애셋 중요도 및 지리적 메타데이터로 이벤트를 보강합니다.

데이터 표 정의

corporate_asset_directory 데이터 테이블에는 hostname을 키 열로 사용하는 다음 행이 포함됩니다.

hostname(키) country_code country_name asset_tier owner_dept risk_score
ES01-W10 ES Spain TIER_3_WORKSTATION Finance 20
CH02-SRV CH Switzerland TIER_1_CRITICAL Engineering 80
US03-LAP US United States TIER_2_PRODUCTION Executive 50

내부 조인 및 왼쪽 외부 조인 조건 비교

  • 내부 조인(#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
    }
    
  • 왼쪽 외부 조인(#corporate_asset_directory >= 0): 제한 없는 조건 #corporate_asset_directory >= 0을 사용하면 왼쪽 외부 조인이 수행됩니다. 호스트 이름이 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를 사용할 때 일치하는 호스트와 일치하지 않는 호스트의 결과 값을 비교합니다.

기본 호스트 이름($hostname) 데이터 테이블 일치 상태 $country $asset_tier $risk_score
CH02-SRV 일치 항목 있음 ["CH"] ["TIER_1_CRITICAL"] 80
ES01-W10 일치 항목 있음 ["ES"] ["TIER_3_WORKSTATION"] 20
unmanaged-byod-09 일치하는 항목 없음(null이(가) 기본값으로 대체됨) ["UNKNOWN"] ["UNMANAGED"] 0

규칙의 왼쪽 안티 조인(제외) 예시

0개 조건(#data_table = 0)이 있는 왼쪽 외부 조인을 사용하여 왼쪽 안티 조인을 수행할 수 있습니다. 이 패턴은 이벤트가 제외 데이터 테이블과 일치할 때 감지를 억제하고 일치하는 항목이 없는 경우에만 알림을 트리거합니다.

  • 단일 열 제외(#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 섹션에 #authorized_scanners_exclusion >= 0를 포함하여 컴파일러가 왼쪽 외부 조인($e and #authorized_scanners_exclusion >= 0 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 #authorized_scanners_exclusion >= 0 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
    }
    

오른쪽 외부 조인

오른쪽 외부 조인(또는 오른쪽 조인)은 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

왼쪽 이벤트 정의

다음 쿼리는 최종 결과의 선택적 이벤트 집합인 조인($e1)의 왼쪽을 정의합니다.


$e1.metadata.event_type = "USER_LOGIN"

다음 표는 초기 사용자 로그인 이벤트 집합을 식별하는 쿼리 결과를 나타냅니다.

이벤트 유형 기본 호스트 이름 IP 주소
USER_LOGIN workstation-01 192.168.1.101
USER_LOGIN laptop-hr-02 192.168.1.102
USER_LOGIN server-db-03 10.0.0.50

적절한 이벤트 정의

다음 쿼리는 조인의 오른쪽($e2)을 정의합니다. 이는 최종 결과에 보존되는 이벤트 집합입니다.


$e2.metadata.event_type = "NETWORK_CONNECTION"

다음 표는 일치에 사용할 수 있는 네트워크 연결 이벤트 집합을 나타냅니다.

이벤트 유형 기본 호스트 이름 IP 주소
NETWORK_CONNECTION workstation-01 192.168.1.101
NETWORK_CONNECTION laptop-hr-02 192.168.1.101
NETWORK_CONNECTION vm-unauth-05 203.0.113.3

이벤트 참여하기

다음 예시에서는 principal.hostname 필드에 오른쪽 외부 조인이 있는 일치 쿼리를 보여줍니다.


$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

  • 오른쪽 외부 조인은 모든 NETWORK_CONNECTION 이벤트($e2)가 최종 결과 집합에 포함되도록 합니다.

  • 자리표시자 $host에는 $e2.principal.hostname의 값이 할당됩니다. 오른쪽 외부 조인은 이벤트 $e2가 있는지 확인하여 집계를 위해 $host 변수가 항상 채워지도록 합니다.

  • 이 규칙은 5분 동안 호스트별로 결과를 집계합니다.

조인 결과

결과 데이터 세트에는 두 이벤트의 조합이 표시됩니다. 오른쪽 테이블($e2)의 모든 레코드가 유지되고 일치하는 호스트 이름이 없으면 왼쪽 테이블($e1)의 필드가 null로 설정됩니다(예: vm-unauth-05).

이벤트 유형($e1) 기본 호스트 이름($e1) IP 주소($e1) 이벤트 유형($e2) 기본 호스트 이름($host) IP 주소($e2) 일치 상태
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION workstation-01 192.168.1.101 일치 항목 있음
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION laptop-hr-02 192.168.1.101 일치 항목 있음
null null null NETWORK_CONNECTION vm-unauth-05 203.0.113.4 일치하는 항목 없음

오른쪽 조인 쿼리 예시

이 섹션에서는 오른쪽 조인 쿼리 예시를 제공합니다.

일치 조건으로 조인

  • 이벤트-이벤트

    
    $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
    
    

일치 조건이 없는 조인

  • 이벤트-이벤트

    
    $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
    
    

제한사항

외부 조인을 만들 때는 다음 제한사항을 고려하세요.

  • 전체 외부 조인(왼쪽 조인 및 오른쪽 조인)은 지원되지 않습니다.

  • 일치하지 않는 조인의 쿼리 기간은 최대 14일로 제한됩니다.

  • 두 개의 컨텍스트 소스를 직접 조인할 수 없습니다(예: 항목을 데이터 테이블에 직접 조인).

  • 기본 통합 데이터 모델(UDM) 이벤트는 외부 조인의 보존된 측면이어야 합니다. 기본 이벤트가 'null 허용' 측에 있으면 쿼리가 잘못된 것입니다.

    • 이벤트-항목 조인은 왼쪽 조인이어야 합니다. 이렇게 하면 이벤트($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와 같은 무제한 조건을 사용합니다. 자세한 내용은 감지 규칙의 왼쪽 조인을 참조하세요.

권장사항

외부 조인 쿼리에서 성능 저하 및 쿼리 시간 초과를 방지하려면 구체적이고 좁은 필터를 사용하세요.

예를 들어 다음과 같은 광범위한 쿼리가 있습니다.


$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 전문가에게 문의하여 답변을 받으세요.