Skip to content

Part 2: Filtering and String Functions

References: - ClickHouse 'WHERE' documentation - ClickHouse 'Functions for working with strings' documentation

You can add logic to a query so that it returns only the results that match specified values. Use a WHERE clause to specify this logic.

In this exercise, you use = , AND, NOT, OR and LIKE.

NOTE: Use '=' to specify an exact match. You can use '=' for all data types. You MUST put string values, IP addresses and number IDs in single quotes (for example, source_hostname = ‘SenseOn’). For columns that return INT32 or INT64, you can omit the quotes. When you do calculations with numbers, the usual BIDMAS rule for the sequence of operations applies. Use brackets to make sure that the operators apply in the correct sequence.

When you combine two or more conditions, put a logic operator (AND or OR) at the start of each new condition. You can put conditions in brackets. For example, in time_observed > 1058926028 AND (column = value 2 OR column = value 3), the value must be 2 or 3. The time must also be greater than the given number. Without brackets, the query returns value = 2 only when the time condition is also true. But it returns all matches where the value = 3, independent of the time.

IMPORTANT: Put this line immediately after the WHERE clause. The line makes the queries faster, and all the queries in the questions must have it to work: _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000. A later part of this training gives more information about this line and other time operations.


Structuring WHERE statements

The structure of a WHERE statement is:

SELECT
WHERE
<column name> <operator> <matching conditions>,
<logic operator (AND.OR)> <column name> <operator> <matching condition>
ORDER BY

NOTE: To use the LIKE operator, put the wildcard value ‘%' before the text that you want to match, after it, or both. With the wildcard, you can specify a part of the text in an entry that you want to return. For example, WHERE name LIKE ‘%.exe’ returns all entries in the name column that end with .exe, independent of the text before it. WHERE name LIKE ‘%edge%’ returns all entries that have ‘edge’ at any position in the text. The text before or after ‘edge’ has no effect.


2a. Common WHERE operators

Using the = operator

Use = to return an exact match of a condition.

Write a query that returns the time observed, hostname, eventid and data from the endpoint_windows_event_log table. Return only the records where eventid = ‘4624’. Use a limit of 20.

(REMEMBER: use the _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 line.) Note: If the query returns no results, remove the eventid condition.

Answer
SELECT _time_observed, _hostname, eventid, data
FROM endpoint_windows_event_log
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
  AND
  eventid = '4624'
LIMIT 20

Using the LIKE operator

Use LIKE when a partial match is necessary. To find a partial match in strings, put a % on one side or on the two sides of the condition. For example, name LIKE 'bat%' returns batman and batmobile but not acrobat.

Write a query that returns a list of the _hostname, name, command and parent name from the endpoint_process table. Return 20 entries that contain ‘dll’ at any position in the name, from the last 24 hours. Sort them alphabetically by the hostname.

Hint - operator The LIKE operator on the `name` column with '%dll%' is useful here.
Answer
SELECT _hostname, _platform, name, command, parent_name
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
  AND
  name LIKE '%dll%'
ORDER BY _hostname asc
LIMIT 20

Next, change the query so that it returns a distinct list of hostname, process names and parent_names from the past 12 hours. Return only the records that contain dll in the process name. Sort the list alphabetically by hostname.

Answer
SELECT distinct _hostname, _platform, name, command, parent_name -- add distinct
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 12)) * 1000 --change to 12h
  AND
  name LIKE '%dll%'
ORDER BY _hostname asc
LIMIT 20

Using the NOT operator

Use NOT LIKE to exclude results. In the next query, you use it together with other conditions to make a more complex query.

Now, look in the endpoint_program table to find specific programs that Microsoft published. Do not include programs with .NET or C++ in the package name.

Change the query so that it looks at the ‘endpoint_program’ table. Return a distinct list of the hostnames and names from the past 24 hours. The publisher name must contain the string ‘Micro’. The name must not contain ‘.NET’ or ‘C++’. Limit the list to the first 100 results. Do not select the 'publisher' column for display. But use this column in the where clause to filter the results.

Answer
SELECT distinct _hostname, name
FROM endpoint_program
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000
  AND
  publisher LIKE '%Micro%'
  AND
  name NOT LIKE '%.NET%'
  AND
  name NOT LIKE '%C++%'
ORDER BY _hostname asc
LIMIT 100

2b. Case sensitivity

SQL databases are case sensitive. But you can make a query return all results, independent of case. Use LOWER or UPPER, followed by the column name in brackets. An example is lower(name) = ‘text in lower case'.

Write a query that returns the hostname, name, and parent_name from the endpoint_process table. Return the records from the past 3 days that contain ‘excel’ in the name. Notice that there are no records, or only a few.

Answer
SELECT _hostname, name, parent_name
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), (24*3))) * 1000 -- changed to 3 days
  AND
  name LIKE '%excel%'
LIMIT 20

Now, use lower on the name column so that the query ignores the case.

Hint Use `lower (name)`.
Answer
SELECT _hostname, name, parent_name
FROM endpoint_process
WHERE
  _time_observed >= toUnixTimestamp(subtractHours(now(), (24*3))) * 1000
  AND
  lower(name) LIKE '%excel%' --added lower
LIMIT 20

Now the query returns results for EXCEL and also for excel.


2c. Mathematical operations in the SELECT clause

You can do mathematical operations in the WHERE clause. This clause is the most usual location for them. But in some situations, you can also use them in the SELECT clause, when the data is numerical.

Write a query that looks back 24 hours in the network flow table. Return the time, hostname, destination host, application protocols, and the data downloaded and uploaded. Convert the bytes into megabytes (use the \ function to divide). Sort the results by the most data received. Give all the columns friendly names. Set a limit of 50 results.

Answer ```sql SELECT _time_observed AS "Time Observed", _hostname AS "Hostname", dest_hostname AS "Destination Hostname", application_protos AS "Application Protocols", source_bytes_sent/1000000 AS "Source MB sent", dest_bytes_sent/1000000 AS "Destination MB sent" FROM network_flow WHERE _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 ORDER BY dest_bytes_sent desc LIMIT 50 ```

Write a query that shows a list of distinct USB devices. Also show the host, platform, hardware model, partitions, type, and the capacity (converted to MB). Sort the results by the capacity column, with the largest disk first. Use Table Reference to find the correct table.

Hint - which table to use The useful table here is `endpoint_disk_info`.
Hint - which column to use for distinct The `_hardware_id` column is a good column to use for distinct values.
Answer ```sql SELECT DISTINCT _hardware_id, _hostname, _platform, hardware_model partitions, type, disk_size/1000000 AS "Disk Size (MB)" FROM endpoint_disk_info WHERE _time_observed >= toUnixTimestamp(subtractHours(now(), 24)) * 1000 ORDER BY disk_size desc ```

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