Introducing Regex (RLIKE) Support for Hostnames in SQL Explorer
Reading time: 3 minutes
A couple of months ago we were delighted to share big news about one of the hottest features of SurfaceBrowser™: SQL Explorer. Introducing this new SQL-based feature allowed many of our customers to perform classic SQL-based queries against our massive infosec database and all its host and IP attributes.
Today we're excited to announce the immediate availability of Regex (RLIKE) support for hostnames in SQL Explorer. Let's see it in action with a few examples.
Find all domains that start with the word "paypal"
The code will be similar to this:
select
domain.hostname
from
hosts
where
domain.hostname rlike "paypal[a-z]*.*"
Take a look at the following screencast to see the results:
As you can see by using Regex (RLIKE) you can find any strings with particular patterns, in this case we used domain.hostname rlike "paypal[a-z].", just as an example, but there are many possibilities. Let’s see some additional variations.
Let's say we want to avoid subdomains in our results. We simply need to add "AND domain.subdomain is null" to our previous query:
The output will look like:
And is numeric regex supported? Well, of course!
For this, we can alter the previous select to include the RLIKE query as shown below:
domain.hostname rlike "paypal<1-100>.*"
The result will include domains such as:
Find paypal-based domain names that are using self-signed certificates and Let's Encrypt SSLs
In this case, we'll repeat the previous syntax, but we can add another attribute such as ssl.is_self_signed = true, which will match only self-signed SSL certificates:
Another variation may be to combine this with ssl.issuer.organization = "Let's Encrypt", as this can unveil all the domain names that are using free SSL certificates, which is pretty common in some phishing-based websites.
Once you have your results ready, click 'Add to downloads' to get all these domains packaged into JSON and CSV formats:
We can take all these examples even further by combining other host and IP attributes that are present in our SurfaceBrowser™ SQL Explorer interface.
If you're interested in playing with the rest of the supported Regex syntaxes for domain.hostname, you may want to check the official ES docs.
We're already working on adding new supported endpoints for this Regex feature. Stay tuned!
Access SQL Explorer today
The SQL Explorer preview is now available to interested customers.
Want to get a head start in using this valuable new feature?
