Skip to content

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

💡 IN against hasAny: Use IN when the column contains one value and you compare it with a list. Use hasAny when 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.