Constructing Query Expressions

Overview

A ThreatConnect Query Language (TQL) query is constructed by using a parameter name, an operator, and a value or list of values, and you can combine multiple queries with parentheses and AND/OR logic. This article provides example TQL queries you can use in ThreatConnect. For a complete list of TQL operators and parameters you can use when constructing TQL queries, see TQL Operators and Parameters.

Before You Start

User Roles

• To use TQL to query for threat intelligence data objects in an Organization, your user account can have any Organization role.
• To use TQL to query for threat intelligence data objects in a Community or Source, your user account can have any Community role except Banned for that Community or Source.
• To use TQL to query for Workflow objects in an Organization, your user account can have any Organization role except App Developer.

Prerequisites

• To query for Workflow objects using TQL, turn on Workflow for your Organization on the Account Settings screen (must be an Accounts Administrator, Operations Administrator, or System Administrator to perform this action).

TQL Tips and Considerations

• TQL parameters and operators are not case sensitive. The examples in this article use a mix of uppercase and lowercase letters in these elements for readability purposes only. However, some String-based parameter values can be case sensitive.
• When writing a TQL query that includes an OR clause, enclose the entire query in parentheses to ensure that the query condition is maintained. This protocol is helpful in certain situations, such as when viewing the Browse screen for selected chart elements displayed on a dashboard Query card.

TQL Escape Character Sequences

When constructing TQL queries in API requests and Apps, certain characters must be escaped with a backslash character (\) if they are used within a string. See Table 1 for a list of these characters and their corresponding escape sequence.

Query for Indicators

Query for Indicators Based on Threat and Confidence Ratings

The following example queries for Indicators of any type that meet one of the following conditions:

• The Indicator has a Threat Rating equal to 3and the summary (the Indicator itself) does not contain bad.com
• The Indicator has a Confidence Rating of less than 20and is an IP address with a CIDR notation that does not fall in the range of 192.168.1.1/8

typeName in ("Address", "EmailAddress", "File", "Host", "URL", "ASN", "CIDR", "Mutex", "Registry Key", "User Agent") and (rating = 3 and summary NE 'bad.com') or (confidence < 20 and addressCIDR not in '192.168.1.1/8')

Query for Indicators By Applied Tags and Confidence Rating

The following example queries for Indicators with the Tag China, Russia, or Japan and a Confidence Rating greater than or equal to 70:

typeName in ("Address", "EmailAddress", "File", "Host", "URL", "ASN", "CIDR", "Mutex", "Registry Key", "User Agent") and (tag in ('china', 'russia', 'japan') and confidence >= 70)

Query for Indicators Added on a Particular Day

The next example queries for Indicators added at any time on January 1, 2021, normalized to your time zone. A range such as the one in this example should be used rather than a query that involves an equals sign (e.g., dateAdded = "2021-01-01") because the equals sign limits returned results to Indicators added at the zeroth hour of the given date, down to the millisecond, rather than Indicators added at any time on the specified date.

dateAdded >= "2021-01-01" and dateAdded < "2021-01-02"

Query for Indicators with Multiple Fields

Certain Indicator types, such as File Indicators and Registry Key Indicators, include multiple fields for which you can provide a value. For example, when creating a File Indicator, you can provide one or more of the following file hashes: MD5, SHA-1, and SHA-256. When constructing a query to search for Indicators that include multiple fields, you can use the value1, value2, and value3 parameters to search for Indicators based on a value assigned to one of their fields.

The next three examples show how to use the value1, value2, and value3 parameters to search for File Indicators based on their MD5, SHA-1, and SHA-256 hashes, where

• value1 corresponds to an MD5 hash,
• value2 corresponds to a SHA-1 hash, and
• value3 corresponds to a SHA-256 hash.

typeName in ("File") and value1 = "EE10F3CACB5B9024783654CA2F271BDB"

typeName in ("File") and value2 = "5DC3F3ACA959F4A965BBDC142223EC20081FDE1B"

typeName in ("File") and value3 = "518C4C27750B7BB716AA0D9C978893AB699E0C769F8279BCF9A3DBF542BF1DC6"

Similarly, the next set of examples shows how to use the value1, value2, and value3 parameters to search for Registry Key Indicators based on their Key Name, Value Name, and Value Type, where

