Skip to content

Part 4: Grouping

References: - ClickHouse GROUP BY clause - ClickHouse HAVING clause

Welcome to the intermediate questions. From here on you will be given fewer clues, and you will often need to work out which columns you need from the shape of the result rather than from the question.

Another way to reduce the amount of output, and to produce a summary of records rather than a list of them, is a GROUP BY clause. At a basic level this creates one row for each matching value and tells you how many times it appeared, instead of returning every individual record.

💡 Grouping rule: every column in the SELECT clause that is not wrapped in an aggregate function must also appear in the GROUP BY clause, as a comma separated list in the same style as the SELECT.

4a. Group basics

Write a query that returns a count of the top 15 entries containing Microsoft in the name column of the endpoint_program table, across all hosts, regardless of case, ordered by the highest count, in the past 4 days.

Hint - ignoring case You met `lower()` in [Part 2](hunt_lab_training_2.md#2b-case-sensitivity). It works the same way here.
Answer 
SELECT
  name,
  COUNT(*) AS Installs
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 4)) * 1000
  AND lower(name) LIKE '%microsoft%'
GROUP BY name
ORDER BY Installs desc
LIMIT 15

Notice how large those numbers are. That is because COUNT(*) counts every individual record in the table, and the telemetry is scanned and added to hourly, so the same program on the same host is counted many times over.

uniqExact gives us a value that counts distinct entries instead.

Modify the query above so it returns the top 15 Microsoft programs showing the exact number of hosts each one is installed on, in descending order.

Answer 
SELECT
  name,
  uniqExact(_hostname) AS Host_Count
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 4)) * 1000
  AND lower(name) LIKE '%microsoft%'
GROUP BY name
ORDER BY Host_Count desc
LIMIT 15

💡 uniqExact against uniq: ClickHouse also has a uniq() function, which is faster but approximate. Use uniqExact() when the number has to be right, which for host counts it usually does.

4b. Filtering grouped results

A WHERE clause filters records before they are grouped, so it cannot filter on a count that does not exist yet. To filter the results a GROUP BY produces, use a HAVING clause. It goes after the GROUP BY and behaves much like a WHERE.

Using the query from before, add a HAVING clause so that only programs installed on 45 or fewer hosts are returned.

Answer 
SELECT
  name,
  uniqExact(_hostname) AS Host_Count
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 4)) * 1000
  AND lower(name) LIKE '%microsoft%'
GROUP BY name
HAVING Host_Count <= 45
ORDER BY Host_Count desc
LIMIT 15

💡 Why this matters for hunting: rare is interesting. A HAVING clause that keeps only the low counts is the simplest form of frequency analysis, and it is how you surface the one host running something the other nine hundred are not.

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