DQL Filtering commands

dedup

Removes duplicates from a list of records.

You can use multiple field names or expressions for deduplication. The output of the dedup command will have the same number of records as there are unique combinations in the given fields or expressions.

The original order of the records is not preserved. Therefore, by default the sequence of records that are chosen during deduplication is random. If you want to pick a particular record out of the duplicates, you can use the sort parameter.

The count of records following the dedup command will be identical to the count of records following the summarize command. The summarize command groups together records with the same values for a given field and aggregates them, whereas dedup removes duplicate values from a list of records.

Syntax

dedup expr, [expr...], [sort:expr [asc|desc], [....expr [asc|desc]]]

Basic examples

Example 1: Show all locations

In the following example, the input for the dedup command are records that show the number of bookings and their locations historically. The dedup command removes duplicate records based on the field location and keeps a random record per unique location.

data record(timestamp = 1, location = "Vienna", bookings = 1254),
     record(timestamp = 1, location = "London", bookings = 4564),
     record(timestamp = 2, location = "Vienna", bookings = 1457),
     record(timestamp = 2, location = "London", bookings = 8741),
     record(timestamp = 3, location = "Vienna", bookings = 1654)
| dedup location

Query result:

Example 2: Bookings per location

In the following example, the input for the dedup command are records that show the number of bookings and their locations historically. The dedup command removes duplicate records based on the field location and for each location keeps the record with the latest timestamp.

data record(timestamp = 1, location = "Vienna", bookings = 1254),
     record(timestamp = 1, location = "London", bookings = 4564),
     record(timestamp = 2, location = "Vienna", bookings = 1457),
     record(timestamp = 2, location = "London", bookings = 8741),
     record(timestamp = 3, location = "Vienna", bookings = 1654)
| dedup location, sort: { timestamp desc }

Query result:

Example 3: Bookings over time

In the following example, the input for the dedup command are records that show the number of bookings and their locations historically. The dedup command removes duplicate records based on the fields timestamp and location and per unique combination keeps the record with the largest value in the bookings field.

data record(timestamp = 1, location = "Vienna", bookings = 1254),
     record(timestamp = 1, location = "London", bookings = 4553),
     record(timestamp = 1, location = "London", bookings = 4564),
     record(timestamp = 2, location = "Vienna", bookings = 1451),
     record(timestamp = 2, location = "Vienna", bookings = 1457),
     record(timestamp = 2, location = "London", bookings = 8741)
| dedup { timestamp, location }, sort: { bookings desc }

Query result:

Practical example

Example: List currently open vulnerabilities

The following example shows the currently open vulnerabilities. Because vulnerabilities are stored as snapshot, the dedup command is used to retrieve the most recent snapshot of a vulnerability.

fetch events
| filter event.provider == "Dynatrace"
| filter event.type == "VULNERABILITY_STATE_REPORT_EVENT"
| filter event.level== "VULNERABILITY"
| filter vulnerability.resolution.status == "OPEN"
| filter vulnerability.mute.status != "MUTED"
| dedup vulnerability.display_id, sort: { timestamp desc }

filter

Reduces the number of records in a list by keeping only those records that match the specified condition.

Syntax

filter condition [, input]

Basic examples

Example 1: Filter on one condition

The following query uses the filter command to filter on the field event.

data record(event = "search failed", prodId = 5, qty = 6, host = "A"),
     record(event = "product found", prodId = 3, qty = 3, host = "A"),
     record(event = "search failed", prodId = 4, qty = 7, host = "B"),
     record(event = "search failed", prodId = 2, qty = 7, host = "C")
| filter event == "search failed"

Query result:

Example 2: Filter on multiple conditions

The following query uses the filter command to filter records based on evaluating multiple conditions. Logical operators connect the conditions.

data record(event = "search failed", prodId = 5, qty = 6, host = "A"),
     record(event = "product found", prodId = 3, qty = 3, host = "A"),
     record(event = "search failed", prodId = 4, qty = 7, host = "B"),
     record(event = "search failed", prodId = 2, qty = 7, host = "C")
| filter in(host, array("B", "C")) or prodId == 3

Query result:

Practical example

Example: Filter for critical logs

The following example uses the fetch command to load data from logs. The filter command keeps only error records containing the string failed in the content field.

fetch logs
| filter loglevel == "ERROR" and matchesPhrase(content, "failed")

filterOut

Removes records that match a specific condition.

Syntax

filterOut condition [, input]

Basic examples

Example 1: Filter out by one condition

The following query uses the filterOut command to remove records based on the field event.

data record(event = "search failed", prodId = 5, qty = 6, host = "A"),
     record(event = "product found", prodId = 3, qty = 3, host = "A"),
     record(event = "search failed", prodId = 4, qty = 7, host = "B"),
     record(event = "search failed", prodId = 2, qty = 7, host = "C")
| filterOut event == "product found"

Query result:

Example 2: Filter out by multiple conditions

The following query uses the filterOut command to remove records based on evaluating multiple conditions. Logical operators connect the conditions.

data record(event = "search failed", prodId = 5, qty = 6, host = "A"),
     record(event = "product found", prodId = 3, qty = 3, host = "A"),
     record(event = "search failed", prodId = 4, qty = 7, host = "B"),
     record(event = "search failed", prodId = 2, qty = 7, host = "C")
| filterOut host == "A" and qty > 3 or prodId == 4

Query result:

Practical example

Example: Filter out informational logs

The following example uses the fetch command to load data from logs. The filterOut command removes records with a loglevel value of NONE or INFO.

fetch logs
| filterOut loglevel == "NONE" or loglevel == "INFO"