Skip to content

Part 7: Sub-queries

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

You can use a sub-query in a SELECT, a FROM or a WHERE statement. A sub-query is a query in a query. At first, this idea can seem difficult. But you do the same thing that you did in all the earlier parts: you select columns from a table. The only difference is that the query makes the table while it runs. The table is not in the database.

Remember this: a sub-query still selects columns from a table.


7a. Sub-queries and the IN operator

The most usual use is in a WHERE clause with the IN operator. In Part 5b, you gave IN a fixed array that you typed. Here, you give it a query. The operator then filters against the results of that query.

Write a query that returns the hostname, an array of usernames, and the date, from the network_ntlm table. Use a sub-query in the WHERE clause to filter for usernames with one or more login failures. The number of failures is not important. Sort the results by the number of usernames in the array, in descending order.

Hint - what the sub-query returns The sub-query must give one column of usernames: the usernames with one or more failures. The outer query then keeps only the rows with a username that 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

💡 Be careful with the column name. The name of the username column is auth_username in network_ntlm, but only username in network_kerberos. This difference often causes mistakes in Part 8 and Part 9, which combine the two tables.

âš  Put a time bound on both halves. The sub-query is a separate query. It does not inherit the WHERE clause of the outer query. If the inner query has no time bound, it scans the full retention period. This is the most usual reason why a sub-query uses all the available memory.


7b. Sub-queries in the SELECT clause

Use a sub-query in the SELECT clause to compare each row with a total. The inner query gives one value: the total population. The query then compares each grouped row with this value.

To keep the count for the current group, you must use a WITH clause. Declare this clause before the SELECT.

Write a query that gives the versions of the program SEE, a count of each version, and the percentage of the estate for each version. Group the results 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 gives the denominator: the total for all versions.
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. If they do not, the total of the percentages will not be 100. If you make the time bound wider on one query, make it wider on the other query also.

A query for the percentage of the estate is a useful shape for threat hunts. A software version that is present on 0.4% of machines is one of two things. It is a version that a patch cycle did not update, or it is software that IT never deployed. It is useful to examine both possibilities.


Now you can continue to Part 8 of the Hunt Lab training.