Skip to main content

LQL Queries

To provide customizable specification of datasets, Lacework provides the Lacework Query Language (LQL).

LQL is a SQL-like query language for specifying the selection, filtering, and manipulation of data. Queries let you interactively request information from specified curated datasources. Queries have a defined structure for authoring detections.

Lacework offers a set of default LQL queries that are available in your account.

To view all LQL queries in your Lacework account.

lacework query ls

To show a query.

lacework query show <query_id>

To delete a query.

lacework query delete <query_id>
note

LQL syntax may change.

Create a Query

There are multiple ways you can create a query:

  • Type the query into your default editor (via $EDITOR)
  • Pipe the query to the Lacework CLI command (via $STDIN)
  • From a local file on disk using the flag --file
  • From a URL using the flag --url

There are two formats you can use to define a query:

  • Javascript Object Notation (JSON)
  • YAML Ain't Markup Language (YAML)

To launch your default editor and create a new query.

lacework lql create

The following example checks for unrestricted ingress to TCP port 445.:

---
queryId: LW_Custom_UnrestrictedIngressToTCP445
queryText: |-
{
source {
LW_CFG_AWS_EC2_SECURITY_GROUPS a,
array_to_rows(a.RESOURCE_CONFIG:IpPermissions) as (ip_permissions),
array_to_rows(ip_permissions:IpRanges) as (ip_ranges)
}
filter {
ip_permissions:IpProtocol = 'tcp'
and ip_permissions:FromPort = 445
and ip_permissions:ToPort = 445
and ip_ranges:CidrIp = '0.0.0.0/0'
}
return distinct {
ACCOUNT_ALIAS,
ACCOUNT_ID,
ARN as RESOURCE_KEY,
RESOURCE_REGION,
RESOURCE_TYPE,
SERVICE
}
}

This query specifies an identifier of LW_Custom_UnrestrictedIngressToTCP445, and identifies AWS EC2 security groups with unrestricted access to TCP port 445. The queryText is expressed in Lacework Query Language (LQL) syntax, which is delimited by '{ }' and contains three sections:

  • Source data is specified in the 'source' clause. The source of data is the LW_CFG_AWS_EC2_SECURITY_GROUPS datasource. LQL queries generally refer to other datasources, and customizable policies always target a suitable datasource.

  • Records of interest are specified by the filter clause. In the example, the records available in LW_CFG_AWS_EC2_SECURITY_GROUPS are filtered for those whose IP protocol is tcp, whose from and to port is 445, and CidrIP is 0.0.0.0/0. The syntax for this filtering expression strongly resembles SQL.

  • The fields this query exposes are listed in the return clause. Because there may be unwanted duplicates among result records when Lacework composes them from just these columns, the distinct modifier is added. This behaves like a SQL SELECT DISTINCT. Each returned column in this case is just a field that is present in LW_CFG_AWS_EC2_SECURITY_GROUPS, but we can compose results by manipulating strings, dates, JSON and numbers as well.

The resulting dataset is shaped like a table. The table's columns are named with the names of the columns selected. If desired, you could alias them to other names as well.

Run a Query

To run an LQL query via editor.

lacework query run --range today

Run a query via ID (uses active profile):

lacework query run MyQuery --start "-1w@w" --end "@w"

Start and end times are required to run a query.

  • Specify start and end times in one of the following formats:
    • A relative time specifier
    • RFC3339 date and time
    • Epoch time in milliseconds
  • Specify start and end times in one of the following ways:
    • As StartTimeRange and EndTimeRange in the ParamInfo block within the query
    • As start_time_range and end_time_range if specifying JSON
    • As --start and --end flags
  • Start and end time precedence:
    • CLI flags take precedence over JSON specifications
    • JSON specifications take precedence over ParamInfo specifications

Update a Query

There are multiple ways you can update a query:

  • Type the query into your default editor (via $EDITOR)
  • Pass the query ID to load it into your default editor
  • From a local file on disk using the flag --file
  • From a URL using the flag --url

There are two formats you can use to define a query:

  • Javascript Object Notation (JSON)
  • YAML Ain't Markup Language (YAML)

To launch your default editor and update a query.

lacework query update <query_id>

Explore Data Sources

Several questions arise as you begin to write LQL queries.

What are the data sources I can query?

lacework query list-sources

What fields within the data source are available to me?

lacework query show-source <datasource_id>

How can I see a sample event from a specified datasource?

lacework query preview-source <datasource_id>

Relative Time Specifiers for LQL Queries

The Lacework CLI is dedicated to making the execution of LQL queries as simple as possible. While the API requires a strict RFC3339 date and time specification, the Lacework CLI introduces the concept of relative time specifiers.

Relative times allow a user to represent time values dynamically by using specifiers to communicate how to offset from the current time. For instance, a relative time of -24h would produce a date/time that was 24 hours less the current time. Relative times can also snap to a particular time. For instance, a relative time of @d would represent the start of the current day.

A relative time has 3 components:

  • A signed (+/-) integer
  • A relative time unit
  • A relative time snap

Lacework supports the following relative time units:

  • y - year
  • mon - month
  • w - week
  • d - day
  • h - hour
  • m - minute
  • s - second

Additional things to consider:

  • To represent the current time, one can either specify now or +0s
  • When specifying an integer and relative time unit, snaps are optional
  • When specifying a snap, the integer and relative time unit are optional. For instance, @d is actually interpreted as +0s@d

Usage:

lacework query run [query_id] --start -1d@d --end @d

Natural Time Ranges for LQL Queries

Natural time ranges allow a user to represent time range values using natural language. For instance, a natural time range of yesterday would represent a relative start time of -1d@d and a relative end time of @d.

A natural time has 3 components:

  • An adjective
  • A positive number (only when using the last adjective)
  • The full text representation of a relative time unit (i.e. year/years)

Lacework supports the following adjectives (disambiguating previous and last by design):

  • this/current
  • previous
  • last

Additional things to consider:

  • last implies "in the last". So last week reads as "in the last week" and represents a start time of -1w and an end time of now
  • previous will always snap. So previous week represents a start time of -1w@w and an end time of @w
  • yesterday is a valid natural time and is equivalent to previous day
  • today is a valid natural time and is equivalent to this day or current day

Usage:

lacework query run --range "this month"