Skip to content

Part 1: Basic Query Structure

References: - ClickHouse 'SELECT' documentation

Each query must have a SELECT statement. This statement specifies the columns that you want the query to return. After it, a FROM statement specifies the table that contains the data.

Some queries also contain a WHERE clause. In this clause, you use operators to specify the values in the columns that you want to return. There is a large number of possibilities and operators that you can use.

In practice, give each query a LIMIT clause at the end. This clause specifies the maximum number of records that the query returns. Without this clause, the default maximum is 10,000. In almost all situations, this number is much too large, because it takes a long time to manually examine each row. Also, a query without a LIMIT clause can often use too much memory. As a result, the query stops with an error.

1a. Selecting

First, use SELECT, FROM, WHERE, and LIMIT (in this sequence) to make a basic query.

HINTS: - If you put '*' after the SELECT clause, the query returns all columns in the table. * You can use _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 to limit queries to the past 24 hours.

SELECT * FROM table_name WHERE _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 LIMIT 1000

Write a query that returns 500 results for all columns in the endpoint_process table.

Answer
SELECT *
FROM endpoint_process
WHERE _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
LIMIT 500

Now refine that query. Change it so that it returns only the _time_observed, _hostname, name, parent_name, and path columns.

Answer
SELECT _time_observed, _hostname, name, parent_name, path
FROM endpoint_process
WHERE _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
LIMIT 500

1b. Ordering and returning unique values

To sort the returned data by a column, use ORDER BY before the LIMIT clause. You can also specify the direction (asc or desc) after the column name.

SELECT *
FROM table_name
WHERE * _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
ORDER BY column_name desc # ORDER BY, add column name and direction
LIMIT 1000

Write a query that lists the host, name, path, and permissions of Chrome extensions. Limit the query to 500 results. Sort the results by name.

Hint: to find the most useful table, use the Table Reference panel on the right side of Hunt Lab.

Hint - which table to use If you search for 'Chrome' in Table Reference, you can see that this table is useful here:
endpoint_chrome_extension
Answer First, learn about the table with a simple query that shows everything:
SELECT *
FROM endpoint_chrome_extension
LIMIT 10
Then change the query so that it returns only the columns that you want, in the sequence that you want. Limit it to 500 results:
SELECT _hostname, _platform, name, path, permissions
FROM endpoint_chrome_extension
LIMIT 500
You can also add the platform and profile columns to this query. These columns return more information that can be of interest.

It is possible that the output from this query contains many duplicate entries. This is because SELECT gets all entries. To refine the output, use distinct. It returns each unique value only one time, independent of how frequently the value occurs in the table.

Use distinct to change your Chrome Extensions query so that it shows only unique values.

Answer Tip: After you use 'distinct', it is possible that the query returns fewer than 500 values.
SELECT distinct _hostname, _platform, name, path, permissions
FROM endpoint_chrome_extension
LIMIT 500

1c. Renaming columns

You can use as to return column names that are more 'friendly'. Put it after the column name. Put the friendly name in "".

For example, to change the name of the 'bruce_wayne' column to 'batman', use select bruce_wayne AS "Batman".

Now use as to give the _hostname column the friendly name Device Hostname.

Answer
SELECT distinct _hostname as "Device Hostname", _platform, name, path, permissions
FROM endpoint_chrome_extension
LIMIT 500

Now use friendly names for the other columns. Then make the query simpler, so that it gives only a list of extensions and their permissions. That is, a list of all Chrome Extensions in use.

Answer
SELECT distinct name as "Extension Name", permissions as "Permissions Granted"
FROM endpoint_chrome_extension
LIMIT 500

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