• value1 corresponds to Key Name,
• value2 corresponds to Value Name, and
• value3 corresponds to a Value Type.

typeName in ("Registry Key") and value1 like "HKEY_CURRENT_USER%Software%MyApp"

typeName in ("Registry Key") and value2 = "msdb01234567.exe"

typeName in ("Registry Key") and value3 = "REG_NONE"

Query for Internationalized Domain Names

The following example queries for Host and URL Indicators whose summary contains xn--:

typeName in ("Host", "URL") and summary contains "xn--"

Query for Hosts Associated to a Particular Group

The following example queries for Host Indicators associated to the Group whose ID is 12345:

typeName in ("Host") and associatedGroup in (12345)

You may also use the hasGroup() nested query to perform this same type of search (see the “Query for Attributes” section for more information):

typeName in ("Host") and hasGroup(id in (12345))

Query for Objects Belonging to Multiple Owners

The following example queries for File and Host Indicators that exist in Demo Organization or Demo Community.

typeName in ("File", "Host") and ownerName in ("Demo Organization", "Demo Community")

For example, the following query searches for Adversary Groups that exist in Demo Organization or TI Organization:

typeName = "Adversary" and ownerName in ("Demo Organization", "TI Organization")

Query for Objects by Association Method

When querying for Groups and Indicators, you can use the associatedGroupSource and associatedIndicatorSource parameters to filter objects based on the method used to create a Group or Indicator association, respectively. The following are accepted values for both parameters:

• UNKNOWN: The association was created during a structured or unstructured Indicator import.
• MANUAL: The association was created from an object’s Details screen, including the Associations tab, Behavior tab (for File Indicators only), and Sharing tab (for all Group types except Task).
• API: The association was created using the v2, v3, or Batch API.
• TQL: The association was created via a TQL query added to an object.
• DNS: The association was created via the DNS resolution tracking feature (for Address and Host Indicators only).
• EMAIL: The association was created during the ingestion of an email.

For example, the following query will search for Adversary Groups that meet the following conditions:

• The Adversary Group is associated to the Indicator whose summary is badguy.com.
• The Adversary Group contains an Indicator association created via a TQL query.

typeName="Adversary" and hasIndicator(summary="badguy.com") and associatedIndicatorSource="TQL"

In the next example, the query will search for Adversary Groups associated to Indicators that meet the following conditions:

• The Indicator’s summary contains the word bad.
• The Indicator has been associated to another object via a TQL query.

typeName="Adversary" hasIndicator(summary contains "bad" AND associatedIndicatorSource="TQL")

Query for CAL ATL Report Groups by AI Summary

As of ThreatConnect version 7.4, AI-generated summaries are available for Report Groups in the CAL Automated Threat Library (ATL) Source. In the following example, the query will search for Report Groups in CAL™ ATL whose AI-generated summary (i.e., the text displayed on the AI Insights card of the Group’s Details screen) contains the word apt29.

typeName in ("Report") and ownerName="CAL Automated Threat Library" and insights contains "apt29"

Query for Open Task Groups Assigned to You

The following example searches for Task Groups with a status of Open and that are assigned to you:

typeName in ("Task") and taskAssignee = me and taskStatus != "Completed" and taskStatus != "Deferred"

Query for Attributes

For objects in ThreatConnect that contain Attributes (e.g., Indicators), you can construct queries to return objects containing Attributes of a specified type and value. Attributes may be referenced by assembling a TQL query starting with the word attribute followed by either the type ID or the type name, formatted with underscores instead of spaces (e.g., External ID becomes attributeExternal_ID). The exact composition of the query depends on the data type of the Attribute (e.g., String, Integer, DateTime).

This section provides examples of queries shown in pairs, where the first query specifies a reference by Attribute Type ID and the other by Attribute Type name. The queries also illustrate some of the ways the different data types can be used.

Example queries for String-type Attributes such as Region:

attribute161 = "Eastern Europe"

attributeRegion LIKE "%Asia"

Example queries for Date-type Attributes such as Source Date Time:

attribute21 > "01-01-2020"

attributeSource_Date_Time > " TODAY() - 10 DAYS"

Example queries for Integer-type Attributes such as Detection Percentage:

attribute86 >= 70

attributeDetection_Percentage < 50

