110 lines
2.4 KiB
Python
110 lines
2.4 KiB
Python
#!/usr/bin/env python3
|
|
import sqlite3
|
|
from datetime import datetime
|
|
|
|
db = sqlite3.connect("sshd_failures.db")
|
|
|
|
def ts(x):
|
|
return datetime.fromtimestamp(x).strftime("%Y-%m-%d %H:%M:%S") if x else "N/A"
|
|
|
|
def bar(n, mx, width=40):
|
|
if not n or mx == 0:
|
|
return ""
|
|
return "█" * max(1, int((n / mx) * width))
|
|
|
|
total = db.execute("SELECT COUNT(*) FROM events").fetchone()[0]
|
|
ips = db.execute("SELECT COUNT(DISTINCT ip) FROM events").fetchone()[0]
|
|
countries = db.execute("""
|
|
SELECT COUNT(DISTINCT COALESCE(country,'Unknown'))
|
|
FROM ip_geo
|
|
""").fetchone()[0]
|
|
|
|
print(f"\nEvents: {total} IPs: {ips} Countries: {countries}")
|
|
|
|
print("\n" + "─"*100)
|
|
print("Types")
|
|
print("─"*100)
|
|
|
|
types = list(db.execute("""
|
|
SELECT type, COUNT(*) c
|
|
FROM events
|
|
GROUP BY type
|
|
ORDER BY c DESC
|
|
"""))
|
|
|
|
mx = max([c for _, c in types] or [1])
|
|
for t, c in types:
|
|
print(f"{t:20} {c:6} {bar(c, mx)}")
|
|
|
|
print("\n" + "─"*100)
|
|
print("Top IPs")
|
|
print("─"*100)
|
|
|
|
ips_top = list(db.execute("""
|
|
SELECT
|
|
e.ip,
|
|
COUNT(*) c,
|
|
MAX(e.ts) last,
|
|
COALESCE(g.country,'Unknown') country
|
|
FROM events e
|
|
LEFT JOIN ip_geo g ON e.ip = g.ip
|
|
GROUP BY e.ip
|
|
ORDER BY c DESC
|
|
LIMIT 15
|
|
"""))
|
|
|
|
mx = max([c for _, c, _, _ in ips_top] or [1])
|
|
for ip, c, last, country in ips_top:
|
|
print(f"{ip:18} {c:6} {country:15} {bar(c, mx)} last={ts(last)}")
|
|
|
|
print("\n" + "─"*100)
|
|
print("Countries")
|
|
print("─"*100)
|
|
|
|
countries_rows = list(db.execute("""
|
|
SELECT
|
|
COALESCE(g.country,'Unknown') country,
|
|
COUNT(*) total,
|
|
COUNT(DISTINCT e.ip) ips,
|
|
MAX(e.ts) last
|
|
FROM events e
|
|
JOIN ip_geo g ON e.ip = g.ip
|
|
GROUP BY country
|
|
ORDER BY total DESC
|
|
"""))
|
|
|
|
mx = max([c for _, c, _, _ in countries_rows] or [1])
|
|
|
|
for country, total_c, ip_count, last in countries_rows:
|
|
top_ip = db.execute("""
|
|
SELECT e.ip
|
|
FROM events e
|
|
JOIN ip_geo g ON e.ip = g.ip
|
|
WHERE COALESCE(g.country,'Unknown') = ?
|
|
GROUP BY e.ip
|
|
ORDER BY COUNT(*) DESC
|
|
LIMIT 1
|
|
""", (country,)).fetchone()
|
|
|
|
top_ip = top_ip[0] if top_ip else "Unknown"
|
|
|
|
print(f"{country:25} {total_c:6} ips={ip_count:4} top={top_ip:16} {bar(total_c, mx)} last={ts(last)}")
|
|
|
|
print("\n" + "─"*100)
|
|
print("Usernames")
|
|
print("─"*100)
|
|
|
|
users = list(db.execute("""
|
|
SELECT COALESCE(user,'unknown') user, COUNT(*) c
|
|
FROM events
|
|
WHERE user IS NOT NULL
|
|
GROUP BY user
|
|
ORDER BY c DESC
|
|
LIMIT 30
|
|
"""))
|
|
|
|
mx = max([c for _, c in users] or [1])
|
|
|
|
for u, c in users:
|
|
print(f"{u:20} {c:6} {bar(c, mx)}")
|