Constructing Query Expressions
  • 03 Nov 2022
  • 10 Minutes to read
  • Dark
    Light

Constructing Query Expressions

  • Dark
    Light

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.

Note
TQL keywords 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. Query strings, however, can be case sensitive.
Note
When writing a TQL string that includes an OR clause, enclose the entire query in parentheses, even if it is the entire query, 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.
Note
All TQL keywords and corresponding information related to Workflow and threat intelligence objects can be found in the ThreatConnect v3 API by submitting a request in the following format: OPTIONS /v3/{objectName}/tql

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"
Important
When querying for a Registry Key Indicator’s Key Name, replace the backwards slash (\) with a percentage symbol (%) to escape the Key Name, and use the like operator instead of the = operator. Otherwise, an error message will be generated when you try to run the query.
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 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.

Important
The only value that taskAssignee accepts is me. It does not accept usernames of other ThreatConnect users. Use taskAssigneePseudo to identify a Task assignee by pseudonym.

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.

Note
Some Attribute Types that contain very large text fields are not filterable in TQL. The ThreatConnect search feature may be used instead for these situations.
Important
When using TQL to query for a custom Attribute Type, the Attribute Type’s Max Length must be less than 500. For custom Attribute Types with a Max Length greater than 500, use the ThreatConnect search feature to search for Attribute values.
Important
To group Attributes by value when querying for Attributes in a dashboard Query card, a Select One Picklist or Select One Radio Attribute Validation Rule must be applied to the Attribute Type.

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.

Important
hasVictimAsset() cannot be performed in a Tag query, due to the lack of a direct relationship between Victim Assets and Tags.

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.
Note
The relative-date syntax is not case sensitive.

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
Note
The label syntax is not case sensitive.
Note
Only one increment can be specified per relative date.

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.
Important
Workflow-related queries are available only in dashboard Query cards and the ThreatConnect v3 API. They are not available in the Browse screen.
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.17.B


Was this article helpful?