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_usernameinnetwork_ntlm, but onlyusernameinnetwork_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
WHEREclause 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
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
WHEREconditions. 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.