Obtaining an Attribute Type ID

When writing TQL queries freehand, it is recommended that Attribute Types be specified by type name, because determining the type ID (attributeNN) for an Attribute requires additional steps. If the type ID must be used, follow these steps to obtain it:

1. Click Browse on the top navigation bar. Then click FILTERS at the top right of the Browse screen.
2. On the FILTERS menu, select the desired Attribute Type from the Attributes dropdown, enter any value in the Value column, and then click APPLY at the bottom right.
3. Click Advanced at the upper-right corner of the Browse screen to access the advanced-query filter. The search bar along the top of the screen will contain an autogenerated TQL query for the filtered search that includes a reference to the selected Attribute Type in attributeNN format.

Query for Intelligence Requirements

Query for Intelligence Requirement Results

The following example queries for results of the Intelligence Requirement (IR) with ID PIR-001 that last matched after 8/1/2023 and that exist on the user’s local ThreatConnect instance:

hasIntelRequirement(uniqueId="PIR-001") and lastMatchedDate > "2023-08-01" and isLocal=true

Query for Number of Local Results by Owner

The following example can be used on a dashboard query card to display a chart showing the number of local results after 8/1/2023:

lastMatchedDate > "2023-08-01" and isLocal=true

Query for Tags

When querying for Tags, you can use the queries provided in this section to filter the Tags by type. You can also use these queries within the hasTag() nested query when filtering for Indicators, Groups, and Victims.

Query for Tags Applied to an Indicator

To retrieve Tags applied to a specific Indicator (the Indicator whose summary is 71.6.135.131 in this example), use the following query:

hasIndicator(summary="71.6.135.131")

Query for Standard Tags

To retrieve only standard Tags (i.e., any Tag that is not an ATT&CK^® Tag), use the following query:

techniqueId is null

Query for ATT&CK Tags

To retrieve only ATT&CK Tags, use the following query:

techniqueId is not null

To retrieve only ATT&CK Tags whose technique ID starts with a specific set of digits (T1001 in this example), use a query in the following format:

techniqueId startswith "T1001"

To retrieve only deprecated ATT&CK Tags, use the following query:

active = false

When querying for Indicators, Groups, or Victims, use the following nested query to retrieve only those objects to which at least one ATT&CK Tag has been applied:

hasTag (techniqueId is not null)

Query for Main Tags

To retrieve only main Tags (that is, standard Tags for which a Tag normalization rule has been enabled so that Tags defined as synonymous to the main Tag are converted to the main Tag when applied to an object), use the following query:

normalized = true

Query for Objects by Tag

The following example queries for Host Indicators with one or more Tags applied to them:

typeName in ("Host") and hasTag()

The next example builds upon the previous one by querying for Host Indicators with a specific Tag applied to them (the Malicious Host Tag):

typeName in ("Host") and hasTag(summary="Malicious Host")

If you want to search for Host Indicators without the Malicious Host Tag applied to them, add the NOT operator before the hasTag() nested query, as demonstrated in the following example:

typeName in ("Host") and NOT hasTag(summary="Malicious Host")

To query for Host Indicators with both the Malicious Host and Targeted Attack Tags applied to them, use either of the following queries. The first query uses two hasTag() nested queries joined by an AND operator, while the second query uses a single hasAllTags() nested query that contains each Tag’s respective ID number.

typeName in ("Host") and hasTag(summary="Malicious Host") AND hasTag(summary="Targeted Attack")

typeName in ("Host") and hasAllTags(id in 100, 105)

Use Nested Queries to Filter on an Object’s Associations

In some cases, you may want to query for data by filtering on the elements of associated data. You can accomplish this by using a nested query. Parameters for nested queries include hasGroup(), hasIndicator(), hasVictim(), hasVictimAsset(), and hasTag() and can be called starting from any of the Group, Indicator, Victim, VictimAsset, or Tag query filters.

For example, the following query searches for Indicators that have one or more associated Groups. Because no parameters are defined within the hasGroup() nested query, Indicators that are associated to any Group, regardless of the Group’s properties, will be returned.

hasGroup()

The next example builds upon the previous one by querying for Indicators that are associated to specific Groups (Adversary Groups named “Harry”):

hasGroup(typeName = "Adversary" and summary = "Harry")

