Part 9: Introduction to joins
References: - ClickHouse JOIN clause
For the queries that you write in Hunt Lab, joins are probably the most difficult concept to learn. The main cause is the large number of possibilities. Also, it is difficult to see in your mind what you try to join.
Joins are also extremely useful. With joins, you can find threats that you cannot find without data from more than one table. It is worth the effort to learn joins well. Nobody expects you to learn them quickly.
Join types
INNER and FULL OUTER operate as they do in standard SQL. For left and right joins, use the ANY LEFT and ANY RIGHT forms of ClickHouse. For each row of the left table, ANY LEFT returns a maximum of one row that matches from the right table. For each row of the right table, ANY RIGHT returns a maximum of one row that matches from the left table.
| Type | Returns | Use when |
|---|---|---|
INNER JOIN |
Only rows where the key matches in both tables | You want only the records that are in both tables, for example processes that opened a socket |
ANY LEFT JOIN |
All rows from the left table, plus matches from the right where they exist | You want all the data from the first table, and any extra detail that the second table can add |
ANY RIGHT JOIN |
All rows from the right table, plus matches from the left where they exist | The mirror image of the left join. Useful to find what is in the second table but not in the first table |
FULL OUTER JOIN |
Everything from both, matched where possible | Rare in practice, and the most expensive |
If a row has no match, the query does not remove the row. The columns with no data are empty. This is the purpose of an outer join: the absence of data is the result.
Joins are memory intensive. In the
WHEREclause, set the narrowest possible time window for each table in the join. Use only the join keys that are strictly necessary. Return only the columns that are necessary. A join without a time bound on both sides is the fastest way to use all the available memory.
Put a time bound on both sides, as in this example:
WHERE
p._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
AND s._time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
This form is correct for an inner join. For an outer join, you must move the time bound of the right-hand table into a sub-query. For the reason, refer to 9a.
9a. Your first join
Write a query that joins the process and process socket tables from the past 24 hours. Use the join keys id/pid and _hostname. Both keys must match exactly. Use a join type that returns only the results where a match occurs. We recommend a limit of 50 to 500.
Hint - which type
An `INNER JOIN` returns results only where a match occurs in both tables.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
Change the query so that it returns all results from the first table. Also return the results that match from the second table. Do not return all results from the second table.
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
Conditions on the right-hand table belong in a sub-query. Filter the right-hand table in a sub-query. Use the outer
WHEREfor left-hand conditions only.Notice that the filters of the socket table moved into the join. A process with no socket has no row that matches. Thus, its
s.columns contain default values. Then aWHEREclause that testss.remote_port > 0ors._time_observed >= ...removes the process. As a result, the left join operates as an inner join. There is no error, but the rows that you look for are not in the output.
9b. Joining authentication data
The username columns differ. Give both columns the same alias in their sub-queries. If you do not, the join key does not match. The column is
auth_usernameinnetwork_ntlmandusernameinnetwork_kerberos.
Use a join to write a query that counts the NTLM and Kerberos failures for each user, for each day. Include a total count of the combined failures. Return only users with failures for both protocols. Filter the results so that the total is greater than 250. Sort the results by failures, in descending order.
Hint - "both protocols"
When the user must be on both sides, the join is an `INNER JOIN` again. First, make each side as a separate grouped sub-query. Then join the two results.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
Change the join type and the query. Remove the 250 failure condition and the GROUP BY on the date. Filter for users with only Kerberos failures, or with both Kerberos and NTLM failures. Do not include users with only NTLM failures. Keep the join sequence: NTLM first, then Kerberos.
Hint - keep the order, change the type
You must have all the rows from the Kerberos side, and only the NTLM rows that match. NTLM is first, thus Kerberos is the right-hand table. This is the table that you must keep complete.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
Change 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
The pattern to remember: When you replace
ANY LEFTwithANY RIGHT, you change the population that you ask 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. The join type selects between them.
Congratulations. You completed the intermediate Hunt Lab questions. Now you can:
- Group and filter aggregates.
- Work with arrays.
- Put data into time intervals.
- Nest queries.
- Combine tables with unions and joins.
With these skills, you can answer a large majority of the questions that an analyst or a customer will reasonably ask.
Now you can continue to Part 10 of the Hunt Lab training.