From 20c8541e828acd578232f9ceb65d0edd8efad5bc Mon Sep 17 00:00:00 2001
From: David Sommerseth <dazo@users.sourceforge.net>
Date: Fri, 19 Sep 2008 14:08:49 +0200
Subject: Added (untested) logging of VPN MAC addresses into
 openvpn_macaddr_history

---
 database/sqlite/sql-schema.sql | 9 +++++++++
 1 file changed, 9 insertions(+)

(limited to 'database/sqlite/sql-schema.sql')

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 <dazo@users.sourceforge.net>
Date: Fri, 19 Sep 2008 14:25:08 +0200
Subject: Added (untested) logging of time_duration into
 openvpn_lastlog.session_duration

---
 database/sqlite/sql-schema.sql | 33 +++++++++++++++++----------------
 1 file changed, 17 insertions(+), 16 deletions(-)

(limited to 'database/sqlite/sql-schema.sql')

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 <dazo@users.sourceforge.net>
Date: Fri, 19 Sep 2008 14:30:27 +0200
Subject: Renamed openvpn_lastlog.session_del to session_deleted

---
 database/sqlite/sql-schema.sql | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

(limited to 'database/sqlite/sql-schema.sql')

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 

