> For the complete documentation index, see [llms.txt](https://docs.panther.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.panther.com/~/changes/Dd8nx2iqd1Pp2OzWJaWk/search/scheduled-searches/templated-searches.md).

# Templated Searches

Export collections of paramaterized SQL expressions for reuse

## Overview

Templated queries are [Saved Searches](/~/changes/Dd8nx2iqd1Pp2OzWJaWk/search/scheduled-searches.md) that contain variables. Template macros are templated queries 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 Query.

Commonly run SQL expressions can be saved as template macros in a single, library-like saved query, 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. 
{% 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:

     <pre class="language-sql" data-overflow="wrap"><code class="lang-sql">-- pragma: template
     select {{p}}, {{q}}
     </code></pre>
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:

     <pre class="language-sql" data-overflow="wrap"><code class="lang-sql">-- pragma: template
     {% set p = "1" %}
     {% set q = "'queue'" %}

     select {{p}}, {{q}}
     </code></pre>
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](/~/changes/Dd8nx2iqd1Pp2OzWJaWk/search/scheduled-searches.md#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 query 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 %}`&#x20;
   * 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](/~/changes/Dd8nx2iqd1Pp2OzWJaWk/search/scheduled-searches.md#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 %}`&#x20;
  * 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.

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>If you would like the expanded SQL to be displayed (to aid in debugging, perhaps) include <code>-- pragma: show macro expanded</code>.</p></div>
3. Import your previously-defined macro with a statement structured like the following:

   ```sql
   {% import '<name of saved query>' <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 query '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'
```


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## 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, and the optional `goal` query parameter:

```
GET https://docs.panther.com/~/changes/Dd8nx2iqd1Pp2OzWJaWk/search/scheduled-searches/templated-searches.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

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.