Part 9: Introduction to joins

References: - ClickHouse JOIN clause

Joins are probably the hardest concept to pick up for the kind of queries you will write in Hunt Lab, mostly because there are so many possibilities and it is difficult to hold a mental picture of what you are trying to join. They are also extremely powerful, letting you hunt for threats that need data from more than one table and would otherwise be impossible. They are well worth mastering, and nobody is expected to pick them up quickly.

Join types

INNER and FULL OUTER behave as they do in standard SQL. For left and right joins, use ClickHouse's ANY LEFT and ANY RIGHT forms, which return at most one matching row from the right-hand side.

Type	Returns	Use when
`INNER JOIN`	Only rows where the key matches in both tables	You only care about records that appear in both, for example processes that actually opened a socket
`ANY LEFT JOIN`	All rows from the left table, plus matches from the right where they exist	You want everything from the first table and any extra detail the second can add
`ANY RIGHT JOIN`	All rows from the right table, plus matches from the left where they exist	The mirror image. Useful for finding what exists in the second table but not the first
`FULL OUTER JOIN`	Everything from both, matched where possible	Rare in practice, and the most expensive

Where a row has no match, the missing columns come back empty rather than being dropped. That is the whole point of an outer join: the absence is the finding.

Joins are memory intensive. Set as narrow a time window as you can in the WHERE clause for every table you are joining, limit the join keys to those strictly needed, and return only the columns you actually require. A join without a time bound on both sides is the fastest way to exhaust the memory limit.

Time bound both sides like this:

WHERE
  p._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
  AND s._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000

That form is right for an inner join. For an outer join, the right-hand table's bound has to move into a sub-query instead, for the reason set out in 9a.

9a. Your first join

Write a query that joins the process and process socket tables, using the join keys id/pid and _hostname, requiring an exact match on both, using a join type that only returns results where a match occurs, in the past 24 hours. A limit of 50 to 500 is suggested.

Hint - which type

"Only where a match occurs" in both tables is the definition of an `INNER JOIN`.

Answer

SELECT
    formatDateTime(toDateTime(p._time_observed / 1000), '%Y-%m-%d %H:%i:%S') AS observed_time,
    p._hostname,
    p.name                  AS process_name,
    p.command,
    s.remote_address        AS dest_ip,
    s.remote_port           AS dest_port
FROM snson_telemetry.endpoint_process AS p
INNER JOIN snson_telemetry.endpoint_process_open_socket AS s
    ON  p.id        = s.pid
    AND p._hostname = s._hostname
WHERE
    p._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
    AND s._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
    AND s.remote_address != ''
    AND s.remote_port > 0
ORDER BY p._time_observed DESC
LIMIT 200

Modify the query so it returns all results from the first table, plus matching results from the second, but not all results from the second.

Answer

SELECT
    formatDateTime(toDateTime(p._time_observed / 1000), '%Y-%m-%d %H:%i:%S') AS observed_time,
    p._hostname,
    p.name                  AS process_name,
    p.command,
    s.remote_address        AS dest_ip,
    s.remote_port           AS dest_port
FROM snson_telemetry.endpoint_process AS p
ANY LEFT JOIN (
    SELECT pid, _hostname, remote_address, remote_port
    FROM snson_telemetry.endpoint_process_open_socket
    WHERE
        _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
        AND remote_address != ''
        AND remote_port > 0
) AS s
    ON  p.id        = s.pid
    AND p._hostname = s._hostname
WHERE
    p._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
ORDER BY p._time_observed DESC
LIMIT 200

You will now see rows where `dest_ip` and `dest_port` are empty. Those are processes that ran without opening a socket, which the inner join discarded entirely.

Conditions on the right-hand table belong in a sub-query. Notice that the socket table's filters moved inside the join. A process with no socket has no matching row, so its s. columns come back as defaults, and a WHERE clause testing s.remote_port > 0 or s._time_observed >= ... then discards it. That silently turns a left join back into an inner join: no error, just the rows you were looking for missing. Filter the right-hand table inside a sub-query, and keep the outer WHERE for left-hand conditions only.

