- 24 Oct 2023
- 14 Minutes to read
Constructing Query Expressions
- Updated on 24 Oct 2023
- 14 Minutes to read
A ThreatConnect Query Language (TQL) query expression is constructed by using a parameter name, an operator, and a value or list of values. Multiple query expressions can be combined using parentheses and AND/OR logic. The examples in this article provide some useful TQL queries. See TQL Operators and Parameters for a list of available operators, general TQL parameters, and Workflow-related TQL parameters.
Before You Start
- 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.
- When writing a TQL string that includes an OR clause, enclose the entire query in parentheses, even if the TQL string is the only element in the 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.
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 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 (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 Open Task Groups 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 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 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
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:
- Navigate to the Browse screen.
- Click the FILTERS menu.
- Select the Attribute from the Attributes dropdown menu at the top right.
- Enter any value in the Value column.
- Click the APPLY button at the bottom right.
- Click Advanced at the upper-right corner of the Browse screen.
- 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.
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 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
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. 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() expression, 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. 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, enabling you to filter 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 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
- 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.
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 of ThreatConnect, Inc.
MITRE ATT&CK® and ATT&CK® are registered trademarks of The MITRE Corporation.