How to Query the Security Data Lake (SDL) using SQL

SELECT * 
FROM "Your Table"
WHERE Column_name LIKE '%value%';

SQL uses the LIKE operator for pattern matching.  The % symbol is a wildcard that represents "any number of characters" (including zero).

• %value% finds the text anywhere in the string
• value% finds text starting with that value
• %value finds text ending with that value

Wildcards are useful when searching for part of a value, such as a partial file path, a domain suffix, or IP subnet.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE company LIKE 'Red%'
LIMIT 100;

SELECT *
FROM "Your Table"
WHERE LOWER(Column_name) = 'value';

By default, SQL is case-sensitive; it treats "Malware" and "malware" as different words.  Since ILIKE is not supported in Security Data Lake, use the  LOWER() function to "normalize" the data.  This temporarily converts all text in your chosen column to lowercase during the search.

However, you must also ensure your search term (i.e. filter_value) is written in all lowercase letters.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE LOWER(company) = 'redcanary'
LIMIT 100;

SELECT *
FROM "Your Table" 
WHERE Column_name_A = 'value_1'
AND Column_name_B = 'value_2';

Logical operators allow you to connect multiple conditions.

• AND narrows your search: every condition must be true for a row to show up
• OR broadens your search: only one of the conditions needs to be true

Use AND when you need to be specific, such as looking for a specific user and a specific action.  Use OR when you want to look for multiple independent indicators at once, like searching for activity from three different IP addresses.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE company = 'RedCanary'
AND message = 'New user added'
LIMIT 100;

SELECT *
FROM "Your Table" 
WHERE NOT Column_name = 'Excluded_value';

Sometimes the most efficient way to find a "needle" is to remove the "hay."   You can use the "not equal to" operator (!=) or the NOT keyword to hide results that match a specific value.

This is primarily used for filtering out noise.  If your search results are flooded with logs not useful for your investigation, you can exclude those specific values so you can focus on the remaining data.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE company != 'Zscaler'
LIMIT 100;

SELECT * 
FROM "Your Table"
WHERE Column_name.Field = 'value';

In SQL, related attributes are often grouped into an Object (also called a Struct).  To access a specific value inside that group, use dot notation (Column.Field).

Use this when a single column (like actor or event) contains multiple sub-fields. Instead of searching the whole blob of data, you can pinpoint exactly what you need, such as actor.displayName or event.ClientIP.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE company.A = 'RedCanary'
LIMIT 100;

SELECT *
FROM "Your Table"
WHERE Column_name.Field.Sub_field = 'Filter_value';

Sometimes data is organized as objects within objects.  You can chain dot notation together to "drill down" through multiple layers of objects to reach a specific value.

This is common in complex cloud or identity logs.  For example, you might need to navigate through loginDetails to geolocation to find a specific country.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE company.B.city = 'Denver'
LIMIT 100;

SELECT *
FROM "Your Table"
WHERE any_match(Column_name, temp -> temp.Field = 'Filter_value');

When a column contains a List (also called an Array) of multiple items, a standard = or LIKE won't work.  To workaround this, use the any_match function combined with a Lambda expression (temp -> ...) to effectively tell the database, "Go through every item in this list one by one and let me know if any of them match my filter".

This is essential for fields that can have multiple values, such as a list of detailed netconns in a single IP chain or a list of roles assigned to a user.

Example:

SELECT rc_timestamp, company, message
FROM "My Data Lake Integration"
WHERE any_match(company, temp -> temp.A = 'RedCanary')
LIMIT 100;

SELECT DISTINCT Column_name
FROM "Your Table"
ORDER BY Column_name ASC;

The DISTINCT operator acts as a de-duplicator.  It scans the specified column and returns only one instance of every unique value it finds.  Adding ORDER BY ... ASC simply sorts that list alphabetically.

This is a useful tool for data exploration.  As examples, if you aren't sure what event types your IdP collects, or you want to see a list of every unique host that sent data in the last hour, DISTINCT will give you a high-level summary without the noise of thousands of repetitive rows.

Example:

SELECT DISTINCT company
FROM "Your Table"
ORDER BY company ASC
LIMIT 100;