Query Builder

Overview

In Query Builder, you can construct a data lake query using no-code filters. This allows you to search and investigate your Panther data without extensive SQL knowledge, making it an alternative to Data Explorer for running queries. You can also save the queries you create in Query Builder for easy reuse.

With Query Builder, you can save time previously spent on writing SQL commands, and expand who on your team can use Panther. If you want to understand how the filters you've used to construct a query in Query Builder translate to raw SQL, Query Builder shows you the resulting SQL command—which you can then run in Data Explorer, if you'd like.

How to use Query Builder in Panther

Query Builder considerations

When building a search with Query Builder, consider the following:

• You can only search one table at a time. If you need to search across multiple tables, we recommend using Indicator Search or Data Explorer instead.

• When adding multiple filters, the operational logic is AND.

  • OR filters are not currently supported.

Step 1: Access the Query Builder

1. Log in to your Panther Console.

2. In the left sidebar, click Investigate > Query Builder.

Step 2: Apply filters

Use the available filters to build a query:

• Date and time range

  • By default, the time range is set to the last 24 hours.

• Database and Table

  • Select your data source using these drop-down menus. By default, Database is set to panther_logs.public, which includes all log data stored in the Panther data lake. You can type in the field to search values.

• Filters conditions

  • To add filter conditions, click Add New on the right side of the Filters tile.

  • Multiple filters are joined by an AND clause. Query Builder does not support OR conditions.

  • You can filter fields using a variety of operators for each data type. For a complete list of operators and functions, refer to Query Builder Filter Operators.

  • JSON Object paths:

    • To filter by JSON object paths instead of fields, click Enter Nested JSON Object Path and type or copy the path you want to filter.

    

    • Nested JSON Object filters are not data type specific. Pick the operator from the data type that matches the values in the object.

      • To revert to a field selection, click Use Field List.

Limit the results of your search

After adding a date, database, table, and filters, use the Limit results to drop down to limit how many records are returned when the search is executed. Limiting the query will return results faster.

Panther limits the size of results to 50 by default.

Step 3: Execute your search

• After you have defined the parameters of your query, click Search to execute.

While a query is executing, you can click Cancel to cancel the search and start over.

Copy as SQL

Optionally, you can copy the resulting SQL from your query, for further analysis in Data Explorer or any other external application.

1. At the bottom of the query, click Copy as SQL.

   • This will copy the SQL command to your clipboard. You can then paste the query into Data Explorer or any other external application for further analysis.

2. Paste the command into Data Explorer or other applications to analyze further.

SELECT
   p_event_time as p_timeline,
   *
FROM
   panther_logs.public.aws_alb
WHERE
   (type = 'https' AND clientPort = 45705) AND p_occurs_between('2023-01-16 20:22:56Z','2023-01-17 20:22:56.219Z')
ORDER by p_event_time ASC
LIMIT 50

Managing Saved Queries with Query Builder

Saving the queries you create in Query Builder means you won't have to rebuild them each time you want to run them. Currently, Saved Queries created in Query Builder cannot be scheduled. Learn more about Saved Queries on Saved and Scheduled Queries.

Save a query in Query Builder

1. Follow the steps in How to use Query Builder to build a query.

2. Under the filters, click Save As.

   

3. A Save Query modal will pop up. Fill in the following fields:

   • Query Name: Add a descriptive name.

   • Tags (optional): Add tags to help you group related queries.

   • Description (optional): Describe the purpose of the query.

4. Click Save Query.

Open and reuse a Saved Query in Query Builder

After creating a Saved Query in Query Builder, you can view and reuse the query. It can be opened from the Query Builder page, or from the Saved Queries page.

Update a Saved Query in Query Builder

1. Open a Saved Query by following the instructions in Open and reuse a Saved Query in Query Builder.

   • You will now be on the Query Builder page, with the Saved Query populated.

2. Make your desired changes to the query.

3. Under the filters, click Update.

   

   • An Update Query modal will pop up.

     • Modify Query Name, Tags, or Description, if desired.

4. In the Update Query modal, click Update Query.

Work with Query Builder results

When executing a search with Query Builder, results will be displayed in a table and a histogram (currently in closed beta).

Query Builder results table

Query Builder and Data Explorer share a common results table and all the associated functionality.

• You can filter Query Builder results by following the instructions in the Data Explorer documentation under "Filter Data Explorer results."

• You can summarize Query Builder results by following the instructions in the Data Explorer documentation under "Summarize Column Data."

Query Builder results histogram

In addition to displaying query results in a table, Query Builder renders results in a histogram, as a function of time. This visualization may be useful in identifying spikes in activity, gaining insight into trends, and iteratively honing searches.

The Query Builder histogram mirrors the one available within Indicator Search—see the Drill Down instructions to learn how to filter results by interacting with the graph.

Query Builder examples

Show VPCFlow log activity for an IP address

During an investigation, often particular IP addresses are identified as being of interest (e.g., a known command and control node). Once the role of an IP address is identified, isolating and explaining that activity helps indicate which resources are likely to be compromised.

1. Select a date range.

2. Select the database and table (aws_vpcflow).

3. Add a filter to search data relating to a specific IP address.

   • Field: p_any_ip_addresses

   • Operator: has

   • Value: Enter the IP address.

Find all console "root" sign-ins in CloudTrail

The root account should seldom sign into the AWS console; find all such sign-ins using Query Builder.

1. Select a date range.

2. Select the database and table (aws_cloudtrail).

3. Add filters to search event types and user ARN relating to root sign-ins.

   • Filter 1:

     • Field: eventType

     • Operator: is

     • Value: AwsConsoleSignin

   • Filter 2:

     • Field: useridentity.arn

     • Operator: like

     • Value: root

Reference

Query Builder Filter Operators