By defining a Group query inside the parentheses of the hasGroup() nested query, you can search for data based on the data’s association to Group(s) with a totally separate and arbitrary set of criteria. However, a normal query for the root data still exists outside of the hasGroup() nested query. For instance, the following query searches for Host Indicators associated to Adversary Groups named “Harry”:

typeName in ("Host") and hasGroup(typeName = "Adversary" and summary = "Harry")

The NOT operator can precede a nested query, enabling you to filter on data that do not have associations with the specified criteria. In the following example, the query searches for Host Indicators that are not associated to Adversary Groups named “Harry”:

typeName in ("Host") and NOT hasGroup(typeName = "Adversary" and summary = "Harry")

Finally, a single query can be nested multiple times, up to four levels of depth, such as in the following example query, which filters on Victim Assets:

typeName ="SocialNetwork" and victimName = "Sally" and hasGroup(typename = "Adversary" and summary = "Harry" and dateAdded < '2021-09-13' and hasIndicator(typename = "Address" and NOT hasTag(name="China")))

A lot is going on in this example, which demonstrates some of the more sophisticated capabilities of this feature. It is querying for Victim Assets of the Social Network variety for a Victim named Sally that are associated to an Adversary named Harry added before 9/13/2021, that in turn is associated to an IP address Indicator that does not have the Tag China.

Query Using Relative Date and Time Increments

Relative dates may be specified in TQL within quotation marks and supplied as the value for any date-type field. The following syntax is supported:

• NOW(): returns the exact current date and time (e.g., 01 September 2021 09:24.328).
• THISHOUR(): returns the date and time at the beginning of the current hour (i.e., :00.000).
• TODAY(): returns midnight (00:00.000) of the current date.
• THISWEEK(): returns midnight (00:00.000) of the previous Sunday.
• THISMONTH(): returns midnight (00:00.000) of the first day of the current month.
• THISYEAR(): returns midnight (00:00.000) of 1 January of the current year.

Increments, defined as an integer value followed by a label, may be used in conjunction with plus/minus (+/-) operators to specify date ranges. TQL supports the following labels:

• HR, HRS, HOUR, HOURS
• MIN, MINS, MINUTE, MINUTES
• DAY, DAYS
• WK, WKS, WEEK, WEEKS
• MO, MOS, MONTH, MONTHS
• YR, YRS, YEAR, YEARS

Examples

• dateAdded > "NOW() - 30 DAYS": returns data added within the last 30 days, as a rolling window relative to the exact moment in time that the query is run.
• dateAdded > "THISMONTH()": returns data added within the current month.
• dateAdded > "THISYEAR()": returns data added within the calendar year.
• dateAdded >= "THISYEAR() - 1 YEAR" AND dateAdded < "THISYEAR()": returns data added during the previous calendar year.

Workflow-Related Queries

Query for Open Tasks in High-Severity Cases

The following example queries for all open Tasks in high-severity Cases:

status = "Open" AND hasCase(severity = "High")

Query for Your Open Tasks Due This Week

The following example queries for all open Tasks that are due this week and are assigned to the current user (who, in this example, has a user ID number of 1234):

dueDate > "THISWEEK()" AND dueDate <= "THISWEEK() + 1 WEEK" AND status = "Open" AND targetType = "User" AND targetId = 1234

Query for Your Team’s Open Cases With Email-Related Artifacts

The following example queries for all open Cases assigned to the user’s team (which, in this example, has a user group ID of 39412) that include Artifacts relating to Emails:

status = "Open" AND targetType = "Group" and targetId = 39412 AND hasArtifact (typeName LIKE "Email%")

Query for Cases Whose Name Contains a Backslash (\)

The example in this section queries for all open Cases with a name containing at least one backslash character (\). Note that a double backslash (\\) is used in the query to escape the single backslash.

status = "Open" AND name contains "\\"

If you wanted to query for all open Cases with a name containing at least two backslash characters (\\) in a row, you would need to use four backslashes in the query (\\\\) to escape each of the two backslashes.

status = "Open" AND name contains "\\\\"

ThreatConnect^® is a registered trademark, and CAL™ is a trademark, of ThreatConnect, Inc.
MITRE ATT&CK^® and ATT&CK^® are registered trademarks of The MITRE Corporation.

20052-03 v.22.A