---
title: Advanced Search
description: Datadog, the leading service for cloud-scale monitoring.
breadcrumbs: Docs > Log Management > Log Explorer > Advanced Search
---

# Advanced Search

## Overview{% #overview %}

When you need to further refine the results of a log search, use subqueries to compare your results against a secondary group of logs, or filter your logs using data from your Reference Tables.

## Filter logs with subqueries{% #filter-logs-with-subqueries %}

Use subqueries when you want to filter the results of a query based on the results of a secondary query. See subquery examples for two example scenarios.

To add a subquery filter:

1. Navigate to [Log Explorer](https://app.datadoghq.com/logs).
1. Enter a query in the search bar to filter your logs. This is the main query.
1. Click **+ Add**.
1. Under the **Add Query Filter** section, select **Logs**.

This introduces new elements to the query editor:

{% image
   source="https://docs.dd-static.net/images/logs/explorer/advanced_search/subquery.61ad69d4a484633a7ea45fa551cf7ebe.png?auto=format&fit=max&w=850 1x, https://docs.dd-static.net/images/logs/explorer/advanced_search/subquery.61ad69d4a484633a7ea45fa551cf7ebe.png?auto=format&fit=max&w=850&dpr=2 2x"
   alt="The subquery editor under the main search query" /%}

1. In the **where** field, use the dropdown menu to select the attribute you want to correlate on. The available attributes are from the logs returned by your main query.
1. Define your subquery filter in the **from** field.
1. In the **Select Column** dropdown menu that precedes the subquery filter, select the attribute you want to group and sort your subquery results by.
1. Select whether you want to use the **IN** or **NOT IN** operator:
   - The results from the **IN** operator only include logs where the attribute value is also found in the subquery results. For example, if you only want to see logs generated by `service:a` and is associated to a user who is also one of the top users of `service:b`.
   - The results from the **NOT IN** operator exclude logs where the attribute value is found in the subquery results. For example, if you only want to see `status:error` logs, but also want to filter out `status:error` logs where users associated with those logs eventually end up with a `status:success` log. See Filter outdated or superseded logs for a detailed example.
1. Optionally, reduce the number of subquery attribute values to match against. The default value and maximum value is `1000`. Choose between **top** (highest frequency values) or **bottom** (lowest frequency values).

### Subquery examples{% #subquery-examples %}

The following are scenarios where you need to use subqueries to get the information you need from your logs.

#### Filter outdated or superseded logs{% #filter-outdated-or-superseded-logs %}

Assume you operate an e-commerce platform. A log is generated each time one of your customers tries to place an order. You want to analyze your logs to understand the total value of potential purchases lost due to an ongoing issue with your website.

You realize, however, that an order can fail multiple times before successfully completing: meaning for that particular order ID, there are log entries in the search results for both `status:error` logs and `status:success` logs. If you extracted lists of unique order IDs from the two queries, this order ID would appear in both. With subqueries, you can obtain a mutually exclusive list.

In this example, you are only interested in logs for orders that did not eventually succeed. To exclude orders that eventually succeeded using the subquery feature:

1. Define a subquery for `status:success` logs.
1. Select the **NOT IN** operator to exclude orders from the subquery result set.

{% image
   source="https://docs.dd-static.net/images/logs/explorer/advanced_search/filter_outdated_example.f1fcf874587f5bb556591e1303f9b8cb.png?auto=format&fit=max&w=850 1x, https://docs.dd-static.net/images/logs/explorer/advanced_search/filter_outdated_example.f1fcf874587f5bb556591e1303f9b8cb.png?auto=format&fit=max&w=850&dpr=2 2x"
   alt="The query editor showing the setup for filtering out orders that ended up succeeding" /%}

#### Correlate across different log sources{% #correlate-across-different-log-sources %}

Assume you have a service named `network_directory` that monitors all internal network resources and access to those resources within your organization. Log events generated by this service include standard attributes (like `host`, `service`, `source`) and custom attributes like the client's IP address.

Additionally, you have another `device-manager` service that tracks all internal assets (infrastructure, employee devices, and so on.)

You are investigating an ongoing attack and observe there is a significant increase in API requests across almost all of your endpoints. You want to first identify IP addresses associated with anomalous request volumes so that you can block them at the firewall level. However, your internal services are some of the largest consumers of these endpoints and you need to exclude them from your query results to avoid mistakenly blocking them.

In this example, use `service:network_directory` as your main query, and then define a subquery filter for your `device-manager` service to filter out results from recognized devices.

{% image
   source="https://docs.dd-static.net/images/logs/explorer/advanced_search/narrow_dataset_example.558161fa5109656c98064b655b5c218b.png?auto=format&fit=max&w=850 1x, https://docs.dd-static.net/images/logs/explorer/advanced_search/narrow_dataset_example.558161fa5109656c98064b655b5c218b.png?auto=format&fit=max&w=850&dpr=2 2x"
   alt="The query editor showing the setup for filtering out the results for recognized devices" /%}

## Filter logs based on Reference Tables{% #filter-logs-based-on-reference-tables %}

{% alert level="danger" %}
Reference Tables containing over 1,000,000 rows cannot be used to filter events. See [Add Custom Metadata with Reference Tables](https://docs.datadoghq.com/integrations/guide/reference-tables.md) for more information on how to create and manage Reference Tables.
{% /alert %}

Reference Tables allow you to combine metadata with logs, providing more information to resolve application issues. Add a query filter based on a Reference Table to perform lookup queries. For more information on creating and managing this feature, see [Reference Tables](https://docs.datadoghq.com/reference_tables.md).

To apply a query filter with Reference Tables, click the **Add** button next to the query editor and select **Join with Reference Table**.

1. Select your reference table in the dropdown menu.
1. Select the log field to join on.
1. Select the **IN** or **NOT IN** operator to filter in or out matching logs.
1. Select the Reference Table column to join on.
1. (Optional) Select Reference Table columns used to enrich logs.
1. (Optional) Filter logs by directly querying data in Reference Table columns.

In the following example, a Reference Table containing product information is used to filter and enrich logs:

{% image
   source="https://docs.dd-static.net/images/logs/explorer/advanced_search/reference_table_join_filter.37b6f307592892d666f9bdad5fe6386c.png?auto=format&fit=max&w=850 1x, https://docs.dd-static.net/images/logs/explorer/advanced_search/reference_table_join_filter.37b6f307592892d666f9bdad5fe6386c.png?auto=format&fit=max&w=850&dpr=2 2x"
   alt="The Datadog Log Explorer with reference table search options highlighted. Includes numbered steps aligned with prior instructions" /%}

## Further reading{% #further-reading %}

- [Perform Log Analytics](https://docs.datadoghq.com/logs/explorer/analytics.md)
- [Export views from the Log Explorer](https://docs.datadoghq.com/logs/explorer/export.md)
- [Learn about Saved Views](https://docs.datadoghq.com/logs/explorer/saved_views.md)