open**N获取用户登录&登出信息

安装和配置VPN不在详细说明问度娘即可。

创建存储用户登陆日志的数据库
sqlite3 /etc/openvpn/openvpn.db

SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>  #进入sqlite数据库后创建表
CREATE TABLE "u_logs" (
"id"  INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
"username"  TEXT NOT NULL,
"timeunix"  TEXT NOT NULL,
"local"  TEXT NOT NULL,
"remote"  TEXT NOT NULL,
"trusted_ip"  TEXT NOT NULL,
"trusted_port"  TEXT NOT NULL,
"logintime"  TEXT,
"logouttime"  TEXT,
"received"  INTEGER,
"sent"  INTEGER
);

编辑vpn服务端配置文件
vim /etc/openvpn/server/server.conf

```powershell
client-connect /etc/openvpn/connect.py
client-disconnect /etc/openvpn/disconnect.py

connect.py 脚本内容
通过环境变量取到所需的字段存入数据库。
```python
#!/usr/bin/python
import os
import time
import sqlite3
username = os.environ['common_name']
trusted_ip = os.environ['trusted_ip']
trusted_port = os.environ['trusted_port']
local = os.environ['ifconfig_local']
remote = os.environ['ifconfig_pool_remote_ip']
timeunix= os.environ['time_unix']
logintime = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
conn = sqlite3.connect("/etc/openvpn/openvpn.db")
cursor = conn.cursor()
query = "insert into u_logs(username, timeunix, trusted_ip,
trusted_port,local,remote,logintime) values('%s','%s','%s','%s','%s','%s','%s')" % \
(username,timeunix,trusted_ip,trusted_port,local,remote,logintime)
cursor.execute(query)
conn.commit()
conn.close()

disconnect.py

#!/usr/bin/python
import os
import time
import sqlite3
username = os.environ['common_name']
trusted_ip = os.environ['trusted_ip']
received = os.environ['bytes_received']
sent = os.environ['bytes_sent']
logouttime = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
conn = sqlite3.connect("/etc/openvpn/openvpn.db")
cursor = conn.cursor()
query = "update u_logs set logouttime='%s', received='%s', sent= '%s'
where username = '%s' and trusted_ip = '%s'" % (logouttime,
received, sent, username, trusted_ip)
cursor.execute(query)
conn.commit()
conn.close()

/etc/init.d/openvpn reload #重载配置文件
进入数据库，通过sql 可以查看用户登陆信息

sqlite> select username, remote,trusted_ip,received,sent,logintime,logouttime from u_logs;