Custom Queries

Avanan stores the metadata of all items (emails, files, user logins, etc.) obtained through the public APIs of the cloud applications you are protecting and inspected by the system.

For items found to be harmless, metadata is retained for two weeks.

For malicious items, the metadata is stored indefinitely.

Custom Queries give you direct access to this database of metadata.

Use Custom Queries to:

  • Troubleshoot
  • Build custom reports
  • Perform bulk action such as quarantining phishing emails

Creating and Saving a New Query

You can create and save custom queries to analyze a specific SaaS application for immediate and future use.

To create and save a new query:

  1. From the left panel, click Analytics > Custom Queries.
  2. Click Create New Query.
    It displays a list of available templates for each protected SaaS application.
    You can use the Filter by search box to filter through the templates.
  3. Select the required template.
  4. After you select a template, a query with predefined conditions and columns is displayed.
    You can edit the Conditions and Columns to fit your needs. See the section below.
  5. To save the query for future use:
    1. Click Query
    2. Click Save As.
    3. Enter the query details and click OK.

Editing the Query Columns and Conditions

After you have selected a template, use the options in Custom Queries to edit the template for your specific needs.

You can edit the template's predefined columns by choosing to add, remove or rename columns.

In addition, you can set conditions on columns.

To add a column:

  1. Open the query to which you need to add the Columns. 
  2. Click Columns.
    A drop-down list opens.
  3. Click on a column to select it, and then click Apply.
    Note - Certain columns are marked with an arrow. Click on the arrow to see more options.

To remove a column:

  1. Click the column's name.
    A condition box opens.
  2. Select Remove column.
    The column gets removed.

To edit (rename) a column's name:

  1. Click on the column's name.
    A condition box opens.
  2. Select Rename column.
    The Rename column box opens.
  3. In the Column name, delete the column's current name and then enter a new name.
  4. Click OK.

To sort a column:

  1. Click on the column's name.
    A condition box opens.
  2. In the Sort field, choose either Sort ascending or Sort descending.
    Note - If the query returns more than 1,000 results, then sorting is not available.

To add a condition to a column:

  1. Click the column's name.
    An editing box opens.
  2. In the condition box, set the condition's parameters.
    Note - You can add more than one condition to a column. To add another condition to the same column click Add condition.
  3. Click OK.
    After adding a condition, it appears next to Add Condition.

You can also add conditions without the need to display the corresponding column. In the section above the query's result table, click Add Condition, and then select from the list of available fields.

Note - By default, all conditions are evaluated with an AND relationship when returning the query's results. For more advanced conditions, click on the gear icon (in the top right corner) and then select Edit Conditions.

Bulk Actions on Query Results

Click on Query Actions to see options for bulk remediation: quarantine, move to junk, or add phishing alert.

If no items in the query's results are selected, the action will be taken on all items. You can select only some items before choosing a manual action to apply that action on those items only.

Additionally, the Send Email Alert option sends an email alert to your email for each item selected in the query's result. A  pop-up enables you to configure the template before sending alerts.

Exporting Query Results

In Custom Queries, you have the option to export the query's results to your email.

This sends an email to your email address with the query's results in any of these file formats.

  • CSV
  • JSON
  • XSLX

To export a query's results to your email:

  1. Go to Analytics > Custom Queries.
  2. Run and save the query. For more information, see Creating a new query.
  3. Click Query Actions, and then select Export Results.
  4. In the Email report to field, enter the email address.
  5. In the Format field, select the required file format.
    • CSV
    • JSON
    • XLSX
  6. Click Export.

Scheduled reports based on Custom Query results

To schedule a query's result export:

  1. Run the query.
  2. Ensure that the query is saved.
  3. Click Query, and then choose Scheduled Report.
    Note - Choose the email address to have the query sent to, the frequency (daily/weekly/monthly) and the exact day and time. Double-click the report to open it.

Using a Query as a Detect and Prevent Policy Rule

Sometimes you may want to create an action (such as quarantine) that will apply to future events matching the query's conditions. In such a case, you can use your query as a policy rule in the Detect and Prevent mode.

Note - No action will be taken on the current results of the query, only future results will be impacted.

To use the query as a Detect and Prevent rule:

  1. In Custom Queries, open a saved query.
  2. Click Query Actions.
  3. Choose an action, such as quarantine, in the list of available actions.
  4. In the pop-up window that opens, you can choose to edit the name of the action, and then click OK.
    Afterward, the action should appear in the menu under Query Actions.
    Note - Actions linked to queries are automatically taken from that point forward in the Detect and Prevent mode. However, policy rules keep priority over custom queries.