Instead of paying for Netflix, I put up a VPS and collected data last month. Interesting results found in the tables below... I started blocking by HASSH this past week, so the numbers are a little off =)

Example of the web dashboard before I started blocking by HASSH:

Example of the web dashboard after I started blocking by HASSH:

Stats

SELECT 
    ip.version,
    COUNT(sce.id) as total_attempts,
    COUNT(DISTINCT sce.source_ip_id) as unique_ips,
    COUNT(DISTINCT sce.username_id) as unique_usernames,
    COUNT(DISTINCT sce.password_id) as unique_passwords
FROM ssh_connection_events sce
JOIN ip_addresses ip ON sce.source_ip_id = ip.id
GROUP BY ip.version;

Authmethods

SELECT 
    am.method_name,
    COUNT(seam.id) as usage_count,
    COUNT(DISTINCT seam.event_id) as unique_events,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_event_auth_methods seam
JOIN auth_methods am ON seam.auth_method_id = am.id
JOIN ssh_connection_events sce ON seam.event_id = sce.id
GROUP BY am.method_name
ORDER BY usage_count DESC;

SSH client names with HASSH fingerprints and usage statistics

SELECT 
    scn.value as ssh_client,
    hf.fingerprint as hassh_fingerprint,
    COUNT(DISTINCT sce.id) as connection_count,
    COUNT(DISTINCT sce.source_ip_id) as unique_ips,
    COUNT(DISTINCT sce.username_id) as unique_usernames,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_connection_events sce
JOIN ha_ssh_fingerprints hf ON sce.ha_ssh_fingerprint_id = hf.id
LEFT JOIN ssh_client_names scn ON sce.ssh_client_name_id = scn.id
GROUP BY hf.fingerprint, scn.value
ORDER BY connection_count DESC;

Top IP addresses by connection count

SELECT 
    ip.address,
    ip.version,
    COUNT(sce.id) as connection_count,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_connection_events sce
JOIN ip_addresses ip ON sce.source_ip_id = ip.id
GROUP BY ip.id, ip.address, ip.version
ORDER BY connection_count DESC
LIMIT 20;

Top IPs with their most common SSH client and HASSH

SELECT 
    ip.address,
    COUNT(sce.id) as total_connections,
    GROUP_CONCAT(DISTINCT scn.value) as ssh_clients,
    GROUP_CONCAT(DISTINCT hf.fingerprint) as hassh_fingerprints,
    COUNT(DISTINCT sce.username_id) as unique_usernames_tried,
    MIN(sce.timestamp) as first_attempt,
    MAX(sce.timestamp) as last_attempt
FROM ssh_connection_events sce
JOIN ip_addresses ip ON sce.source_ip_id = ip.id
JOIN ha_ssh_fingerprints hf ON sce.ha_ssh_fingerprint_id = hf.id
LEFT JOIN ssh_client_names scn ON sce.ssh_client_name_id = scn.id
GROUP BY ip.id, ip.address
ORDER BY total_connections DESC
LIMIT 20;

Top Username Attempts

SELECT 
    u.username,
    COUNT(sce.id) as attempt_count,
    COUNT(DISTINCT sce.source_ip_id) as unique_ips,
    COUNT(DISTINCT sce.password_id) as unique_passwords,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_connection_events sce
JOIN usernames u ON sce.username_id = u.id
GROUP BY u.id, u.username
ORDER BY attempt_count DESC
LIMIT 20;

Top Password Attempts

SELECT 
    p.password,
    COUNT(sce.id) as attempt_count,
    COUNT(DISTINCT sce.source_ip_id) as unique_ips,
    COUNT(DISTINCT sce.username_id) as unique_usernames,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_connection_events sce
JOIN passwords p ON sce.password_id = p.id
GROUP BY p.id, p.password
ORDER BY attempt_count DESC
LIMIT 20;

Most Common Username/Password

SELECT 
    u.username,
    p.password,
    COUNT(sce.id) as attempt_count,
    COUNT(DISTINCT sce.source_ip_id) as unique_ips,
    MIN(sce.timestamp) as first_seen,
    MAX(sce.timestamp) as last_seen
FROM ssh_connection_events sce
JOIN usernames u ON sce.username_id = u.id
JOIN passwords p ON sce.password_id = p.id
GROUP BY u.username, p.password
ORDER BY attempt_count DESC
LIMIT 30;

Username/Password Diversity

SELECT 
    ip.address,
    COUNT(DISTINCT sce.username_id) as unique_usernames,
    COUNT(DISTINCT sce.password_id) as unique_passwords,
    COUNT(sce.id) as total_attempts,
    MIN(sce.timestamp) as first_attempt,
    MAX(sce.timestamp) as last_attempt
FROM ssh_connection_events sce
JOIN ip_addresses ip ON sce.source_ip_id = ip.id
GROUP BY ip.id, ip.address
ORDER BY unique_usernames DESC, unique_passwords DESC
LIMIT 20;

Authmethods by Credential

SELECT 
    u.username,
    p.password,
    GROUP_CONCAT(DISTINCT am.method_name) as auth_methods,
    COUNT(sce.id) as attempt_count
FROM ssh_connection_events sce
JOIN usernames u ON sce.username_id = u.id
JOIN passwords p ON sce.password_id = p.id
LEFT JOIN ssh_event_auth_methods seam ON sce.id = seam.event_id
LEFT JOIN auth_methods am ON seam.auth_method_id = am.id
GROUP BY u.username, p.password
ORDER BY attempt_count DESC
LIMIT 20;

Spray vs. Stuffing

SELECT 
    ip.address,
    COUNT(DISTINCT sce.username_id) as unique_usernames,
    COUNT(DISTINCT sce.password_id) as unique_passwords,
    COUNT(sce.id) as total_attempts,
    CASE 
        WHEN COUNT(DISTINCT sce.username_id) > COUNT(DISTINCT sce.password_id) * 3 THEN 'Credential Spray'
        WHEN COUNT(DISTINCT sce.password_id) > COUNT(DISTINCT sce.username_id) * 3 THEN 'Brute Force'
        ELSE 'Mixed/Stuffing'
    END as attack_type
FROM ssh_connection_events sce
JOIN ip_addresses ip ON sce.source_ip_id = ip.id
GROUP BY ip.address
HAVING total_attempts > 10
ORDER BY total_attempts DESC
LIMIT 30;

Client by HASSH

SELECT DISTINCT
    scn.value AS ssh_client_name,
    hsf.fingerprint AS hassh_fingerprint
FROM ssh_connection_events sce
JOIN ssh_client_names scn ON
    sce.ssh_client_name_id = scn.id
JOIN ha_ssh_fingerprints hsf ON
    sce.ha_ssh_fingerprint_id = hsf.id
WHERE sce.ssh_client_name_id IS NOT NULL
ORDER BY ssh_client_name;