example_analysis.sql 859 Bytes
Newer Older
Alexander Withers's avatar
Alexander Withers committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- computes a checksum of the sorted set of all passwords each host tried as a 
-- 'fingerprint' and then looks for hosts having the same fingerprint.
SELECT
    fingerprint,
    passwords,
    count() AS hosts
FROM
(
    SELECT
        src,
        uniq(password) AS passwords,
        hex(sipHash128(arrayStringConcat(arraySort(groupUniqArray(password))))) AS fingerprint
    FROM events
    WHERE (time > '2018-05-01 00:00:00') AND (password != '')
    GROUP BY src
    HAVING passwords > 5
)
GROUP BY
    fingerprint,
    passwords
ORDER BY hosts DESC
LIMIT 40

-- rare passwords:

select password, uniq(src) as sources from events where length(password) < 50  group by password having sources between 4 and 10;

-- and then, using results, for example:

SELECT DISTINCT src
FROM events
WHERE password = 'Ki!l|iN6#Th3Ph03$%nix@NdR3b!irD'
ORDER BY time ASC