Log Database

The Access Server maintains a database of all VPN and web server usage. This database can be queried via the ./logdba tool. Run these commands from /usr/local/openvpn_as/scripts/ with root access.

Show all records in the DB, formatted as CSV (comma-separated-values):

./logdba --csv

Show all records in the DB, formatted as JSON (Javascript object notation):

./logdba --json

Show active records, i.e. where VPN session is still connected:

./logdba --csv --active=1

Show connections/sessions (active or closed) that were initiated within the last 4 hours:

./logdba --csv --start_time_ge="-4h"

Show authentication failures on both the VPN and Web services that occurred during the past 24 hours:

./logdba --csv --start_time_ge="-1d" --auth_bfilt=0

Show only VPN log records (omit web records):

./logdba --csv --service_filt=VPN

Show only web log records (WEB_ADMIN or WEB_CLIENT). Both forms below are equivalent:

./logdba --csv --service_filt="WEB_ADMIN,WEB_CLIENT"
./logdba --csv --service_filt="WEB%"

Show only XML_API log records (omit web and VPN records). These include sacli commands that access the Access Server XML API but omit all XML API
accesses from the web server. Also show the API Method that was called. Note: adding log_db_xml_api_verbose=true to as.conf will cause web server
accesses to the XML API to also be logged.

./logdba --csv --service_filt=XML_API --columns="+api_method"

Show VPN sessions that used more than 2.5MB of bandwidth during the past 24 hours, and format bandwidth stats using KB, MB, etc. notation.

./logdba --csv --start_time_ge="-1d" --bytes_total_ge="2.5mb" --fmt_bw_all

Show all records formatted as JSON, and use unix time instead of formatted time:

./logdba --json --start_time_outfmt=unix

Show the 10 VPN sessions over the last month that used the most bandwidth, with the highest bandwidth session shown at the top of the list.

./logdba --csv --service_filt=VPN --start_time_ge="-1M" --order_by=bytes_total --limit 10 --desc

Show VPN sessions and restrict the columns shown to a subset:

./logdba --csv --service_filt=VPN --columns="username,start_time,duration,bytes_total,error"

Show users "alice" and "bob" usage during 2009:

./logdba --csv --username_filt="alice,bob" --start_time_ge="2009.01.01" --start_time_lt="2010.01.01"

Show the 8 most recent records for user bob, ordered from more recent to less recent.

./logdba --csv --limit=8 --desc --username_filt=bob

Show all records, but format the start time according to the RFC 2822 Internet email standard:

./logdba --csv --start_time_outfmt="%a, %d %b %Y %H:%M:%S +0000" --start_time_outfmt_gmt

Show VPN records for the previous 1 day + 12 hours (including still-active sessions), show session duration in HH:MM format, and sort by session duration by increasing order.

./logdba --csv --service_filt=VPN --start_time_ge="-1d 12h" --order_by=duration --duration_fmt

For additional info on logdba:

./logdba --help