Getting Started with the ThreatConnect Query Language (TQL)
From Neanderthal to Ninja
Users in ThreatConnect have a lot of data to go through, and a big part of our job is finding ways to empower them to find the right data faster. Our Browse Screen harbors an array of different filters you can apply and chain together, slicing and dicing the dataset to narrow down your query. You can filter by relevant dates, indicator ratings, tags, and attributes if you'd like. The built-in filters are great for most of my queries, and flatten out large datasets instantly with a few button clicks. But occasionally I have to add some sophisticated logic to filter out the signal from the noise, chain conditions together, or drill even deeper into the dataset. Using the sledgehammer when you need the scalpel can make you feel like a neanderthal. That's when I flip into Advanced mode on the Browse screen and get to work.
The Advanced link at the top-right corner of the Browse pane will open up TQL queries for you.
The Advanced Browse view replaces all of the sliders and toggles with a simple text box. This text box serves as an input for a ThreatConnect Query Language (TQL) query. If you're curious, it's pronounced "Tea-quel". Like other structured query languages TQL provides a syntax for you to ask questions textually, and it can do some pretty powerful stuff.
Getting Started with TQL
While there is a full reference of TQL syntax, you don't need to start writing queries from scratch to get started! In fact, you can build an example query with the sliders and toggles in Basic mode and then flip to Advanced mode to see it translated into a TQL query for you!
Filters as defined in Basic mode on the Browse screen.
The same query translated automatically into TQL, and its identical results set.
In the above example, I've set my filters to search for any Address indicator whose Rating is greater than 1 Skull and has a Confidence greater than 19. I also want to filter to only look for such Address indicators who have been recorded as being part of the Reconnaissance phase of intrusion, as declared in the respective System Attribute.
If we flip over into the Advanced view, we'll see that these search settings have been translated automatically into a TQL query for us. This is a great way to get your feet wet with TQL and learn its nuances or build the basis of a query before fine tuning it. Let's fine tune this query by expanding it to include not just Reconnaissance indicators that match our parameters, but also include indicators that are flagged as Actions on Objectives.
Our amended query, with an additional result.
Now that we've expanded the search criteria, we've returned extra results that we otherwise would have missed in the the Browse Filters. This is a pretty basic example of writing or extending a TQL query, but before we get into some meatier examples you probably want to know why you should bother.
Benefits to Using TQL
There's a reason why query languages exist in the first place: they're powerful. Sure, you can add more sliders and toggles to the filter screen, but as you add more power you also add complexity. We've found that sometimes our users need to access datasets in ThreatConnect by asking a very specifically tailored question, similar to querying a database. Show me all indicators added in the last 30 days that are associated to a High Severity Threat. These kinds of questions are typically asked via a query language, otherwise the filter screen turns into a NASA-style control panel with an overwhelming number of options.
In the example above, we extended our query to include an OR clause to specify that we wanted Address indicators that were marked as performing Reconnaissance or Actions on Objectives. We could have just as easily specified Reconnaissance but not Exploitation. The richness of these query possibilities cannot be condensed into an infinite sprawl of buttons on the filter screen, so TQL will give you even more power in crafting exactly the query you want.
Save and Return
You may have noticed that when traversing the Browse Screen, your URL has gotten really long. That's because we're actually taking all of your Browse settings (even in Basic mode) and passing them as TQL to the ThreatConnect application. This means that you can bookmark a query to save it and return to it later! Do you start your day by looking for newly-added indicators that meet a certain set of criteria? Set your query, bookmark it in your browser, and return tomorrow with your morning cup of coffee. You could even share a search with a colleague simply by copying and pasting the URL, assuming they have the same access as you.
If you can't tell, we're really excited about TQL. So much, that we're finding clever ways to embed it into other parts of our application. We have some very exciting features coming that can leverage TQL to do some cool things, like saving queries natively in the application or using it to populate charts and tables on a customizable dashboard! Getting your hands dirty now will allow you to hit the ground running as these features get added.
Becoming a TQL Ninja
So you've read the Knowledge Base, you've tinkered in the Advanced panel, but you want to know how this is actually being used by real people doing real analysis. We've had some fantastic questions that TQL has helped to answer at scale for customers and our internal researchers alike.
A smarter regular query
One customer had a regular task where they wanted to look at highly rated indicators that were tagged as "NEEDS REVIEW". This could be done graphically in the UI by setting filters every time, but they wanted to clean up the results by removing anything that had been marked as a False Positive. The following TQL query not only solved their problem, but allowed them to bookmark the page and revisit as necessary!
Query: typeName in ("Host") and rating > 4 and tag in (
"NEEDS REVIEW") and falsePositiveCount <= 0
Hair on fire lookups
What happens when something like the WannaCry campaign kicks off and your boss wants to know if you're affected? Quickly identifying any observations of known WannaCry indicators in your network is a simple TQL query away:
TQL Query: typeName in ("Host", "Address", "EmailAddress", "File", "URL") and (summary like "%wanna%" or tag like "%wanna%" ) and lastobserved > "2017-05-12"
Make sure to include all of the Communities and Sources in the My ThreatConnect dropdown on the left to leverage all of the intel at your fingertips!
Prioritizing through Nesting
One of our customers has built a comprehensive malware library leveraging playbooks and an automated malware analysis (AMA) engine. Their data is highly connected and highly decorated with attributes and tags to express how a particular malware sample behaves and what it reaches out to. They frequently need to ask questions like "Show me all observed C2 nodes that are contacted by ransomware malware." We worked with this customer to make sure their use case was easily achievable with a TQL query:
TQL Query: typeName in ("Address", "Host", "URL" ) and observationcount > 0 and attribute17 = "C2" and hasIndicator(typename in ("File") and tag in ("Ransomware"))
Note the nesting in the hasIndicator clause, which translates in English to, "is associated with an Indicator that matches this nested TQL query." In this example, the nested TQL query is asking for all Files that are tagged Ransomware. You can build up iterative queries by finding Indicators or Groups of interest and using those as a sort of "super filter" for nested TQL queries.
This nesting can go even deeper! Perhaps you didn't want to just look for File indicators that were tagged Ransomware but rather wanted to filter C2 nodes by file indicators that were associated to a particular Threat Group. You can even apply filters to those third-order Threat Groups, asking for C2 nodes contacted by File indicators that are associated to a Threat Group that's noted as attacking your industry.
TQL Query: typeName in ("Address", "Host", "URL" ) and attribute17 = "C2" and hasIndicator(typeName in ("File") and hasGroup(typeName in ("Threat") and attribute9 = "My Industry"))
These nested queries will help you tailor your search so that you can only include things that matter to you as an individual or an organization.
Give TQL a try the next time you start exploring in the Browse screen, and let us know if you come up with something awesome! Feel free to tweet us @ThreatConnect to let us know what's working well for you, and email us at firstname.lastname@example.org to tell us if something isn't.