Example SQL Queries

SSH login attempts

SELECT
  ipv4_src_ip,
  metadata_organization,
  gn_classification,
  gn_seen,
  timestamp
FROM
  sensor_data
WHERE
  tcp_dst_port = 22
  AND LENGTH(tcp_data) >= 46 
  AND SUBSTRING(tcp_data, 1, 2) = '32'

Top HTTP Requests

SELECT
  from_utf8(from_hex(tcp_data)) as raw_data,
  http_request_url,
  http_request_method,
  http_request_body,
  http_response_status
FROM
  sensor_data
WHERE
  length(http_request_url) >= 1
  or length(http_response_status) >= 1
limit
  100

Identify exploitation of TP-Link CVE-2022-30075

SELECT
  *
FROM
  sensor_data
WHERE
  http_request_url like '/cgi-bin/luci/;stok=/locale?form=country&operation=write&country=$%'
order by
  timestamp desc

Identify any traffic with malicious-looking strings in their requests

SELECT
  *
FROM
  sensor_data
WHERE
  (
    from_utf8(FROM_HEX(tcp_data)) LIKE '%curl%'
    OR from_utf8(FROM_HEX(tcp_data)) LIKE '%wget%'
    OR from_utf8(FROM_HEX(tcp_data)) LIKE '%/bin/bash%'
    OR from_utf8(FROM_HEX(tcp_data)) LIKE '%/bin/sh%'
    OR from_utf8(FROM_HEX(tcp_data)) LIKE '%ping%'
    OR from_utf8(FROM_HEX(udp_data)) LIKE '%curl%'
    OR from_utf8(FROM_HEX(udp_data)) LIKE '%wget%'
    OR from_utf8(FROM_HEX(udp_data)) LIKE '%/bin/bash%'
    OR from_utf8(FROM_HEX(udp_data)) LIKE '%/bin/sh%'
    OR from_utf8(FROM_HEX(udp_data)) LIKE '%ping%'
  )

Return IPs tagged as malicious by GreyNoise General Fleet by country and organization

select
  count(*) as hits,
  count(distinct ipv4_src_ip) as ips,
  metadata_organization,
  metadata_country
from
  sensor_data
where
  gn_classification = 'malicious'
group by
  3,
  4
order by
  2 desc

Identify HTTP BASIC credential stuffers and which credentials they are attempting

WITH
  extracted_credentials AS (
    SELECT
      metadata_organization,
      metadata_country,
      metadata_city,
      gn_seen,
      ipv4_src_ip,
      CAST(
        FROM_UTF8 (FROM_BASE64 (SPLIT_PART (header.value, ' ', 2))) AS VARCHAR
      ) AS credentials
    FROM
      sensor_data
      CROSS JOIN UNNEST (http_request_headers) AS t (header)
    WHERE
      header.key = 'Authorization'
      AND header.value LIKE 'Basic %'
  )
SELECT
  COUNT(*) AS usage_count,
  metadata_organization,
  metadata_country,
  metadata_city,
  gn_seen,
  ipv4_src_ip,
  credentials
FROM
  extracted_credentials
GROUP BY
  metadata_organization,
  metadata_country,
  metadata_city,
  gn_seen,
  ipv4_src_ip,
  credentials
ORDER BY
  usage_count DESC;

Identify scanners with benign-seeming rdns or user agents that have not been seen by GreyNoise global fleet

SELECT
  count(*) as hits,
  ipv4_src_ip, 
  metadata_rdns, 
  metadata_organization,
  metadata_country,
  metadata_city
FROM
  sensor_data
WHERE
  (
    metadata_rdns LIKE '%scan%'
    OR metadata_rdns LIKE '%monitor%'
    OR metadata_rdns LIKE '%robot%'
    OR metadata_rdns LIKE '%security%'
    OR metadata_rdns LIKE '%crawl%'
    OR metadata_rdns LIKE '%research%'
  ) and gn_seen = false
group by 2, 3, 4, 5, 6 order by 1 desc

Identify scanners using the same hard-coded user agents as Mirai

SELECT *
FROM sensor_data
	CROSS JOIN UNNEST(http_request_headers) AS t (header)
WHERE header.key = 'User-Agent'
	AND header.value IN (  
		'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36',
		'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
		'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36',
		'Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/52.0.2743.116 Safari/537.36',
		'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_6) AppleWebKit/601.7.7 (KHTML, like Gecko) Version/9.1.2 Safari/601.7.7'
	);

Top HTTP user agents

SELECT count(*) as hits,
	count(distinct ipv4_src_ip) as ips,
	header.value AS user_agent
FROM sensor_data
	CROSS JOIN UNNEST(http_request_headers) AS t (header)
WHERE header.key = 'User-Agent'
group by header.value
order by hits desc;

Identify scanning or exploitation of Cisco devices:

select *
from sensor_data
where (
		http_request_url like '/+CSCOE+/%'
		OR http_request_url like '/webui/%'
	)

Top 10 Most Active Source IPs

SELECT
  ipv4_src_ip,
  COUNT(*) AS activity_count
FROM
  sensor_data
GROUP BY
  ipv4_src_ip
ORDER BY
  activity_count DESC
LIMIT
  10;

Most common ports used in TCP scans

SELECT
  tcp_dst_port,
  COUNT(*) AS syn_count
FROM
  sensor_data
WHERE
  tcp_syn = true
  AND tcp_ack_flag = false
GROUP BY
  tcp_dst_port
ORDER BY
  syn_count DESC
LIMIT
  10;

Identify Traffic from Known Malicious Actors

SELECT
  ipv4_src_ip,
  gn_actor,
  COUNT(*) AS hit_count
FROM
  sensor_data
WHERE
  gn_classification = 'malicious'
GROUP BY
  ipv4_src_ip,
  gn_actor
ORDER BY
  hit_count DESC;

Frequent HTTP Methods by Source IP

SELECT
  ipv4_src_ip,
  http_request_method,
  COUNT(*) AS method_count
FROM
  sensor_data
WHERE
  length(http_request_method) >= 1
GROUP BY
  ipv4_src_ip,
  http_request_method
ORDER BY
  method_count DESC;

Apache Struts CVE-2018-11776 Worm

SELECT *
FROM sensor_data
WHERE ipv4_protocol = 'TCP'
  AND http_request_url LIKE '%memberAccess%allowStaticMethodAccess%true%';

Atlassian Jira Seraph Authentication Bypass Attempt

SELECT *
FROM sensor_data
WHERE http_request_url LIKE '%/plugins/servlet/seraph%authentication%';