Mettre en corrélation des données à l'aide de jointures externes

Compatible avec :

Ce document décrit les jointures externes (jointure gauche et jointure droite). Les opérations de jointure permettent de mettre en corrélation et de combiner des données provenant de plusieurs sources en fonction de valeurs de champ communes. En combinant les événements liés à la sécurité et les entités associées dans une vue unique et complète, vous pouvez détecter les menaces et enquêter efficacement.

Contrairement aux jointures standards (internes), qui nécessitent des entrées correspondantes dans les deux sources de données, une jointure externe récupère tous les enregistrements d'un côté de la jointure, même s'il n'y a pas d'entrées correspondantes de l'autre côté. Les champs non mis en correspondance de l'autre côté sont généralement remplis avec null. Cela vous évite de perdre des données qui n'ont pas de correspondance.

Fonctionnement des jointures externes

Le concept de jointures externes dans YARA-L 2.0 est identique à celui des jointures externes SQL standard :

  • La jointure externe gauche conserve tous les enregistrements du côté gauche de la jointure.

  • La jointure externe droite conserve tous les enregistrements du côté droit de la jointure.

La syntaxe de jointure externe (jointure gauche et jointure droite) est compatible avec toutes les requêtes, avec et sans condition match. Le comportement fondamental d'une jointure externe est le même pour la recherche, les tableaux de bord et les règles de détection, bien que la syntaxe diffère selon le contexte :

  • Recherche et tableaux de bord : utilisez les mots clés explicites left join ou right join dans la section events.

  • Règles de détection : définissez la relation de jointure à l'aide d'un signe égal (=) dans la section events et spécifiez une condition non limitée dans la section condition (par exemple, #data_table >= 0 pour une jointure externe gauche ou #data_table = 0 pour une exclusion d'anti-jointure).

Comprendre la jointure externe gauche

Une jointure externe gauche (ou jointure gauche) conserve tous les enregistrements de la source de données du côté gauche (conservé) de la jointure, que vous utilisiez le mot clé left join dans la recherche ou une condition non limitée (#data_table >= 0) dans une règle de détection.

  • Si un enregistrement du côté gauche n'a pas de correspondance dans la table d'événements ou de données de droite, les champs du côté droit sont renvoyés en tant que null.

  • Implication de l'espace réservé : toute variable d'espace réservé utilisée dans la section match doit référencer un champ de l'événement de gauche pour garantir une agrégation précise des données sur l'ensemble des résultats.

Exemple de jointure gauche d'événement à événement

L'exemple suivant illustre une jointure externe gauche pour mettre en corrélation les événements de connexion utilisateur avec les événements de connexion réseau ultérieurs se produisant sur le même hôte. La jointure gauche garantit que tous les événements USER_LOGIN sont conservés dans l'ensemble de résultats. Si un événement NETWORK_CONNECTION correspondant ($e2) est trouvé, ses données sont associées. Si aucune correspondance n'est trouvée, les champs de $e2 sont null.


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

Définir l'événement de départ

L'exemple de requête suivant définit le côté gauche de la jointure ($e1), qui correspond à l'ensemble d'événements conservé dans le résultat final :

$e1.metadata.event_type = "USER_LOGIN"

Le tableau suivant représente le résultat de la requête, en identifiant l'ensemble initial d'événements de connexion utilisateur :

Type d'événement Nom d'hôte principal Adresse 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

Définir l'événement approprié

L'exemple de requête suivant définit le côté droit de la jointure ($e2), qui est l'ensemble d'événements mis en correspondance avec les événements de gauche :

$e2.metadata.event_type = "NETWORK_CONNECTION"

Le tableau suivant représente l'ensemble des événements de connexion réseau disponibles pour la mise en correspondance :

Type d'événement Nom d'hôte principal Adresse 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

Participez aux événements

avec une section de correspondance

L'exemple suivant illustre une requête de correspondance utilisant une jointure externe gauche sur le champ 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 jointure externe gauche garantit que chaque événement USER_LOGIN ($e1) est inclus dans l'ensemble de résultats final.

  • La valeur de l'espace réservé $host est attribuée à partir de $e1.principal.hostname. La jointure externe gauche garantit la présence de l'événement $e1, ce qui permet de s'assurer que la variable $host est toujours renseignée pour l'agrégation.

  • La règle regroupe les résultats par hôte pour une période de cinq minutes.

Résultat de la jointure

Les données obtenues montrent la combinaison des deux événements. Tous les enregistrements de la table de gauche ($e1) sont conservés, et les champs de la table de droite ($e2) sont définis sur null lorsqu'aucun nom d'hôte correspondant n'est trouvé (par exemple, pour server-db-03).

Type d'événement ($e1) Nom d'hôte principal ($host) Adresse IP ($e1) Type d'événement ($e2) Adresse IP ($e2) État de la mise en correspondance
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION 192.168.1.101 Correspondance trouvée
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION 192.168.1.101 Correspondance trouvée
USER_LOGIN server-db-03 10.0.0.50 null null Aucune correspondance
USER_LOGIN kiosk-4 192.168.1.104 NETWORK_CONNECTION 203.0.113.3 Correspondance trouvée

Exemples de requêtes de jointure gauche

Cette section fournit des exemples de requêtes de jointure gauche.

Jointures avec une condition de correspondance

  • Entité d'événement

    
$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

Jointures sans condition de correspondance

  • Événement-événement

    
$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é d'événement

    
$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

Jointures gauches dans les règles de détection

Dans les règles de détection, vous n'utilisez pas le mot clé left join dans la section events. À la place, vous associez un événement UDM à une table de données ou à un événement secondaire dans la section events à l'aide de l'égalité (=), et vous contrôlez le comportement de jointure dans la section condition :

Type de jointure Section events Section condition Comportement en cas d'événements sans correspondance
Jointure interne $key = %data_table.key $e and #data_table > 0 Les événements sans correspondance sont supprimés du flux de détection.
Jointure externe gauche (enrichissement) $key = %data_table.key $e and #data_table >= 0 Tous les événements de $e sont conservés. Les champs de table de données sans correspondance sont null.
Anti-jointure (exclusion) $key = %data_table.key $e and #data_table = 0 Déclenche une alerte uniquement lorsque la clé d'événement n'existe pas dans la table de données.

Exemple de jointure gauche d'un événement à une table de données dans une règle

L'exemple suivant utilise une table de données nommée corporate_asset_directory pour enrichir les événements avec la criticité des éléments et les métadonnées géographiques sans supprimer les événements des hôtes non gérés.

Définir la table de données

La table de données corporate_asset_directory contient les lignes suivantes, en utilisant hostname comme colonne de clé :

hostname (clé) 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

Comparer les conditions de jointure interne et de jointure externe gauche

  • Jointure interne (#corporate_asset_directory > 0) : si le nombre de tables de données est supérieur à 0, une jointure interne est effectuée. Si un événement provient d'un hôte non géré qui ne figure pas dans corporate_asset_directory (par exemple, unmanaged-byod-09), l'événement est filtré et aucune détection n'est générée :

    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
}

  • Jointure externe gauche (#corporate_asset_directory >= 0) : l'utilisation de la condition non limitée #corporate_asset_directory >= 0 effectue une jointure externe gauche. Tous les événements de $e sont conservés, même lorsque le nom d'hôte ne correspond pas à une ligne de corporate_asset_directory.

    Lorsqu'un événement ne correspond pas à une ligne de table de données, les références à %corporate_asset_directory.column sont évaluées à null. Dans la section outcome, utilisez strings.coalesce pour les colonnes de chaîne et les expressions if conditionnelles pour les colonnes numériques afin d'attribuer des valeurs de remplacement par défaut :

    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
}

Résultat de la jointure de règles

Le tableau suivant compare les valeurs de résultat obtenues pour les hôtes correspondants et non correspondants lors de l'utilisation de #corporate_asset_directory >= 0 :

Nom d'hôte principal ($hostname) État de la correspondance du tableau de données $country $asset_tier $risk_score
CH02-SRV Correspondance trouvée ["CH"] ["TIER_1_CRITICAL"] 80
ES01-W10 Correspondance trouvée ["ES"] ["TIER_3_WORKSTATION"] 20
unmanaged-byod-09 Aucune correspondance (null remplacé par les valeurs par défaut) ["UNKNOWN"] ["UNMANAGED"] 0

Exemples d'anti-jointure gauche (exclusion) dans les règles

Vous pouvez utiliser une jointure externe gauche avec une condition de décompte nul (#data_table = 0) pour effectuer une antijointure gauche. Ce modèle supprime les détections lorsqu'un événement correspond à une table de données d'exclusion et ne déclenche une alerte que lorsqu'aucune correspondance n'existe.

  • Exclusion d'une seule colonne (#authorized_scanners_exclusion = 0) : la règle suivante joint $e.principal.hostname à une table de données authorized_scanners_exclusion et ne se déclenche que lorsque l'hôte n'est pas présent dans la table :

    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
}

    Vous pouvez également compter les lignes de la table de données correspondantes dans la section outcome ($scanner_match_count = count(%authorized_scanners_exclusion.hostname)) et évaluer $e and $scanner_match_count = 0 dans la section 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
}

  • Exclusion de tuple de ligne multicolonne (#multi_column_admin_exceptions = 0) : pour exclure des événements uniquement lorsqu'une combinaison spécifique de plusieurs champs correspond à la même ligne d'une table de données d'exception, liez chaque champ d'événement à sa colonne correspondante dans la section events et spécifiez #data_table = 0 dans la section 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
}

Jointure externe droite

Une jointure externe droite (ou jointure droite) conserve tous les enregistrements de la source de données située à droite du mot clé right join.

  • Si un enregistrement de l'événement de droite ne correspond à aucun enregistrement de l'événement de gauche, les champs de l'événement de gauche sont renvoyés sous la forme null.

  • Implication de l'espace réservé : toute variable d'espace réservé utilisée dans la section match doit faire référence à un champ de l'événement de droite pour garantir une agrégation précise des données dans l'ensemble des résultats.

Exemple de jointure droite d'événement à événement

L'exemple suivant illustre un right outer join permettant de mettre en corrélation les événements de connexion des utilisateurs avec les événements de connexion réseau ultérieurs se produisant sur le même hôte. right join garantit que tous les événements NETWORK_CONNECTION sont conservés dans l'ensemble de résultats. Si un événement USER_LOGIN correspondant est trouvé, ses données sont jointes. Si aucune correspondance n'est trouvée, les champs de $e1 sont null.


$e1.metadata.event_type = "USER_LOGIN"

$e2.metadata.event_type = "NETWORK_CONNECTION"

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

Définir l'événement de gauche

La requête suivante définit le côté gauche de la jointure ($e1), qui est l'ensemble d'événements facultatif dans le résultat final :


$e1.metadata.event_type = "USER_LOGIN"

Le tableau suivant représente le résultat de la requête, en identifiant l'ensemble initial d'événements de connexion utilisateur :

Type d'événement Nom d'hôte principal Adresse 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

Définir l'événement approprié

La requête suivante définit le côté droit de la jointure ($e2), qui correspond à l'ensemble d'événements conservé dans le résultat final.


$e2.metadata.event_type = "NETWORK_CONNECTION"

Le tableau suivant représente l'ensemble des événements de connexion réseau disponibles pour la mise en correspondance.

Type d'événement Nom d'hôte principal Adresse 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

Participez aux événements

L'exemple suivant montre une requête de correspondance avec une jointure externe droite sur le champ 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

  • La jointure externe droite garantit que chaque événement NETWORK_CONNECTION ($e2) est inclus dans l'ensemble de résultats final.

  • La valeur de $e2.principal.hostname est attribuée à l'espace réservé $host. La jointure externe droite garantit la présence de l'événement $e2, ce qui permet de s'assurer que la variable $host est toujours renseignée pour l'agrégation.

  • La règle regroupe les résultats par hôte pour une période de cinq minutes.

Résultat de la jointure

L'ensemble de données obtenu affiche la combinaison des deux événements. Tous les enregistrements de la table de droite ($e2) sont conservés, et les champs de la table de gauche ($e1) sont définis sur "null" lorsqu'aucun nom d'hôte correspondant n'est trouvé (par exemple, vm-unauth-05).

Type d'événement ($e1) Nom d'hôte principal ($e1) Adresse IP ($e1) Type d'événement ($e2) Nom d'hôte principal ($host) Adresse IP ($e2) État de la mise en correspondance
USER_LOGIN workstation-01 192.168.1.101 NETWORK_CONNECTION workstation-01 192.168.1.101 Correspondance trouvée
USER_LOGIN laptop-hr-02 192.168.1.102 NETWORK_CONNECTION laptop-hr-02 192.168.1.101 Correspondance trouvée
null null null NETWORK_CONNECTION vm-unauth-05 203.0.113.4 Aucune correspondance

Exemples de requêtes de jointure droite

Cette section fournit des exemples de requêtes de jointure droite.

Jointures avec une condition de correspondance

  • Événement-événement

    
$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

  • Entité-événement

    
$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

Jointures sans condition de correspondance

  • Événement-événement

    
$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

  • Entité-événement

    
$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

Limites

Tenez compte des limites suivantes lorsque vous créez des jointures externes :

  • Une jointure externe complète (jointure gauche et jointure droite) n'est pas acceptée.

  • La période de la requête pour les jointures sans correspondance est limitée à 14 jours maximum.

  • Vous ne pouvez pas joindre directement deux sources contextuelles (par exemple, une entité directement à une table de données).

  • L'événement principal du modèle de données unifié (UDM) doit être le côté conservé de la jointure externe. La requête n'est pas valide si l'événement principal se trouve du côté "nullable".

    • Une jointure événement-entité doit être une jointure gauche. L'événement ($e1) est ainsi correctement conservé.

    • Une jointure entité-événement doit être une jointure droite. L'événement ($e1) est ainsi correctement conservé.

    L'exemple suivant n'est pas valide, car l'événement UDM ($e1) se trouve à gauche, mais la jointure droite conserve le côté droit ($g1), ce qui ne respecte pas la règle selon laquelle l'événement UDM doit être conservé :

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

  • Les mots clés explicites left join et right join de la section events ne sont pas compatibles avec les règles de détection. Ils ne sont compatibles qu'avec les recherches YARA-L et les tableaux de bord. Pour effectuer une jointure externe gauche ou une anti-jointure dans une règle de détection, utilisez une condition non limitée telle que #data_table >= 0 ou #data_table = 0 dans la section condition. Pour en savoir plus, consultez Jointures gauches dans les règles de détection.

Bonnes pratiques

Pour éviter les ralentissements et les délais d'expiration des requêtes dans les requêtes de jointure externe, utilisez des filtres spécifiques et précis.

Par exemple, une requête large comme celle-ci :


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

Vous pouvez l'optimiser en ajoutant des critères spécifiques comme suit :


$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

Vous avez encore besoin d'aide ? Obtenez des réponses de membres de la communauté et de professionnels Google SecOps.