Templated Searches

Export collections of paramaterized SQL expressions for reuse

Overview

Templated Searches are Saved 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.

Panther uses the Django template language, which closely mirrors Jinja templates. This means most DBT templates are interoperable.

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.

-- 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.

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:

     -- pragma: template
     select {{p}}, {{q}}

4. To set variable values, add statements like the following at the top of the file:

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

   • Example:

     -- 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.

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.

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:

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

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:

   {% 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.

-- 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.

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

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

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

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.

When writing the SQL in the Query key:

• Include -- pragma: templateat 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:

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

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

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.

   If you would like the expanded SQL to be displayed (to aid in debugging, perhaps) include -- pragma: show macro expanded.

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

   {% 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:

   {{<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

-- 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:

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

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

The template will be replaced with the expanded SQL:

-- pragma: template
-- pragma: show macro expanded

select 1, 'queue'