From 20c8541e828acd578232f9ceb65d0edd8efad5bc Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Fri, 19 Sep 2008 14:08:49 +0200 Subject: Added (untested) logging of VPN MAC addresses into openvpn_macaddr_history --- database/sqlite/eurephiadb-sqlite.c | 10 ++++++++++ database/sqlite/sql-schema.sql | 9 +++++++++ 2 files changed, 19 insertions(+) (limited to 'database/sqlite') diff --git a/database/sqlite/eurephiadb-sqlite.c b/database/sqlite/eurephiadb-sqlite.c index 512bd57..f566320 100644 --- a/database/sqlite/eurephiadb-sqlite.c +++ b/database/sqlite/eurephiadb-sqlite.c @@ -550,6 +550,15 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha return 0; } + // Register MAC address into history table + res = sqlite_query(ctx, "INSERT INTO openvpn_macaddr_history (sessionkey, macaddr) VALUES ('%q','%q')", + session->sessionkey, macaddr); + if( res == NULL ) { + eurephia_log(ctx, LOG_CRITICAL, 0, "Failed to log new MAC address for session"); + return 0; + } + + // Update lastlog to reflect last used MAC address for the session res = sqlite_query(ctx, "UPDATE openvpn_lastlog SET sessionstatus = 2, macaddr = '%q' " " WHERE sessionkey = '%q' AND sessionstatus = 1", macaddr, session->sessionkey); @@ -569,6 +578,7 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha return 1; } + // Register the user as logged out int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, const char *bytes_sent, const char *bytes_received) diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql index a8f9168..5cdb77b 100644 --- a/database/sqlite/sql-schema.sql +++ b/database/sqlite/sql-schema.sql @@ -55,6 +55,15 @@ CREATE TABLE openvpn_lastlog ( ); CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey); +CREATE TABLE openvpn_macaddr_history ( + sessionkey varchar(64) NOT NULL, + macaddr varchar(20) NOT NULL, + registered timestamp DEFAULT CURRENT_TIMESTAMP, + semaid integer PRIMARY KEY AUTOINCREMENT +); +CREATE INDEX openvpn_macaddr_hist_sessionkey ON openvpn_macaddr_history(sessionkey); +CREATE INDEX openvpn_macaddr_hist_macaddr ON openvpn_macaddr_history(macaddr); + CREATE TABLE openvpn_sessions ( sessionkey varchar(128) NOT NULL, datakey varchar(256) NOT NULL, -- cgit From d28d39cabd55b3de3258dabd3fbefe1976dc1bc1 Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Fri, 19 Sep 2008 14:25:08 +0200 Subject: Added (untested) logging of time_duration into openvpn_lastlog.session_duration --- database/sqlite/eurephiadb-sqlite.c | 7 ++++--- database/sqlite/sql-schema.sql | 33 +++++++++++++++++---------------- 2 files changed, 21 insertions(+), 19 deletions(-) (limited to 'database/sqlite') diff --git a/database/sqlite/eurephiadb-sqlite.c b/database/sqlite/eurephiadb-sqlite.c index f566320..67b22f4 100644 --- a/database/sqlite/eurephiadb-sqlite.c +++ b/database/sqlite/eurephiadb-sqlite.c @@ -581,7 +581,7 @@ int eDBregister_vpnmacaddr(eurephiaCTX *ctx, eurephiaSESSION *session, const cha // Register the user as logged out int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, - const char *bytes_sent, const char *bytes_received) + const char *bytes_sent, const char *bytes_received, const char *duration) { dbresult *res = NULL; @@ -592,9 +592,10 @@ int eDBregister_logout(eurephiaCTX *ctx, eurephiaSESSION *skey, res = sqlite_query(ctx, "UPDATE openvpn_lastlog " " SET sessionstatus = 3, logout = CURRENT_TIMESTAMP, " - " bytes_sent = '%i', bytes_received = '%i' " + " bytes_sent = '%i', bytes_received = '%i', session_duration = '%i' " " WHERE sessionkey = '%q' AND sessionstatus = 2", - atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received), skey->sessionkey); + atoi_nullsafe(bytes_sent), atoi_nullsafe(bytes_received), + atoi_nullsafe(duration), skey->sessionkey); if( res == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 0, "Could not update lastlog with logout information (%s)", skey->sessionkey); diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql index 5cdb77b..490669d 100644 --- a/database/sqlite/sql-schema.sql +++ b/database/sqlite/sql-schema.sql @@ -36,22 +36,23 @@ CREATE TABLE openvpn_accesses ( ); CREATE TABLE openvpn_lastlog ( - uid integer , - certid integer , - protocol varchar(4) NOT NULL, - remotehost varchar(128) NOT NULL, - remoteport integer NOT NULL, - macaddr varchar(20) , - vpnipaddr varchar(32) NOT NULL, - vpnipmask varchar(32) NOT NULL, - sessionstatus integer NOT NULL DEFAULT 0, - sessionkey varchar(128) , - login timestamp , - logout timestamp , - session_del timestamp , - bytes_sent integer , - bytes_received integer , - llid integer PRIMARY KEY AUTOINCREMENT + uid integer , + certid integer , + protocol varchar(4) NOT NULL, + remotehost varchar(128) NOT NULL, + remoteport integer NOT NULL, + macaddr varchar(20) , + vpnipaddr varchar(32) NOT NULL, + vpnipmask varchar(32) NOT NULL, + sessionstatus integer NOT NULL DEFAULT 0, + sessionkey varchar(128) , + login timestamp , + logout timestamp , + session_del timestamp , + session_duration timestamp, + bytes_sent integer , + bytes_received integer , + llid integer PRIMARY KEY AUTOINCREMENT ); CREATE UNIQUE INDEX openvpn_lastlog_sessionkey ON openvpn_lastlog(sessionkey); -- cgit From 9e938792700fe96bf714efca5c234c0aeab44a91 Mon Sep 17 00:00:00 2001 From: David Sommerseth Date: Fri, 19 Sep 2008 14:30:27 +0200 Subject: Renamed openvpn_lastlog.session_del to session_deleted --- database/sqlite/eurephiadb-sqlite.c | 2 +- database/sqlite/sql-schema.sql | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'database/sqlite') diff --git a/database/sqlite/eurephiadb-sqlite.c b/database/sqlite/eurephiadb-sqlite.c index 67b22f4..1abcfeb 100644 --- a/database/sqlite/eurephiadb-sqlite.c +++ b/database/sqlite/eurephiadb-sqlite.c @@ -828,7 +828,7 @@ int eDBdestroy_session(eurephiaCTX *ctx, eurephiaSESSION *session) { // Update session status res = sqlite_query(ctx, "UPDATE openvpn_lastlog " - " SET sessionstatus = 4, session_del = CURRENT_TIMESTAMP " + " SET sessionstatus = 4, session_deleted = CURRENT_TIMESTAMP " " WHERE sessionkey = '%q' AND sessionstatus = 3", session->sessionkey); if( res == NULL ) { eurephia_log(ctx, LOG_CRITICAL, 0, diff --git a/database/sqlite/sql-schema.sql b/database/sqlite/sql-schema.sql index 490669d..b677554 100644 --- a/database/sqlite/sql-schema.sql +++ b/database/sqlite/sql-schema.sql @@ -48,7 +48,7 @@ CREATE TABLE openvpn_lastlog ( sessionkey varchar(128) , login timestamp , logout timestamp , - session_del timestamp , + session_deleted timestamp , session_duration timestamp, bytes_sent integer , bytes_received integer , -- cgit