Skip to content

Part 6: Time intervals

References: - ClickHouse toStartOf functions - ClickHouse array functions

For some threat hunts and some analytic development, you must aggregate data into fixed time windows. The standard example is a count of authentication failures from one address over a period, to look for brute force attempts. Part 3 told you how to convert time and filter on time. This section tells you how to put time into buckets.

To do this, use variants of the toStartOf function. The function must have a date time value. Thus, typecast the time first. It is best to do this 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. The last two questions use hasAny and hasAll from Part 5.


6a. Counting per interval

Write a query that returns the hostname, process name, parent name and command that appear in each hour, on each host. Use only processes with a parent named svchost.exe, from the last 12 hours. Include the interval hour. Include a count of the number of times that the combination occurs. Sort the results by count, in ascending order. Filter the results to show only records with 15 or more appearances.

Hint - where the count filter goes You filter on a value that the GROUP BY clause makes. Thus, you must use a `HAVING` clause, not a `WHERE`. Refer to [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

Change the previous query so that it makes an array of hosts. The array contains the hosts where a given combination of process and parent process occurred in each hour. Use the past 12 hours. Also include the number of hosts and the total number of times that the combination occurs. Filter for results with 25 or fewer hosts and 50 or more appearances. Sort the results by the number of hosts, in descending order.

Hint - what leaves the GROUP BY You now aggregate the hostname. You do not group by it. Thus, remove it from 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: Many appearances on few hosts is the signature of something that runs in a tight loop in an incorrect location. If you reverse the filter to find few appearances across many hosts, you see normal behaviour across the estate.


6c. Filtering on array contents

Write a query that makes an array of process names that contain dll or .exe, independent of case. Use the endpoint_process table. Group the arrays by host, in intervals of one minute. Limit the query to the past 2 days. Filter for results where the array contains at least gpupdate.exe, Conhost.exe and svchost.exe. Add a column that shows the number of elements in the array.

Hint - at least these three `hasAll` finds arrays that contain all three values, and possibly other values also.
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. Change the query so that it returns only results that contain gpupdate.exe, Conhost.exe and svchost.exe, and no other values. You can do this with one more line.

Hint - one line `hasAll` makes sure that all three values are present. What can also make sure that no other values are present?
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` makes sure that all three values are present. `length(Processes) = 3` makes sure that no other values are present. Together, they give an exact match.

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