Saved and Scheduled Queries
Scheduled Queries allow you to use saved SQL queries instead of streaming data as a feed into Panther's rules engine using Scheduled Rules.
Below is an overview of the Scheduled Query creation process:
- 1.Save a SQL query and enable a schedule, called a Scheduled Query.
- 2.
- Every time the Scheduled Query runs, the matching rows will be passed through the rules engine.
- If the Scheduled Rule returns any hits, one or more Alerts will be generated from the data and dispatched accordingly.
Please note that at least one Scheduled Rule must be associated with the Scheduled Query for the data to be processed by Panther.
By default, new accounts will have a limit of 10 scheduled queries. This limit is only precautionary, and can be increased via a support request. There is no additional cost from Panther for raising this limit, however you may incur extra charges from the database backend depending on the volume of data processed.
For examples of scheduled queries, please see the documentation: Scheduled Queries and Rules.
Your company will incur costs on your database backend every time a Scheduled Query runs. Please make sure that your queries can complete inside the specified timeout period.
Panther Console
Panther Analysis Tool
- 1.Log in to the Panther Console and click Data > Data Explorer in the left sidebar.
- 2.Enter a new query.
- If you have enabled the system-wide setting to require a LIMIT clause, make sure your query includes a LIMIT.
- 3.Below the code editor, click Save As.
- 4.In the "Save Query" window, fill in the form:
- Query Name: Add a descriptive name.
- Description: Describe the purpose of the query.
- Tags: Add tags to help you group similar queries together.
- Is this a scheduled query?: If you want to use this as a Scheduled Query, switch the toggle to ON.
- When you switch this toggle to ON, the options described below will appear.
- Is it active?: If you want this Scheduled Query to start running on your selected schedule, switch the toggle to ON. Configure one of the following interval options: Period or Cron Expression.
- Period (select if your query should run on fixed time intervals):
- Period(days) and Period(min): Enter the number of days and/or minutes after which the SQL query should run again. For example: setting a period of 0 days and 30 minutes will mean that the query will run every day, every 30 minutes.
- Timeout(min): Enter the timeout period in minutes, with a maximum allowed value of 10 minutes. If your query does not complete inside the allowed time window, Panther will retry 3 times before automatically canceling it.
- Cron Expression (select if your query should run repeatedly at specific dates):
- Minutes and Hours: Enter the time of day for the query to run.
- Day and Month (day of month): If you wish to have this query run on a specific day and month, enter the day and month.
- Day (day of week): If you wish to have this query run on a specific day of the week, enter the day.
- Timeout: Enter the timeout period in minutes, with a maximum allowed value of 10 minutes. If your query does not complete inside the allowed time window, Panther will retry 3 times before automatically canceling it.
- 5.Click Save Query.
Writing Scheduled Queries locally means creating metadata files that define SQL queries on your own machine. Upload the files to your Panther instance (typically via the Panther Analysis Tool) to control your Scheduled Query content.
Each saved query consists of:
- A YAML file (
.ymlor.jsonextension) containing metadata attributes of the Scheduled Query.
We also recommend managing these files in a version control system (VCS). Most commonly we see GitHub or GitLab used, which are managed git providers.
It's best practice to create a fork of Panther's open-source analysis repository, but you can also create your own repo from scratch.
Write your Scheduled Query and save it in your folder of choice as
new-scheduled-query.yml:
def rule(event):- Create a metadata file using the template below:
AnalysisType: scheduled_query
QueryName: ScheduledQuery_Example
Description: Example of a scheduled query for PAT
Enabled: true
Query:
- Your query appears here
SnowflakeQuery:
- Same query appears here
Tags:
- Your tags
Schedule:
CronExpression: "0 0 29 2 *"
RateMinutes: 0
TimeoutMinutes: 2
When your Scheduled Query is uploaded, each of the fields you would normally populate in the Panther Console will be auto-filled. See Scheduled Query Specification Reference for a complete list of required and optional fields.
Panther's Scheduled Query Crontab uses the standard crontab notation consisting of five fields: minutes, hours, day of month, month, day of week. Additionally, you will find a query timeout selector (with a maximum value currently set at 10 minutes). The expression will run on UTC.
The interpreter uses a subset of the standard crontab notation:
┌───────── minute (0 - 59)
│ ┌──────── hour (0 - 23)
│ │ ┌────── day of month (1 - 31)
│ │ │ ┌──── month (1 - 12)
│ │ │ │ ┌── day of week (0 - 6 => Sunday - Saturday)
│ │ │ │ │
↓ ↓ ↓ ↓ ↓
* * * * *
If you want to specify day by day, you can separate days with dashes (
1-5 is Monday through Friday) or commas, for example 0,1,4 in the Day of Week field will execute the command only on Sundays, Mondays and Thursdays. Currently, we do not support using named days of the week or month names.Using the crontab allows you to be more specific in your schedule than the Period frequency option:
Setting up a Scheduled Query makes the data available to the rule engine, but in order for Panther to actually use the data, a Scheduled Rule must be set up to use the Scheduled Query.
To create a scheduled rule:
- 1.Log in to the Panther Console and click Detections in the left sidebar menu.
- 2.Click Create New to create a new Detection.
- 3.Choose Scheduled Rule for the type.
- Fill out the form to give the rule a Severity and a Unique ID, and optionally a Display Name, Description, Runbook, Reference, Events Threshold, Custom Tags, Destination Overrides, Deduplication Period, and Summary Attributes.
- 4.Click the Scheduled Queries dropdown and select the previously saved scheduled query for the rule to run on.
- 5.Click the Functions & Tests tab. Enter your own custom Python code, or if all your filtering logic is already taken care of in the SQL, you can make sure that the event is set to return
truefor each row:def rule(event):return True - 6.
- 7.Click Save in the upper right side of the page when you are finished.
As soon as you click Save, the rule will become active and be run over the SQL at an interval detected by the run frequency of the scheduled query, assuming any rows are returned by the query.
After the scheduled query has a chance to run again, if the SQL returned any rows and the Python rule conditions were met, you should see rule matches starting to populate:
For queries that return multiple rows, each row is treated like a separate event and is processed by the rule. The number of alerts returned depends on the deduplication settings you configured. For example, you might have no deduplication configured and receive one alert for each row, or you can deduplicate by time and receive one alert for all rows combined.
We recommend doing as much of the data processing as possible in SQL. That way you can take advantage of all the database optimizations and improve rule performance. In an ideal case, your rule would be a simple
return true.You can delete queries individually or in bulk. Please note that scheduled queries must be unlinked from their respective rules in order to be deleted. This is to prevent users from accidentally erasing queries used by Scheduled Rules.
- 1.Log in to the Panther Console, then navigate to Data > Saved Queries.
- 2.In the list of Scheduled Queries, check the box next to the queries you want to delete.
- 3.In the upper right, click the Mass Action dropdown. Select Delete to delete, or Download to download the query.
- 4.In the upper right, click Apply.
- 5.In the popup that appears, click Confirm.
- 1.Log in to the Panther Console, then navigate to Data > Saved Queries.
- 2.Click ... in the upper right corner of the Scheduled Query that you want to deactivate.
- 3.In the dropdown menu, click Edit Query Metadata.
- 4.In the Update Query form, toggle the setting Is it active? to OFF to disable the query.
- 5.Click Update Query to save your changes.
To edit the name, tags, description, default data base, whether it's a scheduled query or active, the period, and the cron expression:
- 1.While viewing the Saved Query list, locate the query you wish to edit.
- 2.Click ... in the upper right corner of the query.
- 3.In the dropdown menu that appears, click Edit Query Metadata.
- 4.Make changes in the Update Query form as needed.
- 5.Click Update Query.
You can check the boxes next to Saved Queries to bulk-delete or to download multiple Saved Queries at once. Note that scheduled queries must be unlinked from their respective rules in order to be deleted.
- 1.In the Saved Queries page, check the box next to a query or multiple queries.
- 2.In the upper right corner, click the Mass Action dropdown menu. Select Delete or Download.
- 3.Click Apply.
In the Saved Query screen you can search for queries using:
- The search bar at the top of the queries list
- The date range selector in the upper right
- The Filters option in the upper right
- Filter by whether the query is scheduled or active, or filter by up to 100 tags.
Click on the name of the Saved Query to go directly to Data Explorer with the query pre-populated in the code editor.
In the Panther Data Lake settings page, you can optionally enable a setting that will check if a scheduled query has a LIMIT clause specified. Use this option if you're concerned about a scheduled query unintentionally returning thousands of results, potentially resulting in alert delays, Denial of Service (DoS) for downstream systems and general cleanup overhead from poorly tuned queries.
- 1.In the upper right corner of the Panther Console, click the gear icon. In the dropdown menu that appears, click General.
- 2.Click the Data Lake tab.
- 3.Scroll down to the "Scheduled Queries" header. Below the header, you will see the LIMIT clause toggle setting:
- 4.Next to "LIMIT Clause for Scheduled Queries," toggle the setting to ON to start enforcing LIMITs in Scheduled Queries going forward.
When set to ON, any new scheduled queries marked as "Active" cannot be saved unless a LIMIT clause is specified in the query definition.
Existing scheduled queries without a LIMIT clause will appear with a warning message in the list of Saved Queries, and edits cannot be saved unless a LIMIT clause is included.
The setting only checks for the existence of a LIMIT clause anywhere in the Saved Query. It does not check specifically for outer LIMIT clauses.
Required fields are in bold.
A complete list of scheduled query specification fields:
Scheduled Query Field Name | Description | Expected Value of Scheduled Query Field |
|---|---|---|
AnalysisType | Indicates whether this analysis is a Rule, Policy, Scheduled Query, or global. | scheduled_query |
QueryName | A friendly name to show in the UI. | String |
Enabled | Whether this rule is enabled. | Boolean |
Tags | Tags used to categorize this rule. | List of strings |
Description | A brief description of the rule. | String |
Query | A query that can run on any backend. If this field is specified, you should not specify a SnowflakeQuery or a AthenaQuery. | String |
SnowflakeQuery | A query specifically for a Snowflake backend. | String |
AthenaQuery | A query specifically for Athena. | String |
Schedule | The schedule that this query should run. Expressed with a CronExpression or in Rate Minutes. TimeoutMinutes is required to release the query if it takes longer than expected. Note that cron and rate minutes are mutually exclusive. CronExpression: "0 0 29 2 *" RateMinutes: 0 TimeoutMinutes: 2 | Map |
Last modified 2mo ago