9b. Joining authentication data

The username columns differ. It is auth_username in network_ntlm and username in network_kerberos. Alias both to the same name in their sub-queries, or the join key will not line up.

Write a query using a join that returns the number of NTLM and Kerberos failures per user, per day, and a total count of combined failures, where a user has had failures for both protocols. Filter so the total is greater than 250, in descending order of failures.

Hint - "both protocols"

Requiring the user to appear in both sides is again an `INNER JOIN`. Build each side as its own grouped sub-query first, then join the two results together.

Answer

SELECT
  n.Username,
  n.Date,
  n.NTLM_Failures,
  k.Kerberos_Failures,
  n.NTLM_Failures + k.Kerberos_Failures AS Total_Failures
FROM (
  SELECT
    auth_username AS Username,
    formatDateTime(toDateTime(_time_observed / 1000), '%F') AS Date,
    COUNT(*) AS NTLM_Failures
  FROM network_ntlm
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username, Date
) AS n
INNER JOIN (
  SELECT
    username AS Username,
    formatDateTime(toDateTime(_time_observed / 1000), '%F') AS Date,
    COUNT(*) AS Kerberos_Failures
  FROM network_kerberos
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username, Date
) AS k
  ON n.Username = k.Username AND n.Date = k.Date
WHERE Total_Failures > 250
ORDER BY Total_Failures desc
LIMIT 100

Modify the join type and the query, removing the 250 failure requirement and the date grouping, to filter for users having only Kerberos failures, or both Kerberos and NTLM failures, but not only NTLM failures. Keep the join order as NTLM first, then Kerberos.

Hint - keep the order, change the type

You need everything from the Kerberos side and only the matching NTLM rows. With NTLM written first, that is the right-hand table you want to keep whole.

Answer

SELECT
  k.Username,
  n.NTLM_Failures,
  k.Kerberos_Failures
FROM (
  SELECT
    auth_username AS Username,
    COUNT(*) AS NTLM_Failures
  FROM network_ntlm
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username
) AS n
ANY RIGHT JOIN (
  SELECT
    username AS Username,
    COUNT(*) AS Kerberos_Failures
  FROM network_kerberos
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username
) AS k
  ON n.Username = k.Username
ORDER BY k.Kerberos_Failures desc
LIMIT 100

Every Kerberos-failing user is returned. Those who also failed NTLM show a count in that column, and those who did not show an empty one. Users who failed only NTLM are excluded, because they do not exist on the right-hand side.

Modify the join to do the inverse of the previous question.

Answer

SELECT
  n.Username,
  n.NTLM_Failures,
  k.Kerberos_Failures
FROM (
  SELECT
    auth_username AS Username,
    COUNT(*) AS NTLM_Failures
  FROM network_ntlm
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username
) AS n
ANY LEFT JOIN (
  SELECT
    username AS Username,
    COUNT(*) AS Kerberos_Failures
  FROM network_kerberos
  WHERE
    _time_observed >= toUnixTimestamp(subtractDays(now(), 7)) * 1000
    AND success = 0
  GROUP BY Username
) AS k
  ON n.Username = k.Username
ORDER BY n.NTLM_Failures desc
LIMIT 100

Only the join type changes. Now every NTLM-failing user is returned, with Kerberos counts where they exist, and users who failed only Kerberos are excluded.

The pattern worth remembering: swapping ANY LEFT for ANY RIGHT swaps which population you are asking about. "Everyone who failed Kerberos, did they also fail NTLM?" and "everyone who failed NTLM, did they also fail Kerberos?" are different questions with different answers, and the join type is what picks between them.

Congratulations, you have completed the intermediate Hunt Lab questions. You can now group and filter aggregates, work with arrays, bucket data into time intervals, nest queries, and combine tables with unions and joins. That covers everything needed to answer the large majority of questions an analyst or customer will reasonably ask.

Great! Now you can move onto Part 10 of the Hunt Lab training.