Part 5: Arrays

References: - ClickHouse array functions - ClickHouse IN operator - ClickHouse groupUniqArray

5a. Array overview

Several columns in certain tables return an array of results, which looks like ['host1', 'host2', 'host3']. Working with them needs different syntax from ordinary 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

length is the one you will reach for most. As well as telling you how many elements an array holds, it is how you test whether an array holds anything at all: length(<array column>) > 0 for populated, = 0 for empty.

Write a query that returns the 50 most recent results from the network_dns table, showing the time, hostname and query name, where the answer_values column is not empty. Use a time bound to limit it 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

Adjust the query so it now shows 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 domain generation algorithm activity, where malware works through a list of candidate domains until one resolves.

The same function bounds an array by size. Combine it with >= or <= rather than > or =.

Write a query that returns only results with 5 or more elements in answer_values. Show the hostname, query name, the answer values themselves, and a count of the array size. Order by ascending array length, limited 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

Arrays can also be used to specify a series of values to look for in a given column, using the IN operator. The syntax is <column name> IN ['value1', 'value2', 'value3']. This is a much shorter alternative to chaining together a long series of OR conditions.

Write a query that returns a distinct list of hostnames, process names and parent names within the last 12 hours, for all entries where the name matches at least one of cmd.exe, rundll32.exe or svchost.exe, using 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 holds a single value and you are testing it against a list. Use hasAny when the column itself is an array. Mixing the two up is the most common array mistake.

5c. Creating an array of values in a SELECT statement

You can build an array in the SELECT clause that aggregates all the unique values of one column, grouped by the other columns in the GROUP BY statement. The function is groupUniqArray(<column>).

This is where arrays and Part 4's grouping come together, and it is one of the most useful patterns in Hunt Lab: instead of one row per host, you get one row per thing, listing every host it appears on.

Write a query that aggregates all the hostnames that have a record for each extension name in the endpoint_chrome_extension table, with 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 with an array of the Chrome extension names they have installed, and a distinct count of how many are installed.

Hint - what changes

This is the previous query turned around. Swap what you group by with what 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 answers "what has this host got?". Being able to flip between the two by swapping the grouping is worth practising, because investigations move between those questions constantly.

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