Part 5: Arrays
References: - ClickHouse array functions - ClickHouse IN operator - ClickHouse groupUniqArray
5a. Array overview
In some tables, some columns return an array of results. An array looks like ['host1', 'host2', 'host3']. For these columns, you must use a different syntax from the syntax for usual columns.
| Function | What it does |
|---|---|
hasAny(<array column>, ['value1', 'value2']) |
Returns rows where at least one of the listed values is in the array |
hasAll(<array column>, ['value1', 'value2']) |
Returns rows where all of the listed values are in the array |
length(<array column>) |
Returns the number of elements in the array |
You will use length most frequently. It tells you how many elements an array contains. You also use it to find if an array contains any elements: length(<array column>) > 0 for an array with elements, = 0 for an empty array.
Write a query that returns the 50 most recent results from the network_dns table. Show the time, hostname and query name. Return only the results where the answer_values column is not empty. Use a time bound to limit the query to the past 3 hours.
Answer
SELECT
_time_observed,
_hostname,
query_name
FROM network_dns
WHERE
_time_observed >= toUnixTimestamp(subtractHours(now(), 3)) * 1000
AND length(answer_values) > 0
ORDER BY _time_observed desc
LIMIT 50
Change the query so that it shows the results with no answers.
Answer
SELECT
_time_observed,
_hostname,
query_name
FROM network_dns
WHERE
_time_observed >= toUnixTimestamp(subtractHours(now(), 3)) * 1000
AND length(answer_values) = 0
ORDER BY _time_observed desc
LIMIT 50
Why unanswered lookups matter: A burst of DNS queries that return nothing is one of the signals of activity from a domain generation algorithm. In this activity, malware tries a list of possible domains until one domain resolves.
You can also use the same function to set limits on the size of an array. Use it with >= or <=, not with > or =.
Write a query that returns only results with 5 or more elements in answer_values. Show the hostname, the query name, the answer values, and a count of the array size. Sort the results by array length, in ascending order. Limit the query to 50 results.
Answer
SELECT
_hostname,
query_name,
answer_values,
length(answer_values) AS Answer_Count
FROM network_dns
WHERE
_time_observed >= toUnixTimestamp(subtractHours(now(), 3)) * 1000
AND length(answer_values) >= 5
ORDER BY Answer_Count asc
LIMIT 50
5b. Using arrays to filter for multiple values
You can also use an array with the IN operator, to specify a series of values to look for in a column. The syntax is <column name> IN ['value1', 'value2', 'value3']. This syntax is much shorter than a long series of OR conditions.
Write a query that returns a distinct list of hostnames, process names and parent names from the last 12 hours. Return all entries where the name matches one or more of cmd.exe, rundll32.exe or svchost.exe. Use the IN operator and an array.
Answer
SELECT DISTINCT
_hostname,
name,
parent_name
FROM endpoint_process
WHERE
_time_observed >= toUnixTimestamp(subtractHours(now(), 12)) * 1000
AND name IN ['cmd.exe', 'rundll32.exe', 'svchost.exe']
ORDER BY _hostname asc
LIMIT 500
![]()
INagainsthasAny: UseINwhen the column contains one value and you compare it with a list. UsehasAnywhen the column itself is an array. The most usual array mistake is to use one of these in place of the other.
5c. Creating an array of values in a SELECT statement
In the SELECT clause, you can make an array that aggregates all the unique values of one column. The query groups these values by the other columns in the GROUP BY statement. The function is groupUniqArray(<column>).
This function combines arrays with the GROUP BY clause from Part 4. It is one of the most useful patterns in Hunt Lab. You get one row for each item, not one row for each host. The row lists each host where the item occurs.
Write a query that aggregates all the hostnames that have a record for each extension name in the endpoint_chrome_extension table. Include an exact count of the number of hosts.
Answer
SELECT
name,
groupUniqArray(_hostname) AS Hosts,
uniqExact(_hostname) AS Host_Count
FROM endpoint_chrome_extension
WHERE
_time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
GROUP BY name
ORDER BY Host_Count desc
LIMIT 50
Now write a query that gives a list of hosts. For each host, show an array of the names of the installed Chrome extensions. Also show a distinct count of the installed extensions.
Hint - what changes
This query is the reverse of the previous query. Interchange the column that you group by and the column that you aggregate.Answer
SELECT
_hostname,
groupUniqArray(name) AS Extensions,
uniqExact(name) AS Extension_Count
FROM endpoint_chrome_extension
WHERE
_time_observed >= toUnixTimestamp(subtractDays(now(), 1)) * 1000
GROUP BY _hostname
ORDER BY Extension_Count desc
LIMIT 50
Two views of one dataset: The first query answers "who has this extension?". The second query answers "what has this host got?". To change from one query to the other, interchange the columns that you group by and aggregate. It is useful to practise this change, because investigations frequently move between these two questions.
Now you can continue to Part 6 of the Hunt Lab training.