Part 6: Time intervals

References: - ClickHouse toStartOf functions - ClickHouse array functions

Some threat hunting and analytic development requires aggregating data into fixed time windows. Counting failed authentications from one address over a period, to look for brute force attempts, is the standard example. Part 3 covered converting and filtering on time; this section is about bucketing it.

For that we use variants of the toStartOf function. It needs a date time value, so type cast first, and it is best done in the SELECT or WITH clause.

Function	Bucket
toStartOfMinute(<datetime>)	Per minute
toStartOfFiveMinutes(<datetime>)	Per 5 minutes
toStartOfHour(<datetime>)	Per hour
toStartOfDay(<datetime>)	Per day

This section also builds on arrays. hasAny and hasAll from Part 5 are needed for the last two questions.

---

6a. Counting per interval

Write a query that returns the hostname, process name, parent name and command appearing per hour, per host, for processes with a parent named svchost.exe, within the last 12 hours. Include the interval hour and a count of how many times the combination has been seen, in ascending order of count. Filter so it only shows records with 15 or more appearances.

Hint - where the count filter goes

You are filtering on a value produced by the grouping, so it needs a `HAVING` clause rather than a `WHERE`. See [Part 4b](hunt_lab_training_4.md#4b-filtering-grouped-results).

Answer

SELECT
  toStartOfHour(toDateTime(_time_observed / 1000)) AS Hour,
  _hostname,
  name,
  parent_name,
  command,
  COUNT(*) AS Appearances
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 12)) * 1000
  AND parent_name = 'svchost.exe'
GROUP BY Hour, _hostname, name, parent_name, command
HAVING Appearances >= 15
ORDER BY Appearances asc
LIMIT 100

---

6b. Aggregating hosts per interval

Modify the query above so it creates an array of the hosts that have seen a given process and parent process combination in each hour, within the past 12 hours, along with the number of hosts and the total number of times the combination has been seen. Filter for results with 25 or fewer hosts and 50 or more appearances. Order by number of hosts, descending.

Hint - what leaves the GROUP BY

The hostname is now something you are aggregating, not something you are grouping by, so it has to come out of the `GROUP BY` list.

Answer

SELECT
  toStartOfHour(toDateTime(_time_observed / 1000)) AS Hour,
  name,
  parent_name,
  groupUniqArray(_hostname) AS Hosts,
  uniqExact(_hostname) AS Host_Count,
  COUNT(*) AS Appearances
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 12)) * 1000
GROUP BY Hour, name, parent_name
HAVING Host_Count <= 25 AND Appearances >= 50
ORDER BY Host_Count desc
LIMIT 100

Why this shape is useful: high appearances on few hosts is the signature of something running in a tight loop somewhere it should not be. Reverse the filter, few appearances across many hosts, and you are looking at normal estate-wide behaviour instead.

---

6c. Filtering on array contents

Write a query that creates an array of process names containing dll or .exe, regardless of case, grouped per host within a per minute interval, from the endpoint_process table. Limit it to the past 2 days. Filter for results where the array contains at least gpupdate.exe, Conhost.exe and svchost.exe, and add a column showing the number of elements in the array.

Hint - at least these three

"At least these three, possibly others" is exactly what `hasAll` tests.

Answer

SELECT
  toStartOfMinute(toDateTime(_time_observed / 1000)) AS Minute,
  _hostname,
  groupUniqArray(name) AS Processes,
  length(groupUniqArray(name)) AS Process_Count
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 2)) * 1000
  AND (lower(name) LIKE '%dll%' OR lower(name) LIKE '%.exe%')
GROUP BY Minute, _hostname
HAVING hasAll(Processes, ['gpupdate.exe', 'Conhost.exe', 'svchost.exe'])
ORDER BY Minute asc
LIMIT 100

Remove the element count column and modify the query so it returns only results containing gpupdate.exe, Conhost.exe and svchost.exe and nothing else. You can achieve this by adding a single extra line.

Hint - one line

`hasAll` already guarantees all three are present. What would additionally guarantee that nothing else is?

Answer

SELECT
  toStartOfMinute(toDateTime(_time_observed / 1000)) AS Minute,
  _hostname,
  groupUniqArray(name) AS Processes
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 2)) * 1000
  AND (lower(name) LIKE '%dll%' OR lower(name) LIKE '%.exe%')
GROUP BY Minute, _hostname
HAVING hasAll(Processes, ['gpupdate.exe', 'Conhost.exe', 'svchost.exe'])
  AND length(Processes) = 3
ORDER BY Minute asc
LIMIT 100

`hasAll` guarantees all three are present, and `length(Processes) = 3` guarantees nothing else is, so together they mean an exact match.

---

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

---