429 Zeilen
14 KiB
Python
429 Zeilen
14 KiB
Python
import logging
|
|
from datetime import datetime, timedelta
|
|
from zoneinfo import ZoneInfo
|
|
from flask import Blueprint, render_template, request, redirect, session, url_for, flash
|
|
|
|
import config
|
|
from auth.decorators import login_required
|
|
from utils.audit import log_audit
|
|
from utils.network import get_client_ip
|
|
from db import get_connection, get_db_connection, get_db_cursor
|
|
from models import get_active_sessions
|
|
|
|
# Create Blueprint
|
|
session_bp = Blueprint('sessions', __name__)
|
|
|
|
|
|
@session_bp.route("/sessions")
|
|
@login_required
|
|
def sessions():
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
# Get is_active sessions with calculated inactive time
|
|
cur.execute("""
|
|
SELECT s.id, s.session_id, l.license_key, c.name, s.ip_address,
|
|
s.user_agent, s.started_at, s.last_heartbeat,
|
|
EXTRACT(EPOCH FROM (NOW() - s.last_heartbeat))/60 as minutes_inactive
|
|
FROM sessions s
|
|
JOIN licenses l ON s.license_id = l.id
|
|
JOIN customers c ON l.customer_id = c.id
|
|
WHERE s.is_active = TRUE
|
|
ORDER BY s.last_heartbeat DESC
|
|
""")
|
|
active_sessions = cur.fetchall()
|
|
|
|
# Get recent ended sessions
|
|
cur.execute("""
|
|
SELECT s.id, s.session_id, l.license_key, c.name, s.ip_address,
|
|
s.started_at, s.ended_at,
|
|
EXTRACT(EPOCH FROM (s.ended_at - s.started_at))/60 as duration_minutes
|
|
FROM sessions s
|
|
JOIN licenses l ON s.license_id = l.id
|
|
JOIN customers c ON l.customer_id = c.id
|
|
WHERE s.is_active = FALSE
|
|
AND s.ended_at > NOW() - INTERVAL '24 hours'
|
|
ORDER BY s.ended_at DESC
|
|
LIMIT 50
|
|
""")
|
|
recent_sessions = cur.fetchall()
|
|
|
|
return render_template("sessions.html",
|
|
active_sessions=active_sessions,
|
|
recent_sessions=recent_sessions)
|
|
|
|
except Exception as e:
|
|
logging.error(f"Error loading sessions: {str(e)}")
|
|
flash('Fehler beim Laden der Sessions!', 'error')
|
|
return redirect(url_for('admin.dashboard'))
|
|
finally:
|
|
cur.close()
|
|
conn.close()
|
|
|
|
|
|
@session_bp.route("/sessions/history")
|
|
@login_required
|
|
def session_history():
|
|
"""Zeigt die Session-Historie"""
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
# Query parameters
|
|
license_key = request.args.get('license_key', '')
|
|
username = request.args.get('username', '')
|
|
days = int(request.args.get('days', 7))
|
|
|
|
# Base query
|
|
query = """
|
|
SELECT
|
|
s.id,
|
|
s.license_key,
|
|
s.username,
|
|
s.hardware_id,
|
|
s.started_at,
|
|
s.ended_at,
|
|
s.last_heartbeat,
|
|
s.is_active,
|
|
l.customer_name,
|
|
l.license_type,
|
|
l.is_test
|
|
FROM sessions s
|
|
LEFT JOIN licenses l ON s.license_key = l.license_key
|
|
WHERE 1=1
|
|
"""
|
|
|
|
params = []
|
|
|
|
# Apply filters
|
|
if license_key:
|
|
query += " AND s.license_key = %s"
|
|
params.append(license_key)
|
|
|
|
if username:
|
|
query += " AND s.username ILIKE %s"
|
|
params.append(f'%{username}%')
|
|
|
|
# Time filter
|
|
query += " AND s.started_at >= CURRENT_TIMESTAMP - INTERVAL '%s days'"
|
|
params.append(days)
|
|
|
|
query += " ORDER BY s.started_at DESC LIMIT 1000"
|
|
|
|
cur.execute(query, params)
|
|
|
|
sessions_list = []
|
|
for row in cur.fetchall():
|
|
session_duration = None
|
|
if row[4] and row[5]: # started_at and ended_at
|
|
duration = row[5] - row[4]
|
|
hours = int(duration.total_seconds() // 3600)
|
|
minutes = int((duration.total_seconds() % 3600) // 60)
|
|
session_duration = f"{hours}h {minutes}m"
|
|
elif row[4] and row[7]: # started_at and is_active
|
|
duration = datetime.now(ZoneInfo("UTC")) - row[4]
|
|
hours = int(duration.total_seconds() // 3600)
|
|
minutes = int((duration.total_seconds() % 3600) // 60)
|
|
session_duration = f"{hours}h {minutes}m (aktiv)"
|
|
|
|
sessions_list.append({
|
|
'id': row[0],
|
|
'license_key': row[1],
|
|
'username': row[2],
|
|
'hardware_id': row[3],
|
|
'started_at': row[4],
|
|
'ended_at': row[5],
|
|
'last_heartbeat': row[6],
|
|
'is_active': row[7],
|
|
'customer_name': row[8],
|
|
'license_type': row[9],
|
|
'is_test': row[10],
|
|
'duration': session_duration
|
|
})
|
|
|
|
# Get unique license keys for filter dropdown
|
|
cur.execute("""
|
|
SELECT DISTINCT s.license_key, l.customer_name
|
|
FROM sessions s
|
|
LEFT JOIN licenses l ON s.license_key = l.license_key
|
|
WHERE s.started_at >= CURRENT_TIMESTAMP - INTERVAL '30 days'
|
|
ORDER BY l.customer_name, s.license_key
|
|
""")
|
|
|
|
available_licenses = []
|
|
for row in cur.fetchall():
|
|
available_licenses.append({
|
|
'license_key': row[0],
|
|
'customer_name': row[1] or 'Unbekannt'
|
|
})
|
|
|
|
return render_template("session_history.html",
|
|
sessions=sessions_list,
|
|
available_licenses=available_licenses,
|
|
filters={
|
|
'license_key': license_key,
|
|
'username': username,
|
|
'days': days
|
|
})
|
|
|
|
except Exception as e:
|
|
logging.error(f"Fehler beim Laden der Session-Historie: {str(e)}")
|
|
flash('Fehler beim Laden der Session-Historie!', 'error')
|
|
return redirect(url_for('sessions.sessions'))
|
|
finally:
|
|
cur.close()
|
|
conn.close()
|
|
|
|
|
|
@session_bp.route("/session/end/<int:session_id>", methods=["POST"])
|
|
@login_required
|
|
def terminate_session(session_id):
|
|
"""Beendet eine aktive Session"""
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
# Get session info
|
|
cur.execute("""
|
|
SELECT license_key, username, hardware_id
|
|
FROM sessions
|
|
WHERE id = %s AND is_active = true
|
|
""", (session_id,))
|
|
|
|
session_info = cur.fetchone()
|
|
if not session_info:
|
|
flash('Session nicht gefunden oder bereits beendet!', 'error')
|
|
return redirect(url_for('sessions.sessions'))
|
|
|
|
# Terminate session
|
|
cur.execute("""
|
|
UPDATE sessions
|
|
SET is_active = false, ended_at = CURRENT_TIMESTAMP
|
|
WHERE id = %s
|
|
""", (session_id,))
|
|
|
|
conn.commit()
|
|
|
|
# Audit log
|
|
log_audit('SESSION_TERMINATE', 'session', session_id,
|
|
additional_info=f"Session beendet für {session_info[1]} auf Lizenz {session_info[0]}")
|
|
|
|
flash('Session erfolgreich beendet!', 'success')
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
logging.error(f"Fehler beim Beenden der Session: {str(e)}")
|
|
flash('Fehler beim Beenden der Session!', 'error')
|
|
finally:
|
|
cur.close()
|
|
conn.close()
|
|
|
|
return redirect(url_for('sessions.sessions'))
|
|
|
|
|
|
@session_bp.route("/sessions/terminate-all/<license_key>", methods=["POST"])
|
|
@login_required
|
|
def terminate_all_sessions(license_key):
|
|
"""Beendet alle aktiven Sessions einer Lizenz"""
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
# Count is_active sessions
|
|
cur.execute("""
|
|
SELECT COUNT(*) FROM sessions
|
|
WHERE license_key = %s AND is_active = true
|
|
""", (license_key,))
|
|
|
|
active_count = cur.fetchone()[0]
|
|
|
|
if active_count == 0:
|
|
flash('Keine aktiven Sessions gefunden!', 'info')
|
|
return redirect(url_for('sessions.sessions'))
|
|
|
|
# Terminate all sessions
|
|
cur.execute("""
|
|
UPDATE sessions
|
|
SET is_active = false, ended_at = CURRENT_TIMESTAMP
|
|
WHERE license_key = %s AND is_active = true
|
|
""", (license_key,))
|
|
|
|
conn.commit()
|
|
|
|
# Audit log
|
|
log_audit('SESSION_TERMINATE_ALL', 'license', None,
|
|
additional_info=f"{active_count} Sessions beendet für Lizenz {license_key}")
|
|
|
|
flash(f'{active_count} Sessions erfolgreich beendet!', 'success')
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
logging.error(f"Fehler beim Beenden der Sessions: {str(e)}")
|
|
flash('Fehler beim Beenden der Sessions!', 'error')
|
|
finally:
|
|
cur.close()
|
|
conn.close()
|
|
|
|
return redirect(url_for('sessions.sessions'))
|
|
|
|
|
|
@session_bp.route("/sessions/cleanup", methods=["POST"])
|
|
@login_required
|
|
def cleanup_sessions():
|
|
"""Bereinigt alte inaktive Sessions"""
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
days = int(request.form.get('days', 30))
|
|
|
|
# Delete old inactive sessions
|
|
cur.execute("""
|
|
DELETE FROM sessions
|
|
WHERE is_active = false
|
|
AND ended_at < CURRENT_TIMESTAMP - INTERVAL '%s days'
|
|
RETURNING id
|
|
""", (days,))
|
|
|
|
deleted_ids = [row[0] for row in cur.fetchall()]
|
|
deleted_count = len(deleted_ids)
|
|
|
|
conn.commit()
|
|
|
|
# Audit log
|
|
if deleted_count > 0:
|
|
log_audit('SESSION_CLEANUP', 'system', None,
|
|
additional_info=f"{deleted_count} Sessions älter als {days} Tage gelöscht")
|
|
|
|
flash(f'{deleted_count} alte Sessions bereinigt!', 'success')
|
|
|
|
except Exception as e:
|
|
conn.rollback()
|
|
logging.error(f"Fehler beim Bereinigen der Sessions: {str(e)}")
|
|
flash('Fehler beim Bereinigen der Sessions!', 'error')
|
|
finally:
|
|
cur.close()
|
|
conn.close()
|
|
|
|
return redirect(url_for('sessions.session_history'))
|
|
|
|
|
|
@session_bp.route("/sessions/statistics")
|
|
@login_required
|
|
def session_statistics():
|
|
"""Zeigt Session-Statistiken"""
|
|
conn = get_connection()
|
|
cur = conn.cursor()
|
|
|
|
try:
|
|
# Aktuelle Statistiken
|
|
cur.execute("""
|
|
SELECT
|
|
COUNT(DISTINCT s.license_key) as active_licenses,
|
|
COUNT(DISTINCT s.username) as unique_users,
|
|
COUNT(DISTINCT s.hardware_id) as unique_devices,
|
|
COUNT(*) as total_active_sessions
|
|
FROM sessions s
|
|
WHERE s.is_active = true
|
|
""")
|
|
|
|
current_stats = cur.fetchone()
|
|
|
|
# Sessions nach Lizenztyp
|
|
cur.execute("""
|
|
SELECT
|
|
l.license_type,
|
|
COUNT(*) as session_count
|
|
FROM sessions s
|
|
JOIN licenses l ON s.license_key = l.license_key
|
|
WHERE s.is_active = true
|
|
GROUP BY l.license_type
|
|
ORDER BY session_count DESC
|
|
""")
|
|
|
|
sessions_by_type = []
|
|
for row in cur.fetchall():
|
|
sessions_by_type.append({
|
|
'license_type': row[0],
|
|
'count': row[1]
|
|
})
|
|
|
|
# Top 10 Lizenzen nach aktiven Sessions
|
|
cur.execute("""
|
|
SELECT
|
|
s.license_key,
|
|
l.customer_name,
|
|
COUNT(*) as session_count,
|
|
l.device_limit
|
|
FROM sessions s
|
|
JOIN licenses l ON s.license_key = l.license_key
|
|
WHERE s.is_active = true
|
|
GROUP BY s.license_key, l.customer_name, l.device_limit
|
|
ORDER BY session_count DESC
|
|
LIMIT 10
|
|
""")
|
|
|
|
top_licenses = []
|
|
for row in cur.fetchall():
|
|
top_licenses.append({
|
|
'license_key': row[0],
|
|
'customer_name': row[1],
|
|
'session_count': row[2],
|
|
'device_limit': row[3]
|
|
})
|
|
|
|
# Session-Verlauf (letzte 7 Tage)
|
|
cur.execute("""
|
|
SELECT
|
|
DATE(started_at) as date,
|
|
COUNT(*) as login_count,
|
|
COUNT(DISTINCT license_key) as unique_licenses,
|
|
COUNT(DISTINCT username) as unique_users
|
|
FROM sessions
|
|
WHERE started_at >= CURRENT_DATE - INTERVAL '7 days'
|
|
GROUP BY DATE(started_at)
|
|
ORDER BY date
|
|
""")
|
|
|
|
session_history = []
|
|
for row in cur.fetchall():
|
|
session_history.append({
|
|
'date': row[0].strftime('%Y-%m-%d'),
|
|
'login_count': row[1],
|
|
'unique_licenses': row[2],
|
|
'unique_users': row[3]
|
|
})
|
|
|
|
# Durchschnittliche Session-Dauer
|
|
cur.execute("""
|
|
SELECT
|
|
AVG(EXTRACT(EPOCH FROM (ended_at - started_at))/3600) as avg_duration_hours
|
|
FROM sessions
|
|
WHERE is_active = false
|
|
AND ended_at IS NOT NULL
|
|
AND ended_at - started_at < INTERVAL '24 hours'
|
|
AND started_at >= CURRENT_DATE - INTERVAL '30 days'
|
|
""")
|
|
|
|
avg_duration = cur.fetchone()[0] or 0
|
|
|
|
return render_template("session_statistics.html",
|
|
current_stats={
|
|
'active_licenses': current_stats[0],
|
|
'unique_users': current_stats[1],
|
|
'unique_devices': current_stats[2],
|
|
'total_sessions': current_stats[3]
|
|
},
|
|
sessions_by_type=sessions_by_type,
|
|
top_licenses=top_licenses,
|
|
session_history=session_history,
|
|
avg_duration=round(avg_duration, 1))
|
|
|
|
except Exception as e:
|
|
logging.error(f"Fehler beim Laden der Session-Statistiken: {str(e)}")
|
|
flash('Fehler beim Laden der Statistiken!', 'error')
|
|
return redirect(url_for('sessions.sessions'))
|
|
finally:
|
|
cur.close()
|
|
conn.close() |