Constructing Query Expressions

A ThreatConnect Query Language (TQL) query expression is constructed by using a parameter name, an operator, and a value or list of values. Multiple expressions can be combined by using parentheses and AND/OR logic to tie queries together. The following examples illustrate some useful TQL queries. See TQL Operators and Parameters for a list of available operators, general TQL parameters, and Workflow-related TQL parameters.

Basic Query

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')

In this example, the query is searching for Indicators of any type that meet one of the following conditions:

• The Indicator has a Threat Rating equal to 3 and the summary (the Indicator itself) does not contain ‘bad.com’
• The Indicator has a Confidence Rating of less than 20 and 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 (tag in ('china', 'russia', 'japan') and confidence >= 70)

In this example, the query is searching for Indicators with the Tag ‘China’, ‘Russia’, or ‘Japan’ and a Confidence Rating greater than or equal to 70.

Query for Indicators Added on a Particular Day

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

In this example, the query is searching 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.

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.

Each of the following examples shows 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, each of 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 Objects Belonging to Multiple Owners

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

In this example, the query is searching for File and Host Indicators that exist in Demo Organization or Demo Community.

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

Super Users can leverage this functionality to search for objects that exist in multiple Organizations on their instance. In this example, the query is searching for Adversary Groups that exist in Demo Organization or 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 All of the User’s Open Tasks

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

This example refers to the Task Group in ThreatConnect, not a Workflow Task. To use this query, select Groups from the dropdown menu to the left of the advanced-query filter search bar on the Browse screen.

Query for Internationalized Domain Names

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

Query for Hosts Associated to a Particular Group

typeName in ("Host") and associatedGroup in (<Group ID>)

Equivalent nested query (see next section):

typeName in ("Host") and hasGroup(id in (<Group ID>))

Query for Attributes

For objects in ThreatConnect that can 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 keyword 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 shows examples of queries shown in pairs, where the first one 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 query for String-type Attributes such as Region:

• attribute161 = "Eastern Europe"
• attributeRegion LIKE "%Asia"

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

• attribute21 > "01-01-2020"
• attributeSource_Date_Time > " TODAY() - 10 DAYS"

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

• attribute86 >= 70
• attributeDetection_Percentage < 50

When writing TQL queries freehand, it is recommended that Attributes 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. Navigate to the Browse screen.
2. Click the FILTERS menu.
3. Select the Attribute from the Attributes dropdown menu at the top right.
4. Enter any value in the Value column.
5. Click the APPLY button at the bottom right.
6. Click Advanced at the upper-right corner of the Browse screen.
7. An autogenerated TQL query expression for the filtered search that includes a reference to the selected Attribute in attributeNN format will be displayed in the search bar along the top of the Browse screen. Locate this value and use it in custom TQL queries as desired.

See the “Filtering Results” and “Advanced Query” sections of The Browse Screen for more information.

Use Nested Queries to Filter on a Data Object’s Associated Intel

In some cases, you may want to query for data by filtering on the elements of associated data. A simple example would be to show Indicators that are associated to an Adversary with a name of “Harry”. A query that accomplishes this might look like the following:

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

Note the “hasGroup()” keyword, with a Group query inside the parentheses. This syntax allows you to show data based on the data’s association to Group(s) with a totally separate and arbitrary set of criteria. A normal query for the root data still exists outside of the hasGroup() section, so, for instance, the following Indicator query could be used to show only hostnames with the criteria from the previous example:

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

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

The keyword “NOT” can precede any of these nested queries, filtering on data that do not have associations with the specified criteria.

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

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

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

This example queries for all open Tasks in high-severity Cases.

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

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

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

This 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 name contains "\\"

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

ThreatConnect^® is a registered trademark of ThreatConnect, Inc.

20052-03 v.18.A