Create a Query

This topic describes how to create a query with the Lacework CLI.

If you are new to the Lacework CLI, see Get Started to learn about installing and configuring the CLI.

note

For additional documentation about creating queries, see LQL Queries.

Learn About Datasources

Before you create a query, learn about the LQL datasources.

List All Datasources

Listing all datasources lets you discover their names.

This command lists all AWS datasources:

lacework query list-sources | grep AWS

List Datasource Details

List the details for the datasource you are interested in to see which fields to use in your query.

This command shows the details for a datasource:

lacework query show-source LW_CFG_AWS_EC2_SECURITY_GROUPS

Response

            DATASOURCE                      DESCRIPTION
---------------------------------+---------------------------------
  LW_CFG_AWS_EC2_SECURITY_GROUPS   Results from AWS EC2
                                   'describe-security-groups'

     FIELD NAME      DATA TYPE            DESCRIPTION
-------------------+-----------+---------------------------------
  BATCH_START_TIME   Timestamp   Beginning of time interval
  BATCH_END_TIME     Timestamp   End of time interval
  QUERY_START_TIME   Timestamp   Start time of query for this
                                 resource
  QUERY_END_TIME     Timestamp   End time of query for this
                                 resource
  ARN                String      ARN for the resource
  API_KEY            String      Key describing the API used to
                                 fetch data for this resource
  SERVICE            String      Service this resource belongs
                                 to
  ACCOUNT_ID         String      AWS Account ID
  ACCOUNT_ALIAS      String      User friendly alias for AWS
                                 Account
  RESOURCE_TYPE      String      Type of this resource
  RESOURCE_ID        String      Identifier for this resource
  RESOURCE_REGION    String      Region this resource belongs
                                 to
  RESOURCE_CONFIG    JSON        JSON Definition of this
                                 resource
  RESOURCE_TAGS      JSON        Tags associated with this
                                 resource

The RESOURCE_CONFIG field is frequently used in LQL. Because it is a JSON datasource, the LQL query must first convert the field using the array_to_rows() function. To know exactly which JSON fields you need, you can either read the cloud provider's API documentation, or write an LQL query to explore the full content before writing the actual policy.

Preview Events for a Datasource

For some datasources, you can run the lacework query preview-source command to show a preview of a sample event for a datasource.

lacework query show-source LW_CFG_AWS_EC2_SECURITY_GROUPS

Create a Query

This example detects unrestricted ingress to TCP port 445.

The following creates a query file on your local system and then adds it to your Lacework instance.

1. Open your text editor, create a new file, and add the following content:

   ---
   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
         }
     }
   • source: Specify the datasource(s) where the query looks for data. The example specifies LW_CFG_AWS_EC2_SECURITY_GROUPS.
   • filter: Specify the query's records of interest. The example filters the records available in LW_CFG_AWS_EC2_SECURITY_GROUPS.
   • return: List the fields the query exposes. The example adds the distinct modifier, which returns deduped event details because there may be unwanted duplicates among result records.
2. Save the file as YAML with the filename LW_Custom_UnrestrictedIngressToTCP445.yaml. Note the file's location.
3. In the Lacework CLI, run this command:

   lacework query create -f <path_to>/LW_Custom_UnrestrictedIngressToTCP445.yaml
   Response

   The query LW_Custom_UnrestrictedIngressToTCP445 was created.

Run a Query

This command runs the query you just created:

lacework query run LW_Custom_UnrestrictedIngressToTCP445

Example response

[
  {
     "ACCOUNT_ALIAS": "",
     "ACCOUNT_ID": "aaa",
      "RESOURCE_KEY": "arn:aws:ec2:us-east-2:aaa:security-group/sg-bbb",
     "RESOURCE_REGION": "us-east-2",
     "RESOURCE_TYPE": "ec2:security-group",
     "SERVICE": "ec2"
  }
]

Update a Query

To update the query you just created, use this command:

1. Run the following command:

   lacework query update LW_Custom_UnrestrictedIngressToTCP445
2. Update the content.
3. Save the query.
   Response

   The query LW_Custom_UnrestrictedIngressToTCP445 was updated.