- 31 Aug 2022
- 9 Minutes to read
Constructing Query Expressions
- Updated on 31 Aug 2022
- 9 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 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.
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 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:
- 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.
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
- 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.
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.
ThreatConnect® is a registered trademark of ThreatConnect, Inc.