"""
FIXED SYSMON SERVER - Corrected API endpoints and database
"""
from flask import Flask, request, jsonify, render_template, send_from_directory
import sqlite3
import json
from datetime import datetime, timedelta
import threading
import time
from collections import defaultdict
import os
import re
app = Flask(__name__)
def init_database():
"""Inicializiraј ja bazata so site polinja"""
conn = sqlite3.connect('sysmon_server.db', check_same_thread=False)
cursor = conn.cursor()
# KREIRAJ NOVA BAZA - DELETE OLD ONE FIRST
cursor.execute("DROP TABLE IF EXISTS sysmon_events")
cursor.execute("DROP TABLE IF EXISTS windows_events")
cursor.execute("DROP TABLE IF EXISTS system_info")
cursor.execute("DROP TABLE IF EXISTS processes")
cursor.execute("DROP TABLE IF EXISTS threats")
cursor.execute("DROP TABLE IF EXISTS computers")
# Kreiraј tabela za eventi so site polinja
cursor.execute('''
CREATE TABLE sysmon_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id INTEGER,
event_type TEXT,
timestamp TEXT,
computer TEXT,
-- Process Creation (Event ID 1)
process_name TEXT,
process_path TEXT,
command_line TEXT,
process_user TEXT,
parent_process TEXT,
parent_command TEXT,
current_directory TEXT,
hashes TEXT,
-- Network Connection (Event ID 3)
source_ip TEXT,
source_port INTEGER,
dest_ip TEXT,
dest_port INTEGER,
protocol TEXT,
network_initiated TEXT,
-- DNS Query (Event ID 22)
dns_query TEXT,
dns_results TEXT,
dns_status TEXT,
dns_process TEXT,
-- File Create (Event ID 11)
file_path TEXT,
file_hash TEXT,
-- DLL Loaded (Event ID 7)
dll_loaded TEXT,
dll_signed TEXT,
-- Raw data
raw_data TEXT,
received_at TEXT
)
''')
# Kreiraј tabela za Windows events
cursor.execute('''
CREATE TABLE windows_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
event_id INTEGER,
log_name TEXT,
source TEXT,
timestamp TEXT,
computer TEXT,
level TEXT,
message TEXT,
user TEXT,
process_name TEXT,
process_path TEXT,
file_created TEXT,
file_deleted TEXT,
file_modified TEXT,
service_name TEXT,
service_action TEXT,
login_user TEXT,
login_ip TEXT,
login_type TEXT,
raw_data TEXT,
received_at TEXT
)
''')
# Kreiraј tabela za system info
cursor.execute('''
CREATE TABLE system_info (
id INTEGER PRIMARY KEY AUTOINCREMENT,
computer TEXT,
user TEXT,
os TEXT,
cpu_usage REAL,
ram_usage REAL,
disk_usage REAL,
timestamp TEXT,
received_at TEXT
)
''')
# Kreiraј tabela za procesi SO EXE_PATH
cursor.execute('''
CREATE TABLE processes (
id INTEGER PRIMARY KEY AUTOINCREMENT,
pid INTEGER,
name TEXT,
user TEXT,
cpu_percent REAL,
memory_mb REAL,
cmdline TEXT,
exe_path TEXT,
timestamp TEXT,
computer TEXT,
received_at TEXT
)
''')
# Kreiraј tabela za threats
cursor.execute('''
CREATE TABLE threats (
id INTEGER PRIMARY KEY AUTOINCREMENT,
computer TEXT,
threat_type TEXT,
level TEXT,
description TEXT,
details TEXT,
timestamp TEXT,
resolved BOOLEAN DEFAULT 0
)
''')
# Kreiraј tabela za computers
cursor.execute('''
CREATE TABLE computers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
computer_name TEXT UNIQUE,
user TEXT,
ip_address TEXT,
os TEXT,
last_seen TEXT,
total_events INTEGER DEFAULT 0,
status TEXT DEFAULT 'online'
)
''')
# Kreiraј indeksi
cursor.execute('CREATE INDEX idx_computer ON sysmon_events(computer)')
cursor.execute('CREATE INDEX idx_event_type ON sysmon_events(event_type)')
cursor.execute('CREATE INDEX idx_timestamp ON sysmon_events(timestamp)')
cursor.execute('CREATE INDEX idx_dns_query ON sysmon_events(dns_query)')
cursor.execute('CREATE INDEX idx_process_name ON sysmon_events(process_name)')
conn.commit()
conn.close()
print("✅ Database initialized successfully with ALL columns")
def get_db_connection():
"""Vrati database connection"""
conn = sqlite3.connect('sysmon_server.db', check_same_thread=False)
conn.row_factory = sqlite3.Row
return conn
# Site drugi funkcii se isti kako predhodno...
# (kopiraј gi site funkcii od prethodniot kod)
# FIXED API ENDPOINTS - koristi gi tochnite endpoints
@app.route('/api/detailed_events')
def api_detailed_events():
"""Vrati detalni eventi"""
conn = get_db_connection()
cursor = conn.cursor()
limit = request.args.get('limit', 50, type=int)
cursor.execute('SELECT * FROM sysmon_events ORDER BY timestamp DESC LIMIT ?', (limit,))
events = []
for row in cursor.fetchall():
event = dict(row)
event['display_text'] = format_event_for_display(row)
events.append(event)
conn.close()
return jsonify({
"events": events,
"count": len(events)
})
@app.route('/api/dns_queries')
def api_dns_queries():
"""Vrati DNS queries"""
conn = get_db_connection()
cursor = conn.cursor()
limit = request.args.get('limit', 100, type=int)
cursor.execute('''
SELECT timestamp, computer, dns_query, dns_results, dns_process
FROM sysmon_events
WHERE dns_query != '' AND dns_query IS NOT NULL
ORDER BY timestamp DESC
LIMIT ?
''', (limit,))
dns_queries = []
for row in cursor.fetchall():
query = dict(row)
dns_queries.append(query)
conn.close()
return jsonify({
"dns_queries": dns_queries,
"count": len(dns_queries)
})
@app.route('/api/process_creations')
def api_process_creations():
"""Vrati process creations"""
conn = get_db_connection()
cursor = conn.cursor()
limit = request.args.get('limit', 100, type=int)
cursor.execute('''
SELECT timestamp, computer, process_name, command_line, process_user
FROM sysmon_events
WHERE event_id = 1 AND process_name != ''
ORDER BY timestamp DESC
LIMIT ?
''', (limit,))
processes = []
for row in cursor.fetchall():
proc = dict(row)
processes.append(proc)
conn.close()
return jsonify({
"process_creations": processes,
"count": len(processes)
})
@app.route('/api/network_connections')
def api_network_connections():
"""Vrati network connections"""
conn = get_db_connection()
cursor = conn.cursor()
limit = request.args.get('limit', 100, type=int)
cursor.execute('''
SELECT timestamp, computer, process_name, dest_ip, dest_port
FROM sysmon_events
WHERE event_id = 3 AND dest_ip != ''
ORDER BY timestamp DESC
LIMIT ?
''', (limit,))
connections = []
for row in cursor.fetchall():
conn_data = dict(row)
connections.append(conn_data)
conn.close()
return jsonify({
"network_connections": connections,
"count": len(connections)
})
@app.route('/api/stats')
def api_stats():
"""Vrati statistika"""
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM sysmon_events")
total_events = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(DISTINCT computer) FROM sysmon_events")
total_computers = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM computers WHERE status = 'online'")
online_computers = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM sysmon_events WHERE dns_query != ''")
total_dns = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM sysmon_events WHERE event_id = 1")
total_processes = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM sysmon_events WHERE event_id = 3")
total_connections = cursor.fetchone()[0]
conn.close()
return jsonify({
"total_events": total_events,
"total_computers": total_computers,
"online_computers": online_computers,
"dns_queries": total_dns,
"process_creations": total_processes,
"network_connections": total_connections,
"timestamp": datetime.now().isoformat()
})
@app.route('/api/computers')
def api_computers():
"""Return list of computers"""
conn = get_db_connection()
cursor = conn.cursor()
cursor.execute('''
SELECT c.*,
COUNT(e.id) as event_count,
MAX(e.timestamp) as last_event
FROM computers c
LEFT JOIN sysmon_events e ON c.computer_name = e.computer
GROUP BY c.computer_name
ORDER BY c.last_seen DESC
''')
computers = []
for row in cursor.fetchall():
computer = dict(row)
computers.append(computer)
conn.close()
return jsonify({
"computers": computers,
"count": len(computers)
})
@app.route('/api/dns')
def api_dns():
"""Return DNS queries"""
conn = get_db_connection()
cursor = conn.cursor()
limit = request.args.get('limit', 100, type=int)
cursor.execute('''
SELECT timestamp, computer, dns_query, dns_results, process_name
FROM sysmon_events
WHERE dns_query IS NOT NULL AND dns_query != ''
ORDER BY timestamp DESC
LIMIT ?
''', (limit,))
dns_queries = [dict(row) for row in cursor.fetchall()]
# Get top queried domains
cursor.execute('''
SELECT dns_query, COUNT(*) as query_count
FROM sysmon_events
WHERE dns_query IS NOT NULL AND dns_query != ''
GROUP BY dns_query
ORDER BY query_count DESC
LIMIT 10
''')
top_domains = [dict(row) for row in cursor.fetchall()]
conn.close()
return jsonify({
"dns_queries": dns_queries,
"top_domains": top_domains,
"count": len(dns_queries)
})
@app.route('/ping', methods=['GET'])
def ping():
"""Ping endpoint"""
return jsonify({
"message": "pong",
"status": "ok",
"server": "Fixed Sysmon Server",
"time": datetime.now().isoformat()
}), 200
@app.route('/')
def dashboard():
"""Dashboard so popraveni API endpoints"""
return render_template('dashboard_fixed.html')
# HTML template so popraveni API calls
dashboard_fixed_html = '''
Sysmon Dashboard - Fixed
| Time |
Computer |
Domain |
Results |
Process |
| Time |
Computer |
Process |
User |
Command |
| Time |
Computer |
Process |
Destination |
Port |
'''
# Dodadi gi site funkcii sto nedostavaat (od prethodniot kod)
def update_computer_status(computer_name, user, ip_address, os_info):
conn = get_db_connection()
cursor = conn.cursor()
try:
cursor.execute('''
INSERT OR REPLACE INTO computers
(computer_name, user, ip_address, os, last_seen, status)
VALUES (?, ?, ?, ?, ?, ?)
''', (computer_name, user, ip_address, os_info, datetime.now().isoformat(), 'online'))
conn.commit()
except Exception as e:
print(f"Error updating computer status: {e}")
finally:
conn.close()
def parse_sysmon_event_data(event_data):
result = {}
result['process_name'] = event_data.get('Image', '')
result['command_line'] = event_data.get('CommandLine', '')
result['process_user'] = event_data.get('User', '')
result['parent_process'] = event_data.get('ParentImage', '')
result['dns_query'] = event_data.get('QueryName', '')
result['dns_results'] = event_data.get('QueryResults', '')
result['dns_process'] = event_data.get('Image', '')
result['source_ip'] = event_data.get('SourceIp', '')
result['dest_ip'] = event_data.get('DestinationIp', '')
result['dest_port'] = event_data.get('DestinationPort', '')
result['file_path'] = event_data.get('TargetFilename', '')
return result
def format_event_for_display(row):
event_id = row['event_id']
if event_id == 22 and row['dns_query']:
return f"DNS Query: {row['dns_query']} → {row['dns_results'] or 'No results'} (Process: {row['dns_process'] or 'Unknown'})"
elif event_id == 1 and row['process_name']:
proc_name = row['process_name'].split('\\')[-1] if '\\' in row['process_name'] else row['process_name']
return f"Process: {proc_name} | User: {row['process_user'] or 'Unknown'}"
elif event_id == 3 and row['dest_ip']:
proc_name = row['process_name'].split('\\')[-1] if row['process_name'] and '\\' in row['process_name'] else row['process_name'] or 'Unknown'
return f"Network: {proc_name} → {row['dest_ip']}:{row['dest_port']}"
return f"Event {event_id}: {row['event_type']}"
@app.route('/receive_data', methods=['POST'])
def receive_data():
try:
data = request.json
if not data:
return jsonify({"error": "No data"}), 400
computer_name = data.get("metadata", {}).get("computer", "Unknown")
user = data.get("metadata", {}).get("user", "Unknown")
conn = get_db_connection()
cursor = conn.cursor()
events_inserted = 0
# Update computer
system_info = data.get("system_info", {})
update_computer_status(
computer_name,
user,
system_info.get("ip_address", "Unknown"),
system_info.get("os", "Unknown")
)
# Save sysmon events
if "sysmon_events" in data:
for event in data["sysmon_events"]:
try:
event_data = event.get("data", {})
parsed = parse_sysmon_event_data(event_data)
cursor.execute('''
INSERT INTO sysmon_events
(event_id, event_type, timestamp, computer,
process_name, command_line, process_user, parent_process,
dns_query, dns_results, dns_process,
source_ip, dest_ip, dest_port, file_path,
raw_data, received_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
''', (
event.get("event_id"),
event.get("event_type"),
event.get("timestamp"),
event.get("computer"),
parsed['process_name'],
parsed['command_line'],
parsed['process_user'],
parsed['parent_process'],
parsed['dns_query'],
parsed['dns_results'],
parsed['dns_process'],
parsed['source_ip'],
parsed['dest_ip'],
parsed['dest_port'],
parsed['file_path'],
json.dumps(event_data),
datetime.now().isoformat()
))
events_inserted += 1
except Exception as e:
print(f"Error inserting event: {e}")
continue
# Save processes (bez exe_path)
if "processes" in data:
for proc in data["processes"]:
try:
cursor.execute('''
INSERT INTO processes
(pid, name, user, cpu_percent, memory_mb, cmdline, timestamp, computer, received_at)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
''', (
proc.get("pid"),
proc.get("name"),
proc.get("user"),
proc.get("cpu_percent", 0),
proc.get("memory_mb", 0),
proc.get("cmdline", ""),
datetime.now().isoformat(),
computer_name,
datetime.now().isoformat()
))
except Exception as e:
print(f"Error inserting process: {e}")
continue
conn.commit()
conn.close()
return jsonify({
"message": "OK",
"events_inserted": events_inserted
}), 200
except Exception as e:
return jsonify({"error": str(e)}), 500
# Write template
if not os.path.exists('templates'):
os.makedirs('templates')
with open('templates/dashboard_fixed.html', 'w', encoding='utf-8') as f:
f.write(dashboard_fixed_html)
if __name__ == '__main__':
# REINITIALIZE DATABASE
init_database()
print("\n" + "="*60)
print("🚀 FIXED SYSMON SERVER")
print("="*60)
print("📊 Dashboard: http://localhost:5555/")
print("📡 Correct API Endpoints:")
print(" - GET /api/detailed_events - All events")
print(" - GET /api/dns_queries - DNS queries")
print(" - GET /api/process_creations - Process executions")
print(" - GET /api/network_connections - Network connections")
print(" - GET /api/stats - Statistics")
print(" - POST /receive_data - Receive client data")
print("\n✅ Database recreated with correct schema")
print("="*60)
app.run(host='0.0.0.0', port=5555, debug=True)