# Templated Searches

## Overview

Templated Searches are [Saved Searches](https://docs.panther.com/search/scheduled-searches) (written in SQL) that contain variables. Template macros are Templated Searches which can be imported into other Saved Searches, and called with arguments (similar to function calls).

It is possible to save a Templated Search without defining it as a template macro. In this case, the query cannot be run elsewhere, and the variable values are provided in the same Saved Search.

Commonly run SQL expressions can be saved as template macros in a single, library-like Saved Search, making it easier to manage and reuse your complex SQL code. You could, for example, define "libraries" of related queries for the below areas:

* Employee macros
* Correlating macros
* Enrichment macros

Templated Searches are defined in Panther by including `-- pragma: template` as the first line of SQL.

{% hint style="info" %}
Panther uses the [Django template language](https://django.readthedocs.io/en/1.7.x/topics/templates.html), which closely mirrors [Jinja templates](https://jinja.palletsprojects.com/en/3.1.x/). This means most [DBT](https://www.getdbt.com/) templates are interoperable.

Panther's templating engine is built on [pongo2](https://github.com/flosch/pongo2?tab=readme-ov-file), which means that not all Django syntax is supported. See [caveats listed for pongo2](https://github.com/flosch/pongo2?tab=readme-ov-file#caveats) for more information.
{% endhint %}

### Example use case

To demonstrate the usefulness of template macros, imagine you have a complex query (spanning, say, 50 lines of SQL) that requires a date range and username. Without template macros, each time you want to run this search, you must edit it to input a specific date range and username, which can be tedious and error prone.

Instead, this complex query can be created as a template macro (named `user_activity_profile` in the example below) in a Templated Search (named `user queries` in the example below), with variables for the date range and username.

You can then create a new query like the below, which imports the template macro (`user_activity_profile`) from the Templated Search (`user queries`) and call the macro, passing in username and date time values. You can then save *this* search (calling it `run_user_activity_profile`, for example), for even faster access.

```sql
-- pragma: template

--loads query user_activity_profile from the saved query 'user queries'
{% import 'user queries' user_activity_profile %}

{{user_activity_profile('bob_smith', '2023-01-01 00:00:00', '2023-01-02 00:00:00')}}
```

## How to use Templated Searches

You can create a Templated Search that uses variables without defining it as a macro. In this case, while the query cannot be imported in other Saved Searches, future execution of the query will be simpler, as all variable values are defined at the top of the file.

### Creating a Templated Search

You can create a Templated Search in Data Explorer within the Panther Console, or locally, in the CLI workflow.

{% tabs %}
{% tab title="Console" %}
**How to create a Templated Search in the Panther Console**

1. In the left-hand navigation bar of your Panther Console, click **Investigate** > **Data Explorer**.
2. In the SQL editor, add `-- pragma: template` at the top.
3. Create a SQL query that declares variables using `{{}}` (double curly brackets).
   * Example:

     ```sql
     -- pragma: template
     select {{p}}, {{q}}
     ```
4. To set variable values, add statements like the following at the top of the file:

   ```sql
   {% set <variable one name> = <variable one value> %}
   {% set <variable two name> = <variable two value> %}
   ```

   * Example:

     ```sql
     -- pragma: template
     {% set p = "1" %}
     {% set q = "'queue'" %}

     select {{p}}, {{q}}
     ```
5. Click **Save as,** and provide values for the following fields:
   * **Query Name**: Add a descriptive name.
   * **Tags**: Add tags to help you group similar queries together.
   * **Description**: Describe the purpose of the query.
   * **Default Database**: Choose the database to query from.
   * **Is this a Scheduled Query?**: Toggle this `ON` if you would like this query to run on a defined interval.
     * If you toggle this `ON`, provide a time interval.
6. Click **Save Query**.
   {% endtab %}

{% tab title="CLI" %}
**How to create a Templated Search in the CLI workflow**

To create a Templated Search in the CLI workflow, follow the [How to create a Saved Search in the CLI workflow instructions](https://docs.panther.com/search/scheduled-searches/..#how-to-create-a-saved-search-in-the-cli-workflow).

When writing the SQL in the `Query` key:

* Include `-- pragma: template` at the top.
* Add the query, with variables enclosed in double curly brackets.

Example:

```yaml
Query: |-
    -- pragma: template
    {% set p = "1" %}
    {% set q = "'queue'" %}
    select '{{p}}', '{{q}}'
```

{% endtab %}
{% endtabs %}

### Running a Templated Search in Data Explorer

You can run a previously defined Templated Search in Data Explorer by opening it and providing variable values.

1. In the left-hand navigation bar of your Panther Console, click **Investigate** > **Data Explorer**.
2. In the upper-right corner, click **Open Saved Query**.
3. Select the Templated Search you would like to run, then click **Open Query**.
4. Ensure all variables in your SQL expression have values defined at the top of the file, in statements like the following:

   ```sql
   {% set <variable one name> = <variable one value> %}
   {% set <variable two name> = <variable two value> %}
   ```
5. Click **Run Query**.

**Example**

In the example below, the `p` and `q` variables are given values of `1` and `'queue'`, respectively.

```sql
-- pragma: template
{% set p = "1" %}
{% set q = "'queue'" %}

select {{p}}, {{q}}
```

## How to use template macros

Template macros are one or more Templated Searches that are exported, meaning they can be imported in other searches. It may be particularly useful to group multiple macros in one templated query, effectively creating a "library" of related macros.

### Creating template macros

You can create template macros in Data Explorer within the Panther Console, or locally, in the CLI workflow.

{% tabs %}
{% tab title="Console" %}
**How to create template macros in the Panther Console**

1. In the left-hand navigation bar of your Panther Console, click **Investigate** > **Data Explorer**.
2. In the SQL editor, add `-- pragma: template` at the top.
3. Create one or more SQL queries that declare variables using `{{}}` (double curly brackets).
4. Surround each templated query with:
   * Before the query: `{% macro <macro name>(<variable_one>, <variable_two>) export %}`
   * After the query: `{% endmacro %}`
5. Click **Save as,** and provide values for the following fields:
   * **Query Name**: Add a descriptive name.
     * This value will be used later, on import.
   * **Tags**: Add tags to help you group similar queries together.
   * **Description**: Describe the purpose of the query.
   * **Default Database**: Choose the database to query from.
   * **Is this a Scheduled Query?**: Leave this toggled `OFF`.
6. Click **Save Query**.

**Example**

```sql
-- pragma: template
{% macro my_query1(p, q) export %}
select '{{p}}', '{{q}}'
{% endmacro %}

{% macro my_query2(x) export %}
select {{x}}}
{% endmacro %}
```

{% endtab %}

{% tab title="CLI" %}
**How to create a templated macros in the CLI workflow**

To create a Templated Search in the CLI workflow, follow the [How to create a Saved Search in the CLI workflow instructions](https://docs.panther.com/search/scheduled-searches/..#how-to-create-a-saved-search-in-the-cli-workflow).

When writing the SQL in the `Query` key:

* Include `-- pragma: template`at the top.
* Add one or more queries, with variables enclosed in double curly brackets.
* Surround each templated query with:
  * Before the query: `{% macro <macro name>(<variable_one>, <variable_two>) export %}`
  * After the query: `{% endmacro %}`

Example:

```yaml
Query: |-
    -- pragma: template
    {% macro my_query1(p, q) export %}
    select '{{p}}', '{{q}}'
    {% endmacro %}

    {% macro my_query2(x) export %}
    select {{x}}}
    {% endmacro %}
```

{% endtab %}
{% endtabs %}

### Calling template macros in other queries

You can import previously created template macros, and run them by passing in variable values.

1. In the left-hand navigation bar of your Panther Console, click **Investigate** > **Data Explorer**.
2. In the SQL editor, add a `-- pragma: template` statement at the top.

{% hint style="info" %}
If you would like the expanded SQL to be displayed (to aid in debugging, perhaps) include `-- pragma: show macro expanded`.
{% endhint %}

3. Import your previously-defined macro with a statement structured like the following:

   ```sql
   {% import '<name of Saved Search>' <name of macro> %}
   ```
4. Call the macro, passing in values for its arguments, with a statement structured like the following:

   ```sql
   {{<name of macro>('<value of variable one>', '<value of variable two>')}}
   ```
5. Click **Run Query**.
   * Optionally save the query by clicking **Save as**.

#### Full example

```sql
-- pragma: template

--loads my_query1 from the Saved Search 'example macros'
{% import 'example macros' my_query1 %}

{{my_query1('1.1.1.1', 'test')}}
```

### Debugging template macros

When running a template macro in Data Explorer, you can view the expanded SQL statement (rather than only the template code) by adding `-- pragma: show macro expanded` below the `-- pragma: template` statement.\
\
For example, if you run the below in Data Explorer:

```sql
-- pragma: template
-- pragma: show macro expanded
{% set p = "1" %}
{% set q = "'queue'" %}

select {{p}}, {{q}}
```

The template will be replaced with the expanded SQL:

```sql
-- pragma: template
-- pragma: show macro expanded

select 1, 'queue'
```

### Custom functions

Panther has implemented a custom function to make templating easier.

#### `split`

Django doesn't support splitting strings into lists. Panther has added a `split` function to provide this capability. This can be useful when working with `for` loops.

For example, consider the following query to easily union across tables:

```jinja
{% for table_name in split("aws_cloudtrail,panther_audit", ",") %}
    {% if not forloop.First %} union {% endif %}
    select
        '{{ table_name | upper }}' as p_table_name,
        p_event_time,
        p_log_type,
        p_any_ip_addresses,
        p_row_id,
    from panther_logs.public.{{table_name}}
    where p_occurs_since(1d)
        and ARRAY_CONTAINS('142.161.78.139'::variant, p_any_ip_addresses)
{% endfor %}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.panther.com/search/scheduled-searches/templated-searches.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.