Skip to content

Part 4: Grouping

References: - ClickHouse GROUP BY clause - ClickHouse HAVING clause

Welcome to the intermediate questions. From this point, the questions give fewer clues. You must often find the necessary columns from the shape of the result, not from the question.

A GROUP BY clause is another way to decrease the quantity of output. It gives a summary of records, not a list of them. At a basic level, the clause makes one row for each value that matches. The row tells you how many times the value occurred. The clause does not return each individual record.

💡 The GROUP BY rule: If a column in the SELECT clause is not in an aggregate function, it must also be in the GROUP BY clause. These columns are in a comma-separated list, in the same style as the SELECT.


4a. Group basics

Write a query that counts the entries that contain Microsoft in the name column of the endpoint_program table. Return the top 15. Include all hosts. Ignore the case. Use the past 4 days. Sort the results by the highest count.

Hint - ignoring case You used `lower()` in [Part 2](hunt_lab_training_2.md#2b-case-sensitivity). It works in 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 that these numbers are large. This is because COUNT(*) counts each individual record in the table. Each hour, a new scan adds records to the telemetry. As a result, the count includes the same program on the same host many times.

uniqExact gives a value that counts only distinct entries.

Change the previous query so that it returns the top 15 Microsoft programs. Show the exact number of hosts on which each program is installed, 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. This function is faster, but its result is approximate. Use uniqExact() when the number must be correct. For host counts, the number must usually be correct.


4b. Filtering grouped results

A WHERE clause filters records before the query groups them. Thus, it cannot filter on a count that does not exist at that time. To filter the results of a GROUP BY, use a HAVING clause. This clause comes after the GROUP BY, and it operates almost the same as a WHERE.

Add a HAVING clause to the previous query. Make the query return only the programs that are installed on 45 or fewer hosts.

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 is important for threat hunts: Rare items are of interest. A HAVING clause that keeps only the low counts is the simplest form of frequency analysis. With it, you can find the one host that runs something that the other nine hundred hosts do not run.


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