Skip to content

Part 7: Sub-queries

References: - ClickHouse IN operator and sub-queries - ClickHouse WITH clause

A sub-query can be used inside a SELECT, a FROM or a WHERE statement. It is literally a query within a query. The idea can feel awkward at first, but what you are doing is exactly what you have been doing all along: selecting columns from a table. The only difference is that the table is generated while the query runs, rather than sitting in the database.

Hold on to that: a sub-query is still just selecting columns from a table.

7a. Sub-queries and the IN operator

The most common use is inside a WHERE clause with the IN operator. In Part 5b you gave IN a fixed array you had typed out. Here you give it a query instead, and it filters against whatever that query returns.

Write a query returning the hostname, an array of usernames, and the date, from the network_ntlm table, using a sub-query within the WHERE clause to filter for usernames that have had any login failures. The number of failures does not matter. Order the results by the number of usernames in the array, descending.

Hint - what the sub-query returns The sub-query needs to produce a single column of usernames: the ones with at least one failure. The outer query then keeps only rows whose username is `IN` that list.
Answer 
SELECT
  _hostname,
  groupUniqArray(auth_username) AS Usernames,
  formatDateTime(toDateTime(_time_observed / 1000), '%F') AS Date
FROM network_ntlm
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
  AND auth_username IN (
    SELECT auth_username
    FROM network_ntlm
    WHERE
      _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
      AND success = 0
  )
GROUP BY _hostname, Date
ORDER BY length(Usernames) desc
LIMIT 50

💡 Mind the column name. The username column is called auth_username in network_ntlm but plain username in network_kerberos. This catches people out in Part 8 and Part 9, where the two tables are combined.

âš  Time bound both halves. The sub-query is a query in its own right and does not inherit the outer query's WHERE clause. Leave the time bound off the inner one and it scans the full retention period, which is the most common reason a sub-query runs out of memory.

7b. Sub-queries in the SELECT clause

A sub-query in the SELECT clause is how you compare each row against a total. The inner query produces one value, the whole population, and each grouped row is measured against it.

To hold the count for the current group you need a WITH clause, which is declared before the SELECT.

Write a query that outputs the versions of the program SEE, a count of each version, and the percentage of the estate each version represents. Group by version.

Hint - the skeleton 
WITH COUNT() AS Version_Count
SELECT
  Version,
  Version_Count * 100 / (SELECT COUNT(*) FROM the same table) AS Percentage
The sub-query supplies the denominator: the total across every version.
Answer 
WITH COUNT() AS Version_Count
SELECT
  version AS Version,
  Version_Count AS Installs,
  round(
    Version_Count * 100 / (
      SELECT COUNT(*)
      FROM endpoint_program
      WHERE
        _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
        AND name = 'SEE'
    ),
    2
  ) AS Percent_Of_Estate
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
  AND name = 'SEE'
GROUP BY Version
ORDER BY Installs desc

💡 Keep the filters identical. The sub-query and the outer query must apply the same WHERE conditions, or the percentages will not add up to 100. If you widen the time bound on one, widen it on the other.

Percentage-of-estate is a genuinely useful hunting shape. A software version present on 0.4% of machines is either a straggler that missed a patch cycle or something that was never deployed by IT at all, and both are worth a look.

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