Correlare i dati utilizzando i join esterni

Supportata in:

Questo documento descrive le outer join (left join e right join). Le operazioni di join vengono utilizzate per correlare e combinare i dati di più origini in base a valori di campo comuni. Combinando eventi ed entità di sicurezza correlati in un'unica visualizzazione completa, puoi fornire un rilevamento e un'indagine efficaci delle minacce.

A differenza dei join (interni) standard, che richiedono voci corrispondenti in entrambe le origini dati, un join esterno recupera tutti i record da un lato del join, anche se non esistono voci corrispondenti nell'altro. I campi non corrispondenti dell'altro lato vengono in genere compilati con null. In questo modo eviti di perdere i dati che non hanno una corrispondenza.

Come funzionano gli outer join

Il concetto di outer join in YARA-L 2.0 è identico agli outer join SQL standard:

  • La left outer join conserva tutti i record del lato sinistro del join.

  • Il right outer join conserva tutti i record del lato destro del join.

La sintassi di outer join (left join e right join) è supportata per tutte le query, sia con che senza una condizione match. Il comportamento fondamentale di un outer join è lo stesso per la ricerca, le dashboard e le regole di rilevamento, anche se la sintassi varia a seconda del contesto:

  • Ricerca e dashboard: utilizza le parole chiave esplicite left join o right join nella sezione events.

  • Regole di rilevamento: definisci la relazione di join utilizzando un segno di uguale (=) nella sezione events e specifica una condizione non limitata nella sezione condition (ad esempio, #data_table >= 0 per un left outer join o #data_table = 0 per un'esclusione anti-join).

Comprendi il left outer join

Un join esterno sinistro (o join sinistro) conserva tutti i record dell'origine dati sul lato sinistro (conservato) del join, indipendentemente dal fatto che utilizzi la parola chiave left join in Ricerca o una condizione non limitata (#data_table >= 0) in una regola di rilevamento.

  • Se un record del lato sinistro non ha corrispondenze nella tabella degli eventi o dei dati a destra, i campi del lato destro vengono restituiti come null.

  • Implicazione del segnaposto: qualsiasi variabile segnaposto utilizzata nella sezione match deve fare riferimento a un campo dell'evento di sinistra per garantire un'aggregazione dei dati accurata nell'intero set di risultati.

Esempio di left join da evento a evento

L'esempio seguente mostra un left outer join per correlare gli eventi di accesso degli utenti con i successivi eventi di connessione di rete che si verificano sullo stesso host. Il left join garantisce che tutti gli eventi USER_LOGIN vengano conservati nel set di risultati. Se viene trovato un evento NETWORK_CONNECTION ($e2) corrispondente, i relativi dati vengono uniti. Se non viene trovata alcuna corrispondenza, i campi per $e2 sono null.


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

Definisci l'evento di sinistra

Il seguente esempio di query definisce il lato sinistro del join ($e1), ovvero l'insieme di eventi che viene conservato nel risultato finale:

$e1.metadata.event_type = "USER_LOGIN"

La tabella seguente rappresenta il risultato della query, identificando l'insieme iniziale di eventi di accesso degli utenti:

Tipo di evento Nome host principale Indirizzo 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

Definisci l'evento giusto

L'esempio di query seguente definisce il lato destro dell'unione ($e2), ovvero l'insieme di eventi che viene confrontato con gli eventi di sinistra:

$e2.metadata.event_type = "NETWORK_CONNECTION"

La tabella seguente rappresenta l'insieme di eventi di connessione di rete disponibili per la corrispondenza:

Tipo di evento Nome host principale Indirizzo 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

Partecipa agli eventi

con una sezione di corrispondenza

L'esempio seguente mostra una query di corrispondenza che utilizza un left outer join nel campo 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

  • La left outer join garantisce che ogni evento USER_LOGIN ($e1) sia incluso nel set di risultati finale.

  • Al segnaposto $host viene assegnato il valore di $e1.principal.hostname. La left outer join garantisce la presenza dell'evento $e1, assicurando che la variabile $host sia sempre compilata per l'aggregazione.

  • La regola aggrega i risultati per host per un periodo di tempo di 5 minuti.

Risultato della join

I dati risultanti mostrano la combinazione dei due eventi. Tutti i record della tabella di sinistra ($e1) vengono conservati e i campi della tabella di destra ($e2) vengono impostati su null quando non viene trovato alcun nome host corrispondente (ad esempio per server-db-03).

Tipo di evento ($e1) Nome host principale ($host) Indirizzo IP ($e1) Tipo di evento ($e2) Indirizzo IP ($e2) Stato corrispondenza
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION 192.168.1.101 Corrispondenza trovata
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION 192.168.1.101 Corrispondenza trovata
USER_LOGIN server-db-03 10.0.0.50 null null Nessuna corrispondenza
USER_LOGIN kiosk-4 192.168.1.104 NETWORK_CONNECTION 203.0.113.3 Corrispondenza trovata

Esempi di query LEFT JOIN

Questa sezione fornisce esempi di query LEFT JOIN.

Join con una condizione di corrispondenza

  • Entità evento

    
$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

Join senza una condizione di corrispondenza

  • 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

  • Entità evento

    
$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

Left join nelle regole di rilevamento

Nelle regole di rilevamento, non utilizzi la parola chiave left join nella sezione events. Invece, colleghi un evento UDM a una tabella di dati o a un evento secondario nella sezione events utilizzando l'uguaglianza (=) e controlli il comportamento di join nella sezione condition:

Tipo di unione events sezione condition sezione Comportamento in caso di eventi non corrispondenti
Inner join $key = %data_table.key $e and #data_table > 0 Gli eventi non corrispondenti vengono eliminati dallo stream di rilevamento.
Left outer join (arricchimento) $key = %data_table.key $e and #data_table >= 0 Tutti gli eventi in $e vengono conservati; i campi della tabella di dati non corrispondenti sono null.
Anti-join (esclusione) $key = %data_table.key $e and #data_table = 0 Attiva un avviso solo quando la chiave evento non esiste nella tabella dati.

Esempio di join sinistro da evento a tabella di dati in una regola

L'esempio seguente utilizza una tabella di dati denominata corporate_asset_directory per arricchire gli eventi con la criticità degli asset e i metadati geografici senza eliminare gli eventi dagli host non gestiti.

Definisci la tabella di dati

La tabella di dati corporate_asset_directory contiene le seguenti righe, utilizzando hostname come colonna chiave:

hostname (chiave) 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

Confronta le condizioni di inner join e left outer join

  • Inner join (#corporate_asset_directory > 0): se il numero di tabelle di dati deve essere maggiore di 0, viene eseguito un inner join. Se un evento proviene da un host non gestito che non è elencato in corporate_asset_directory (ad esempio, unmanaged-byod-09), l'evento viene filtrato e non viene generato alcun rilevamento:

    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
}

  • Left outer join (#corporate_asset_directory >= 0): l'utilizzo della condizione non limitata #corporate_asset_directory >= 0 esegue un left outer join. Tutti gli eventi in $e vengono conservati anche se il nome host non corrisponde a una riga in corporate_asset_directory.

    Quando un evento non corrisponde a una riga della tabella dati, i riferimenti a %corporate_asset_directory.column restituiscono null. Nella sezione outcome, utilizza strings.coalesce per le colonne di stringhe ed espressioni if condizionali per le colonne numeriche per assegnare valori di riserva predefiniti:

    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
}

Risultato dell'unione delle regole

La tabella seguente mette a confronto i valori dei risultati per gli host corrispondenti e non corrispondenti quando si utilizza #corporate_asset_directory >= 0:

Nome host principale ($hostname) Stato della corrispondenza della tabella di dati $country $asset_tier $risk_score
CH02-SRV Corrispondenza trovata ["CH"] ["TIER_1_CRITICAL"] 80
ES01-W10 Corrispondenza trovata ["ES"] ["TIER_3_WORKSTATION"] 20
unmanaged-byod-09 Nessuna corrispondenza (null sostituito dai valori predefiniti) ["UNKNOWN"] ["UNMANAGED"] 0

Esempi di left anti-join (esclusione) nelle regole

Puoi utilizzare un left outer join con una condizione di conteggio pari a zero (#data_table = 0) per eseguire un left anti-join. Questo pattern sopprime i rilevamenti quando un evento corrisponde a una tabella di dati di esclusione e attiva un avviso solo quando non esiste una corrispondenza.

  • Esclusione di una singola colonna (#authorized_scanners_exclusion = 0): la seguente regola unisce $e.principal.hostname a una tabella di dati authorized_scanners_exclusion e si attiva solo quando l'host non è presente nella tabella:

    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
}

    In alternativa, puoi contare le righe della tabella dei dati corrispondenti nella sezione outcome ($scanner_match_count = count(%authorized_scanners_exclusion.hostname)) e valutare $e and $scanner_match_count = 0 nella sezione condition:

    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
}

  • Esclusione di tuple di righe su più colonne (#multi_column_admin_exceptions = 0): per escludere gli eventi solo quando una combinazione specifica di più campi corrisponde alla stessa riga in una tabella di dati di eccezione, associa ogni campo dell'evento alla colonna corrispondente nella sezione events e specifica #data_table = 0 nella sezione condition:

    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 outer join

Un join esterno destro (o right join) conserva tutti i record dell'origine dati sul lato destro della parola chiave right join.

  • Se un record dell'evento a destra non ha corrispondenze nell'evento a sinistra, i campi dell'evento a sinistra vengono restituiti come null.

  • Implicazione del segnaposto: qualsiasi variabile segnaposto utilizzata nella sezione match deve fare riferimento a un campo dell'evento corretto per garantire un'aggregazione dei dati accurata nell'intero set di risultati.

Esempio di right join da evento a evento

L'esempio seguente mostra una right outer join per correlare gli eventi di accesso degli utenti con i successivi eventi di connessione di rete che si verificano sullo stesso host. right join garantisce che tutti gli eventi NETWORK_CONNECTION vengano conservati nel set di risultati. Se viene trovato un evento USER_LOGIN corrispondente, i relativi dati vengono uniti. Se non viene trovata alcuna corrispondenza, i campi per $e1 sono null.


$e1.metadata.event_type = "USER_LOGIN"

$e2.metadata.event_type = "NETWORK_CONNECTION"

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

Definisci l'evento di uscita

La seguente query definisce il lato sinistro del join ($e1), ovvero l'insieme di eventi facoltativo nel risultato finale:


$e1.metadata.event_type = "USER_LOGIN"

La tabella seguente rappresenta il risultato della query, identificando l'insieme iniziale di eventi di accesso utente:

Tipo di evento Nome host principale Indirizzo 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

Definisci l'evento giusto

La seguente query definisce il lato destro del join ($e2), ovvero l'insieme di eventi che viene conservato nel risultato finale.


$e2.metadata.event_type = "NETWORK_CONNECTION"

La tabella seguente rappresenta l'insieme di eventi di connessione di rete disponibili per la corrispondenza.

Tipo di evento Nome host principale Indirizzo 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

Partecipa agli eventi

L'esempio seguente mostra una query di corrispondenza con un right outer join sul campo 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

  • Il right outer join garantisce che ogni evento NETWORK_CONNECTION ($e2) sia incluso nel set di risultati finale.

  • Al segnaposto $host viene assegnato il valore di $e2.principal.hostname. La right outer join garantisce la presenza dell'evento $e2, assicurando che la variabile $host sia sempre compilata per l'aggregazione.

  • La regola aggrega i risultati per host per un periodo di tempo di 5 minuti.

Risultato della join

Il set di dati risultante mostra la combinazione dei due eventi. Tutti i record della tabella di destra ($e2) vengono conservati e i campi della tabella di sinistra ($e1) vengono impostati su null se non viene trovato alcun nome host corrispondente (ad esempio, vm-unauth-05).

Tipo di evento ($e1) Nome host principale ($e1) Indirizzo IP ($e1) Tipo di evento ($e2) Nome host principale ($host) Indirizzo IP ($e2) Stato corrispondenza
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION workstation-01 192.168.1.101 Corrispondenza trovata
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION laptop-hr-02 192.168.1.101 Corrispondenza trovata
null null null NETWORK_CONNECTION vm-unauth-05 203.0.113.4 Nessuna corrispondenza

Esempi di query RIGHT JOIN

Questa sezione fornisce esempi di query RIGHT JOIN.

Join con una condizione di corrispondenza

  • 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

  • Entity-event

    
$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

Join senza una condizione di corrispondenza

  • 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

  • Entity-event

    
$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

Limitazioni

Tieni presenti le seguenti limitazioni quando crei outer join:

  • Un full outer join (left join e right join insieme) non è supportato.

  • L'intervallo di tempo della query per i join senza corrispondenza è limitato a un massimo di 14 giorni.

  • Non puoi unire direttamente due origini contestuali (ad esempio, un'entità direttamente a una tabella di dati).

  • L'evento principale di Unified Data Model (UDM) deve essere il lato conservato dell'outer join. La query non è valida se l'evento principale si trova sul lato "nullable".

    • Un join evento-entità deve essere un left join. In questo modo l'evento ($e1) viene conservato correttamente.

    • Un join entità-evento deve essere un join destro. In questo modo l'evento ($e1) viene conservato correttamente.

    L'esempio seguente non è valido perché l'evento UDM ($e1) si trova a sinistra, ma la join destra conserva il lato destro ($g1), il che viola la regola secondo cui l'evento UDM deve essere conservato:

    
// 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

  • Le parole chiave esplicite left join e right join nella sezione events non sono supportate nelle regole di rilevamento e sono supportate solo nelle ricerche YARA-L e nelle dashboard. Per eseguire un left outer join o un anti-join in una regola di rilevamento, utilizza una condizione non limitata come #data_table >= 0 o #data_table = 0 nella sezione condition. Per maggiori dettagli, consulta Left join nelle regole di rilevamento.

Best practice

Per evitare prestazioni lente e timeout delle query nelle query di outer join, utilizza filtri specifici e ristretti.

Ad esempio, una query generica come la seguente:


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

Può essere ottimizzato aggiungendo criteri specifici come segue:


$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

Hai bisogno di ulteriore assistenza? Ricevi risposte dai membri della community e dai professionisti di Google SecOps.