Files
2026-04-01 10:58:34 -06:00

1318 lines
44 KiB
Go

package integration_test
import (
"context"
"database/sql"
"errors"
"fmt"
"strconv"
"testing"
"github.com/activecm/rita/v5/database"
"github.com/activecm/rita/v5/modifier"
"github.com/activecm/rita/v5/util"
"github.com/ClickHouse/clickhouse-go/v2"
"github.com/stretchr/testify/require"
)
// TestRareSignatures tests the rare_signatures table
func (it *ValidDatasetTestSuite) TestRareSignatures() {
type signatureRecord struct {
Signature string `ch:"signature"`
TimesUsedDst uint64 `ch:"times_used_dst"`
TimesUsedFqdn uint64 `ch:"times_used_fqdn"`
}
tests := []struct {
name string
logDir string
src string
ja3Records []signatureRecord
useragentRecords []signatureRecord
}{
{
name: "Both Useragent and JA3",
logDir: "../test_data/valid_tsv",
src: "10.55.100.104",
useragentRecords: []signatureRecord{
{"Microsoft-CryptoAPI/10.0", 19, 24},
{"Microsoft-Delivery-Optimization/10.0", 2, 4},
{"Microsoft-WNS/10.0", 7, 1},
{"Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko", 324, 145},
{"Mozilla/5.0 (Windows NT 10.0; Win64; x64; Trident/7.0; rv:11.0) like Gecko", 3, 3},
{"Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98", 4, 1},
{"Windows-Update-Agent/10.0.10011.16384 Client-Protocol/1.70", 1, 1},
},
ja3Records: []signatureRecord{
{"0eecb7b1551fba4ec03851810d31743f", 1, 1},
{"10ee8d30a5d01c042afd7b2b205facc4", 987, 501},
{"3b5074b1b5d032e5620f69f9f700ff0e", 7, 6},
{"54328bd36c14bd82ddaa0c04b25ed9ad", 4, 1},
{"a0e9f5d64349fb13191bc781f81f42e1", 2, 2},
{"bd0bf25947d4a37404f0424edf4db9ad", 31, 23},
{"ce5f3254611a8c095a3d821d44539877", 6, 2},
{"f8128c51dc8d1f49da1d6126735300d5", 13, 5},
},
},
{
name: "Both Useragent and JA3",
logDir: "../test_data/valid_tsv",
src: "10.55.100.100",
useragentRecords: []signatureRecord{
{"Microsoft-CryptoAPI/10.0", 19, 26},
{"Microsoft-Delivery-Optimization/10.0", 6, 6},
{"Microsoft-WNS/10.0", 6, 1},
{"Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko", 366, 185},
{"Mozilla/5.0 (Windows NT 10.0; Win64; x64; Trident/7.0; rv:11.0) like Gecko", 4, 4},
{"Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98", 4, 1},
{"Windows-Update-Agent/10.0.10011.16384 Client-Protocol/1.70", 2, 1},
},
ja3Records: []signatureRecord{
{"0eecb7b1551fba4ec03851810d31743f", 1, 1},
{"10ee8d30a5d01c042afd7b2b205facc4", 1031, 547},
{"54328bd36c14bd82ddaa0c04b25ed9ad", 4, 1},
{"a0e9f5d64349fb13191bc781f81f42e1", 2, 2},
{"b89be837a4a296476fcd758189908728", 1, 1},
{"bd0bf25947d4a37404f0424edf4db9ad", 34, 25},
{"ce5f3254611a8c095a3d821d44539877", 6, 2},
{"f8128c51dc8d1f49da1d6126735300d5", 12, 6},
},
},
{
name: "Both Useragent and JA3",
logDir: "../test_data/valid_tsv",
src: "10.55.100.111",
useragentRecords: []signatureRecord{
{"MICROSOFT_DEVICE_METADATA_RETRIEVAL_CLIENT", 2, 2},
{"Microsoft-CryptoAPI/10.0", 21, 28},
{"Microsoft-Delivery-Optimization/10.0", 1, 2},
{"Microsoft-WNS/10.0", 10, 2},
{"Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko", 307, 178},
{"Mozilla/5.0 (Windows NT 10.0; Win64; x64; Trident/7.0; rv:11.0) like Gecko", 3, 2},
{"Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98", 4, 1},
{"Windows-Update-Agent/10.0.10011.16384 Client-Protocol/1.70", 1, 1},
},
ja3Records: []signatureRecord{
{"0eecb7b1551fba4ec03851810d31743f", 1, 1},
{"10ee8d30a5d01c042afd7b2b205facc4", 1486, 679},
{"3b5074b1b5d032e5620f69f9f700ff0e", 2, 2},
{"54328bd36c14bd82ddaa0c04b25ed9ad", 4, 1},
{"a0e9f5d64349fb13191bc781f81f42e1", 2, 2},
{"b89be837a4a296476fcd758189908728", 3, 3},
{"bd0bf25947d4a37404f0424edf4db9ad", 25, 17},
{"ce5f3254611a8c095a3d821d44539877", 6, 1},
{"f8128c51dc8d1f49da1d6126735300d5", 14, 6},
},
},
{
name: "JA3 Only",
logDir: "../test_data/valid_tsv",
src: "192.168.88.2",
useragentRecords: nil,
ja3Records: []signatureRecord{
{"08bf94d7f3200a537b5e3b76b06e02a2", 1, 1},
},
},
}
for _, test := range tests {
it.Run(test.name+" "+test.src, func() {
t := it.T()
ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
"src": test.src,
}))
t.Run("Useragent", func(t *testing.T) {
var res []signatureRecord
// query the rare_signatures table for the given src and is_ja3 = false
err := it.db.Conn.Select(ctx, &res, `
SELECT signature, uniqExactMerge(times_used_dst) as times_used_dst, uniqExactMerge(times_used_fqdn) as times_used_fqdn FROM rare_signatures
WHERE src=={src:String} AND is_ja3==false
GROUP BY src, src_nuid, signature
`)
require.NoError(t, err, "querying rare_signatures table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.useragentRecords), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.useragentRecords, res, "result list should match expected value")
})
t.Run("JA3", func(t *testing.T) {
var res []signatureRecord
// query the rare_signatures table for the given src and is_ja3 = true
err := it.db.Conn.Select(ctx, &res, `
SELECT signature, uniqExactMerge(times_used_dst) as times_used_dst, uniqExactMerge(times_used_fqdn) as times_used_fqdn FROM rare_signatures
WHERE src=={src:String} AND is_ja3==true
GROUP BY src, src_nuid, signature
`)
require.NoError(t, err, "querying rare_signatures table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.ja3Records), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.ja3Records, res, "result list should match expected value")
})
})
}
}
/*
check if ip is associated with an fqdn and won't be in threat mixtape:
(if a row is returned, the ip is associated with an fqdn)
select src, dst, count() as count
from chickenstrip4.conn
//inner JOIN chickenstrip4.http USING zeek_uid -- for http
inner JOIN chickenstrip4.ssl USING zeek_uid -- for ssl
group by src, dst
having src='192.168.88.2' and dst='165.227.88.15' -- btw these can be WHERE bc it's not an aggregation function, having is for HAVING sum(bytes) > 0
//having src='10.55.100.111' and dst='24.220.113.59'
*/
func (it *ValidDatasetTestSuite) TestPortInfoTable() {
type protoInfo struct {
PortProtoService string `ch:"port_proto_service"`
ConnCount uint64 `ch:"conn_count"`
BytesSent uint64 `ch:"bytes_sent"`
BytesReceived uint64 `ch:"bytes_received"`
}
// make sure table values are getting populated correctly from the multiple materialized views
it.Run("MV Populated Values", func() {
tests := []struct {
name string
src string
dst string
fqdn string
portInfoList []protoInfo
shouldBeInMixtape bool
}{
{
name: "IP - conn http ssl 1",
src: "10.55.100.111",
dst: "24.220.113.59",
portInfoList: []protoInfo{
// 15 + 2 + 32 + 33 = 82 connections total
// 9830 + 13500 + 28638275 + 854620 = 29516225 bytes received total
// 4260 + 0 + 181628 + 52057 = 237945 bytes sent total
// 29516225 + 237945 = 29754170 total bytes
{"80:tcp:", 15, 4260, 9830},
{"443:tcp:", 2, 0, 13500},
{"443:tcp:ssl", 32, 181628, 28638275},
{"80:tcp:http", 33, 52057, 854620},
},
shouldBeInMixtape: false, // associated with fqdn
},
{
name: "IP - conn http ssl 1",
src: "10.55.100.111",
dst: "162.208.22.39",
portInfoList: []protoInfo{
{"80:tcp:", 4, 160, 0},
{"443:tcp:", 22, 0, 2654},
{"443:tcp:ssl", 17, 27970, 62243},
{"80:tcp:http", 2, 2147, 1745},
},
shouldBeInMixtape: false, // associated with fqdn
},
{
name: "IP - conn http ssl 2",
src: "10.55.100.105",
dst: "192.132.33.27",
portInfoList: []protoInfo{
{"80:tcp:", 22, 880, 0},
{"443:tcp:", 2, 80, 0},
{"443:tcp:ssl", 2, 2625, 12645},
{"80:tcp:http", 15, 21367, 11705},
},
shouldBeInMixtape: false, // associated with fqdn
},
{
name: "IP - conn 1",
src: "10.55.100.111",
dst: "165.227.216.194",
portInfoList: []protoInfo{
{"443:tcp:", 20054, 1042860, 802160},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn 2",
src: "10.55.182.100",
dst: "173.243.138.98",
portInfoList: []protoInfo{
{"80:tcp:", 4, 312, 0},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn 3",
src: "10.55.182.100",
dst: "96.45.33.73",
portInfoList: []protoInfo{
{"8888:udp:", 1424, 140151, 93828},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn dns 1",
src: "192.168.88.2",
dst: "165.227.88.15",
portInfoList: []protoInfo{
{"53:tcp:", 2, 120, 80},
{"53:udp:dns", 108856, 9780152, 11945319},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn dns 2",
src: "10.55.200.10",
dst: "217.70.179.1",
portInfoList: []protoInfo{
{"53:udp:dns", 4, 300, 552},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn dns 3",
src: "10.55.200.10",
dst: "216.239.34.10",
portInfoList: []protoInfo{
{"53:udp:dns", 3856, 289630, 388326},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn dns 4",
src: "10.55.200.11",
dst: "205.251.198.178",
portInfoList: []protoInfo{
{"53:udp:dns", 213, 15458, 54195},
},
shouldBeInMixtape: true,
},
{
name: "IP - conn ssl 1",
src: "10.55.182.100",
dst: "172.217.8.206",
portInfoList: []protoInfo{
// 8 connections total
// 7711 + 172 + 5269 = 13152 bytes received total
// 8163 + 92 + 22271 = 30526 bytes sent total
// 13152 + 30526 = 43678 total bytes
{"443:udp:", 3, 7711, 8163},
{"443:tcp:", 1, 172, 92},
{"443:tcp:ssl", 4, 5269, 22271},
},
shouldBeInMixtape: false,
},
{
name: "FQDN - http ssl 1",
src: "10.55.100.103",
fqdn: "code.jquery.com",
portInfoList: []protoInfo{
{"443:tcp:ssl", 3, 5707, 102655},
{"80:tcp:http", 1, 720, 39328},
},
shouldBeInMixtape: false, // not enough unique timestamps
},
{
src: "10.55.100.103",
fqdn: "geo-um.btrll.com",
portInfoList: []protoInfo{
{"443:tcp:ssl", 23, 50397, 95507},
{"80:tcp:http", 2, 2689, 2039},
},
shouldBeInMixtape: true,
},
{
name: "FQDN - ssl 1",
src: "10.55.100.110",
fqdn: "g.live.com",
portInfoList: []protoInfo{
{"443:tcp:ssl", 4, 4942, 20158},
},
shouldBeInMixtape: true,
},
}
for _, test := range tests {
it.Run(test.name, func() {
t := it.T()
// update pair and pairField based on whether we are querying by dst or fqdn
pair := test.dst
pairField := "dst"
if test.fqdn != "" {
pair = test.fqdn
pairField = "fqdn"
}
// set the context parameters
ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
"src": test.src,
"pair": pair,
"pair_field": pairField,
}))
t.Run("Check Values", func(t *testing.T) {
// query the proto table for the given src and dst/fqdn
var res []protoInfo
err := it.db.Conn.Select(ctx, &res, `
SELECT concat(dst_port, ':', proto, ':', service) AS port_proto_service,
countMerge(count) AS conn_count,
sumMerge(bytes_sent) AS bytes_sent,
sumMerge(bytes_received) AS bytes_received
FROM port_info
where src={src:String} and {pair_field:Identifier}={pair:String}
GROUP BY src, dst, fqdn, dst_port, proto, service
`)
require.NoError(t, err, "querying proto table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.portInfoList), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.portInfoList, res, "result list should match expected value")
})
t.Run("Threat Mixtape Propagation", func(t *testing.T) {
// vet that entries that make it to the threat_mixtape table have all the port-proto-service info from the proto_info table
type protoInfo2 struct {
PortProtoServices []string `ch:"port_proto_service"`
ConnCount uint64 `ch:"count"`
OpenCount uint64 `ch:"open_count"`
TotalBytes uint64 `ch:"total_bytes"`
}
// query the threat mixtape for the given src and dst/fqdn
var res2 protoInfo2
err := it.db.Conn.QueryRow(ctx, `
SELECT --src, {pair_field:Identifier},
port_proto_service,
count,
open_count,
total_bytes,
FROM threat_mixtape
where src={src:String} and {pair_field:Identifier}={pair:String}
-- and count > 0
and length(port_proto_service) > 0
`).ScanStruct(&res2)
// check that threat mixtape entry is present or absent based on test
if test.shouldBeInMixtape {
require.NoError(t, err, "querying threat mixtape should not produce an error")
// verify result is not empty
require.NotEmpty(t, res2, "result should not be empty")
// get list of port-proto-services
var portProtoServices []string
connCount := uint64(0)
openCount := uint64(0)
totalBytes := uint64(0)
for _, p := range test.portInfoList {
portProtoServices = append(portProtoServices, p.PortProtoService)
connCount += p.ConnCount
openCount += p.ConnCount // the test log's open conn log is exactly the same as conn
totalBytes += uint64(p.BytesSent + p.BytesReceived)
}
// vet list of port-proto-services
require.ElementsMatch(t, portProtoServices, res2.PortProtoServices, "port-proto-services should match")
// vet conn count
require.EqualValues(t, connCount, res2.ConnCount, "conn count should match")
// vet open count (open_conn.log is identical to conn.log in the test data. We keep its count, but do not
// keep the bytes info like we do for conn.log)
require.EqualValues(t, int64(connCount), int64(res2.OpenCount), "open count should match")
// vet total bytes (multiplied by 2 because threat mixtape includes open conns and since that log is identical to conn.log
// in the test data, the total bytes is doubled)
require.EqualValues(t, int64(totalBytes)*2, int64(res2.TotalBytes), "total bytes should match")
} else {
if errors.Is(err, sql.ErrNoRows) {
// This is expected, as we should not find any rows
err = nil
}
require.NoError(t, err, "querying threat_mixtape table should not produce an error")
require.Empty(t, res2, "no result is expected")
}
})
})
}
})
}
func (it *ValidDatasetTestSuite) TestTLSProtoTable() {
type protoInfo struct {
JA3 string `ch:"ja3"`
Version string `ch:"version"`
ValidationStatus string `ch:"validation_status"`
Count uint64 `ch:"count"`
}
tests := []struct {
name string
src string
fqdn string
protoInfoList []protoInfo
}{
{
name: "Single Entry 1",
src: "10.55.100.107",
fqdn: "comet.yahoo.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 19},
},
},
{
name: "Single Entry 2",
src: "10.55.100.107",
fqdn: "www.googletagmanager.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 84},
},
},
{
name: "Single Entry 3",
src: "10.55.100.110",
fqdn: "www.facebook.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 152},
},
},
{
name: "Multiple Entries 1",
src: "10.55.100.111",
fqdn: "ml314.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 41},
{"b89be837a4a296476fcd758189908728", "TLSv10", "", 1},
{"b89be837a4a296476fcd758189908728", "TLSv10", "ok", 1},
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "", 87},
{"10ee8d30a5d01c042afd7b2b205facc4", "", "", 1},
},
},
{
name: "Multiple Entries 2",
src: "10.55.100.100",
fqdn: "oneclient.sfx.ms",
protoInfoList: []protoInfo{
{"a0e9f5d64349fb13191bc781f81f42e1", "TLSv12", "", 1},
{"bd0bf25947d4a37404f0424edf4db9ad", "TLSv12", "ok", 2},
{"a0e9f5d64349fb13191bc781f81f42e1", "TLSv12", "ok", 1},
},
},
{
name: "Multiple Entries 3",
src: "10.55.100.109",
fqdn: "pixel.adsafeprotected.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 26},
{"10ee8d30a5d01c042afd7b2b205facc4", "", "", 2},
},
},
{
name: "Status != ok",
src: "10.55.100.106",
fqdn: "settings-win.data.microsoft.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "unable to get local issuer certificate", 3},
{"bd0bf25947d4a37404f0424edf4db9ad", "TLSv12", "unable to get local issuer certificate", 69},
},
},
{
name: "High Count",
src: "10.55.100.108",
fqdn: "www.alexa.com",
protoInfoList: []protoInfo{
{"10ee8d30a5d01c042afd7b2b205facc4", "TLSv12", "ok", 21},
{"54328bd36c14bd82ddaa0c04b25ed9ad", "TLSv10", "ok", 290},
},
},
}
for _, test := range tests {
it.Run(test.name, func() {
t := it.T()
// create a hash for the given src and fqdn
hash, err := util.NewFixedStringHash(test.src, util.UnknownPrivateNetworkUUID.String(), test.fqdn)
require.NoError(t, err, "creating hash should not produce an error")
// set the context parameters
ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
"src": test.src,
"hash": hash.Hex(),
}))
// query the proto table for the given src and dst/fqdn
var res []protoInfo
err = it.db.Conn.Select(ctx, &res, `
SELECT ja3, version, validation_status, countMerge(count) as count FROM tls_proto
WHERE hash=unhex({hash:String})
GROUP BY hash, ja3, version, validation_status
`)
require.NoError(t, err, "querying proto table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.protoInfoList), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.protoInfoList, res, "result list should match expected value")
})
}
}
func (it *ValidDatasetTestSuite) TestHTTPProtoTable() {
type protoInfo struct {
Useragent string `ch:"useragent"`
Method string `ch:"method"`
Referrer string `ch:"referrer"`
URI string `ch:"uri"`
DstMimeTypes []string `ch:"dst_mime_types"`
Count uint64 `ch:"count"`
}
tests := []struct {
name string
src string
fqdn string
protoInfoList []protoInfo
}{
{
name: "Single Entry",
src: "10.55.100.104",
fqdn: "cdn.taboola.com",
protoInfoList: []protoInfo{
{
Useragent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
Method: "GET",
URI: "/libtrc/businessinsider/loader.js",
Referrer: "http://www.businessinsider.com/",
DstMimeTypes: []string{"text/plain"},
Count: 16,
},
},
},
{
name: "Multiple Entries",
src: "10.55.100.107",
fqdn: "www.google.com",
protoInfoList: []protoInfo{
{
Useragent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
Method: "GET",
URI: "/ads/user-lists/863238793/?guid=ON&script=0&cdct=2&is_vtc=1&random=2136862691",
Referrer: "http://www.fedex.com/",
DstMimeTypes: []string{"image/gif"},
Count: 1,
},
{
Useragent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
Method: "GET",
URI: "/ads/user-lists/863238793/?guid=ON&script=0&cdct=2&is_vtc=1&random=3719609297",
Referrer: "http://www.fedex.com/",
DstMimeTypes: []string{"image/gif"},
Count: 1,
},
{
Useragent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
Method: "GET",
URI: "/ads/user-lists/863238793/?guid=ON&script=0&cdct=2&is_vtc=1&random=4251233521",
Referrer: "http://www.fedex.com/",
DstMimeTypes: []string{"image/gif"},
Count: 1,
},
},
},
{
name: "Multiple Unique MIME Types",
src: "10.55.100.109",
fqdn: "imasdk.googleapis.com",
protoInfoList: []protoInfo{
{
Useragent: "Mozilla/5.0 (Windows NT 10.0; WOW64; Trident/7.0; rv:11.0) like Gecko",
Method: "GET",
URI: "/js/sdkloader/ima3.js",
Referrer: "http://www.businessinsider.com/",
DstMimeTypes: []string{"text/plain", "application/javascript"},
Count: 14,
},
},
},
{
name: "Empty Referrer",
src: "10.55.100.105",
fqdn: "www.alexa.com",
protoInfoList: []protoInfo{
{
Useragent: "Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98",
Method: "GET",
URI: "/topsites/category;2/Top/Business/",
DstMimeTypes: []string{"text/html"},
Count: 71,
},
{
Useragent: "Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98",
Method: "GET",
URI: "/topsites/category;1/Top/Business/",
DstMimeTypes: []string{"text/html"},
Count: 62,
},
{
Useragent: "Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98",
Method: "GET",
URI: "/topsites/category;4/Top/Business/",
DstMimeTypes: []string{"text/html"},
Count: 60,
},
{
Useragent: "Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98",
Method: "GET",
URI: "/topsites/category;0/Top/Business/",
DstMimeTypes: []string{"text/html"},
Count: 45,
},
{
Useragent: "Mozilla/5.0 (Windows NT; Windows NT 10.0; en-US) WindowsPowerShell/5.1.16299.98",
Method: "GET",
URI: "/topsites/category;3/Top/Business/",
DstMimeTypes: []string{"text/html"},
Count: 50,
},
},
},
{
name: "MIME Types Empty",
src: "10.55.100.107",
fqdn: "ctldl.windowsupdate.com",
protoInfoList: []protoInfo{
{
Useragent: "Microsoft-CryptoAPI/10.0",
Method: "GET",
URI: "/msdownload/update/v3/static/trustedr/en/pinrulesstl.cab?40f9d126e5b63a43",
Referrer: "",
DstMimeTypes: []string{},
Count: 1,
},
{
Useragent: "Microsoft-CryptoAPI/10.0",
Method: "GET",
URI: "/msdownload/update/v3/static/trustedr/en/disallowedcertstl.cab?525cb8ffc6c284d5",
Referrer: "",
DstMimeTypes: []string{},
Count: 1,
},
{
Useragent: "Microsoft-CryptoAPI/10.0",
Method: "GET",
URI: "/msdownload/update/v3/static/trustedr/en/pinrulesstl.cab?52033f8ab02770a1",
Referrer: "",
DstMimeTypes: []string{},
Count: 1,
},
},
},
}
for _, test := range tests {
it.Run(test.name, func() {
t := it.T()
// create a hash for the given src and fqdn
hash, err := util.NewFixedStringHash(test.src, util.UnknownPrivateNetworkUUID.String(), test.fqdn)
require.NoError(t, err, "creating hash should not produce an error")
// set the context parameters
ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
"src": test.src,
"hash": hash.Hex(),
}))
// import the data
// query the proto table for the given src and fqdn
var res []protoInfo
err = it.db.Conn.Select(ctx, &res, `
SELECT useragent,
method,
referrer,
uri,
groupUniqArrayMerge(dst_mime_types) AS dst_mime_types,
countMerge(count) as count FROM http_proto
where hash=unhex({hash:String})
GROUP BY hash, useragent, method, referrer, uri
`)
require.NoError(t, err, "querying proto table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.protoInfoList), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.protoInfoList, res, "result list should match expected value")
})
}
}
func (it *ValidDatasetTestSuite) TestMimeTypesURIsTable() {
type mimeTypesURIInfo struct {
URI string `ch:"uri"`
Path string `ch:"path"`
Extension string `ch:"extension"`
MimeType string `ch:"mime_type"`
MismatchCount uint64 `ch:"mismatch_count"`
}
type modifierInfo struct {
ModifierName string `ch:"modifier_name"`
ModifierScore float64 `ch:"modifier_score"`
ModifierValue string `ch:"modifier_value"`
}
tests := []struct {
name string
src string
fqdn string
mimeTypesURIInfoList []mimeTypesURIInfo
}{
{
name: "Single Entry",
src: "10.55.100.103",
fqdn: "ml314.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/tag.aspx?3102018",
Path: "/tag.aspx",
Extension: "aspx",
MimeType: "text/plain",
MismatchCount: 3,
},
},
},
{
name: "Multiple Entries",
src: "10.55.100.104",
fqdn: "a.scorecardresearch.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/rpc.flow?uid=uid1517427661057&ns_mod_ns=mvce&ns__p=1517427661054&ns__t=1517427661054&ns__c=utf-8&ns_ad_conn=true|undefined&c1=3&c3=20577465&c4=97390356&c5=211121863&c6=&c10=1&c11=936679&c13=320x50&c16=dcm&c2=26816564&ax_iframe=1&ns_ce_sv=5.1710.03&ns_ce_mod=vce_st&ns_ad_event=load&c8=&c7=http://www.espn.com/&c9=",
Path: "/rpc.flow",
Extension: "flow",
MimeType: "text/plain",
MismatchCount: 1,
},
{
URI: "/rpc.flow?uid=uid1517432161482&ns_mod_ns=mvce&ns__p=1517432161476&ns__t=1517432161476&ns__c=utf-8&ns_ad_conn=true|undefined&c1=3&c3=20577465&c4=97390356&c5=211121863&c6=&c10=1&c11=936679&c13=320x50&c16=dcm&c2=26816564&ax_iframe=1&ns_ce_sv=5.1710.03&ns_ce_mod=vce_st&ns_ad_event=load&c8=&c7=http://www.espn.com/&c9=",
Path: "/rpc.flow",
Extension: "flow",
MimeType: "text/plain",
MismatchCount: 1,
},
{
URI: "/rpc.flow?uid=uid1517433060023&ns_mod_ns=mvce&ns__p=1517433060017&ns__t=1517433060017&ns__c=utf-8&ns_ad_conn=true|undefined&c1=3&c3=20577465&c4=97390356&c5=211121863&c6=&c10=1&c11=936679&c13=320x50&c16=dcm&c2=26816564&ax_iframe=1&ns_ce_sv=5.1710.03&ns_ce_mod=vce_st&ns_ad_event=load&c8=&c7=http://www.espn.com/&c9=",
Path: "/rpc.flow",
Extension: "flow",
MimeType: "text/plain",
MismatchCount: 1,
},
{
URI: "/rpc.flow?uid=uid1517441160618&ns_mod_ns=mvce&ns__p=1517441160615&ns__t=1517441160615&ns__c=utf-8&ns_ad_conn=true|undefined&c1=3&c3=20577465&c4=97390356&c5=211121863&c6=&c10=1&c11=936679&c13=320x50&c16=dcm&c2=26816564&ax_iframe=1&ns_ce_sv=5.1710.03&ns_ce_mod=vce_st&ns_ad_event=load&c8=&c7=http://www.espn.com/&c9=",
Path: "/rpc.flow",
Extension: "flow",
MimeType: "text/plain",
MismatchCount: 1,
},
{
URI: "/rpc.flow?uid=uid1517436360940&ns_mod_ns=mvce&ns__p=1517436360935&ns__t=1517436360935&ns__c=utf-8&ns_ad_conn=true|undefined&c1=3&c3=20577465&c4=97390356&c5=211121863&c6=&c10=1&c11=936679&c13=320x50&c16=dcm&c2=26816564&ax_iframe=1&ns_ce_sv=5.1710.03&ns_ce_mod=vce_st&ns_ad_event=load&c8=&c7=http://www.espn.com/&c9=",
Path: "/rpc.flow",
Extension: "flow",
MimeType: "text/plain",
MismatchCount: 1,
},
},
},
{
name: "Extensions in URIs",
src: "10.55.100.107",
fqdn: "static.adsafeprotected.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/main.17.4.65.js",
Path: "/main.17.4.65.js",
Extension: "js",
MimeType: "text/plain",
MismatchCount: 1,
},
{
URI: "/main.17.4.64.js",
Path: "/main.17.4.64.js",
Extension: "js",
MimeType: "text/plain",
MismatchCount: 1,
},
},
},
{
name: "No Extensions in URIs",
src: "10.55.100.108",
fqdn: "www.businessinsider.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/esi/user_menubar?0=json:[]&1=NULL",
Path: "/esi/user_menubar",
Extension: "",
MimeType: "text/plain",
MismatchCount: 17,
},
{
URI: "/esi/ed_sidebar",
Path: "/esi/ed_sidebar",
Extension: "",
MimeType: "text/plain",
MismatchCount: 17,
},
},
},
{
name: "Variety of Extensions in URIs",
src: "10.55.100.105",
fqdn: "static1.businessinsider.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/assets/js/min-foot.js?1517260501",
Path: "/assets/js/min-foot.js",
Extension: "js",
MimeType: "text/plain",
MismatchCount: 12,
},
{
URI: "/public/fonts/LabGrotesque-Regular.woff",
Path: "/public/fonts/LabGrotesque-Regular.woff",
Extension: "woff",
MimeType: "text/html",
MismatchCount: 12,
},
{
URI: "/assets/css/min-base-us.css?1517260501",
Path: "/assets/css/min-base-us.css",
Extension: "css",
MimeType: "text/plain",
MismatchCount: 12,
},
},
},
{
name: "High MisMatch Counts",
src: "10.55.100.106",
fqdn: "www.alexa.com",
mimeTypesURIInfoList: []mimeTypesURIInfo{
{
URI: "/topsites/category;3/Top/Business/",
Path: "/topsites/category;3/Top/Business/",
Extension: "",
MimeType: "text/html",
MismatchCount: 61,
},
{
URI: "/topsites/category;0/Top/Business/",
Path: "/topsites/category;0/Top/Business/",
Extension: "",
MimeType: "text/html",
MismatchCount: 63,
},
{
URI: "/topsites/category;2/Top/Business/",
Path: "/topsites/category;2/Top/Business/",
Extension: "",
MimeType: "text/html",
MismatchCount: 67,
},
{
URI: "/topsites/category;1/Top/Business/",
Path: "/topsites/category;1/Top/Business/",
Extension: "",
MimeType: "text/html",
MismatchCount: 53,
},
{
URI: "/topsites/category;4/Top/Business/",
Path: "/topsites/category;4/Top/Business/",
Extension: "",
MimeType: "text/html",
MismatchCount: 44,
},
},
},
}
for _, test := range tests {
// check mime types uris table values
it.Run(test.name, func() {
t := it.T()
// create a hash for the given src and fqdn
hash, err := util.NewFixedStringHash(test.src, util.UnknownPrivateNetworkUUID.String(), test.fqdn)
require.NoError(t, err, "creating hash should not produce an error")
// set the context parameters
ctx := clickhouse.Context(context.Background(), clickhouse.WithParameters(clickhouse.Parameters{
"hash": hash.Hex(),
"modifier_name": modifier.MIME_TYPE_MISMATCH_MODIFIER_NAME,
}))
t.Run("Verify Values", func(t *testing.T) {
// query the proto table for the given src and fqdn
var res []mimeTypesURIInfo
err = it.db.Conn.Select(ctx, &res, `
SELECT uri,
path,
extension,
mime_type,
countMerge(mismatch_count) as mismatch_count
FROM mime_type_uris
WHERE hash=unhex({hash:String})
GROUP BY hash, uri, path, extension, mime_type
`)
require.NoError(t, err, "querying mime_type_uris table should not produce an error")
// ensure that the length of the result list matches the expected value
require.Len(t, res, len(test.mimeTypesURIInfoList), "length of result list should match expected value")
// ensure that the result list matches the expected value
require.ElementsMatch(t, test.mimeTypesURIInfoList, res, "result list should match expected value")
})
// check threat mixtape table modifier entry
t.Run("Verify Modifier", func(t *testing.T) {
// query the threat_mixtape table for the given hash and modifier name
var res2 modifierInfo
err = it.db.Conn.QueryRow(ctx, `
SELECT modifier_name, modifier_score, modifier_value
FROM threat_mixtape
WHERE hash=unhex({hash:String}) AND modifier_name={modifier_name:String}
`).ScanStruct(&res2)
// verify that the query did not produce an error and that the result is not empty
require.NoError(t, err, "querying threat_mixtape table should not produce an error")
require.NotEmpty(t, res2, "result should not be empty")
// check score was set correctly based on config
require.InDelta(t, it.cfg.Modifiers.MIMETypeMismatchScoreIncrease, res2.ModifierScore, 0.001, "modifier score must match expected value")
// verify that modifier value is equal to the sum of all the mismatch counts
var sum uint64
for _, info := range test.mimeTypesURIInfoList {
sum += info.MismatchCount
}
modifierValue, err := strconv.Atoi(res2.ModifierValue)
require.NoError(t, err, "modifier value must be able to be converted to an integer")
require.EqualValues(t, sum, modifierValue, "modifier value must match the sum of all mismatch counts")
})
})
}
}
func (it *ValidDatasetTestSuite) TestThreatMixtape() {
t := it.T()
var count uint64
err := it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count(DISTINCT hash) FROM threat_mixtape
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 4668, count, "threat mixtape should have 4668 unique hashes, got: %d", count)
err = it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count() FROM threat_mixtape
WHERE modifier_name = ''
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 4668, count, "threat mixtape should have one non-modifier row per unique hash, got: %d", count)
err = it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count() FROM (
SELECT hash, count() as c FROM threat_mixtape
WHERE modifier_name = 'rare_signature'
GROUP BY hash
) WHERE c > 1
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "threat mixtape should have at most one rare_signature modifier row per unique hash, got: %d", count)
err = it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count() FROM (
SELECT hash, count() as c FROM threat_mixtape
WHERE modifier_name = 'mime_type_mismatch'
GROUP BY hash
) WHERE c > 1
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "threat mixtape should have at most one mime_type_mismatch modifier row per unique hash, got: %d", count)
err = it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count() FROM threat_mixtape
WHERE beacon_type != 'dns' AND count != open_count
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "open conn count should always match conn count (for non-DNS)")
minTimestamp, maxTimestamp, _, err := it.db.GetBeaconMinMaxTimestamps()
require.NoError(t, err)
chCtx := it.db.QueryParameters(clickhouse.Parameters{
"min_ts": fmt.Sprintf("%d", minTimestamp.UTC().Unix()),
"max_ts": fmt.Sprintf("%d", maxTimestamp.UTC().Unix()),
"first_seen_increase_score": fmt.Sprintf("%1.3f", it.cfg.Modifiers.FirstSeenScoreIncrease),
"prevalence_decrease_score": fmt.Sprintf("%1.3f", -it.cfg.Modifiers.PrevalenceScoreDecrease),
"beacon_none_thresh": fmt.Sprintf("%1.3f", float64(it.cfg.Scoring.Beacon.ScoreThresholds.Base)/100),
})
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE last_seen < {min_ts:Int64} OR last_seen > {max_ts:Int64}
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no entries should have a last seen date less than the min timestamp or greater than the max timestamp")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE modifier_name = '' AND (first_seen_historical < {min_ts:Int64} OR first_seen_historical > {max_ts:Int64})
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no non-modifier entries should have a historical first seen date less than the min timestamp or greater than the max timestamp")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE first_seen_score != 0
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no entries should have a historical first seen score for a non-rolling dataset")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count(DISTINCT import_id) FROM threat_mixtape
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 1, count, "there should be only one import id")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count(DISTINCT analyzed_at) FROM threat_mixtape
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 1, count, "there should be only one unique analyzed at timestamp")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE src != '::' AND dst = '::' AND fqdn = ''
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no entries with a src IP should have missing dst IP and missing FQDN")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE beacon_type = 'dns' AND (src != '::' OR dst != '::')
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no DNS entries should have a src or dst IP")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE beacon_type = 'sni' AND length(server_ips) = 0
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all SNI entries should have at least 1 server IP")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE beacon_type != '' AND beacon_type != 'dns' AND (count = 0)
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all non-DNS (non-modifier) entries should have a connection count")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE count >= 86400 AND strobe_score <= 0
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all strobes should have a strobe score")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE beacon_score >= {beacon_none_thresh:Float64} AND beacon_threat_score <= 0
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all entries with a beacon score should have a beacon threat score")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE modifier_name = '' AND (prevalence_total <= 0 OR prevalence <= 0)
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all non-modifier entries should have prevalence set")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE prevalence_total >= 8 AND (prevalence < 8/15 OR prevalence_score > {prevalence_decrease_score:Float64})
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all entries with a prevalence total being over 50% (8/15) should have a prevalence of >= 0.53 and prevalence decrease score")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE prevalence_total < 8 AND (prevalence > 8/15 OR prevalence_score != 0)
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all entries with a prevalence total being under 50% (8/15) should have a prevalence of < 0.53 and prevalence score of 0")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM (
SELECT src, dst, fqdn, count(hash) as hash_count FROM threat_mixtape
WHERE beacon_type = 'sni'
GROUP BY src, dst, fqdn
) WHERE hash_count != 1
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "each unique SNI connection should use only one unique hash")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM (
SELECT src, dst, count(hash) as hash_count FROM threat_mixtape
WHERE beacon_type = 'ip'
GROUP BY src, dst
) WHERE hash_count != 1
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "each unique IP connection should use only one unique hash")
err = it.db.Conn.QueryRow(chCtx, `
SELECT count() FROM threat_mixtape
WHERE beacon_type = 'dns' AND hash != MD5(fqdn)
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "all dns entries should have a hash that contains only the fqdn")
// uconns with no connections that weren't part of a SNI connection
verifyHashInMixtape(t, it.db, "0006AF9EDD0596EE5C87E7AAD63DC2FF", false)
verifyHashInMixtape(t, it.db, "000DB22CFD645023D2FE454685A067E5", false)
verifyHashInMixtape(t, it.db, "00281FB4049C4613CA0F4307F7B96932", false)
// uconns with connections that weren't part of a SNI connection
verifyHashInMixtape(t, it.db, "001569CF7AA5791DE4C5DF3498816D34", true)
verifyHashInMixtape(t, it.db, "005BE49BF3FC40ECF0366A79408074B1", true)
}
func verifyHashInMixtape(t *testing.T, db *database.DB, hash string, shouldExist bool) {
t.Helper()
ctx := db.QueryParameters(clickhouse.Parameters{
"hash": hash,
})
var count uint64
err := db.Conn.QueryRow(ctx, `
SELECT count() FROM threat_mixtape
WHERE hash = unhex({hash:String})
`).Scan(&count)
require.NoError(t, err, "querying threat mixtape should not produce an error")
if shouldExist {
require.Positive(t, count, "hash %s should exist in threat mixtape", hash)
} else {
require.EqualValues(t, 0, count, "hash %s should not exist in threat mixtape", hash)
}
}
func (it *ValidDatasetTestSuite) TestBigOlHistogramTable() {
t := it.T()
var count uint64
err := it.db.Conn.QueryRow(it.db.GetContext(), `
SELECT count() FROM (
SELECT import_hour, hash, bucket, src_ip_bytes, countMerge(count) AS c FROM big_ol_histogram
GROUP BY import_hour, hash, bucket, src_ip_bytes
HAVING c < 1
)
`).Scan(&count)
require.NoError(t, err)
require.EqualValues(t, 0, count, "no entries in big_ol_histogram should have a count less than 1")
type histogram struct {
Bucket uint32 `ch:"bucket_ts"`
SrcIPBytes uint64 `ch:"src_ip_bytes"`
Count uint64 `ch:"count"`
}
testCases := []struct {
name string
hash string
hour int64
Data []histogram
}{
{
name: "10.55.100.107 -> 23.217.28.150",
hash: "00281FB4049C4613CA0F4307F7B96932",
hour: 1517418000,
Data: []histogram{
{Bucket: 1517418000, SrcIPBytes: 1691, Count: 3},
{Bucket: 1517418900, SrcIPBytes: 1600, Count: 2},
{Bucket: 1517420700, SrcIPBytes: 1599, Count: 2},
},
},
{
name: "10.55.100.109 -> www.alexa.com (HTTP & SSL)",
hash: "F9433F6806956E558A72AE934BB7CC4F",
hour: 1517338800,
Data: []histogram{
{Bucket: 1517338800, SrcIPBytes: 7134, Count: 6},
{Bucket: 1517339700, SrcIPBytes: 6352, Count: 6},
{Bucket: 1517340600, SrcIPBytes: 7402, Count: 6},
{Bucket: 1517341500, SrcIPBytes: 6392, Count: 6},
},
},
}
for _, test := range testCases {
t.Run(test.name, func(t *testing.T) {
var res []histogram
chCtx := it.db.QueryParameters(clickhouse.Parameters{
"hash": test.hash,
"hour": fmt.Sprintf("%d", test.hour),
})
err = it.db.Conn.Select(chCtx, &res, `
SELECT toUnixTimestamp(bucket) as bucket_ts, sum(src_ip_bytes) as src_ip_bytes, countMerge(count) as count FROM big_ol_histogram
WHERE hash = unhex({hash:String}) AND toStartOfHour(bucket) = fromUnixTimestamp({hour:Int64})
GROUP BY bucket_ts
ORDER BY bucket_ts
`)
require.NoError(t, err)
require.Equal(t, test.Data, res)
})
}
}