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
hasAnyandhasAllfrom 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
Now you can continue to Part 7 of the Hunt Lab training.