extract_stats.py

import sqlite3
import csv
import glob
import pprint

def get_count(conn, query_string):
    cursor.execute(query_string)
    return cursor.fetchone()[0]

def get_general_stats(cursor):
    result = {}
    result['total_login'] = get_count(cursor, "SELECT COUNT(auth_id) from auth")
    result['unique_usernames'] = get_count(cursor, "SELECT COUNT(DISTINCT username) from auth")
    result['unique_passwords'] = get_count(cursor, "SELECT COUNT(DISTINCT password) from auth")
    return result

def protocols_percentage(cursor):
    result = {}
    query = 'SELECT protocol, COUNT(auth_id), COUNT(auth_id)*100.0/(SELECT COUNT(auth_id) FROM auth) FROM auth GROUP ' \
            'BY protocol ORDER BY COUNT(protocol) DESC'
    for row in cursor.execute(query):
        result[row[0]] = {'count': row[1], 'percentage': row[2]}
    return result

def get_top_grouped_by(cursor, column_name, limit):
    result = {}
    limit = str(limit)
    for row in cursor.execute('SELECT COUNT(?), ' + column_name + ' FROM auth GROUP BY ' + column_name + ' ORDER BY '
                              'COUNT(?) DESC limit ?', (column_name, column_name, limit)):
        result[row[1]] = row[0]
    return result

if __name__ == '__main__':
    conn = sqlite3.connect('heralding.db')
    conn.text_factory = str
    cursor = conn.cursor()

    general_stats = get_general_stats(cursor)
    top_usernames = get_top_grouped_by(cursor, 'username', 15)
    top_passwords = get_top_grouped_by(cursor, 'password', 15)
    protocols_percentage = protocols_percentage(cursor)

    pp = pprint.PrettyPrinter(indent=4)
    print "************************************* TOTALS *************************************"
    pp.pprint(general_stats)
    print "************************************* PROTOCOL DISTRIBUTION *************************************"
    pp.pprint(protocols_percentage)
    print "************************************* TOP 15 USERNAMES *************************************"
    pp.pprint(top_usernames)
    print "************************************* TOP 15 PASSWORDS *************************************"
    pp.pprint(top_passwords)
    conn.close()

Putting the data from heralding logfiles into sqlite (db will be created if not existing). Note that the directory tmp/ contains activity logs downloaded from heralding honeypots.

(heralding) jkv$ python scripts/process_activity_file.py tmp/
[*] Starting file parsing
Start parsing: tmp/107.170.14.78_1471702718_activity.log
[+] tmp/107.170.14.78_1471702718_activity.log: Had 22506 rows with 22506 new entries. Entries per second: 22806.9108639
Start parsing: tmp/192.168.89.254_1471702716_activity.log
[+] tmp/192.168.89.254_1471702716_activity.log: Had 34165 rows with 34165 new entries. Entries per second: 7718.24212994
Start parsing: tmp/52.32.154.39_1471702721_activity.log
[+] tmp/52.32.154.39_1471702721_activity.log: Had 66514 rows with 66514 new entries. Entries per second: 5307.92914489
Start parsing: tmp/77.66.48.67_1471702718_activity.log
[+] tmp/77.66.48.67_1471702718_activity.log: Had 26185 rows with 26185 new entries. Entries per second: 3000.14721688
[*] 4 files parsed. A total of 149370 rows was added, the database now contains 149370 rows.

Generation some stats, the sqlite db (heralding.db) must exist in the current working directory.

(heralding)  jkv$ python scripts/extract_stats.py 
************************************* TOTALS *************************************
{   'total_login': 149370, 'unique_passwords': 16796, 'unique_usernames': 966}
************************************* PROTOCOL DISTRIBUTION *************************************
{   'ftp': {   'count': 6, 'percentage': 0.004016870857601928},
    'http': {   'count': 1, 'percentage': 0.000669478476266988},
    'pop3': {   'count': 1745, 'percentage': 1.1682399410858941},
    'smtp': {   'count': 4691, 'percentage': 3.140523532168441},
    'ssh': {   'count': 91994, 'percentage': 61.5880029457053},
    'telnet': {   'count': 50933, 'percentage': 34.0985472317065}}
************************************* TOP 15 USERNAMES *************************************
{   '': 174,
    'admin': 6746,
    'administrator': 245,
    'enable': 11440,
    'guest': 1370,
    'oracle': 187,
    'root': 99329,
    'service': 270,
    'sh': 2045,
    'shell': 13413,
    'supervisor': 165,
    'support': 1490,
    'test': 276,
    'ubnt': 225,
    'user': 2713}
************************************* TOP 15 PASSWORDS *************************************
{   '': 1123,
    '/bin/busybox MIRAI': 2021,
    '1234': 1642,
    '12345': 2250,
    '123456': 1758,
    'admin': 2633,
    'enable': 2025,
    'pass': 1221,
    'password': 1812,
    'root': 1075,
    'sh': 11391,
    'support': 1173,
    'system': 11640,
    'vizxv': 1408,
    'xc3511': 1532}
(heralding) Johnnys-MacBook-Pro:heralding jkv$