Using ThreatConnect Query Language (TQL)

Last Updated: Jun 21, 2018 10:58PM EDT
User
None

Overview

The advanced search filter allows users to build structured queries using a SQL-like query language called ThreatConnect Query Language (TQL). With this feature, an analyst can specify criteria that cannot be defined using a simple string search. At any time, a query can be saved for later viewing and use.

Steps

  1. On the top navigation bar (Figure 1), click on BROWSE to display the Browse screen (Figure 2).
  2. Click on the Advanced text at the top right of the screen to initiate an advanced search (Figure 3). Some commonly used TQL expressions are displayed on the left-hand pane.
  3. Enter a query expression in the field to the left of the magnifying glass  icon and press Enter.
  4. Queries may be saved for later viewing and use in custom dashboards (if enabled). See Browse for more information.

Constructing Query Expressions

A 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 advanced-query searches, and Tables 1 and 2 list the available operators and parameter names, respectively.

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. Search strings, however, can be case sensitive.

Basic Search

  • 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: (1) the Indicator has a Threat Rating equal to 3 and the summary (the Indicator itself) does not contain ‘bad.com’; (2) 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 an Indicator with the tag ‘China’, ‘Russia’, or ‘Japan’ and a Confidence Rating greater than or equal to 70.

Search for All of the User’s Open Tasks

 

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

Note: To use this query, the drop-down menu to the left of the ThreatConnect Query Search field must be set to “Groups”.

Search for Internationalized Domain Names

 

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

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

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

 

In some cases, it may be desirable to search for data not by filtering on the elements of the data itself, but 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 search 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 the user 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 search filters.

Note: hasVictimAsset() cannot be performed in a Tag search, 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 does 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 < '2017-04-01' 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 searching for Victim Assets of the Social Network variety for a Victim named Sally that are associated to an Adversary named Harry added before 4/1/2017, that in turn is associated to an IP address Indicator that does not have the Tag ‘China’.

Search 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 November 2017 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.


 

Table 1

 
Operators
=, ==, EQ, EQUALS
!=, NE
>, GT
<, LT
<=, LEQ
>=, GEQ
[NOT] IN
[NOT] LIKE
[NOT] CONTAINS
[NOT] STARTSWITH
[NOT] ENDSWITH
 
 

Table 2

 
Browse Type Parameter Data Type Comments
Indicators id Integer  
Indicators owner Integer  
Indicators ownerName String  
Indicators type Integer  
Indicators typeName String  
Indicators summary String  
Indicators dateAdded DateTime Accepted formats:
yyyy-MM-dd HH:mm
yyyy-MM-dd
MM-dd-yyyy
Indicators lastModified DateTime  
Indicators rating Integer  
Indicators confidence Integer  
Indicators description String  
Indicators source String  
Indicators hostDnsActive Boolean  
Indicators hostWhoisActive Boolean  
Indicators addressASN Integer  
Indicators addressRegisteringOrg String  
Indicators addressCountryCode String  
Indicators addressIpVal BigInteger  
Indicators addressIsIpv6 Boolean  
Indicators addressCIDR CIDR Expression  
Indicators fileSize BigInteger  
Indicators fileName String  
Indicators filePath String  
Indicators fileOccuranceDate DateTime  
Indicators securityLabel String  
Indicators observationCount Integer  
Indicators lastObserved DateTime  
Indicators falsePostiveCount Integer  
Indicators lastFalsePositive DateTime  
Indicators threatAssessScore Integer  
Indicators tag String  
Indicators tagOwner Integer  
Indicators tagOwnerName String  
Indicators attributeNN Dependent  
Indicators associatedGroup Integer Deprecated by nested query; equivalent to hasGroup(id=n)
Indicators [NOT] hasIndicator() Indicator TQL filter  
Groups id Integer  
Groups owner Integer  
Groups ownerName String  
Groups type Integer  
Groups typeName String  
Groups summary String  
Groups status String  
Groups dateAdded Date  
Groups eventDate Date  
Groups upvoteCount Integer  
Groups downvoteCount Integer  
Groups tag String  
Groups tagOwner Integer  
Groups tagOwnerName String  
Groups securityLabel String  
Groups associatedIndicator Integer Deprecated by nested query; equivalent to hasIndicator(id=n)
Groups victimAsset String Deprecated by nested query; equivalent to hasVictimAsset(name="")
Groups attributeNN Dependent  
Groups documentStatus String  
Groups documentFilename String  
Groups documentFilesize Long  
Groups documentDateAdded Date  
Groups documentType String  
Groups emailScore Integer  
Groups emailSubject String  
Groups emailDate Date  
Groups emailFrom String  
Groups emailScoreIncludesBody Boolean  
Groups signatureDateAdded Date  
Groups signatureType String  
Groups signatureFilename String  
Groups taskAssignee User  
Groups taskStatus String  
Groups taskEscalated Boolean  
Groups taskReminded Boolean  
Groups taskOverdue Boolean  
Groups taskDueDate Date  
Groups taskReminderDate Date  
Groups taskEscalationDate Date  
Groups taskDateAdded Date  
Groups taskLastModified Date  
Groups [NOT] hasGroup() Group TQL filter  
Tags id Integer  
Tags owner Integer  
Tags ownerName String  
Tags name String  
Tags summary String  
Tags weight Integer  
Tags description String  
Tags associatedIndicator Integer Deprecated by nested query; equivalent to hasIndicator(id=n)
Tags associatedGroup Integer Deprecated by nested query; equivalent to hasGroup(id=n)
Tags associatedVictim Integer Deprecated by nested query; equivalent to hasVictim(id=n)
Tags [NOT] hasTag() Tag TQL filter  
Tracks owner Integer  
Tracks ownerName String  
Tracks summary String  
Tracks dateAdded Date  
Tracks description String  
Tracks contains String  
Tracks notContains String  
Tracks lastUpdated Date  
Tracks active Boolean  
Tracks resultCount Integer  
Tracks result String  
Tracks resultDate Date  
Tracks associatedIndicator Integer Not deprecated, because Tracks are not part of the nested-query feature
Victims id Integer  
Victims owner Integer  
Victims ownerName String  
Victims summary String Equivalent to name
Victims summary String  
Victims description String  
Victims organization String  
Victims suborg String  
Victims workLocation String  
Victims nationality String  
Victims tag String Deprecated by nested query; equivalent to hasTag(summary="")
Victims tagOwner Integer Deprecated by nested query; equivalent to hasTag(owner=n)
Victims tagOwnerName String Deprecated by nested query; equivalent to hasTag(ownerName="")
Victims securityLabel String  
Victims attributeNN Dependent  
Victims assetType Integer Deprecated by nested query; equivalent to hasVictimAsset(type=n)
Victims assetTypeName String Deprecated by nested query; equivalent to hasVictimAsset(typeName="")
Victims assetName String Deprecated by nested query; equivalent to hasVictimAsset(summary="")
Victims [NOT] hasVictim() Victim TQL filter  
Victim Assets id Integer  
Victim Assets owner Integer  
Victim Assets victimName String  
Victim Assets ownerName String  
Victim Assets type Integer  
Victim Assets typeName String  
Victim Assets asset String  
Victim Assets summary String  
Victim Assets [NOT] hasVictimAsset VictimAsset TQL filter  

20052-07 EN Rev. D

Contact Us

  • ThreatConnect, Inc.
    3865 Wilson Blvd.
    Suite 550
    Arlington, VA 22203

    Toll Free:   1.800.965.2708
    Local: +1.703.229.4240
    Fax +1.703.229.4489

    Email Us



https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete